La méthode la plus rapide pour migrer des bases de données MySQL vers un autre serveur avec Mysqldump

Migrer une base de données MySQL vers un autre serveur peut être un défi, en particulier si vous visez un temps d’arrêt minimal et des performances élevées.

L’un des outils les plus populaires pour accomplir cette tâche est mysqldump, un utilitaire fourni par MySQL pour sauvegarder et restaurer des bases de données. Dans cet article, nous allons vous guider dans l’utilisation de mysqldump pour migrer votre base de données et créer un script pour automatiser le processus.

Migrer des bases de données MySQL vers un autre serveur et cloner les données.

Prérequis

  • Serveurs source et destination avec MySQL installé
  • Identifiants root MySQL ou un compte disposant de privilèges suffisants sur les deux serveurs
  • Accès SSH aux deux serveurs ou le serveur DB doit autoriser les connexions entrantes (s’il n’est pas sur la même machine)

Exporter la base de données avec mysqldump

La première étape du processus de migration consiste à créer une sauvegarde de la base de données source. Nous utiliserons mysqldump pour ce faire. Mysqldump crée un script SQL contenant toutes les commandes nécessaires pour recréer la structure de la base de données et les données sur le serveur de destination.

Connectez-vous au serveur source et exécutez la commande suivante pour créer une sauvegarde compressée de votre base de données :

bash
mysqldump -u [username] -p --single-transaction --extended-insert --quick --lock-tables=false [database_name] | gzip > [backup_file].sql.gz

Remplacez [username], [database_name] et [backup_file] respectivement par votre nom d’utilisateur MySQL, le nom de la base de données que vous souhaitez migrer, et le nom souhaité du fichier de sauvegarde.

Cette commande utilise l’option --single-transaction pour garantir la cohérence des données, --quick pour réduire l’utilisation de la mémoire, et –lock-tables=false pour éviter le verrouillage des tables pendant le processus d’exportation.

Le paramètre le plus important est –extended-insert. Cela rendra l’importation du fichier SQL exporté 10 à 15 fois plus rapide sur le serveur de destination.

Transférer le fichier de sauvegarde vers le serveur de destination

Après avoir créé la sauvegarde, vous pouvez l’importer sur l’autre serveur de base de données.

  • Copiez le fichier gzip compressé sur le serveur et importez-le
  • Décompressez le fichier et chargez-le avec MySQL via SSH sur le serveur DB

Pour le transférer vers le serveur de destination, vous pouvez utiliser un outil comme SCP, SFTP ou rsync. Par exemple, pour utiliser SCP :

bash
scp [backup_file].sql.gz [destination_user]@[destination_ip]:/path/to/destination

Remplacez [backup_file], [destination_user], [destination_ip] et /path/to/destination par les valeurs appropriées.

Importer la base de données sur le serveur de destination

Une fois le fichier de sauvegarde transféré, connectez-vous au serveur de destination et exécutez la commande suivante pour importer la base de données :

bash
gunzip < [backup_file].sql.gz | mysql -u [username] -p [database_name]

Remplacez [username], [database_name] et [backup_file] respectivement par votre nom d’utilisateur MySQL, le nom de la base de données que vous souhaitez migrer, et le nom du fichier de sauvegarde.

Télécharger le script de migration de base de données

Pour automatiser le processus de migration de la base de données MySQL, vous pouvez utiliser ce script bash que nous avons utilisé ici sur wp-staging.com pour migrer notre base de données vers une DB managée hébergée chez DigitalOcean. (Il n’est plus utilisé car WP Staging peut désormais effectuer la migration de base de données) :

bash
#!/bin/sh
# migrate.sh

sourceUser=""
sourcePassword=""
sourceHostname=""
sourcePort=""
sourceDatabase=""

destinationUser=""
destinationPassword=""
destinationHostname=""
destinationPort=""
destinationDatabase=""

echo "Exporting $sourceDatabase database to ./wp-staging.com.sql"

start_export=`date +%s`

mysqldump -P $sourcePort -h $sourceHostname -u $sourceUser  -p$sourcePassword --set-gtid-purged=OFF --extended-insert --single-transaction --skip-lock-tables --column-statistics=0 $sourceDatabase > ./wp-staging.com.sql

# Done creating a dump of wp-staging.com

end_export=`date +%s`

echo "Database exported in" $((end_export-start_export)) "seconds"

echo "Importing Database to... "

start_import=`date +%s`

mysql -u $destinationUser -p$destinationPassword -h $destinationHostname -P $destinationPort $destinationDatabase < ./wp-staging.com.sql

# Done importing the dump to managed db

end_import=`date +%s`

echo "Database imported in" $((end_import-start_import)) "seconds"

echo "Total runtime: " $((end_export-start_export+end_import-start_import)) "seconds"

Remplacez les valeurs des identifiants de base de données en haut du fichier par les informations appropriées pour vos bases de données source et destination.

Avant d’exécuter le script, assurez-vous que les bases de données source et destination sont accessibles.

Ensuite, accordez au script des autorisations d’exécution :

bash
chmod +x migrate.sh

Exécutez le script pour effectuer la migration :

bash
./migrate.sh

Ce script exporte la base de données source à l’aide de mysqldump, importe la base de données sur le serveur de destination à l’aide de la commande mysql, puis nettoie en supprimant le fichier de sauvegarde.

Articles connexes

Rene Hermenau

Auteur : Rene Hermenau

À propos de l'auteur : René Hermenau est le fondateur de WP STAGING. Il travaille sur les sauvegardes WordPress, les environnements de staging, les migrations, la gestion des bases de données et les workflows de déploiement sécurisés.