Taking periodic backups of a MySQL database

Question

Hi guys, 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.

Answer 1

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)

Reference http://www.bradtrupp.com/mysql-backup-cron.html

Answer 2

In addition to mysqldump, there are a few other backup options. These are mainly to work around the mysqldump causes highload/long reload times.

1. There's InnoDB hot backup, it's a pay product by the makers of InnoDB to backup in an online fashion. It's also harder to backup InnoDB tables than MyISAM, because with the latter, if the table is flushed, you can just copy the files, but the former has issues.
2. If your backup's can take instantaneous snapshots of your harddrive, FLUSH TABLES WITH READ LOCK will flush out your writes to disk so your backups are in a consistent state. However, this will block all reads, until your done, so if they aren't instantaneous, it won't be good.

Answer 3

You should also take a look at: http://www.percona.com/docs/wiki/percona-xtrabackup:start