====== Mysql - Βάση δεδομένων ======


===== Εγκατάσταση / Ρυθμίσεις =====

Για να εγκαταστήσουμε την mysql εκτελούμε
  apt install  mariadb-server mariadb-client

Αρχικά θα πρέπει να αλλάξουμε τον κωδικό του διαχειριστή της mysql. Αρχικά εκτελούμε

  mysql -u root
Στην προτροπή "mysql>" πληκτρολογούμε: 

**Για εκδόσεις έως 5.7.5**

  mysql> set password = password("yournewpassword");

**Για εκδόσεις 5.7.6 και μετά** (βλ. https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/) 

  mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
  
Στην συνέχεια θα συνδεόμαστε με την παράμετρο -p ώστε να μας ζητείται κωδικός
  mysql -u root -p




==== Ελληνικά ====
Για να ορίσουμε σωστά τα ελληνικά στην κονσόλα της mysql αλλά και να ορίσουμε utf-8 κωδικοποίηση στις νέες βάσεις που δημιουργούμε μπορούμε να εισάγουμε ένα αρχείο με όνομα greek.cnf στον φάκελο /etc/mysql/conf.d/ με τα παρακάτω περιεχόμενα
  [client]
  default-character-set=utf8

  [mysql]
  default-character-set=utf8


==== unix socket authentication plugin ====
Για να μπορεί να λειτουργήσει το mariadb-upgrade που εκτελείται σε κάθε εκκίνηση της mysql μέσα στο /etc/mysql/debian-start χρειάζεται να μπορεί να συνδεθεί στην mysql. Για να μην χρειάζεται να αποθηκευτεί ο κωδικός plain text στο /etc/mysql/my.cnf μπορούμε να ρυθμίσουμε το unix authentication plugin εκτελώντας
  mysql_secure_installation -S /run/mysqld/mysqld.sock
Έτσι σε περίπτωση αναβάθμισης με ελέγχονται όλοι οι πίνακες και θα αναβαθμίζεται σωστά η εσωτερική δομή της mysql στην 1η εκκίνηση και δεν θα εμφανίζονται τα παρακάτω μηνύματα
  Jun 14 10:09:47 s2 /etc/mysql/debian-start[4107]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
  Jun 14 10:09:47 s2 /etc/mysql/debian-start[4107]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  Jun 14 10:09:47 s2 /etc/mysql/debian-start[4107]: FATAL ERROR: Upgrade failed
 
Στην συνέχεια ως root δεν θα χρειάζεται να παρέχουμε κωδικό στην σύνδεση τοπικά στην mysql. 


===== Επιδόσεις =====
Κατεβάζουμε το MySQLTuner
  wget http://mysqltuner.com/mysqltuner.pl
Το κάνουμε εκτελέσιμο
  chmod +x mysqltuner.pl
και το εκτελούμε (προσοχή καλό είναι να το κάνουμε μετά από 2-3 μέρες συνεχούς λειτουργίας της mysql)
  ./mysqltuner.pl

Από τις προτάσεις του 




==== my.cnf  ====
Ακολουθούμε γενικά τις προτάσεις του  αρχείου /usr/share/doc/mysql-server-5.0/examples/my-huge.cnf.gz που αφορά σύστημα με 1-2GB μνήμη 
  key_buffer = 384M
  max_allowed_packet = 1M
  table_cache = 512
  sort_buffer_size = 2M
  read_buffer_size = 2M
  read_rnd_buffer_size = 8M
  myisam_sort_buffer_size = 64M
  thread_cache_size = 8
  query_cache_size = 32M
και στο τέλος του αρχείου
  [isamchk]
  key_buffer = 256M
  sort_buffer_size = 256M
  read_buffer = 2M
  write_buffer = 2M
  #  
  [myisamchk]
  key_buffer = 256M
  sort_buffer_size = 256M
  read_buffer = 2M
  write_buffer = 2M

==== defrag ====
Με την παρακάτω εντολή κάνουμε defrag
   mysqlcheck -Aao --auto-repair -u root -p

