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
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.
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 Nexample 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