Base de connaissances

A database is only as safe as its most recent tested backup. This guide covers manual and automated backup strategies for MySQL/MariaDB, plus restoring from a backup file.

Prerequisites

• MySQL or MariaDB installed
• Root or sudo access

Backing Up a Single Database

mysqldump -u root -p myapp > myapp-backup.sql

Backing Up All Databases

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

Compressing the Backup

mysqldump -u root -p myapp | gzip > myapp-backup.sql.gz

Backing Up Structure Only (No Data)

mysqldump -u root -p --no-data myapp > myapp-schema.sql

Backing Up Data Only (No Structure)

mysqldump -u root -p --no-create-info myapp > myapp-data.sql

Restoring a Database

mysql -u root -p myapp < myapp-backup.sql

For a compressed backup:

gunzip < myapp-backup.sql.gz | mysql -u root -p myapp

Restoring to a New/Different Database Name

mysql -u root -p -e "CREATE DATABASE myapp_restored;"
mysql -u root -p myapp_restored < myapp-backup.sql

Automating Daily Backups with Cron

sudo nano /usr/local/bin/backup-mysql.sh

#!/bin/bash
TIMESTAMP=$(date +%F)
BACKUP_DIR="/var/backups/mysql"
mkdir -p "$BACKUP_DIR"
mysqldump -u root --all-databases | gzip > "$BACKUP_DIR/all-$TIMESTAMP.sql.gz"
find "$BACKUP_DIR" -type f -mtime +7 -delete

sudo chmod +x /usr/local/bin/backup-mysql.sh

sudo crontab -e

0 2 * * * /usr/local/bin/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

Using ~/.my.cnf with restricted permissions to store credentials avoids embedding a password directly in the script — see MySQL's documentation on option files for the exact syntax.

Verifying a Backup File Is Valid

head -20 myapp-backup.sql

A valid dump starts with MySQL dump header comments and CREATE TABLE/INSERT statements — an empty or truncated file indicates a failed backup.

Testing a Restore (Not Optional)

Periodically restore a backup into a temporary database to confirm it actually works:

mysql -u root -p -e "CREATE DATABASE restore_test;"
mysql -u root -p restore_test < myapp-backup.sql
mysql -u root -p -e "DROP DATABASE restore_test;"

Common Errors

"mysqldump: Got error: 1044: Access denied" — the user lacks sufficient privileges; use the root account or grant SELECT, LOCK TABLES at minimum.

Restore fails with foreign key errors — add SET FOREIGN_KEY_CHECKS=0; before the restore and SET FOREIGN_KEY_CHECKS=1; after, if tables have interdependencies.

Best Practices

• Automate backups — never rely on remembering to run them manually
• Copy backups off-server (see How to Set Up Automated VPS Backups)
• Test restores regularly, not just backup creation
• Keep multiple backup generations, not just the most recent one

Related Articles

• How to Install and Secure MySQL 8 on Ubuntu & Debian
• How to Set Up Automated VPS Backups (rsync, cron & Off-Site Storage)
• How to Tune MySQL/MariaDB Performance for a VPS