===== phpmyadmin =====
Χρησιμοποιώντας το Web Developer/Performance utility του firefox παρατηρήθηκε ότι στο login screen και στην περιήγηση στο phpmyadmin υπήρχε μεγάλη καθυστέρηση περίπου **15s** από το **get_scripts.js.php** σε έναν server με 1Mbit upload. 

Με την παρακάτω ρύθμιση στο αρχείο **/etc/phpmyadmin/apache.conf**  γίνεται πλέον caching από τους browser και η λήψη μειώθηκε από από 1Mbyte σε 200Kb
<code apache>
<Directory /usr/share/phpmyadmin>
..
AddOutputFilter DEFLATE css js
ExpiresActive On
ExpiresByType application/javascript A2592000
</Directory>

</code>

===== mycli =====

Για σύνδεση στην mysql από κονσόλα μπορούμε να επιλέξουμε το **mycli** το οποίο παρέχει **auto-completion**. 

Για εγκατάσταση σε Debian 9
  apt-get install mycli
Σε προηγούμενες εκδόσεις Debian
  pip install mycli


===== Παρακολούθηση Λειτουργίας =====
Ο πιο απλός τρόπος είναι μια εντολή στο crontab του root. Εάν παρακολουθούμε τα e-mail του root θα ενημερωνόμαστε για τυχόν προβλήματα

Εκτελούμε ως root
  crontab -e  
και εισάγουμε την παρακάτω γραμμή
  0 8  * * * cat /var/log/syslog.0 | grep mysqld
Στις 8:00am κάθε μέρα θα ελέγχεται το αρχείο syslog της προηγούμενης μέρας.

Εάν δεν υπάρχει έξοδος από την εντολή δεν θα λαμβάνουμε κανένα μήνυμα

Η ώρα μπορεί να επιλεχθεί βλέπωντας κάθε πότε γίνεται rotate στο σρχείο syslog
  ls -al /var/log/syslog*
  -rw-r----- 1 root adm  245701 2008-12-28 16:20 /var/log/syslog
  -rw-r----- 1 root adm 3088106 2008-12-28 06:27 /var/log/syslog.0
  -rw-r----- 1 root adm   34882 2008-12-27 06:25 /var/log/syslog.1.gz
  -rw-r----- 1 root adm   23928 2008-12-26 06:25 /var/log/syslog.2.gz
  -rw-r----- 1 root adm   13909 2008-12-25 06:25 /var/log/syslog.3.gz
  ..
Αυτό μας δείχνει ότι μπορούμε να επιλέξουμε μια ώρα μετά τις 6:25am

===== Εντολές =====
Για να εμφανίσουμε εγγραφές με τα πεδία αριστερά και τις τιμές δεξιά εκτελούμε με την παράμετρο \G π.χ 
  mysql> select * from mysql.user\G




===== Master - Master Replication Server =====

Ακολουθούμε τα βήματα από : http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

Για την περίπτωση που σταματήσει το replication και αργήσουμε να το αντιληφθούμε μπορούμε να αυξήσουμε τις μέρες που κρατιώνται τα logs από 10 σε 30.

Τροποποιούμε το αρχείο /etc/mysql/my.cnf 
  expire_logs_days        = 30

Καλό είναι επίσης να ορίσουμε την αυτόματη παράκαμψη κάποιων προβληματικών sql επερωτήσεων που μπορούν να προκύψουν κατά το replication (βλ http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/). 
  slave-skip-errors = 1062,1146
  #1062 : duplicate inserts (common in joomla) #1146 : table does not exist

Άλλη μία καλή ρύθμιση είναι να γίνεται συγχρονισμός στο replication binary log. Ορίζουμε στο my.cnf 
  [mysqld]
  sync_binlog = 1

=====Αντιμετώπιση Προβλημάτων=====
==== Access denied for user 'root'@'s1.myserver.com' ====
Εάν η εντολή 
  mysqladmin -h s1.myserver.com -p -u root password <password>
