Backing Up a MySQL Database

Data Tutorial

We recommend that you set up a backup or slave 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 you should append _backup or something to the existing name.

$ mysql -u root -p
mysql> CREATE DATABASE mydata_backup;
mysql> exit
  1. Load data from old database into new database
    $ mysqldump -u root -password=mypass mydata | mysql -u root -password=mypass mydata_backup
    
  2. 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 every night at midnight.