Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

Time:2020-6-28

Because some handy small projects and personal blogs may use MySQL at any time, MySQL is installed on the ECS. Sometimes when the program bugs or the data is deleted by mistake, it’s really hard to find it. I want to write a script to realize the automatic backup of MySQL database data, and regularly delete the previous backup files. This article is very detailed and suitable for Xiaobai. Of course, this is just a very simple data backup, which is not applicable to enterprise projects. For the article about data security, see my previous post: from the recent database deletion event to “change RM command to implement the recycle bin mechanism under Linux”
Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

1. Choose the right disk space

Before backup, try to select a disk with enough space. We use DF command to check the disk space:

df -h     

Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

2. Create backup directory

As can be seen from the above, there is enough space in the home directory / under directory

Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

cd /mkdir backup

You can also choose to create a directory:
Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

3. Create backup script

establish backup.sh Script and edit

vim  backup.sh  Or VI backup.sh

Here is to create directly in the directory where the shell is currently located. I will create the script file in / directory

Enter and press I to enter the insert mode, then you can start to write the script file.

Note: all cases in editing mode are in English input mode.

First, we need to know the MySQL backup command:

mysqldump

There are mainly two ways of writing here, but the principle is the same:

  • Style 1
mysqldump -uusername -ppassword database_name > /backup/database_name_$(date +%Y%m%d%H%M%S).sql

Note: 1username、password、database_ Name is replaced with your own database user name, password, and database name to be backed up

2. database_ name_ $(date +% Y% m% d% H% m% s) is the name of the generated backup file, which can be customized. Here, the file name is database name + underscore + specific time, $(date +% Y% m% d% H% m% s) can get the current date, and% Y% m% d% H% m% s corresponds to the year, month, day, hour, minute and second compression backup respectively:

mysqldump -uusername -ppassword database_name | gzip > /backup/database_name$(date +%Y%m%d%H%M%S).sql.gz

You can also choose to back up all data:

mysqldump -uusername -ppassword --all-databases > /backup/database_name_$(date +%Y%m%d%H%M%S).sql mysqldump -uusername -ppassword --all-databases | gzip > /backup/database_name$(date +%Y%m%d%H%M%S).sql.gz

Please pay attention to space and enter when copying the above code.
Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

  • Writing method 2
db_user="username"
db_password="password"
db_name="database_name"
# the directory for story your backup file.you shall change this dir
backup_dir="/backup"
# date format for backup file (dd-mm-yyyy)
time="$(date +"%Y%m%d%H%M%S")"
#!/bin/bash
mysqldump -u$db_user -p$db_password --all-databases > $backup_dir/sqldata_$time.sql
#!/bin/bash
mysqldump -u$db_user -p$db_password --all-databases | gzip >  $backup_dir/$db_name"_"$time.sql.gz

Back up all data:

#!/bin/bash
mysqldump -u$db_user -p$db_password --all-databases > $backup_dir/sqldata_$time.sql
#!/bin/bash
mysqldump -u$db_user -p$db_password --all-databases | gzip >  $backup_dir/$db_name"_"$time.sql.gz

After script editing, press ESC to exit editing mode, and directly type: WQ to save and exit.

3. Give executable authority

chmod u+x  backup.sh  Or Chmod + X backup.sh

This command needs to be executed in the path where the file exists, or

chmod u+x /direction/backup.shchmod +x /direction/backup.sh

U represents the user
G represents the user group
O for others
A stands for all
Chmod is the abbreviation of change the permissions mode of a file.chmod u+x file.sh Indicates that the file.sh Add executable rights to the owner of the fileChmod + X is the same as Chmod a + X, which means that all users can add executable permissions.

4. Test script

First, manually test the correctness and enforceability of the script we wrote to find the location of the file, or add a path to start the command:

./backup.sh

Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

./backup.sh

Then two warning lines pop up: it doesn’t matter, this is MySQL warning that we have used the password in clear text, ha ha. It matters.
At this time, we will switch to the / backup directory to check whether the script is executed successfully!

Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly

Script executed successfully!
It can be seen that we have backup files, which proves that our script has no problem.

5. Create scheduled backup task

implementCrontab commandIf the command is not found, it means that crontab is not installed. There is a tutorial on the Internet. I will not repeat that my Linux server system is centos7. Crontab has been installed to execute the command:

crontab -e

As with VIM editing, press I in English to enter the insert mode, and then you can add a scheduled task

Crontab format

Time sharing day month week execution order
1-59 minutes in the first column, expressed with * or * / 1 per minute, and 00 or 0 minutes in the whole point
The second train hour 1-23 (0 represents 0 point)
The third Liege 1-31
Month 1-12 of the fourth train
Row 5 week 0-6 (0 for Sunday)
Column 6 command to run.

0 3 * * * /backup.sh, this command representsExecute the script at 3 a.m. every day, you can adjust the time by yourself,Note the absolute path of the script to be used

6. Regularly delete backup files

It’s not good to just back up blindly. No matter how big the disk is, it will run out. Besides, it’s meaningless to save the data long ago. What we need to back up is the latest data in the near future, so it’s necessary to delete the file regularly. We just need to add the following commands to the script file:

#Delete the backup found / backup / MySQL / - name $DB seven days ago_ name"*. sql.gz " -type f
 -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1

#Delete the backup one minute ago find / backup / MySQL / - name $DB_ name"*. sql.gz " -type f
 -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1

-Type F means to find files of common type, f means to find files of common type, not to write.

-Mtime + 7 by file change timeTo find the file, + 7 means the file change time is 7 days ago; if – mmin + 7 means the file change time is 7 minutes ago.

-Exec RM {}; means to execute a shell command. The exec option is followed by the command or script to be executed, followed by a pair of {}, a space and a \, and finally a semicolon;

/dev/null 2>&1Redirect the standard error to standard output and throw it under / dev / null. Generally speaking, all the standard output and standard errors are thrown into the garbage can; the & in it means that the command is executed in the background

This is a screenshot of all commands:

Realize the automatic backup of MySQL database data under Linux, and delete the previous backup files regularly
This is all code, no format, suitable for copy and paste:

mysqldump -uusername -ppassword --all-databases > /backup/mysql/mbook_$(date +%Y%m%d%H%M%S).sql
mysqldump -uusername -ppassword --all-databases | gzip > /backup/mysql/mbook_$(date +%Y%m%d%H%M%S).sql.gz
find /backup/mysql/ -name $mbook"*.sql.gz" -type f -mtime +30 -exec rm -rf {} \; > /dev/null 2>&1
find /backup/mysql/ -name $mbook"*.sql" -type f -mtime +30 -exec rm -rf {} \; > /dev/null 2>&1