Ceci est une ancienne révision du document !
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 ;)
A voir le paquet php5-mysqlnd qui est un client mysql natif qui est optimisé par mysql pour mysql
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 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
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;
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)
<?php $host = 'localhost'; $db = 'maBase'; $password = 'p4ssW0rD'; $user = 'root'; echo "Votre base est en cours de sauvegarde....... "; system("mysqldump --host=$host --user=$user --password=$password $db > $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
Permet des backups à chaud sans perte en copiant directement les fichiers de mysql et en appliquant les transactions en cours.
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
Une requête intégressante
SHOW ENGINE INNODB STATUS
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/
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