If you have a website running WordPress that relies on a MySQL database, you probably should be backing up your MySQL database on a daily basis. Here are the steps to create an automatic solution to do a MySQL backup and sends the results to your Email on a daily basis. I assume that you are using a web host that has the control panel cpanel.
Setup Backup Directory
Login to your hosting control panel and create a new folder directory named backup in your home folder directory using the File Manager application.
Create MySQL Backup to Email Script
Create a file named mysql_backup in your /home/USERNAME/backup folder directory created above. In this file insert the following code: [sourcecode language=“bash”]#!/bin/bash # CLEAN UP SENT FOLDER rm /home/USERNAME/sent # MySQL BACKUP mysqldump DATABASE –user=MYSQL_USERNAME –password=MYSQL_PASSWORD | gzip > /home/USERNAME/backup/mysql_backup.sql.gz ; mutt -s “MySQL Backup” -a /home/USERNAME/backup/mysql_backup.sql.gz – EMAIL_ADDRESS < /dev/null [/sourcecode] In the code above you need to substitute your username, database name, MySQL username, MySQL password, and Email Address. The code that needs to be substituted are all listed in UPPERCASE. Please note that part of the code above deletes your sent email. If you don’t want to do this, please remove this line. I do this because the sent email gets quite large every time a backup runs and it is wasting storage space. Lastly you need to change permissions of this file so that is executable. In the File Manager:
- click on the mysql_backup file you just created
- click the Change Permissions button
- click the 3 boxes next to Execute
- click Change Permissions
Schedule the Daily MySQL Backup
To schedule the daily MySQL backup you need to go to the home of your control panel and click on Cron jobs in the advanced section. On this screen you need to schedule the job using the template listed to the right. This will run your mysql_backup script every day at midnight and email you with the MySQL backup file gzip.