μας βγάλει μήνυμα λάθους 
  error: 'Access denied for user 'root'@'s1.myserver.com' (using password: YES)
Θα πρέπει να ορίσουμε τον χρήστη 'root'@'s1.myserver.com' με πλήρη δικαιώματα
mysql -u root -p
  mysql> GRANT ALL PRIVILEGES ON *.* to 'root'@'s1.myserver.com' identified by 'password'; 
  mysql> quit

Εάν κατά την εκκίνηση της mysql η εντολή
  tail -f /var/log/syslog
μας βγάλει το ακόλουθο μήνυμα λάθους
  [ERROR] Could not find target log during relay log initialization
αρκεί να σβήσουμε ή να τροποποιήσουμε το αρχείο relay-log.info. Τέτοιου είδους πρόβλημα μπορεί να συμβεί και σε αλλαγή του ονόματος του μηχανήματος (hostname)

====/usr/bin/mysqladmin: connect to server at 'localhost' failed ====
Εάν μας βγάλει το ακόλουθο μήνυμα 
  /usr/bin/mysqladmin: connect to server at 'localhost' failed
  error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
θα πρέπει να τοποθετήσουμε το σωστό κωδικό για τον debian-sys-maint στο αρχείο /etc/mysql/debian.cnf. Τον κωδικό θα τον βρούμε κρυπτογραφημένο στο αντίστοιχο αρχείο του υπολογιστή από τον οποίο πήραμε backup της βάσης mysql









====Replication : Error running query, slave SQL thread aborted====

Εάν κάποιος από τους 2 servers σταματήσει την λειτουργία προσωρινά στην επαναφορά ελέγχουμε πάντα για τυχόν λάθη με
  tail -f /var/log/syslog
Εάν μας εμφανιστεί το παρακάτω λάθος
  Error_code: 145
  Dec 27 16:50:25 s1 mysqld[4420]: 071227 16:50:25 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000544' position 18245

Για να δούμε την κατάσταση εκτελούμε
  mysql>SHOW SLAVE STATUS \G

Ακολουθούμε με την σειρά τις παρακάτω προτεινόμενες μεθόδους. 

Μετά από κάθε προτεινόμενη διαδικασία επίλυσης, ελέγχουμε εάν το πρόβλημα λύθηκε
  mysql> START SLAVE;
  mysql> SHOW SLAVE STATUS \G
Ελέγχουμε τις Slave_IO_Running και Slave_SQL_Running να είναι Yes καθώς και το Last_Erro να είναι κενό

Εναλλακτικά επανεκκινούμε την mysql
  /etc/init.d/mysql restart
Και τσεκάρουμε από το syslog ότι όλα συνεχίστηκαν κανονικά
  tail -f -n 100 /var/log/syslog

Τα mysql-bin logs αποθηκεύονται στον φάκελο /var/log/mysql/








=== 1η λύση === 

Αν αφορά απλή περίπτωση διπλοεγγραφής τότε από το phpmyadmin την διαγράφουμε. Επανεκκινώντας και πάλι τους 2 servers από τα replication logs του ενός θα ξαναεκτελεστεί

Αλλιώς μπορούμε να προσπεράσουμε την προβληματική εντολή ακολουθώντας τις οδηγίες εδω : http://www.brandonchecketts.com/archives/2008/09
  mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;
  Query OK, 0 rows affected (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)
Για να δούμε αν όλα είναι ok
  mysql> SHOW SLAVE STATUS \G
Αν δούμε κάποιο μήνυμα λάθους επαναλαμβάνουμε την διαδικασία
Καλό είναι να κάνουμε και μια επανεκκίνηση για να σιγουρευτούμε ότι όλα είναι ok
  /etc/init.d/mysql restart
Τσεκάρωντας πάλι από το syslog ή με 
  mysql> SHOW SLAVE STATUS \G


