Difference between revisions of "Automate MySQL Database Backup via Cronjob"
Docs admin (Talk | contribs) |
Docs admin (Talk | contribs) |
||
Line 1: | Line 1: | ||
Automating a MySQL Database backup in cPanel is a two step process. | Automating a MySQL Database backup in cPanel is a two step process. | ||
+ | <br> | ||
+ | {{warning|This cron will continue to create backup files until your disk space is completely filled up. In order to prevent this, you will want to create a logrotate file and create a cronjob to automatically rotate out the older backups, to keep disk space down.}} | ||
+ | <br><br> | ||
==Create the cron entry in cPanel== | ==Create the cron entry in cPanel== | ||
Line 16: | Line 19: | ||
<br> | <br> | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
− | date=`date -I`; /usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/ | + | date=`date -I`; /usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname$date.sql |
</syntaxhighlight> | </syntaxhighlight> | ||
<br><br><br> | <br><br><br> | ||
As an example, the cPanel user is example, Database username is myusername, database name is mydatabase, password is mypassword. The command to enter into the cron entry is | As an example, the cPanel user is example, Database username is myusername, database name is mydatabase, password is mypassword. The command to enter into the cron entry is | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
− | date=`date -I`; /usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/ | + | date=`date -I`; /usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql |
</syntaxhighlight> | </syntaxhighlight> | ||
− | + | <br><br> | |
+ | This will create a sql file in your /home/cpanelusername directory with a name similar to mydatabase_20180101.sql. | ||
+ | <br> | ||
==Configure the Log rotation== | ==Configure the Log rotation== |
Revision as of 16:15, 6 June 2018
Automating a MySQL Database backup in cPanel is a two step process.
Create the cron entry in cPanel
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.
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.
date=`date -I`; /usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname$date.sql
As an example, the cPanel user is example, Database username is myusername, database name is mydatabase, password is mypassword. The command to enter into the cron entry is
date=`date -I`; /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 similar to mydatabase_20180101.sql.