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 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/
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
# myisamchk dashboard_log Checking MyISAM file: dashboard_log 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 'dashboard_log' is corrupted Fix it using switch "-r" or "-o"
# myisamchk -r -v dashboard_log - recovering (with sort) MyISAM-table 'dashboard_log' 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
Ca remarche ! et meme à chaud sans redémarrer mysql !