Setting a MySQL Database Dump Cron Job in cPanel

[EmeraldHike]

I know I've seen the instructions for doing this around the internet over the past few years, but I completely forget how to do it or where the instructions were. My goal is to save backups (database dumps) of my website's MySQL database to my hosting account's home directory. I obviously don't want this file publicly accessible. Also, since the database can get over 50MB, I'd like to zip it, or compress it before it saves to the home directory. I would like to run a cron job once per day and have the new file overwrite the old one. Every so often, I'll FTP in to the home directory and download the database for safe keeping (backup). Can anyone please give me the directions for setting up a compressed MySQL database dump cron job in cPanel?

 

[15Katey]

For creating a cron job in general, you may view this post:

https://gaulard.com/forum/threads/25/

For creating a specific cron command that'll dump your database, compress it, and store it in a specific file, I can help with that. Be warned though, I've tried this command on various servers and it's only worked on some of them. Oddly enough, some cron commands only work with some servers. I am currently running a Linux server with cPanel, so if you've got the same, this might work for you. If not, contact your host and someone should be able to help. Cron commands are oftentimes easy for systems admins to put together.

To create your cron job, log into cPanel and access your Cron Job page. Then, set up the times and days of the week you'd like the job to run. Finally, the create the command, you can use something like this:

mysqldump --user=database_user --password='database_password' database_name | gzip > /home/yourhostingaccount/database_name.sql.gz

Obviously, you'd want to change out the "database_user," "password," database_name," and "yourhostingaccount" variable to your own. I just placed them in there to make things easier.

As you can see the command used gzip to compress the database and then saves it out in the user account of the home directory as an .sql.gz file. I think that's what you wanted.

For more instructions and ideas for creating a database dump cron command, check out these resources:

https://stackoverflow.com/questions/6578533/daily-database-backup-using-cron-job
https://www.a2hosting.com/kb/developer-corner/mysql/mysql-database-backups-using-cron-jobs
https://www.comentum.com/mysqldump-cron.html
https://blog.cpanel.com/how-to-back-up-and-restore-mysql-databases-in-cpanel/

That last link actually covers how to do backups and restores in cPanel. It does give cron command instructions though.

 

[CraigHardy]

How to Set Up a Database Dump Cron Job in cPanel​

This is one I’ve been doing for years with every hosting company I’ve worked with and for some reason, the syntax of the command changes every time. It’s very odd. I have to keep tweaking things to make it work. This command I’ll share with you below works with my latest host. I’m using WHM and cPanel and after visiting the Cron page and filling in the frequency of the database dump, I fill in this code:

mysqldump –user=database_username –password=’database_password’ database_name | gzip > /home/account_username/database_name.sql.gz

Obviously, fill in the “database_username” and similar parts with your own information. As you can see, the zipped dump will be placed in the root directory. Always zip your database dumps because the zipping makes the files so so much smaller and easier to work with. I’m talking like 300 MBs down to 65MBs. It’s crazy.