Outils pour utilisateurs
linux:mysql (lu 82500 fois)
Différences
Ci-dessous, les différences entre deux révisions de la page.
| Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédente | ||
| linux:mysql [09-10-2019 14:40] – edmc73 | linux:mysql [14-02-2023 10:39] (Version actuelle) – [Sauvegarder] edmc73 | ||
|---|---|---|---|
| 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 54: | 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 | ||
| Ligne 203: | Ligne 223: | ||
| Une requête intégressante | Une requête intégressante | ||
| SHOW ENGINE INNODB STATUS | 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 | ||
| | | ||
linux/mysql.1570632027.txt.gz · Dernière modification : 09-10-2019 14:40 de edmc73
Outils de la page
