Backing up MySQL Databases (mysqldump & Automation)

While Tilaa snapshots backup your entire server, creating specific SQL-dumps of your databases is essential for granular recovery. Using the mysqldump utility, you can export your database to a portable SQL file.

1. Secure Configuration (Avoid Passwords in Scripts)

To automate backups safely, you should never put your password directly in a command or cronjob (where it can be seen by other users).

Instead, create a configuration file in your home directory:

nano ~/.my.cnf

Add the following content (replace with your user/password):

[mysqldump]
user=root
password=YourStrongPassword

Secure the file so only you can read it:

chmod 600 ~/.my.cnf

2. Manual Backup (One-off)

Now you can run mysqldump without being prompted for a password. This creates a text file containing all SQL commands needed to recreate the database.

Backup a single database:

mysqldump my_database > my_database_backup.sql

Backup all databases (Full Server):

mysqldump --all-databases > full_backup.sql

3. Automating with Cron (Scheduled Backups)

To ensure you always have a recent copy, schedule a daily backup. We will pipe the output through gzip to save disk space.

1. Create a backup directory:

   mkdir -p /root/db_backups

2. Edit the crontab:

   crontab -e

3. Add the following line to run every day at 03:00 AM:

0 3 * * * mysqldump --all-databases | gzip > /root/db_backups/db_backup_$(date +\%F_\%H\%M).sql.gz

4. How to Restore

A backup is useless if you cannot restore it. Here is how to import your data back into the database.

If the file is a standard .sql file:

mysql -u root -p my_database < my_backup.sql

If the file is compressed (.sql.gz):

gunzip < my_backup.sql.gz | mysql -u root -p my_database