Difference between revisions of "Automate MySQL Database Backup via Cronjob"

From Acenet Knowledgebase
Jump to: navigation, search
Line 55: Line 55:
  
 
<syntaxhighlight lang="bash">
 
<syntaxhighlight lang="bash">
30 2 * * * /usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1
+
/usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1
 
</syntaxhighlight>
 
</syntaxhighlight>
 
<br>
 
<br>
This will run the rotation at 2:30 in the morning daily, using the my.conf file for the instructions.
+
This will run the rotation at the specified time daily, using the my.conf file for the rotation instructions.<br><br>
  
 
After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.
 
After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.

Revision as of 13:16, 6 June 2018

Automating a MySQL Database backup in cPanel will take a few minutes of work, but only needs to be set up once.

The backup cron will continue to create backup files until your disk space is completely filled up.
In order to prevent this, you will create a logrotate file as well as another cronjob entry to automatically rotate out the older backups, to keep disk space down.


Create the Backup cron job in cPanel


You will need to know your Database username and database password in order to configure the backup.



1) Log into your cPanel

2) Go to Advanced -> Cron Jobs

3) Enter the email you wish to email the results of the cron job to.

4) Scroll down a bit, and either select a common setting (Once a day, once a week, etc) from the drop down. or enter a custom entry for days, weeks, months, etc.

Mysqlcron.png

5) For the command to run, enter the following command. Replace CPANELUSERNAME with your cpanel username. Replace DBUSERNAME with your database username. Replace PASSWORD with the database password, and CPANELUSERNAME with your cPanel username.

/usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname_$date.sql


For Example, cPanel username is example, database name is mydatabase, and database password is mypassword

/usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql


This will create a sql file in your /home/cpanelusername directory with a name of: mydatabase_20180101.sql

6) Click the blue "Add New Cron Job" button when done.

Create the Log Rotation config file

You will need to create a logrotate directory under your home directory, and a my,conf fike to enter the directives you will need. Here are the contents of a sample my.conf file:

/home/CPANELUSER/mydatabase*.sql {
daily
rotate 3
compress
}

This is set to rotate daily, keeping the 3 newest backups. They will be called something similar to:
mydatabase_20180103.sql.gz
mydatabase_20180102.sql.gz
mydatabase_20180101.sql.gz

Set up the Log Rotation cron job

This will be similar to Step 1, where you set up the mysql database backup. The command to enter would be similar to:

/usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1


This will run the rotation at the specified time daily, using the my.conf file for the rotation instructions.

After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.