How to write a MySQL backup script

Preface:

The importance of database backup is self-evident, especially in a production environment, where any data loss may have serious consequences. Therefore, no matter what the environment is, we should have a corresponding backup strategy to back up the database regularly. In MySQL, the more commonly used logical backup tool is mysqldump. This article will introduce the method of scheduled backup of MySQL.

1. Develop a suitable backup strategy

For different database environments, we should consider different backup strategies. When developing a backup strategy, you should consider the following factors:

• Physical backup or logical backup. This can be determined by the size of the database. For example, if it is less than 100G, use logical backup, and if it is larger than 100G, use physical backup.
• Backup file retention period. This may be determined by the size of the disk and is generally retained for at least 7 days.
• The time when the backup was performed. It is usually performed during off-peak hours, such as early morning when backup operations are performed.
• Backup interval. It is generally recommended to back up once a day. If the system is not too important, the backup interval can be extended.
• Is there a slave library? If there is a slave database, it is recommended to back up the data on the slave database to reduce the pressure on the master database.
  

2.Linux system backup script

In Linux system, we can use crontab to execute backup scripts. If you don’t know much about crontab, you can refer to the following introduction to learn quickly.

Crontab is a command commonly used in Unix and Unix-like operating systems, used to set instructions to be executed periodically.

Format:

* * * * * command
Time-sharing daily, monthly and weekly commands

The first column indicates the minutes from 1 to 59. Each minute is represented by * or */1. The second column indicates the hours from 1 to 23 (0 indicates 0 o'clock).
The third column represents the date 1 to 31
The fourth column represents the months 1 to 12
Column 5 identifies the day of the week from 0 to 6 (0 for Sunday)
Column 6 The command to be run

crontab -e Edit the scheduled task settings under this user
crontab -l lists all scheduled tasks under this user

Now let's write the backup script. Without further ado, here is the script template:

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: mysql_backup.sh 
# Describe: Used for database backup
# Revision: 1.0
# Date: 2020/08/11
# Author: wang

# Set the mysql login username and password (fill in according to actual situation)
mysql_user = "root"
mysql_password = "yourpassword"
mysql_host = "localhost"
mysql_port = "3306"
backup_dir = /data/mysql_backup

dt=date +'%Y%m%d_%H%M'
echo "Backup Begin Date:" $(date +"%Y-%m-%d %H:%M:%S")

# Back up all databasesmysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -R -E --all-databases --single-transaction > $backup_dir/mysql_backup_$dt.sql

find $backup_dir -mtime +7 -type f -name '*.sql' -exec rm -rf {} \;
echo "Backup Succeed Date:" $(date +"%Y-%m-%d %H:%M:%S")

The above script can be modified according to actual conditions, such as backing up a certain library, changing the retention time, etc. After writing the script, pay attention to debugging. After debugging is completed, you can deploy it. For example, if we plan to back up at 2 am every day, we can set a scheduled task like this.

# Pay attention to the script execution permission and modify the script path 00 02 * * * sh /root/scripts/mysql_backup.sh > /root/scripts/mysql_backup.log 2>&1

3. Windows system backup script

The Windows system backup script is similar, except that it becomes a bat script and needs to be scheduled to execute at a scheduled time. For example, we can create a MySQLdata_Bak directory in the E drive, and create a mysql_backup directory in this directory to store backup files. mysql_bak.bat is the backup script. The script content is as follows (automatically delete backup files 7 days ago):

rem auther:wang
rem date:20200811
rem ******MySQL backup start********
@echo off
forfiles /p "E:\MySQLdata_Bak\mysql_backup" /m backup_*.sql -d -7 /c "cmd /c del /f @path"
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%"
"E:\mysql5.7.23\bin\mysqldump" -uroot -p123456 -P3306 --default-character-set=utf8 -R -E --single-transaction --all-databases > "E:\MySQLdata_Bak\mysql_backup\backup_%Ymd%.sql"
@echo on
rem ******MySQL backup end********

The above script is for reference only and can be slightly modified according to your own environment. Similarly, after the script is debugged, it can be added to the scheduled task. If you don’t know much about Windows scheduled tasks, you can search it on Baidu, which is also quite convenient and simple.

4. Backup availability check

In addition to backup, a very important thing is to verify the availability of the backup data. Imagine how painful it would be if you suddenly found that all the backup data you had saved were invalid when you needed to recover the data. Many friends write backup scripts and add them to scheduled tasks. Then they just check whether the scheduled tasks are executed and whether there are files in the backup directory. They often find that there are problems with the backup data when they need to use the backup files.

Currently, there is no very convenient way to verify the data of backup files. The more commonly used method is to pull out the backup files regularly for backup and recovery drills. For example, doing a backup and recovery drill once a month can effectively improve the availability of backup files and give you peace of mind.

Therefore, do not think that having a backup is foolproof. You should also check whether the backup script is executed correctly and whether the content of the produced backup script is available. It is best to perform recovery drills regularly.

Summarize:

This article mainly shares the MySQL scheduled backup script under Linux and Windows systems. The script content is relatively simple and clear, and the functions are not complicated. If you have other backup requirements, you can modify and improve it on this basis. If this article is helpful to you, please share and forward it.

The above is the detailed content of how to write MySQL backup script. For more information about MySQL backup script, please pay attention to other related articles on 123WORDPRESS.COM!