Cron job to backup mysql database

Open terminal and type:
sudo tcsh

pico /etc/crontab
or
nano /etc/crontab

And add one of the following lines depending on your situation. This schedule the backup on 1am every day.

Remote Host Backup with linked PATH to mysqldump:
0 1 * * * mysqldump -h mysql.host.com -uusername -ppassword --opt database > /path/to/directory/filename.sql

Remote Host Backup:
0 1 * * * /usr/local/mysql/bin/mysqldump -h mysql.host.com -uusername -ppassword --opt database > /path/to/directory/filename.sql

Local Host mysql Backup:
0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword --opt database > /path/to/directory/filename.sql

(There is no space between the -p and password or -u and username - replace root with a correct database username.)

For Information Contact:
Comentum Corp
6222 Ferris Sq.
San Diego, CA 92121
Phone: 858-410-1500
Fax: 858-410-0707

Sales: 800-387-1920
Hours: Mon. - Fri., 9 a.m. - 5 p.m. PST

Contact Us »

Mobile App Development

iPhone, Google Android, BlackBerry, Windows Mobile
In-house development team.
San Diego, CA, USA.

Learn More

Web App Estimator

Select and unselect modules such CMS or eCommerce for your web application and watch the cost update in real time.

Try It »

You may not have provided enough information for us to be sure. You may want to advise WHICH cron file this is in (ie system crons - /etc/crontab for example, have an extra field with a username - although I suspect that's not the problem. There are also different implementations of cron - it would be useful to know which you used, alongside which OS)

First thing I would do is add a path - ie change mysqldump to /usr/bin/mysqldump assuming that that is the correct path to it for your system.

It is not clear why you would expect to receive emails from this command. Do you have a command higher up in the crontab with a line like MAILTO=? Even with this though, if the mysqldump command does not produce any output it won't send you an email.

You might want to try bang this into a batch file and call that instead, along with some echo statements. Also, redirecting stderr to stdout can be useful (ie add 2>&1 to the end of the command in the batch file).

You say there is nothing under etc logs or var folders - but have you looked in the system logs (eg /var/log/messages, /var/log/syslog) for output from crontab?

As an aside, I put to you that hard coding your password on the command line is a bad idea from a security POV. You should drop the username and password from the command line and ad it to .my.cnf - see https://serverfault.com/questions/358903/store-the-mysql-client-password-or-setup-password-less-authentication

Hi guys ! In this write up, we will see how to automate the mysql backup using the cronjob.

Create Directory & File

At first, we need to create a directory to make that our working directory. In this case I am doing that on /var directory

Let’s do this like this

sudo mkdir -p /var/db-db-backup

Make a Script

Next we need to write a bash script to make things done. We will name that say “backup_script.sh”

- Advertisement -

Also, give it the proper execution permission.

cd /var/db/db-backup
touch backup_script.sh
chmod u+x backup_script.sh

Now the main script.

After writhing the script, it will look like this

### SECTION 1 set up all the mysqldump variables
DATE=`date +"%d_%b_%Y_%H%M"`
SQLFILE=/var/db//db-backup/db-backup_$(date +%Y%m%d-%H-%M-%S).sql
DATABASE=<database_name>
USER=<db_user>
PASSWORD=<db_user_password>

### Section 2 if you need to run this more than once a day,
# remove the previous version of the file
unalias rm     2> /dev/null
rm ${SQLFILE}     2> /dev/null
rm ${SQLFILE}.gz  2> /dev/null

### Section 3 mysql database backup (dump)
sudo mysqldump -u ${USER} -p${PASSWORD} ${DATABASE}|gzip > ${SQLFILE}.gz

There are 3 different sections of the contents of this file, let’s have a look:

Section 1

The first section allows you to set specific values, which suit your environment into variables that are later used in the other two sections of the same file.

• DATE

To append the current date & time to the name of the backup file in order to facilitate you in identifying any required file later by just having a look at its name.

• SQLFILE

To let you set the path and file name of the backup file, appending the current date & time (calculated by previous variable) for your ease.

• DATABASE

To specify which database is to be backed up. This is used in the MySQL script for taking database dump/backup.

• USER

To let you set the database username that has access to take a dump of the previous mentioned database.

• PASSWORD

To mention the password of the database user that you chose to provide in the previous variable.

Section 2:

The second section helps you remove any database with the same name. You won’t really need this section when you are appending the date and time parameters to your file’s name, but if you are using a general name for the file and you wish your script to override the previous file, then you may include this section to your script.

Section 3:

The third section uses the variables of the first section and executes the mysqldump command to create an exported gzip file. Please note that you need to place the database password in this file that you might not want in some cases, however there exists an alternate method to dump the database without providing the password in the script. To do so, you need to edit the my.cnf file of your mysql configuration. The file might be in 5 or more locations, all are valid because they all load cascading.

• /etc/my.cnf
• /etc/mysql/my.cnf
• $MYSQL_HOME/my.cnf
• [datadir]/my.cnf
• ~/.my.cnf

Edit the file and locate the [mysqldump] section in it.

Now, place the password in this file section just below the user, like the one displayed below:

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
user=root
password=backup$unixcop!

Save this file and restart the mysql service using the following command:

sudo systemctl restart mysql

Test & Verify

Traverse to the script’s folder & run the script

cd /var/db/db-backup
./backup_script.sh

If your script is working correctly, you should have a backup file created in your /var/db/db-backup folder.

If there is no such file, it is likely that you have missed something in the aforementioned commands or you have some user permission restrictions that might require you to use sudo with each command.

Schedule with Cron

Edit the server’s cron with the undermentioned command:

sudo crontab -e

Enter the following line at the bottom of this file:

0 1 * * * /var/db/db-backup/backup_script.sh

The above line in cron will run at 1:00 AM server time every day. If you want to schedule it differently, you may modify it as per your need.

How do I automatically backup MySQL database?

How do I create a cron backup?

How do I schedule a backup of MySQL database in Linux?

Which command is used to backup MySQL data?