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

Για να μπορεί να λειτουργήσει το 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

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

Ακολουθούμε γενικά τις προτάσεις του αρχείου /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

 mysqlcheck -Aao --auto-repair -u root -p

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

Με την παρακάτω ρύθμιση στο αρχείο /etc/phpmyadmin/apache.conf γίνεται πλέον caching από τους browser και η λήψη μειώθηκε από από 1Mbyte σε 200Kb

<Directory /usr/share/phpmyadmin>
..
AddOutputFilter DEFLATE css js
ExpiresActive On
ExpiresByType application/javascript A2592000
</Directory>

Για σύνδεση στην 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

Ακολουθούμε τα βήματα από : 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

Εάν η εντολή

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
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

θα πρέπει να τοποθετήσουμε το σωστό κωδικό για τον debian-sys-maint στο αρχείο /etc/mysql/debian.cnf. Τον κωδικό θα τον βρούμε κρυπτογραφημένο στο αντίστοιχο αρχείο του υπολογιστή από τον οποίο πήραμε backup της βάσης mysql

Εάν κάποιος από τους 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/

Αν αφορά απλή περίπτωση διπλοεγγραφής τότε από το 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

Εάν οι προβληματικές 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

Ως τελευταία λύση (που δεν ενδείκνυται γιατί πιθανόν ΘΑ ΧΑΘΟΥΝ σημαντικές εγγραφές) για να παρακάμψουμε το 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)

Συνεχίζουμε στο 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)

(ή 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 εμφανίσει το παρακάτω μύνημα τότε στον 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

Οι εντολές που εκτελούνται είναι περίπου οι εξής

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

Στον 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;

(βλ. 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

Σε συγκεκριμένο χρήστη κάθε λίγες μέρες ή και ώρες χανόταν η σύνδεση από την βάση. Αλλάζοντας ξανά τον κωδικό από το διαχειριστικό 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.

Η λύση βρέθηκε εδώ

Εκτελέστηκαν

mysqlcheck mysql -p
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
..
mysql.user                                         OK
mysql.user_info                                    OK

και

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 χωρίς να βρεθεί αντίστοιχο πρόβλημα.

Βλ. http://alidba.blogspot.com/2017/05/table-mysqlgtidslavepos-doesnt-exist.html

Βλ. https://globedrill.com/table-mysql-innodb_index_stats-doesnt-exist-engine/

Βλ. 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