Sauvegarde et restauration MySQL: mysqldump et phpMyAdmin

Why MySQL Backups Are Important

Regular database backups are one of the most critical parts of server administration. A reliable backup strategy ensures that important data can be recovered when unexpected problems occur.

Common situations that may require a database restore include:

• Hardware failures
• Server crashes
• Storage corruption
• Accidental data deletion
• Application errors
• Security incidents
• Failed software upgrades

Backups are also commonly used when:

• Migrating websites to a new server
• Cloning environments
• Testing software updates
• Creating staging environments
• Upgrading MySQL or MariaDB versions

Without a recent backup, recovering lost data may be impossible.

Backup Methods Available for MySQL

MySQL and MariaDB support several backup methods.

The most commonly used options are:

Hébergement Web WordPress

À partir de 3,99 $/mois

Acheter maintenant

• Command-line backups using mysqldump
• Backups through phpMyAdmin
• Automated backup systems
• Snapshot-based backups

This guide focuses on using mysqldump and phpMyAdmin because they are available on most hosting environments.

Requirements

Use a client compatible with the server version; current MariaDB installations may use mariadb-dump and mariadb instead. For transactional InnoDB tables, –single-transaction helps create a consistent snapshot, but avoid schema changes during the dump. Nontransactional tables and cross-database consistency need a separate locking or application-quiescing plan. Include –routines –events when those objects are required.

Before creating backups, ensure the following requirements are met:

• Linux server or VPS
• MySQL or MariaDB installed
• mysqldump utility available
• Database credentials
• Root or sudo access (si nécessaire)

Understanding mysqldump

mysqldump is a command-line utility included with MySQL and MariaDB.

It exports database contents into a text file containing SQL statements that can recreate the original database.

A typical dump file contains statements such as:

Serveur VPS pas cher

À partir de 2,99 $/mois

Acheter maintenant

• CREATE DATABASE
• CREATE TABLE
• INSERT
• DROP TABLE

These commands allow the database to be rebuilt during restoration.

Basic mysqldump Syntax

The general syntax is:

mysqldump -u username -p database_name > backup.sql

Components:

-u          Database username
-p          Prompt for password
database_name   Database to export
>           Redirect output to a file
backup.sql  Backup filename

Backup a Single MySQL Database

To export a single database:

mysqldump -u database_username -p database_name > database_backup.sql

Exemple:

mysqldump -u wordpress_user -p wordpress_db > wordpress_backup.sql

After entering the password, the backup file will be generated in the current directory.

Hébergement VPS Windows

Accès à distance et administrateur complet

Acheter maintenant

Backups Without Interactive Passwords

For unattended jobs, store a dedicated backup account in a restricted option file, for example /root/.mysql-backup.cnf owned by root with mode 600. Do not put passwords in command arguments or source control.

[client]
user=backupuser
password=REPLACE_WITH_SECRET

Use the option-file argument first:

mysqldump --defaults-extra-file=/root/.mysql-backup.cnf --single-transaction --routines --events database_name > database_backup.sql

Backup Multiple Databases

To export multiple databases into a single file:

mysqldump -u database_username -p --databases database1 database2 > multi_database_backup.sql

Exemple:

mysqldump -u admin -p --databases shop_db forum_db > backup.sql

The resulting file contains all selected databases.

Automate Multiple-Database Backups

mysqldump --defaults-extra-file=/root/.mysql-backup.cnf --single-transaction --routines --events --databases database1 database2 > backup.sql

Backup All Databases

To export every database on the server:

mysqldump -u database_username -p --all-databases > all_databases.sql

Exemple:

mysqldump -u root -p --all-databases > full_backup.sql

This exports databases accessible to the account; it is not a full operating-system or server-configuration backup.

Automate a Full Logical Backup

mysqldump --defaults-extra-file=/root/.mysql-backup.cnf --single-transaction --routines --events --all-databases > all_databases.sql

Review server-version permissions and how users, grants and system schemas are handled. A logical SQL dump does not include configuration files, encryption keys or a point-in-time binary-log recovery plan. Prefer an explicit list of application databases when exporting each database separately; do not loop blindly over system schemas.

