[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cron job for backing up MySQL database automatically in CPanel X

Posted on 2013-06-05
5
Medium Priority
?
761 Views
Last Modified: 2013-06-06
Hi Experts

I am looking for a simple way to set up a cron job in CPanel X that will automatically back up a MySQL database running on the local machine (ie the same machine that CPanel is running on) on a daily basis.

Please can you provide instructions/details for the best way to go about doing this.

500 points for this one.

Thanks
0
Comment
Question by:wesmanbigmig13
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
RedLondon earned 2000 total points
ID: 39224620
This example works on the basis that your cPanel username is wesmanbi, your database username is wesmanbi_webdata and your database user's password is G%g(pKPZ7*g3

First, create a folder outside of public_html, I'm calling it dbbackups - so you can do that using your FTP client or 'File Manager' in cPanel (putting it there and not *inside* public_html means that nobody can find and download the backup files, but of course if you want to be able to do that, you need to make the folder inside public_html, and then maybe password protect that folder so that the wrong eyes don't see it)

Next, the password needs any special characters escaping with a \ so that they don't get processed as instructions.  That means the password of G%g(pKPZ7*g3  will be entered as G\%g\(pKPZ7\*g3 (ie you put a \ before each non-alphanumeric character)

Log into cPanel and click the 'Cron jobs' icon

On the pulldown "Common settings", select "Once a day"

Into the box at the bottom , "Command", paste this:

date=`date -I`; mysqldump -uwesmanbi_webdata -pG\%g\(pKPZ7\*g3 --all-databases | gzip > /home/wesmanbi/dbbackups/db_wesmanbi_webdata_$date.sql.gz

Note that there's no space between -u and the username, and no space between -p and the password

Click 'Add new cron job', and that's it.

It'll run at midnight every day, creating a backup of your database with the name db_wesmanbi_webdata_2013-06-06.sql.gz today.  I just manually delete the older files once a month, but if you don't want a backlog of previous passwords you can skip the date=`date -I`; part at the start and the _$date part of the filename
0
 

Author Comment

by:wesmanbigmig13
ID: 39224656
Thanks for your excellent instructions. I have modified the script to incorporate my own Cpanel username, mysql dbusername and mysql dbpassword, and have set it up as a cron job in Cpanel and it runs nicely. However, I would like to have the weekday name appended to the filename instead of the date. Do you know how I would do that?

Thanks
0
 

Author Comment

by:wesmanbigmig13
ID: 39224694
I figured the weekday / day of week variable out myself using date=`date +'%A'


Thanks very much for your help,  RedLondon. Much appreciated.
0
 

Author Closing Comment

by:wesmanbigmig13
ID: 39224697
One also needs to ensure that the folder where the database dump file is being written to has to have its permissions set to 775 or higher.

Thanks
0
 
LVL 11

Expert Comment

by:RedLondon
ID: 39226831
Glad it worked for you: took me ages to figure it out when I needed to do it!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 8 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question