Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve MySQL regular automatic backup function under CentOS7, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

We need to implement the following functions:

First: database backup

Second: the database is restored by backup

Third: database backup automation

Fourth: clean up expired backup files

First: automatic backup of database

1. Create a backup directory

Here I chose to put the backup file under / data/backup/mysql and the script under / data/backup:

[root@izwz99z5o9dc90keftqhlrz /] # mkdir-p / data/backup/mysql [root@izwz99z5o9dc90keftqhlrz /] # cd / data/backup

two。 Create a script file

Create mysql_backup.sh

[root@izwz99z5o9dc90keftqhlrz backup] # vi mysql_backup.sh #! / bin/bash# db_name change it here, oh, db_name='baizhan'backup_dir='/data/backup/mysql/'current_time=$ (date +'% Ymuri% mMI% dice% H% M% S') filepath=$backup_dir$current_time'.sql.gz'# does not use $db_password $db_user here Has been written to the configuration file echo 'start exporting database...' mysqldump-- defaults-extra-file=/data/backup/my_mysql.cnf $db_name | gzip > $filepathecho 'exported successfully The file name is:'$filepath

3. New profile

In our current directory, that is, / data/backup

[root@izwz99z5o9dc90keftqhlrz backup] # vi my_mysql.cnf [mysqldump] max_allowed_packet = 400Mhostaccount 127.0.0.1username rootpasswordcards SgDGfsrfEi3 certificates% ugslp% zroomdAP' [mysql] hoststocks 127.0.0.1 usernames rootpasswordletters SgDGfsrfEi3 certificates% ugslp% zroomdAP'

Ladies and gentlemen, please change the above parameters to your own, don't miss this step.

The parameters under mysqldump are used by the exported command, and the parameters under mysql are used when importing.

4. Give file permissions

If you come here, the exported shell script has been written. Let's add executable permissions to this script.

[root@izwz99z5o9dc90keftqhlrz backup] # chmod + x. / mysql_backup.sh

5. Carry out our orders.

[root@izwz99z5o9dc90keftqhlrz backup] # sh. / mysql_backup.sh# check the result [root@izwz99z5o9dc90keftqhlrz backup] # ll. / mysql

6. Extended use

Let's extract the successfully exported file and see the comparison between the original file size and the compressed file size.

[root@izwz99z5o9dc90keftqhlrz backup] # gzip-dc. / mysql/2019-12-22_180359.sql.gz >. / mysql/2019-12-22_180359.sql [root@izwz99z5o9dc90keftqhlrz backup] # ll-sh. / mysqltotal 44K 36K-rw-r--r-- 1 root root 36K Dec 22 18:06 2019-12-22_180359.sql8.0K-rw-r--r-- 1 root root 5.9K Dec 22 18:03 2019-12-22_180359.sql.gz 36Kb and 8Kb if the database is large Can save more space.

So far, our export script is complete, and then it's time to import the script.

Second: the database is restored by backup

1. Create a script file

Following the first step, we should be in the / data/backup directory at this time to continue.

[root@izwz99z5o9dc90keftqhlrz backup] # vi mysqlroomrestore.shangxinqlxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Please enter the .sql.gz file 'exit 1fisqlpictures fileholders ${gz_sql_file%.*} echo' in the unzipped file. 'gzip-dc $base_dir$gz_sql_file > $base_dir$sql_fileecho' unzipped. 'echo' starts importing the database. 'mysql-- defaults-extra-file=/data/backup/my_mysql.cnf $db_name < $base_dir$sql_fileif [- f $base_dir$sql_file] then echo' delete temporary files. Rm-f $base_dir$sql_filefiecho 'Import complete.'

The configuration file in the above code, which is the one we created in the first step, is the same here.

two。 Increase file executable permissions

[root@izwz99z5o9dc90keftqhlrz backup] # chmod + x. / mysql_restore.sh

3. Let's perform the recovery of the database from a backup file

[root@izwz99z5o9dc90keftqhlrz backup] # sh. / mysql_restore.sh. / mysql/2019-12-22_180359.sql.gz

Unzipped the file.

Decompression completed.

Start importing the database.

Delete temporary files.

Import completed.

Here our first parameter can directly write the name of the backup file, not the directory, but because after adding the directory, you can use table for automatic completion, so it is very convenient, other execution methods: in fact, people who know it are all the same.

You can actually restore it in any folder, and you can do it anywhere.

[root@izwz99z5o9dc90keftqhlrz backup] # sh / data/backup/mysql_restore.sh / data/backup/mysql/2019-12-22_180359.sql.gz can also be the same [root@izwz99z5o9dc90keftqhlrz mysql] # pwd/data/backup/mysql [root@izwz99z5o9dc90keftqhlrz mysql] # sh / data/backup/mysql_restore.sh 2019-12-22_180359.sql.gz

Third: database backup automation

1. Add scheduled task

[root@izwz99z5o9dc90keftqhlrz mysql] # crontab-e # paste the following content, and the database is automatically backed up 01 * / data/backup/mysql_backup.sh # every day at 1 am, 12:00 data backup

Fourth: clean up expired backup files

1. Create a delete file script

[root@izwz99z5o9dc90keftqhlrz mysql] # vi remove_backup.sh#/bin/bash # Delete a backup 15 days ago find / data/backup/mysql-type f-mtime + 15 | xargs rm-f

Here, I choose to back up the data only for the last 15 days, and you can modify it according to your needs.

two。 Add executable permissions

[root@izwz99z5o9dc90keftqhlrz mysql] # chmod + x. / remove_backup.sh# manually delete the backup 15 days ago [root@izwz99z5o9dc90keftqhlrz mysql] #. / remove_backup.sh

3. Automatic cleaning

[root@izwz99z5o9dc90keftqhlrz mysql] # crontab-e # the following is the content of the script. Add this sentence 0 1 * / data/backup/remove_backup.sh # at 1: 00 a. M. every day and automatically delete the backup 15 days ago [15 is configured in remove_backup.sh].

Fifth: scheduled task configuration should be posted.

# paste the following content, and the database automatically backs up 0 1 * / data/backup/mysql_backup.sh # every day at 1 am and 12:00 at noon. # this is the content of the script below. Add the sentence 0 1 * / data/backup/remove_backup.sh # every day at 1 am. Automatically delete the backup before 15 days [15 is configured in remove_backup.sh]. The above is all the contents of this article entitled "how to achieve regular automatic backup of MySQL under CentOS7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.