Automate MySQL Backups with Cron


Automating the process of backing up your databases is simple as creating a cron job for the mysqldump command.

To set up automated backups of a MySQL database using cronjob, follow the steps below:

–Create a file named .my.cnf in your user home directory:

vi ~/.my.cnf
Copy and paste the following text into the .my.cnf file.

user = root
password = root

Do not forget to replace dbuser and dbpasswd with the database user and userís password.

–Restrict permissions of the credentials file so only your user has access to it:

chmod 600 ~/.my.cnf

–Create a directory for your database backup:

mkdir -p /Data/Backup

–To edit your user crontab file and setup a new cronjob type:

crontab -e

–Copy and paste the following cron job that will create a backup of a database name mydb every day at 3am:

00 20 * * 3 /usr/bin/mysqldump -u root ctrdb > /Data/Backup/ctrdb-$(date +%Y%m%d).sql
00 21 * * 3 /usr/bin/mysqldump -u root ctrassetdb > /Data/Backup/ctrassetdb-$(date +%Y%m%d).sql
00 19 * * 3 /usr/bin/mysqldump -u root –all-databases > /Data/Backup/all_databases-$(date +%Y%m%d).sql

Deleting Backups older than 15 Days

find /Data/Backup -type f -name “*.sql” -mtime +15 -delete

========================================OR USE BELOW 2nd Method=========================================================================

Backing up MySQL Database using mysqldump & crontab
Open terminal and type:
sudo tcsh

pico /etc/crontab
nano /etc/crontab

And add one of the following lines depending on your situation. This schedule the backup on 1am every day.

Remote Host Backup with linked PATH to mysqldump:
0 1 * * * mysqldump -h -uusername -ppassword –opt database > /path/to/directory/filename.sql

Remote Host Backup:
0 1 * * * /usr/local/mysql/bin/mysqldump -h -uusername -ppassword –opt database > /path/to/directory/filename.sql

Local Host mysql Backup:
0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword –opt database > /path/to/directory/filename.sql

(There is no space between the -p and password or -u and username – replace root with a correct database username.)


Restore a Single MySQL Database from a Full MySQL Dump
If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the –one-database option as shown below:

mysql –one-database database_name < all_databases.sql

–Create a Compressed MySQL Database Backup
If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown below:

mysqldump -uroot database_name > | gzip > database_name.sql.gz

——–Backup all MySQL databases to separate files

The mysqldump utility doesnít provide an option to backup all databases to separate files but we easily achieve that with a simple bash FOR loop:

for DB in $(mysql -e ‘show databases’ -s –skip-column-names); do
mysqldump $DB > “$DB.sql”;
The command above will create a separate dump file for each database using the database name as the filename.