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 [17-10-2017 10:19] edmc73 |
linux:mysql [03-10-2019 11:30] edmc73 [Log binaire] |
||
---|---|---|---|
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 167: | Ligne 192: | ||
+ | ===== 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 ' | ||
+ | |||
+ | ===== Log binaire ===== | ||
+ | |||
+ | 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)]> | ||
+ | +------------------+-----------+ | ||
+ | | Log_name | ||
+ | +------------------+-----------+ | ||
+ | | mysql-bin.000001 | 328 | | ||
+ | +------------------+-----------+ | ||
+ | 1 row in set (0.000 sec) | ||
+ | |||
+ | |||
+ | MariaDB [(none)]> | ||
+ | +---------------------------------+--------------------------------+ | ||
+ | | Variable_name | ||
+ | +---------------------------------+--------------------------------+ | ||
+ | | log_bin | ||
+ | | log_bin_basename | ||
+ | | log_bin_compress | ||
+ | | log_bin_compress_min_len | ||
+ | | log_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 / | ||
+ | Décommenter la ligne | ||
+ | log_bin | ||
+ | et restarter mariaDB | ||
+ | systemctl restart mariadb.service | ||
+ | |||
+ | |||
+ | Vos fichiers de log binaires sont maintenant stocké dans / | ||
+ | |||
+ | **mysqlbinlog** permet de lire les fichiers de log binaire et de générer une sortie sql | ||
+ | < | ||
+ | # mysqlbinlog mysql-bin.000001 | ||
+ | mysqlbinlog: | ||
+ | |||
+ | ---------- Si vous avez ce message d' | ||
+ | |||
+ | # mysqlbinlog --no-defaults mysql-bin.000001 | ||
+ | </ | ||
+ | |||
+ | Filtre possible: | ||
+ | * --database=ma_base | ||
+ | * --start-datetime=" | ||
+ | * --stop-datetime=" | ||
+ | * --start-position=N | ||
+ | * --stop-position=N | ||
+ | |||
+ | example de restauration | ||
+ | mysqlbinlog / | ||
+ | |||
+ | mysqlbinlog --no-defaults --start-datetime=" |