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 | ||
linux:mysql [20-09-2017 16:19] edmc73 [Faire un backup via un script php] |
linux:mysql [14-02-2023 11:39] edmc73 [Sauvegarder] |
||
---|---|---|---|
Ligne 4: | Ligne 4: | ||
* http:// | * http:// | ||
- | * http://mysqltuner.com/ - script | + | * https://github.com/major/ |
wget --no-check-certificate https:// | wget --no-check-certificate https:// | ||
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 40: | Ligne 54: | ||
mysqldump -h hostname -u user -p $(mysql nom_de_la_base -h hostname -u user -p -Bse 'show tables like" | mysqldump -h hostname -u user -p $(mysql nom_de_la_base -h hostname -u user -p -Bse 'show tables like" | ||
- | Restaurer | + | La bonne pratique en prod est d' |
+ | |||
+ | mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.sql.gz | ||
+ | |||
+ | * --quick : This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out | ||
+ | |||
+ | * --single-transaction : This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. | ||
+ | |||
+ | * --lock-tables=false : option stops MyISAM tables (if they exsit) being locked during the backup | ||
+ | |||
+ | |||
+ | Sauvegarder une table avec des conditions WHERE | ||
+ | |||
+ | <code bash> | ||
+ | # Dump only the rows with the id column bigger than 500 | ||
+ | mysqldump my_db_name my_table_name --where=" | ||
+ | |||
+ | # Dump only the rows with the created_at column in the given interval | ||
+ | mysqldump my_db_name my_table_name --where=" | ||
+ | </ | ||
+ | ==== Restaurer | ||
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; | ||
+ | </ | ||
+ | |||
+ | Pour automatiser la connexion a mysql et ne pas avoir à taper de mot de passe, créer un fichier **~/ | ||
+ | < | ||
+ | [client] | ||
+ | user=root | ||
+ | password=somepassword | ||
+ | </ | ||
===== Logguer les requêtes en temps réel ponctuellement ===== | ===== Logguer les requêtes en temps réel ponctuellement ===== | ||
Ligne 111: | Ligne 160: | ||
Plein d' | Plein d' | ||
+ | |||
+ | Un autre script dans les paquets **automysqlbackup** https:// | ||
+ | |||
===== Percona XtraBackup ===== | ===== Percona XtraBackup ===== | ||
Ligne 117: | Ligne 169: | ||
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 | ||
+ | |||
+ | La commande **innotop** permet de faire comme **htop** avec beaucoup d'info sur ce qu'il se passe sur innodb | ||
+ | innotop | ||
+ | | ||
+ | |||
+ | ===== 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: | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | example de restauration | ||
+ | mysqlbinlog / | ||
+ | |||
+ | mysqlbinlog --no-defaults --start-datetime=" | ||
+ | |||
+ | Par défaut, mysql s' | ||
+ | |||
+ | mysqlbinlog --no-defaults --start-datetime=" | ||
+ | |||
+ | ===== Auto increment ===== | ||
+ | |||
+ | On peut réinitialiser la valeur de l'auto increment d'un id avec la requete | ||
+ | <code sql> | ||
+ | ALTER TABLE `users` AUTO_INCREMENT = 1; | ||
+ | </ | ||
+ | Mysql prendra automatiquement le max(id) + 1 | ||
+ | |||
+ | Pour réinitialiser complètement une colonne d'id | ||
+ | <code sql> | ||
+ | ALTER TABLE `users` DROP `id`; | ||
+ | ALTER TABLE `users` AUTO_INCREMENT = 1; | ||
+ | ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; | ||
+ | </ | ||
+ | |||
+ | Si votre id est utilisé dans d' | ||
+ | |||
+ | <code sql> | ||
+ | SET @count = 0; | ||
+ | UPDATE `users` SET `users`.`id` = @count:= @count + 1; | ||
+ | ALTER TABLE `users` AUTO_INCREMENT = 1; | ||
+ | </ |