Setting up a Backup Database in MySQL

We recommend that you setup a backup database for Chartio analytics. This guarantees your production database will bear no unnecessary load. Below, we’ve sketched out easy process of setting up a backup with regular data dumps.

1. Create the backup database

   Generally should append “_backup” or something to the existing name.

   $: mysql -u root -p
   mysql> CREATE DATABASE mydata_backup;
   mysql> exit
   

2. Load data from old database into new database:

   $: mysqldump -u root –password=mypass mydata | mysql -u root –password=mypass mydata_backup
   

3. Cron this job up to run every day at, say, midnight:

   $: crontab -e
   

   to edit the crontab, add this line:

   0 0 * * * mysqldump -u root –password=mypass mydata | mysql -u root –password=mypass mydata_backup
   

Tada! MySQL database called “mydata” now gets copied to “mydata_backup” everynight at midnight.