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.
- Create a backup directory:
mkdir -p /root/db_backups - Edit the crontab:
crontab -e - 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
Disk space fills up quickly. Consider adding a command to delete files older than 30 days:
find /root/db_backups/ -type f -name "*.sql.gz" -mtime +30 -delete
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
Restoring a dump will overwrite existing tables with the same name. Be careful when running this on a production server.