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

Copyright NOTICE: This article for TESTCS_DN (micro-wx laughter) original article, non-commercial free reprint-keep the Attribution-note the source, thank you.

Directory (?) [+]

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-HFilesystemSizeUsedAvailUse% Mounted on/dev/mapper/volgroup-Lv_root50G46G1.6G97% /Tmpfs1.9G92K1.9g 1%/dev/shm/dev/sda1 485m 39m 421m 9%/boot/dev/mapper/VolGroup-lv_home 534G 3.6g 503 g 1%/home      

• 1
• 2
• 3
• 4
• 5
• 6

• 1
• 2
• 3
• 4
• 5
• 6

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

• 1
• 2
• 3

• 1
• 2
• 3

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

• 1

• 1

Enter/paste the following:

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

• 1
• 2

• 1
• 2

To compress a backup:

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

• 1
• 2

• 1
• 2

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

• 1

• 1

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

./bkDatabaseName.sh

• 1

• 1

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

# crontab-bash: crontab: command not found

• 1
• 2

• 1
• 2

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

• 1

• 1

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

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

• 1

• 1

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

• 1

• 1

The output looks like this:

Sep3014:01:Bogon Run-parts (/etc/cron. hourly) [2503]: Starting0anacronSep3014:01:Bogon Run-parts (/etc/cron. hourly) [2512]: Finished0anacronSep3015:01:Bogon crond[3092]: (Root) CMD (Run-parts/etc/cron. Hourly) Sep3015:01:Bogon Run-parts (/etc/cron. hourly) [3092]: Starting0anacronSep3015:01:Bogon Run-parts (/etc/cron. hourly) [3101]: Finished0anacronSep3015:30|Bogon crontab[3598]: (Root) BEGIN EDIT (root) Sep3016:01:Bogon crond[3705]: (Root) CMD (run-parts/etc/cron.hourly) Sep 30 16:01:01 Bogon run-parts (/etc/cron.hourly) [3705]: Starting 0anacronsep 30  16: 01:01 bogon run-parts (/etc/cron .hourly) [3714]: finished 0anacronsep 30 Span class= "Hljs-number" >16:15:29 bogon crontab[3598": (Root) END EDIT (root)           

How to implement MySQL database to automatically backup scheduled backups on a daily basis under Linux