=== 2η λύση === 
Εάν οι προβληματικές sql επερωτήσεις αφορούν μία βάση μόνο και είναι δεκάδες τότε μπορούμε προσωρινά να ρυθμίσουμε το replication να αγνοεί αυτή τη βάση ώστε να προχωρήσει. 

Τροποποιούμε το αρχείο my.cnf ώστε να αγγνοεί την βάση testdb
  replicate-ignore-db = testdb
Στην συνέχεια επανεκκινούμε την mysql
  /etc/init.d/mysql restart
Ελέγχουμε πάλι αν όλα είναι ok 
  mysql> SHOW SLAVE STATUS \G
Στον server που υπάρχει η βάση testdb χωρίς πρόβλημα θα πρέπει να κρατήσουμε backup και ύστερα να την διαγράψουμε.
  mysqldump -e --force --quick --quote-names -h localhost -u testdbuser -pPassword testdb > /backup/various/testdb-$(date +%Y-%m-%d).sql
  mysql -uroot -e"DROP DATABASE testdb;"

Στην συνέχεια τροποιούμε πάλι το αρχείο my.cnf βάζωντας σε σχόλια 
  #replicate-ignore-db = testdb
Eπανεκκινούμε την mysql
  /etc/init.d/mysql restart

Δημιουργούμε ξανά την βάση testdb στον άλλο server επαναφέροντας τα δεδομένα της από το backup
   mysql -uroot -e"CREATE DATABASE testdb;"
   mysql --force --quick -h localhost -u testdbuser -pPassword testdb < /backup/various/testdb-$(date +%Y-%m-%d).sql
Ελέγχουμε πάλι αν όλα είναι ok στον server που είχε προηγουμένως το πρόβλημα
   mysql> SHOW SLAVE STATUS \G




=== 3η λύση ===

Ως τελευταία λύση (που δεν ενδείκνυται γιατί πιθανόν ΘΑ ΧΑΘΟΥΝ σημαντικές εγγραφές) για να παρακάμψουμε το replication από παλιά logs σε περιπτώσεις πολλών συνεχώμενων διπλοεγγραφών  από εκεί που ξεκινάει τα logs μετά την επανεκκίνηση ο 2ος server (εκτελούμε στον 2ο server)
  mysql> show master status;
Καταγράφουμε το master log file και log position π.χ. mysql-bin.000544 και position 77465
(εκτελούμε στον 1ο server)
  mysql> stop slave;
  Query OK, 0 rows affected (0.00 sec)
FIXME Συνεχίζουμε στο mysql-bin.000545 (Xάνοντας πολλές εντολές, δυστυχώς)
  mysql> CHANGE MASTER TO MASTER_HOST='server.domainname.gr', MASTER_USER='slave2_user', MASTER_PASSWORD='mypasswprd', MASTER_LOG_FILE='mysql-bin.000545', MASTER_LOG_POS=4;
  Query OK, 0 rows affected (0.01 sec)
  mysql> start slave;
  Query OK, 0 rows affected (0.00 sec)



==== Table is marked as crashed and should be repaired ====

**(ή Incorrect key file for table)**

Ακολουθούμε τις οδηγίες στο link http://www.spiration.co.uk/post/1221

  mysql> use databasename;
  mysql> check table table_name;
αν επιμένει το λάθος εκτελούμε
  /usr/bin/myisamchk -r /var/lib/mysql/database_name/table_name.MYI

==== Client requested master to start replication from impossible position ή I/O Error====

Εάν ο client εμφανίσει το παρακάτω μύνημα τότε στον client εκτελούμε 
  mysql>show slave status \G;
  ..
  ..
  Relay_Master_Log_File: mysql-bin.002669
  ..
  Exec_Master_Log_Pos: 172858       

Με τις παραπάνω πληροφορίες πηγαίνουμε στον master στον φάκελο /var/log/mysql και ανοίγουμε το αρχείο π.χ. mysql-bin.002669 με vi

Στο vi εκτελούμε την παρακάτω εντολή ώστε να πάμε στο byte 172858
  : goto 172858
