How to Backup MySQL Databases in Debian 12 Bookworm System

Backing up MySQL databases is one of the most important administrative tasks you can perform on a Linux server. Whether you’re running a small web application or managing enterprise-level systems, regular backups ensure that your data remains safe and recoverable in case of system failure, data corruption, or accidental deletions.

This article will guide you through the process of backing up MySQL databases on a Debian 12 Bookworm system. We’ll cover command-line tools, backup strategies, and automation techniques to make your backup process efficient and reliable.

Why Backing Up MySQL Databases Is Critical

MySQL is a powerful and widely used relational database management system. However, like any software, it is susceptible to various risks:

• Hardware failures
• Accidental data deletion or updates
• Software bugs
• Ransomware and cyber attacks
• Human error

Regular backups help you:

• Recover quickly from disasters
• Migrate data between servers or environments
• Maintain data integrity and availability
• Comply with data retention policies

Prerequisites

Before starting, ensure you have the following:

• A Debian 12 Bookworm system with administrative privileges (sudo or root).
• MySQL or MariaDB installed and running.
• Basic familiarity with Linux command line.

To check your MySQL version:

mysql --version

To check that the MySQL service is running:

sudo systemctl status mysql

Method 1: Backup Using mysqldump

mysqldump is a command-line utility that creates logical backups of MySQL databases by exporting them as SQL scripts.

Step 1: Backup a Single Database

To back up a single database:

mysqldump -u root -p database_name > /path/to/backup/database_name.sql

Replace database_name with the actual name of your database. You’ll be prompted for the MySQL root password.

Step 2: Backup All Databases

To back up all databases:

mysqldump -u root -p --all-databases > /path/to/backup/all_databases.sql

Step 3: Compress the Backup

To save disk space, compress the SQL file:

gzip /path/to/backup/all_databases.sql

This will create a all_databases.sql.gz file.

Step 4: Restore from Backup

To restore a backup:

mysql -u root -p database_name < /path/to/backup/database_name.sql

For all databases:

mysql -u root -p < /path/to/backup/all_databases.sql

Method 2: Backup Using mysqlpump

mysqlpump is a newer utility designed to perform faster parallel dumps and supports modern features like JSON support and data masking.

To back up all databases:

mysqlpump -u root -p --all-databases > /path/to/backup/mysqlpump_all.sql

To back up a specific database:

mysqlpump -u root -p database_name > /path/to/backup/mysqlpump_database_name.sql

Method 3: Physical Backups with rsync or cp

Logical backups with mysqldump are convenient but not ideal for large databases. Physical backups involve copying actual database files.

⚠️ Warning: Only use this method when the MySQL server is stopped, or data corruption may occur.

Step 1: Stop MySQL Service

sudo systemctl stop mysql

Step 2: Copy Database Files

Use rsync or cp:

sudo rsync -av /var/lib/mysql /backup/mysql-files

Or:

sudo cp -a /var/lib/mysql /backup/mysql-files

Step 3: Restart MySQL

sudo systemctl start mysql

Note that you’ll need to maintain file permissions and SELinux/AppArmor contexts when using physical copies.

Automating Backups with Cron

Manual backups are not sustainable in the long run. You can schedule backups using cron.

Step 1: Create a Backup Script

Create a script named mysql_backup.sh:

sudo nano /usr/local/bin/mysql_backup.sh

Paste the following:

#!/bin/bash

# Set variables
USER="root"
PASSWORD="your_mysql_password"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%F_%H-%M-%S)

# Create backup directory if not exists
mkdir -p "$BACKUP_DIR"

# Dump all databases
mysqldump -u $USER -p$PASSWORD --all-databases | gzip > "$BACKUP_DIR/all_databases_$DATE.sql.gz"

# Delete backups older than 7 days
find "$BACKUP_DIR" -type f -mtime +7 -name "*.gz" -delete

Make it executable:

sudo chmod +x /usr/local/bin/mysql_backup.sh

🔐 Security Tip: Avoid placing passwords directly in scripts. Consider using .my.cnf file with proper permissions.

Step 2: Schedule with Cron

Edit the crontab:

crontab -e

Add a line to back up daily at 2 AM:

0 2 * * * /usr/local/bin/mysql_backup.sh

Using my.cnf for Passwordless Authentication

To avoid embedding passwords in scripts, create a MySQL option file:

nano ~/.my.cnf

Add:

[client]
user=root
password=your_mysql_password

Then change permissions:

chmod 600 ~/.my.cnf

Now, you can run mysqldump without -u and -p:

mysqldump --all-databases > backup.sql

Backup Best Practices

• ✅ Automate: Use cron jobs or Ansible playbooks to automate backups.
• ✅ Secure: Store backups in secure, access-controlled directories.
• ✅ Offsite Storage: Sync backups to cloud storage (e.g., AWS S3, Google Drive).
• ✅ Retention Policy: Keep backups for a reasonable duration (e.g., 7–30 days).
• ✅ Verify: Regularly test restoring from backups to verify integrity.

Advanced Backup Tools

You may consider more advanced backup solutions if you manage multiple servers or large databases:

1. Percona XtraBackup

• Ideal for large, busy MySQL/MariaDB servers.
• Takes hot backups without locking the database.

2. Mariabackup

• Fork of Percona XtraBackup, tailored for MariaDB.

3. MySQL Enterprise Backup

• Official tool from Oracle (proprietary license).
• Advanced compression, encryption, and incremental backups.

Backup to Remote Server

You can back up to a remote server using ssh:

mysqldump -u root -p --all-databases | gzip | ssh user@remote_server "cat > /backups/mysql_backup.sql.gz"

Or use rsync to copy local backups:

rsync -avz /var/backups/mysql user@remote_server:/backups/mysql

Conclusion

Backing up MySQL databases on Debian 12 Bookworm is a straightforward but essential process. Whether you’re using simple tools like mysqldump or advanced options like Percona XtraBackup, the key is consistency and automation. Make sure your backups are stored safely, tested regularly, and integrated into your broader system administration practices.

By implementing a good backup strategy today, you’re protecting your data and ensuring business continuity for tomorrow.

Additional Resources

• MySQL Backup and Recovery Official Docs
• Percona XtraBackup Documentation
• Debian System Administration Guide