Mysql – Taking periodic backups of a MySQL database

backupmysql

I need to know how can I backup my MySQL databases on a periodic basis and have the backups downloaded to my local server. I'm using navicat and it has an easy backup interface and tasks schedule facility but it backs up the db and stores the backup on the server itself. I would like to have a way to download the backups once made to my local system rather than have them on the same server as the database.

Best Solution

Setup a server cron which runs mysqldump command after some interval (e.g. 24 hours)

mysqldump -hMY_HOST.COM -uDB_USERNAME -pDB_PASSWORD USERNAME_DATABASENAME > MysqlDump.sql

After creating dump file. Setup another cron to copy this dump to target server(preferably local) make this execute with same interval of above cron.

scp user@MY_HOST.COM:/some/path/file user2@MY_HOST2.COM:/some/path/file

NOTE: This commands may cause high server load (make sure you are executing them when server having minimum load)