Smart Mysql database backup with cron
Create and assign a DB user to the DB you want to backup with all privileges from your Cpanel >> Mysql section.
following is a simple script to take backup using cron. Just create the file, chmod it to 750 and execute it using cron.
/usr/bin/mysqldump –user=db_User –password=xyz –databases db_name | gzip > /home/foo/db_name-`date “+%Y%m%d%H%M”`.sql.gz
db_user is your database user who has access on your database.
db_name is the database name you are scheduling backup for.
xyz is the actual password of above database user.
+%Y%m%d%H%M is the time stamp which will help to distinguish between backup date and time.
the backup destination in above example is “/home/foo/”
add following line in cron to take backup everyday at 23:00 and will store in separate file
0 23 * * * /PathToScript.sh
Security notes Note ::
1) Dont use the command directly in cron. Put the command in a file and execute that file in cron.
2) I asked to chmod to 750 so that other will not be able to view your files.
If the file is readable by others anyone may view it and get your DB access very easily.