Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente Prochaine révision Les deux révisions suivantes | ||
linux:mysql [20-09-2017 16:19] edmc73 [Faire un backup via un script php] |
linux:mysql [30-08-2019 11:09] edmc73 [Erreur] |
||
---|---|---|---|
Ligne 15: | Ligne 15: | ||
* http:// | * http:// | ||
* https:// | * https:// | ||
- | * http:// | + | * http:// |
+ | |||
+ | |||
+ | A voir le paquet **php5-mysqlnd** qui est un client mysql natif qui est optimisé par mysql pour mysql | ||
Ligne 24: | Ligne 27: | ||
mysql> show variables; | mysql> show variables; | ||
mysql> show global variables like ' | mysql> show global variables like ' | ||
+ | |||
+ | Pour modifier une variable global | ||
+ | mysql> set global max_connections=500; | ||
Pour voir les status | Pour voir les status | ||
Ligne 33: | Ligne 39: | ||
mysqld --verbose --help | head | mysqld --verbose --help | head | ||
| | ||
- | | + | |
+ | |||
+ | Process list | ||
+ | mysql> | ||
+ | Kill process | ||
+ | mysql> | ||
+ | |||
+ | |||
+ | ==== Sauvegarder ==== | ||
Sauvegarder une base | Sauvegarder une base | ||
mysqldump -u user -p nom_de_la_base > backup.sql | mysqldump -u user -p nom_de_la_base > backup.sql | ||
Ligne 43: | Ligne 57: | ||
mysql -u user -p nom_de_la_base < backup.sql | mysql -u user -p nom_de_la_base < backup.sql | ||
- | Process list | ||
- | mysql> | ||
- | Kill process | ||
- | mysql> | ||
+ | Script PERL pour extraire une table d'un dump SQL | ||
+ | <code perl> | ||
+ | # | ||
+ | #extract a single table from a mysql dump | ||
+ | BEGIN { | ||
+ | $table or warn | ||
+ | " | ||
+ | and exit 1; | ||
+ | } | ||
+ | |||
+ | /^DROP TABLE IF EXISTS `$table`/ .. /^UNLOCK TABLES;$/ and print; | ||
+ | </ | ||
===== Logguer les requêtes en temps réel ponctuellement ===== | ===== Logguer les requêtes en temps réel ponctuellement ===== | ||
Ligne 111: | Ligne 133: | ||
Plein d' | Plein d' | ||
+ | |||
+ | Un autre script dans les paquets **automysqlbackup** https:// | ||
+ | |||
===== Percona XtraBackup ===== | ===== Percona XtraBackup ===== | ||
Ligne 117: | Ligne 142: | ||
Permet des backups à chaud sans perte en copiant directement les fichiers de mysql et en appliquant les transactions en cours. | 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' | ||
+ | |||
+ | < | ||
+ | log_slow_queries | ||
+ | long_query_time = 1 # pour logguer les requêtes qui prennet plus d'une seconde | ||
+ | </ | ||
+ | |||
+ | Ensuite, utiliser l' | ||
+ | |||
+ | |||
+ | < | ||
+ | # mysqldumpslow / | ||
+ | |||
+ | Reading mysql slow query log from / | ||
+ | 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, | ||
+ | |||
+ | 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` = ' | ||
+ | |||
+ | 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, | ||
+ | |||
+ | 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`, | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ===== InnoDB ===== | ||
+ | |||
+ | Une requête intégressante | ||
+ | SHOW ENGINE INNODB STATUS | ||
+ | | ||
+ | |||
+ | ===== Partition ===== | ||
+ | |||
+ | Sur le lien ci-dessous, des scripts permettant d' | ||
+ | |||
+ | => http:// | ||
+ | |||
+ | ===== 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 ' | ||
+ | </ | ||
+ | |||
+ | Se placer dans le bon répertoire | ||
+ | |||
+ | cd / | ||
+ | |||
+ | < | ||
+ | # myisamchk MA_TABLE | ||
+ | Checking MyISAM file: MA_TABLE | ||
+ | Data records: | ||
+ | myisamchk: warning: Table is marked as crashed and last repair failed | ||
+ | - check file-size | ||
+ | myisamchk: warning: Size of indexfile is: 275469312 | ||
+ | - 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 | ||
+ | myisamchk: warning: Found 484145612 deleted space. | ||
+ | myisamchk: warning: Found 1581466 deleted blocks | ||
+ | myisamchk: warning: Found 8799790 key parts. Should be: 654724 | ||
+ | MyISAM-table ' | ||
+ | Fix it using switch " | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | # myisamchk -r -v MA_TABLE | ||
+ | - recovering (with sort) MyISAM-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' | ||
+ | |||
+ | |||
+ | 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 | ||
+ | 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 | ||
+ | - 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 ' | ||
+ | Fix it using switch " | ||
+ | |||
+ | |||
+ | # myisamchk -r -v statistiques | ||
+ | - recovering (with sort) MyISAM-table ' | ||
+ | Data records: 43197952 | ||
+ | myisamchk: error: Can't create new tempfile: ' | ||
+ | MyISAM-table ' | ||
+ | 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 ' | ||
+ | Data records: 43197952 | ||
+ | - Fixing index 1 | ||
+ | - Searching for keys, allocating buffer for 102782 keys | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | Wrong bytesec: | ||
+ | - 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]: | ||
+ | |||
+ | Strict mode affects whether the server permits ' | ||
+ | |||
+ |