Solved

Cron job for backing up MySQL database automatically in CPanel X

Posted on 2013-06-05
5
744 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 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
AWS EC2 Linux 1 32
linux boot fsck problem 3 43
Fixing Non-Standard characters in text 8 31
MySQL left join performance 4 9
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
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 …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now