Create Timestamped Backups

Using timestamps helps maintain multiple backup versions.

Exemple:

mysqldump database_name -u database_username -p > database_name-$(date +%Y%m%d).sql

Sortir:

wordpress-20260617.sql

This approach makes backup rotation and recovery much easier.

Create Compressed MySQL Backups

In Bash automation, enable set -o pipefail so a failed dump cannot be hidden by a successful gzip exit. Write to a temporary filename, check the command result, then rename the file. Keep permissions restrictive and alert on failures.

Large databases can consume significant storage space.

To compress the backup during creation:

mysqldump database_name -u database_username -p | gzip > database_name.sql.gz

Exemple:

mysqldump wordpress_db -u wordpress_user -p | gzip > wordpress.sql.gz

Benefits:

• Smaller backup files
• Utilisation réduite du stockage
• Des transferts plus rapides

Restore a MySQL Database

Restore first into an isolated test environment. SQL dumps can drop and replace tables. Back up the destination, confirm the database name and create an empty target database if the dump does not include CREATE DATABASE. Keep production writes stopped during an intentional replacement.

To restore a database from a SQL backup file:

mysql -u database_username database_name -p < database_backup.sql

Exemple:

mysql -u wordpress_user wordpress_db -p < wordpress_backup.sql

After entering the password, MySQL executes all statements from the backup file.

Recover One Database from a Full Dump

The mysql –one-database filter is based on USE statements and is not a security boundary. Fully qualified names and cross-database statements can make its behavior surprising. A safer recovery workflow is to restore the full dump into an isolated compatible server, export only the required application database, and then restore that verified export to the intended destination.

Move a Database Between Servers

Create and verify the dump first, transfer it using SSH/SFTP or a private authenticated channel, then import it on the destination. A network pipeline is not a retained backup and makes failures harder to recover from. Keep database ports private, use TLS with certificate verification for remote database connections, and do not overwrite a live destination without a rollback copy.

Creating a Backup with phpMyAdmin

If you prefer a graphical interface, phpMyAdmin provides a simple export process.

Étape 1

Log in to phpMyAdmin.

Étape 2

Select the database from the left navigation menu.

Étape 3

Ouvrez le:

Export

tab.

Étape 4

Choisir:

Quick

for a standard backup.

Advanced users may select:

Custom

for additional export options.

Étape 5

Cliquez:

Go

to download the backup file.

The SQL export will be saved to your local computer.

Restoring a Backup with phpMyAdmin

To restore a database through phpMyAdmin:

Étape 1

Log in to phpMyAdmin.

Étape 2

Select the target database.

Étape 3

If necessary, remove existing tables before importing.

Étape 4

Ouvrez le:

Import

tab.

Étape 5

Cliquez:

Choose File

and select the backup file.

Étape 6

Cliquez:

Go

to begin the import.

Once completed, phpMyAdmin will display a success message.

Backup Best Practices

Creating backups is only one part of a reliable disaster recovery strategy.

Follow these best practices:

Maintain Multiple Backup Copies

Keep backups in multiple locations.

Exemples:

• Local storage
• Stockage à distance
• Cloud storage

Automate Backups

Use scheduled cron jobs to generate regular backups automatically.

Verify Backups

Test restoration procedures periodically to ensure backups are valid.

Use Compression

Compress backup files to reduce storage consumption.

Protect Backup Files

Backup files often contain sensitive data.

Store them securely and restrict access permissions.

Keep Historical Versions

Avoid relying on a single backup file.

Maintain multiple recovery points in case corruption goes unnoticed.

Building a Reliable Database Recovery Strategy

Database backups are one of the most important safeguards for any website or application. Que vous’re running a small WordPress site, an ecommerce store, a business application, or a large hosting environment, regular backups ensure you can recover quickly from hardware failures, accidental deletions, software issues, or security incidents.

Using tools such as mysqldump and phpMyAdmin makes it easy to create and restore backups whenever necessary. Combined with automation, offsite storage, and regular testing, a strong backup strategy helps protect your data and minimize downtime when unexpected events occur.