Solved

Cron job for backing up MySQL database automatically in CPanel X

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

707 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