Automatic backup?

[Skipper]

I'm sure this seems like a pretty rudimentary question, but how do I set my MySQL database to back itself up weekly? Also, is there any way to automatically download the backup file, or at least have it sent to my email account?

[Ralph]

Hi,

You can schedule mysql backup using cron. Please use following script to take backup using cron. Create file in public_html and correct the permissions/ownership.

#vi /home/cPanel-User/public_html/Script-For-Backup.sh

Add following code into the above mentioned file.

#/bin/bash
/usr/bin/mysqldump –user=Database_User –password=***** –databases Database_Name | gzip > /home/cPanel-User/db_name-`date “+%Y%m%d%H%M”`.sql.gz

Note :-

Database_User is the database user
Database_Name is the database name
***** is the password of database user
+%Y%m%d%H%M is the time stamp
cPanel-User is username of account

The backup file will create under directory "/home/cPanel-User/". Now, you need to add cron. You can do it with the help of your control panel.

0 0 * * * /home/cPanel-User/public_html/Script-For-Backup.sh

This cron will executes every day at 00:00 hour and creates backup file for your MySQL.

=========
Ralph Debus

[Skipper]

I understand how to set up the cron job, but I'm a little confused about the file: what do I need to set the permissions to, exactly? And is the code you quoted the only thing that should go into the Script-For-Backup.sh file? Also, what am I supposed to put for the time stamp (or do I leave it how it is)?

Sorry if I'm being thick-skulled, and thanks for the help!

[Matthew]

the file (script) should be executable by the user (cpanel-user).
You can give execute permissions to this file from your Cpanel >> File Manager or chmod command if using ssh. 700 permissions would be sufficient to execute file by cron.

First create and assign a DB user to the DB you want to backup with all privileges from your Cpanel >> Mysql section and use that details in the script file.

%Y = stands for year
%m = stand for month
%d = stands for date
%H = stands for hour
%M = stand for minute

you can exclude any of it if not needed or leave it as it is for more detailed timestamps.

[Skipper]

Originally Posted by Matthew

the file (script) should be executable by the user (cpanel-user).
You can give execute permissions to this file from your Cpanel >> File Manager or chmod command if using ssh. 700 permissions would be sufficient to execute file by cron.

First create and assign a DB user to the DB you want to backup with all privileges from your Cpanel >> Mysql section and use that details in the script file.

%Y = stands for year
%m = stand for month
%d = stands for date
%H = stands for hour
%M = stand for minute

you can exclude any of it if not needed or leave it as it is for more detailed timestamps.

Okay, I understand what the parts of the timestamp stand for. I'm just wondering what purpose they serve, and whether I need to substitute any specific values for them or just leave them as they are.