Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Cron job for backing up MySQL database automatically in CPanel X

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SMB share across internet 15 65
Linux FTP Error 5 33
Why does Opencart Use Product tables use the MyISAM storage Engine 4 36
$_SERVER Variable question 31 27
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

856 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