Blog

Here’s one way to create an automated MySQL backup process.

1. Login as root.

2. Create the MySQL option file /root/.my.cnf with the following contents. This will allow you to run mysqldump without passing the password parameter.

	[client]
	password=YOUR_MYSQL_PASSWORD

	Commands:
	> vi /root/.my.cnf

3. Protect your MySQL password by limiting access to /root/.my.cnf.

	Commands:
	> chmod 600 /root/.my.cnf

4. Create a backup script under cron.daily with the following contents. This will create a compressed database dump. The filename will contain the day of the week, which will automatically handle backup rotation.

	mysqldump --defaults-extra-file=/root/.my.cnf -u YOUR_MYSQL_USERNAME YOUR_MYSQL_DATABASE > /root/backup/db/mysql-`date +%a`.sql
	bzip2 -f /root/backup/db/mysql-`date +%a`.sql

	Commands:
	> cd /etc/cron.daily
	> vi mysqlbackup.cron
	> chmod u+x mysqlbackup.cron