Knowledgebase
How to automatically back up your databases
In this article we'll provide you with a brief outline of how to back up your MySQL database automatically. The solution that we'll suggests consists of adding on your hosting account a script file with a command for backing up database(s) and adding a cron job which executes that file.
- First you need to create a file that will contain the command for database backups. You can do this from the Files section of the Pixie control panel, or you can create it on your local computer with a text editor (e.g. Notepad, Wordpad) and then upload it with an FTP client. You can name the file backup.sh (the name itself can be different but use the extension .sh).
- In the file backup.sh you have to put a command for exporting your database(s). The command has the following form:
mysqldump -u(username) -p(password) (database name) > (name of database dump file)
You have to replace (username) and (password) with the username and password respectively of a database user that has access to the particular database. You can add and manage databases and database users from the Databases section of the Pixie control panel. Replace (database name) with the name of the database that you want to back up.
In the place of (name of database dump file) put the path on the hosting account where you want the backups to be stored and also a name for the database dump file. The name of the dump file itself can be anything you want but it should end with the extension .sql. If, for example, you create a folder backups in the private_files directory on the account, then in the command in the place of (name of database dump file) you should put /home/username/private_files/backups/database_dump.sql (the default location of the private_files directory on HostKnox accounts is /home/username/private_files). You have to replace username with your actual hosting account username. Note that if you don't put any date variables in the name of the dump file, then each time a database is backed up it will automatically delete and replace the previous backup.
- After you put the database backup command in the backup.sh file you have to upload the file on your hosting account (provided you didn't create it directly on the account). By default, HostKnox hosting accounts have a folder private_files that's suitable for such cases. You can put the file in that folder so that the path to it will become /home/username/private_files/backup.sh (replace username with your real username).
- Add a cron job that executes the backup.sh file. To do this, go to the Cron Jobs section of the Pixie control panel. Click on the Add Cron Job button on the right, on the following page select how often you want the cron job to be performed and put the command for executing the backup.sh file in the Command field. Then save the cron job. The cron job command should be:
sh (path to script file for database backup)
where (path to script file for database backup) should be replaced with the actual path to the backup.sh file. So using our example it should be sh /home/username/private_files/backup.sh.
For some more details you can also read the tutorial on how to back up databases automatically. There you'll also find how to avoid overwriting the previous database dump file, as well as how to back up more than one or all databases in one dump file.
In case you include date variables in the name of the database dump file (in the command for backing up databases), then you may also need to delete older dump files periodically. Otherwise, gradually you'll accumulate a lot of old backups that you probably won't need. For example, you can delete backups that are older than a week. To learn how to do this automatically check out the article on how to delete old database backups automatically.