Βρισκόμαστε κατά πάσα πιθανότητα στο μέσο μιας εντολής mysql. Με διαδοχικές δοκιμές goto βρίσκουμε το 1ο byte στο οποίο ξεκινάει η επόμενη mysql εντολή π.χ. 172964

Επιβεβαιώνουμε με την εντολή **mysqlbinlog**. Θα πρέπει να εμφανίσει την επόμενη έγκυρη mysql εντολή
  mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=172964 /var/log/mysql/mysql-bin.002669

Για να βρούμε την επόμενη έγκυρη θέση εκτελούμε  
  /usr/lib/wad/mysql2 checkValidLogPos -p 172964 -l "mysql-bin.002669" |less

Οι εντολές που εκτελούνται είναι περίπου οι εξής
<code bash>
pos="$1"
log="$2"

while [ "1" ]; do
    mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=$pos /var/log/mysql/$log &> /dev/null
    retval=$?
    output=$(mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=$pos /var/log/mysql/$log 2>&1 | grep -w at)
    if [ "$retval" = "0" ] && [ "$output" ]; then
        echo $output 
        break
    else
        (( pos++ ))
    fi
done
</code>

Στον client επανεκκινούμε το replication από την νέα θέση
Εάν είχαμε σταματήσει στην **θέση 4** (στην αρχή δηλαδή και η mysqlbinlog δείχνει αποτέλεσμα από την αρχή του log δοκιμάζουμε πάντα πρώτα θέση **MASTER_LOG_POS=1**, αλλιώς την θέση που πιστεύουμε ότι είναι έγκυρη

  mysql> stop slave; CHANGE MASTER TO MASTER_HOST='masterdomain.com', MASTER_USER='master_user', MASTER_PASSWORD='xxxxxx', MASTER_LOG_FILE='mysql-bin.002669', MASTER_LOG_POS=172964;  start slave;
Βλέπουμε εαν πέτυχε εκτελώντας
  mysql>show slave status \G;
Αν δεν τα καταφέρουμε θα πρέπει να προχωρήσουμε στο επόμενο log (χάνοντας δυστυχώς δεδομένα στο replication)
  mysql> stop slave; CHANGE MASTER TO MASTER_HOST='masterdomain.com', MASTER_USER='master_user', MASTER_PASSWORD='xxxxxx', MASTER_LOG_FILE='mysql-bin.002670', MASTER_LOG_POS=4;  start slave;

==== Relay log read failure: ====
(βλ. https://alexzeng.wordpress.com/2013/10/17/how-to-fix-mysql-slave-after-relay-log-corrupted/)

Σε περίπτωση που έχει γίνει corruption στο master log ή στο relay log εκτελούμε
  mysql> show slave status \G
  ..
  Master_Log_File: mysql-bin.003436
  Read_Master_Log_Pos: 39029
  Relay_Log_File: slave-relay.000718
  Relay_Log_Pos: 1649436
  Relay_Master_Log_File: mysql-bin.003434
  ..
  Exec_Master_Log_Pos: 1660435
  ..      


Προσοχή χρειαζόμαστε το **Relay_Master_Log_File: mysql-bin.003434** όχι το //Master_Log_File// και το **Exec_Master_Log_Pos: 1660435** όχι το //Read_Master_Log_Pos//

Στον master 
  mysqlbinlog /var/log/mysql/mysql-bin.003434
Στον slave
  mysqlbinlog /var/lib/mysql/slave-relay.000718
Αν το πρόβλημα είναι στον slave
  mysql> stop slave;
  mysql> reset slave all;
  mysql> CHANGE MASTER TO MASTER_HOST='hostname', MASTER_USER='slave1_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.003434', MASTER_LOG_POS=1660435;
  mysql> start slave;

Δοκιμάζουμε πάλι με 
  mysql> show slave status \G
κάθως και ελέγχουμε για λάθη στην επανεκκίνηση της mysql
  service mysql restart
  tail -f /var/log/syslog

==== both a password and an authentication plugin problem ====

Σε συγκεκριμένο χρήστη κάθε λίγες μέρες ή και ώρες χανόταν η σύνδεση από την βάση. Αλλάζοντας ξανά τον κωδικό από το διαχειριστικό virtualmin επανερχόταν η σύνδεση και μετά από λίγες μέρες ξανά τα ίδια. Δοκιμάστηκαν να απενεργοποιηθούν sites μήπως είχε γίνει κάποιο hack σε mysql από security hole αλλά και πάλι εμφανιζόταν το πρόβλημα.
Στο /var/log/syslog εμφανίστηκε
  mysqld[18424]: [Warning] 'user' entry '(username)@localhost' has both a password and an authentication plugin specified. The password will be ignored.

Η λύση βρέθηκε [[https://forums.cpanel.net/threads/mysql-errors-password-and-plugin-authentication-specified.641169/|εδώ]]

Εκτελέστηκαν
<code>mysqlcheck mysql -p
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
..
mysql.user                                         OK
mysql.user_info                                    OK</code>
και
  mysql -p -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c
      59 
      6 mysql_native_password
Το πρόβλημα ήταν το mysql_native_password και διορθώθηκε με τις εντολές μέσα στην mysql
  UPDATE mysql.user SET plugin = '' WHERE plugin = 'mysql_native_password';
  FLUSH PRIVILEGES;

Προληπτικά εκτελέστηκαν οι ίδιοι ελέγχοι και στον 2o mysql server χωρίς να βρεθεί αντίστοιχο πρόβλημα.

==== Table mysql.gtid_slave_pos doesn't exist ====
Βλ. http://alidba.blogspot.com/2017/05/table-mysqlgtidslavepos-doesnt-exist.html

==== mysql.innodb_table_stats doesn’t exist in engine. mysql.innodb_table_stats not found ====
Βλ. https://globedrill.com/table-mysql-innodb_index_stats-doesnt-exist-engine/


==== mysql.innodb_index_stats doesn’t exist in engine. mysql.innodb_index_stats not found ====
Βλ. https://globedrill.com/table-mysql-innodb_index_stats-doesnt-exist-engine/
===== Πηγές =====
  * Master-Master Replication 
    * Ενεργοποίηση: http://www.howtoforge.com/mysql5_master_master_replication_debian_etch
    * Κανόνες replication : http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
    * Multi-Master MySQL Replication Guide : http://forums.mysql.com/read.php?26,169850,169850
  * secure replication 
    * mysql docs : http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html 
    * quick howto on sarge : http://www.terryburton.co.uk/blog/2005/06/mysql-secure-replication-over-ssl-on.html     
    * ssh tunnels : http://www.jaisenmathai.com/blog/2008/10/10/secure-mysql-replication-between-colos-over-an-ssh-tunnel/    
  * Monitoring replication
    * http://www.brandonchecketts.com/archives/checking-mysql-replication
  * Επιδόσεις
    * Περιγραφή ρυθμίσεων : http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/    
    * perl script : Έλεγχος και προτάσεις : http://www.debianadmin.com/check-your-mysql-server-performance-with-mysqltuner.html
    * defrag : http://www.mydigitallife.info/2007/09/21/check-and-optimize-mysql-database-automatically-with-crontabcron/
  * How To Back Up MySQL Databases Without Interrupting MySQL :
    * http://www.howtoforge.com/back_up_mysql_dbs_without_interruptions
  * Αντιμετώπιση προβλημάτων
    * http://www.darkcoding.net/software/restarting-mysql-master-master-replication/
    * Skipping the query : http://www.brandonchecketts.com/archives/2008/09
    * http://www.spiration.co.uk/post/1221
    * http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html
    * https://alexzeng.wordpress.com/2013/10/17/how-to-fix-mysql-slave-after-relay-log-corrupted/
    * mysql_native : https://forums.cpanel.net/threads/mysql-errors-password-and-plugin-authentication-specified.641169/
  * Versions : http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs.html
  * FAQs
    * http://www.modwest.com/help/kb6-242.html