Menu
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.
433
MySQL has different ways to perform full backups, such as those described earlier in this section. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.
Lazy – Full backup every 168 hours (weekly), Differential every 24 hours: This is ideal for a database with just a few transactions per week or one whose importance is not mission-critical. The loss of a few hours of data can be tolerated or easily recreated.
Automated backups are taken daily, within a 4-hour backup window. The backup starts during the backup window. When possible, schedule backups when your instance has the least activity. During the backup window, automated backups occur every day your instance is running.
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)
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
