How to take mysql database backup periodically

mysqldump is a database backup program used to create copy of database or collection databases. When you run mysqldump, it will prompt you for password, on valid password it will create backup copy of the database in given path.

But, when you want to create database backup periodically, say once a week, then you need to run this from cronjob. Bad thing is, this command will prompt you for password at runtime, which is out of our control.

With few tweaks in mysql configuration you can able to backup mysql database periodically, lets see those configs.

create ".my.cnf" file in home directory with this command:

vi ~/.my.cnf

copy below content and paste it into .my.cnf file by replacing with your mysql username and password
 
Change this file permission to 600 as given below:

chmod 600 ~/.my.cnf

Now setup a cronjob to take peroidic backup of our database, enter the following command

crontab -e

Add following line at end of the line,

0 0 * * 0 mysqldump -u mysql_user_name -h localhost database_name > /path/to/backup/file

This will take backup of given database weekly once at 12.00 am, if you want to backup all database then change above command as follows:

0 0 * * 0 mysqldump -u mysql_user_name -h localhost --all-databases | gzip -9  > /path/alldb.sql.gz > /dev/null