How to implement MySQL database automatically backup scheduled backups every day under Linux

Overview

?? Backup is the basis of disaster recovery, which is the process of copying all or part of the data collection from the application host's hard disk or array to other storage media to prevent the system from operating errors or system failures that result in data loss. And for some websites, the system, the database is everything, so good database backup is crucial!

What is a backup?

Why to back up

Disaster Tolerance Plan Construction

Storage media

Disc
Tape
Hard disk
Disk array
DAS: Direct Attach Storage
NAS: Network-attached storage
SAN: Storage Area Network
Cloud storage

This is mainly a local disk for storage media to talk about the addition of scheduled tasks, basic backup scripts, other storage media only media access may not be the same way.

1. Check the disk space situation:

Since it is scheduled backup, it is necessary to choose a space sufficient disk space, to avoid the lack of space caused by the backup failure, the consequences of data loss!
Store to current disk This is the simplest, but the least recommended, the server has more than one hard disk, it is best to store the backup to another hard disk, the choice of better and more secure storage media;

#DF-hFilesystem                    Size  used Avail  Use% mounted on/Dev/Mapper/VolGroup-Lv_root    -G    $G  1.6G   the% /Tmpfs                         1.9G    theK  1.9G   1%/dev/shm/Dev/sda1                     485M    theM  421M   9%/boot/Dev/Mapper/VolGroup-Lv_home  534G  3.6G  503G   1% /home

2. Create a backup directory:

Above we use the command to see Home/home under sufficient space, so you can consider the home/home save backup file;

cd /homemkdir backupcd backup

3. Create a backup shell script:

Note Replace the databasename in the following command with the actual database name;
Of course, you can also use the naming rules in fact!

vi bkDatabaseName.sh

Enter/paste the following:

#!/bin/bashmysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

To compress a backup:

#!/bin/bashmysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

Note:
Replace the username with the actual user name;
Replace the password with the actual password;
Replace the DatabaseName with the actual database name;

4. Add executable permissions:

chmod u+x bkDatabaseName.sh

Add executable permissions to execute first, see if the script is wrong, can not be used properly;

./bkDatabaseName.sh

5. Add a scheduled task to detect or install Crontab

Confirm that the crontab is installed:
Execute the crontab command if you do not found, it indicates that there is no installation

# crontabcommand not found

If you do not install crontab, you need to install it first, the specific steps please refer to:
Install the Scheduled task program under CentOS using the Yum command crontab
Install the scheduled task program from the CentOS system disk using the RPM command crontab

Add a Scheduled task

Execute command:

-e

You can edit the scheduled tasks just as you would with the VI editor.
Enter the following and save:

*/1 * * * * /home/backup/bkDatabaseName.sh

What does it mean exactly?
This means that the shell script "/home/backup/bkdatabasename.sh" executes once every minute.

6. Test whether the task is performed

Quite simply, we execute the "ls" command several times to see if the file has been created in a minute.

If the task execution fails, you can view the task log with the following command:

# tail -f /var/log/cron

The output looks like this:

Sep -  -: on: onBogon Run-parts (/etc/cron. Hourly)[2503]: Starting0Anacronsep -  -: on: onBogon Run-parts (/etc/cron. Hourly)[2512]: Finished0Anacronsep -  the: on: onBogon crond[3092]: (Root) CMD (Run-parts/etc/cron. Hourly) Sep -  the: on: onBogon Run-parts (/etc/cron. Hourly)[3092]: Starting0Anacronsep -  the: on: GenevaBogon Run-parts (/etc/cron. Hourly)[3101]: Finished0Anacronsep -  the: -: -Bogon crontab[3598]: (Root) BEGIN EDIT (root) Sep -  -: on: onBogon crond[3705]: (Root) CMD (Run-parts/etc/cron. Hourly) Sep -  -: on: onBogon Run-parts (/etc/cron. Hourly)[3705]: Starting0Anacronsep -  -: on: onBogon Run-parts (/etc/cron. Hourly)[3714]: Finished0Anacronsep -  -: the: inBogon crontab[3598]: (Root) END EDIT (Root)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

How to implement MySQL database automatically backup scheduled backups every day under Linux