How to implement scheduled automatic backup of MySQL under CentOS7

The happiest thing that happens in a production environment is that in some scenarios, it is unavoidable to ignore a parameter when updating or deleting. It’s over, it’s over. I’ve messed up the data. What should I do? I need to go to operations and find a backup! The operation said: Bullshit, our system never gets backed up. You said: Then give me the logs. The operation said: Bullshit, there are GB of logs every day, I haven’t even opened them for you. You said: What should I do, I crashed the database. The operation said: That is your business, it has nothing to do with me... This is a portrayal of a certain company, so today we are going to put an end to this situation

Today I am going to deploy a system for a customer, using MySQL 5.7, but I cannot guarantee the capabilities of the developers on the customer's side. I can only back up all their important files such as startup files and databases automatically in case anything goes wrong. We can accomplish it with just one line of command, which is a responsible approach. Getting Started:

We need to implement the following functions:

First: Database backup

Second: Database recovery through backup

Third: Database backup automation

Fourth: Clean up expired backup files

First: Automatic database backup

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

2. Create a script file

Create mysql_backup.sh

[root@izwz99z5o9dc90keftqhlrz backup]# vi mysql_backup.sh
 
#!/bin/bash
# db_name change it yourself db_name = 'baizhan'
backup_dir='/data/backup/mysql/'
current_time=$(date +'%Y-%m-%d_%H%M%S')
filepath=$backup_dir$current_time'.sql.gz'
#$db_password $db_user is not used here, it has been written into the configuration file echo 'Start exporting the database...'
mysqldump --defaults-extra-file=/data/backup/my_mysql.cnf $db_name | gzip > $filepath
echo 'Export successful, file name: '$filepath

3. Create a new configuration file

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

[root@izwz99z5o9dc90keftqhlrz backup]# vi my_mysql.cnf
 
[mysqldump]
max_allowed_packet = 400M
host=127.0.0.1
user=root
password='SgDGfsrfEi3#@%#%ugslp%z!dAP'
[mysql]
host=127.0.0.1
user=root
password='SgDGfsrfEi3#@%#%ugslp%z!dAP'

Dear readers, please change the above parameters to your own, don't miss this step.

The parameters under mysqldump are used for the export command, and the parameters under mysql are used when importing.

4. Give file permissions

At this point, the exported shell script has been written. Let's add executable permissions to this script

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

5. Execute our command

[root@izwz99z5o9dc90keftqhlrz backup]# sh ./mysql_backup.sh
# Check the results [root@izwz99z5o9dc90keftqhlrz backup]# ll ./mysql

6. Extended use

Let's decompress the successfully exported file and compare the original file size with 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 ./mysql
total 44K
 36K -rw-r--r-- 1 root root 36K Dec 22 18:06 2019-12-22_180359.sql
8.0K -rw-r--r-- 1 root root 5.9K Dec 22 18:03 2019-12-22_180359.sql.gz
 Comparison between 36Kb and 8Kb. If the database is large, it can save more space.

So far, our export script has been completed, and the next step is to import the script

Second: Database recovery through backup

1. Create a script file

Following the first step, we should now be in the /data/backup directory. Continue

[root@izwz99z5o9dc90keftqhlrz backup]# vi mysql_restore.sh
#!/bin/bash
if [ -z $1 ] || [ ! -f $1 ]
then
 echo "Please enter the sql compressed file (*.sql.gz)"
 exit 1
fi
#Enter your own database name db_name='jx_guides'
base_dir='/data/backup/mysql/'
gz_sql_file=`basename $1`
file_ext=${gz_sql_file##*.}
if [ $file_ext != 'gz' ]
then
 echo 'The file format is incorrect, please enter a .sql.gz file'
 exit 1
fi
sql_file=${gz_sql_file%.*}
echo 'In the decompressed file...'
gzip -dc $base_dir$gz_sql_file > $base_dir$sql_file
echo 'Decompression completed.'
echo 'Start importing database...'
mysql --defaults-extra-file=/data/backup/my_mysql.cnf $db_name < $base_dir$sql_file
if [ -f $base_dir$sql_file ]
then
 echo 'Delete temporary files.'
 rm -f $base_dir$sql_file
fi
echo 'Import completed.'

The configuration file in the above code is the configuration file we created in the first step, and it is used here in the same way.

2. Increase file executable permissions

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

3. Let's restore the database through the backup file

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

Unzip the file...
Decompression completed.
Starting to import database...
Delete temporary files.
Import completed.

Here, our first parameter can directly write the name of the backup file without writing 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 understand know that they are all the same

In fact, you can restore in any folder, and execute this in any location

[root@izwz99z5o9dc90keftqhlrz backup]# sh /data/backup/mysql_restore.sh /data/backup/mysql/2019-12-22_180359.sql.gz
 You can also do this [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 a scheduled task

[root@izwz99z5o9dc90keftqhlrz mysql]# crontab -e
 
# Paste the following content, automatic database backup 0 1,12 * * * /data/backup/mysql_backup.sh # Back up data once every day at 1:00 a.m. and 12:00 p.m.

Fourth: Clean up expired backup files

1. Create a script to delete files

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

I choose to only keep the latest 15 days of backup data here. You can modify it as needed.

2. Add executable permissions

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

3. Automatic Cleanup

[root@izwz99z5o9dc90keftqhlrz mysql]# crontab -e
 
# The following is the script content. Add this sentence 0 1 * * * /data/backup/remove_backup.sh # At 1 a.m. every day, automatically delete the backup 15 days ago [15 is configured in remove_backup.sh]

Fifth: Post the scheduled task configuration

# Paste the following content, automatic database backup 0 1,12 * * * /data/backup/mysql_backup.sh # Back up data once every day at 1 a.m. and 12 noon # The following is the script content, add this sentence 0 1 * * * /data/backup/remove_backup.sh # Every day at 1 a.m., automatically delete the backup 15 days ago [15 is configured in remove_backup.sh]

Summarize

The above is the implementation method of scheduled automatic backup of MySQL under CentOS7 introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in: