LLI Wiki — My Digital Knowledge Base

MySQL : dump, restauration et suppression de toutes les tables

Commandes mysqldump et mysql pour sauvegarder, restaurer, copier et vider des bases MySQL, y compris vers un serveur Azure managé.

Cet article regroupe des commandes pratiques pour sauvegarder, restaurer, déplacer et vider des bases MySQL / MariaDB en ligne de commande. Les mots de passe apparaissent sous la forme <PASSWORD> ; préférez MYSQL_PWD ou un fichier d'options plutôt qu'un mot de passe sur la ligne de commande.

Connexion locale

mysql -h 127.0.0.1 -u root -p
mysql -h 127.0.0.1 -u dba -p

Sauvegarder une base

mysqldump -h 127.0.0.1 -u root -p AppDb_Dev > 20190122_AppDb_Dev.sql
mysqldump -h 127.0.0.1 -u root -p AppDb_Prod | gzip -6 > 20190122_AppDb_Prod.sql.gz

Un dump plus complet et cohérent d'une base, avec le mot de passe dans une variable d'environnement :

ts=$(date +%Y%m%d_%H%M%S); set -o pipefail
export MYSQL_PWD='<PASSWORD>'
mysqldump --no-defaults -h mysql01.example.com -u app_admin --ssl-mode=REQUIRED \
  --single-transaction --quick --no-tablespaces --routines --triggers --events \
  --set-gtid-purged=OFF --databases AppDb_Prod | gzip -6 > "${ts}_AppDb_Prod.sql.gz"

Toutes les bases, compressées :

mysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event \
  --extended-insert --add-drop-database --disable-keys --flush-privileges \
  --quick --routines --triggers | gzip > all_databases.gz

Restaurer une base

mysql -h 127.0.0.1 -u root -p AppDb_Dev < 20190122_AppDb_Prod.sql

# dump compressé
gunzip < AppDb_Prod-2022-06-02.sql.gz | mysql -h 127.0.0.1 -u root -p AppDb_Dev
zcat AppDb_Prod-2022-06-02.sql.gz | mysql -h 127.0.0.1 -u root -p AppDb_Dev

# un dump produit avec --databases ou --all-databases contient déjà CREATE DATABASE / USE
zcat AppDb_Prod-2022-10-28.sql.gz | mysql -u dba -p

Si la base cible n'existe pas encore :

mysql -h 127.0.0.1 -u root -p -e "CREATE DATABASE AppDb_Prod"

Pour restaurer une base existante de zéro, supprimez-la d'abord (DROP DATABASE IF EXISTS AppDb_Prod;) puis recréez-la.

Restaurer toutes les bases ou une seule depuis un dump complet

# toutes les bases
gunzip < alldb.sql.gz | mysql -u root -p -h localhost

# une base, depuis un dump par base
gunzip < alldb.sql.gz | mysql -u user -p dbname

# une base depuis un dump complet, avec --one-database
mysqldump -p --all-databases > all_databases.sql
mysql -p --one-database db1 < all_databases.sql

# extraire la section d'une base d'un dump complet
sed -n '/^-- Current Database: `db_name`/,/^-- Current Database: `/p' all_databases.sql > db.sql

Copier une base vers un autre serveur

Envoyer le dump directement vers le serveur distant :

mysqldump -u root -p AppDb_Prod | mysql -h remote_host -u root -p AppDb_Prod

Via SSH (configurez l'authentification par clé pour éviter la saisie du mot de passe) :

mysqldump dbname | ssh user@example.com "mysql -D dbname"

Vider une base avant la restauration

Un dump créé avec les options par défaut contient déjà DROP TABLE IF EXISTS avant chaque CREATE TABLE. Si vous devez d'abord vider la cible, les méthodes suivantes fonctionnent. Désactivez les contrôles de clés étrangères pour pouvoir supprimer les tables dans n'importe quel ordre.

Requête préparée supprimant toutes les tables

select database();
use AppDb_Dev;

SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables
  WHERE table_schema = 'AppDb_Dev'; -- indiquer le nom de la base ici
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

Générer les DROP puis les exécuter

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'AppDb_Prod';

Exécutez la liste générée entre les deux instructions sur les clés étrangères :

SET FOREIGN_KEY_CHECKS = 0;
-- liste des DROP séparés par des points-virgules ici
SET FOREIGN_KEY_CHECKS = 1;

Une ligne avec mysqldump

--add-drop-table --no-data ne produit que la structure avec ses DROP ; on garde les lignes DROP et on les rejoue :

mysqldump -u${user} -p -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p -h ${host} ${database}

Script shell supprimant toutes les tables

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
	echo "Drops all tables from a MySQL"
	exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Utilisation : ./drop.table.sh app_user <PASSWORD> AppDb

Supprimer toutes les bases utilisateur

Cela conserve les schémas mysql et information_schema et supprime tout le reste. À utiliser avec une grande prudence.

mysql -uroot -p -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema \
  | gawk '{print "drop database `" $1 "`;select sleep(0.1);"}' | mysql -uroot -p

Sauvegarder et restaurer utilisateurs et privilèges

Une restauration de base ne ramène pas les comptes. Examinez d'abord les droits existants :

SELECT DATABASE();
SELECT user();
SELECT User, Host, Password FROM mysql.user;
SHOW GRANTS FOR 'app_user'@'localhost';

Droits typiques pour recréer un compte applicatif :

GRANT USAGE ON *.* TO 'app_user'@'%' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON `AppDb\_Prod`.* TO 'app_user'@'%';

Exporter la table mysql.user pour conserver comptes et privilèges :

mysqldump -h 127.0.0.1 -u root -p mysql user > user_table_dump.sql

Pour une migration, voir l'approche d'export des utilisateurs avec SHOW GRANTS (How to restore MySQL users and passwords during migration).

Se connecter à un serveur MySQL managé (Azure Database for MySQL)

Les serveurs managés exigent TLS. Testez la connectivité et la latence avant un gros dump ou une restauration :

time mysql -h mysql01.mysql.database.azure.com -u app_admin -p --ssl-mode=REQUIRED -e "SELECT SLEEP(0.1);"
time mysql -h mysql01.mysql.database.azure.com -u app_admin -p --ssl-mode=REQUIRED -N -s -e "SELECT 1;"

Si la connexion échoue, vérifiez la résolution DNS et l'accessibilité réseau du port :

getent hosts mysql01.mysql.database.azure.com
time nc -vz mysql01.mysql.database.azure.com 3306

Dump d'une base du serveur managé, compressé :

mysqldump -h mysql01.mysql.database.azure.com -u app_admin -p AppDb --compress | gzip > AppDb.sql.gz

Restauration vers un autre serveur managé, avec le mot de passe en variable d'environnement et une taille de paquet augmentée :

export MYSQL_PWD='<PASSWORD>'
mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_admin --ssl-mode=REQUIRED -e "SELECT CURRENT_USER();"
gunzip -c 20260820_102316_AppDb_Prod.sql.gz | mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_admin --ssl-mode=REQUIRED --max-allowed-packet=512M AppDb_Prod

Vérifiez ensuite que le compte applicatif peut se connecter :

export MYSQL_PWD='<PASSWORD>'
mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_user --ssl-mode=REQUIRED -e "SELECT CURRENT_USER();"

Remarques

Mastodon LLIMastodon SocialFosstodon