====== MySQL ====== ===== Optimisation ===== * http://www.openlogic.com/wazi/bid/195905/Tips-and-Tricks-to-Optimize-MySQL - astuces * https://github.com/major/MySQLTuner-perl - script wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.pl Y'a plus qu'a corriger les valeurs des variables qu'il suggère et faire un restart de mysql ;) * http://www.debianhelp.co.uk/mysqlperformance.htm - exemple * http://hackmysql.com/mysqlreport - script * https://wiki.deimos.fr/MysqlTuner_:_Optimiser_votre_serveur_MySQL * http://www.bocciolesi.fr/tutoriels-et-cours/optimisation-du-serveur-mysql/ A voir le paquet **php5-mysqlnd** qui est un client mysql natif qui est optimisé par mysql pour mysql ===== Quelques commandes ===== Pour voir toutes les variables mysqladmin variables -p mysql> show variables; mysql> show global variables like 'log_output'; Pour modifier une variable global mysql> set global max_connections=500; Pour voir les status mysql> show status; Pour vérifier vos fichiers de config avant de redémarrer le service mysql mysqld --help ou mysqld --verbose --help | head Process list mysql>show processlist; Kill process mysql>kill "number from first col"; ==== Sauvegarder ==== Sauvegarder une base mysqldump -u user -p nom_de_la_base > backup.sql Sauvegarder toutes les tables avec un prefix mysqldump -h hostname -u user -p $(mysql nom_de_la_base -h hostname -u user -p -Bse 'show tables like"phpbb\_%"') > dump_phpbb_.sql La bonne pratique en prod est d'utiliser les options suivantes: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.sql.gz * --quick : This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out * --single-transaction : This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. * --lock-tables=false : option stops MyISAM tables (if they exsit) being locked during the backup Sauvegarder une table avec des conditions WHERE # Dump only the rows with the id column bigger than 500 mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql # Dump only the rows with the created_at column in the given interval mysqldump my_db_name my_table_name --where="date between '2020-09-01' and '2020-09-30'" > my_backup.sql ==== Restaurer ==== mysql -u user -p nom_de_la_base < backup.sql Script PERL pour extraire une table d'un dump SQL #!/usr/bin/perl -s -wnl #extract a single table from a mysql dump BEGIN { $table or warn "Usage: $0 -table=TABLE_TO_EXTRACT mysqldumpfile.sql" and exit 1; } /^DROP TABLE IF EXISTS `$table`/ .. /^UNLOCK TABLES;$/ and print; Pour automatiser la connexion a mysql et ne pas avoir à taper de mot de passe, créer un fichier **~/.my.cnf** [client] user=root password=somepassword ===== Logguer les requêtes en temps réel ponctuellement ===== Se connecter à mysql mysql -h localhost -u root -p Vérifier la config des logs mysql> show global variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'general_log_file'; +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log_file | /var/run/mysqld/mysqld.log | +------------------+----------------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.00 sec) Activer les logs mysql> set global general_log='ON'; Query OK, 0 rows affected (0.04 sec) Désactiver les logs mysql> set global general_log='OFF'; Query OK, 0 rows affected (0.02 sec) Puis consulter votre fichier de log (ex:/var/run/mysqld/mysqld.log) ===== Faire un backup via un script php ===== $db.sql"); echo "C'est fini. Vous pouvez récupérer la base par FTP"; ?> Plein d'exemples de codes de backup et restauration sur => http://vitobotta.com/smarter-faster-backups-restores-mysql-databases-with-mysqldump/ Un autre script dans les paquets **automysqlbackup** https://www1.zonewebmaster.eu/serveur-debian-mysql:automysqlbackup ===== Percona XtraBackup ===== A tester => https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-ubuntu-14-04 Permet des backups à chaud sans perte en copiant directement les fichiers de mysql et en appliquant les transactions en cours. ===== Les slow query ===== Afin d'identifier les requêtes qui prennent du temps, activer les slow_query log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 # pour logguer les requêtes qui prennet plus d'une seconde Ensuite, utiliser l'utilitaire **mysqldumpslow** pour analyser le log. # mysqldumpslow /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 8 Time=11.45s (91s) Lock=0.00s (0s) Rows=2.1 (17), INF[INF]@localhost select `process_id` from `dashboard_log` left join `dashboard_process` on `process_id` = `dashboard_process`.`id` where `dashboard_process`.`id` is null group by `process_id` order by `process_id` asc Count: 2 Time=7.05s (14s) Lock=0.00s (0s) Rows=1.0 (2), zabbix[zabbix]@localhost SELECT hu.itemid,AVG(hu.value) AS avg,MIN(hu.value) AS min,MAX(hu.value) AS max,MAX(hu.clock) AS clock FROM history_uint hu WHERE hu.itemid='S' AND hu.clock>='S' AND hu.clock<='S' GROUP BY hu.itemid Count: 1 Time=5.43s (5s) Lock=0.00s (0s) Rows=557.0 (557), root[root]@localhost SHOW TABLE STATUS Count: 8 Time=4.34s (34s) Lock=0.00s (0s) Rows=2.1 (17), INF[INF]@localhost select `process_id` from `dashboard_events` left join `dashboard_process` on `process_id` = `dashboard_process`.`id` where `dashboard_events`.`deleted_at` is null and `dashboard_process`.`id` is null group by `process_id` order by `process_id` asc Count: 11 Time=1.27s (13s) Lock=0.00s (0s) Rows=1.0 (11), INF[INF]@localhost select count(*) as aggregate from `dashboard_log` where `process_id` = 'S' Count: 11 Time=1.08s (11s) Lock=0.00s (0s) Rows=59.0 (649), INF[INF]@localhost SELECT B.* FROM ( SELECT MAX( started_at ) as started_at, process_id, `order` FROM dashboard_events D INNER JOIN dashboard_page_process C USING ( process_id ) where page_id=N GROUP BY process_id ) A INNER JOIN dashboard_events B USING (started_at,process_id) ORDER BY A.order Count: 1 Time=1.03s (1s) Lock=0.00s (0s) Rows=2.0 (2), INF[INF]@localhost select `dashboard_events`.`id` as `id`, `etat`, `etape`, `process_id`, `started_at`, `updated_at`, `commentaire`, `erreur`, `nbr_log` from `dashboard_events` left join `dashboard_process` on `dashboard_events`.`process_id` = `dashboard_process`.`id` where `dashboard_events`.`deleted_at` is null and `etape` < 'S' order by `created_at` asc ===== InnoDB ===== Une requête intégressante SHOW ENGINE INNODB STATUS La commande **innotop** permet de faire comme **htop** avec beaucoup d'info sur ce qu'il se passe sur innodb innotop ===== Partition ===== Sur le lien ci-dessous, des scripts permettant d'automatiser la création et la purge de partition de type date => http://www.geoffmontee.com/automatic-partition-maintenance-in-mysql-and-mariadb-part-3/ ===== Erreur ===== Après avoir lancé une optimization puis violemment killé la requete en cours, la table était inaccessible ... Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. ERROR 144 (HY000) at line 1: Table 'dashboard_log' is marked as crashed and last (automatic?) repair failed Se placer dans le bon répertoire cd /var/lib/mysql/MA_BDD # myisamchk MA_TABLE Checking MyISAM file: MA_TABLE Data records: 654724 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: warning: Size of indexfile is: 275469312 Should be: 25405440 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 - check record links myisamchk: error: Record-count is not ok; is 5773841 Should be: 654724 myisamchk: warning: Found 484145612 deleted space. Should be 0 myisamchk: warning: Found 1581466 deleted blocks Should be: 0 myisamchk: warning: Found 8799790 key parts. Should be: 654724 MyISAM-table 'MA_TABLE' is corrupted Fix it using switch "-r" or "-o" # myisamchk -r -v MA_TABLE - recovering (with sort) MyISAM-table 'MA_TABLE' Data records: 654724 - Fixing index 1 - Searching for keys, allocating buffer for 147793 keys - Merging 5773841 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 139720 keys - Merging 5773841 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 2604 keys - Merging 5773841 keys - Last merge and dumping keys Data records: 5773841 Ça remarche ! et même à chaud sans redémarrer mysql ! J'ai même l'impression que ça a été plus rapide de faire cette manip que d'attendre la fin de la requête d'optimisation... Autre cas un peu plus complexe ou il a fallu rajouté un --force pour que ça passe. 30 minutes environ de traitement et ça remarche. # myisamchk statistiques Checking MyISAM file: statistiques Data records: 43197952 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size myisamchk: warning: Size of indexfile is: 2134406144 Should be: 1024 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 myisamchk: Unknown error 126 myisamchk: error: Can't read indexpage from filepos: -1 - check record links myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 10578706084 MyISAM-table 'statistiques' is corrupted Fix it using switch "-r" or "-o" # myisamchk -r -v statistiques - recovering (with sort) MyISAM-table 'statistiques' Data records: 43197952 myisamchk: error: Can't create new tempfile: 'statistiques.TMD' MyISAM-table 'statistiques' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag # myisamchk -f -r -v statistiques - recovering (with sort) MyISAM-table 'statistiques' Data records: 43197952 - Fixing index 1 - Searching for keys, allocating buffer for 102782 keys Wrong bytesec: 0- 0- 0 at 10578706084; Skipped Wrong bytesec: 0- 0- 0 at 10578706104; Skipped Wrong bytesec: 0- 0- 0 at 10578706124; Skipped Wrong bytesec: 0- 0- 0 at 10578706144; Skipped Wrong bytesec: 0- 0- 0 at 10578706164; Skipped Wrong bytesec: 0- 0- 0 at 10578706184; Skipped Wrong bytesec: 0- 0- 0 at 10578706204; Skipped Wrong bytesec: 0- 0- 0 at 10578706224; Skipped Wrong bytesec: 0- 0- 0 at 10578706244; Skipped Wrong bytesec: 0- 0- 0 at 10578706264; Skipped Wrong bytesec: 0- 0- 0 at 10578706284; Skipped Wrong bytesec: 0- 0- 0 at 10578706304; Skipped Wrong bytesec: 0- 0- 0 at 10578706324; Skipped Wrong bytesec: 0- 0- 0 at 10578706344; Skipped Wrong bytesec: 0- 0- 0 at 10578706364; Skipped Wrong bytesec: 0- 0- 0 at 10578706384; Skipped Wrong bytesec: 0- 0- 0 at 10578706404; Skipped Wrong bytesec: 0- 0- 0 at 10578706424; Skipped - Merging 43197952 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 94325 keys - Merging 43197952 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 16873 keys - Merging 43197952 keys - Last merge and dumping keys - Fixing index 4 - Searching for keys, allocating buffer for 115510 keys - Merging 43197952 keys - Last merge and dumping keys - Fixing index 5 - Searching for keys, allocating buffer for 66644 keys - Merging 43197952 keys - Last merge and dumping keys ===== Strict Mode ===== Bon à savoir si jamais vous copiez des dates en 0000-00-00 et que vous avez une erreur SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column... Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning. ===== Log binaire ===== Utiliser des log binaire pour mettre à jour une base de données distante après avoir fait un mysqldump Vérifier si les log binaires sont activé MariaDB [(none)]> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 328 | +------------------+-----------+ 1 row in set (0.000 sec) MariaDB [(none)]> show variables like 'log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/log/mysql/mysql-bin | | log_bin_compress | OFF | | log_bin_compress_min_len | 256 | | log_bin_index | /var/log/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | +---------------------------------+--------------------------------+ 6 rows in set (0.001 sec) Si non mysql> SHOW BINARY LOGS; ERROR 1381 (HY000): You are not using binary logging Pour activer dans le cas de mariaDB vi /etc/mysql/mariadb.conf.d/50-server.cnf Décommenter la ligne log_bin = /var/log/mysql/mysql-bin.log et restarter mariaDB systemctl restart mariadb.service Vos fichiers de log binaires sont maintenant stocké dans /var/log/mysql/ **mysqlbinlog** permet de lire les fichiers de log binaire et de générer une sortie sql # mysqlbinlog mysql-bin.000001 mysqlbinlog: unknown variable 'default-character-set=utf8mb4' ---------- Si vous avez ce message d'erreur, utilisez l'option suivante # mysqlbinlog --no-defaults mysql-bin.000001 Filtre possible: * ''--database=ma_base'' ou ''-d ma_base'' * ''--start-datetime="2019-09-09 12:15:00"'' supérieur ou égal à cette date * ''--stop-datetime="2019-12-24 13:15:00"'' inférieur à cette date * ''--start-position=N'' commence à la position N * ''--stop-position=N'' s'arrête à la position N example de restauration mysqlbinlog /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p mysqlbinlog --no-defaults --start-datetime="2019-09-09 12:15:00" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p Par défaut, mysql s'arrête dès la 1ère erreur (par ex: duplicate entry), pour continuer après une erreur, ajouté l'option -f mysqlbinlog --no-defaults --start-datetime="2019-09-09 12:15:00" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -p -f ===== Auto increment ===== On peut réinitialiser la valeur de l'auto increment d'un id avec la requete ALTER TABLE `users` AUTO_INCREMENT = 1; Mysql prendra automatiquement le max(id) + 1 Pour réinitialiser complètement une colonne d'id ALTER TABLE `users` DROP `id`; ALTER TABLE `users` AUTO_INCREMENT = 1; ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; Si votre id est utilisé dans d'autres tables, il convient de définir les clés étrangères sur les autres tables avec l'option ''ON UPDATE CASCADE'' plutot que l'option par défaut ''ON UPDATE NO ACTION'' SET @count = 0; UPDATE `users` SET `users`.`id` = @count:= @count + 1; ALTER TABLE `users` AUTO_INCREMENT = 1;