Solved

Cron job for backing up MySQL database automatically in CPanel X

Posted on 2013-06-05
5
753 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
[X]
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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 37
PHP SMTP authentication 6 25
Why is my $_POST not going to results page 10 36
How do I post more than 1 item to php backend 24 30
Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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