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 [26-10-2017 22:39] edmc73 [Les slow query] |
linux:mysql [06-08-2019 11:35] 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 171: | Ligne 196: | ||
Une requête intégressante | Une requête intégressante | ||
SHOW ENGINE INNODB STATUS | 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 | ||
+ | |||
+ | </ | ||