Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to write a MySQL backup script. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Foreword:

The importance of database backup is self-evident, especially in the production environment, any loss of data may have serious consequences. Therefore, no matter what the environment, we should have the 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 regular backup of MySQL.

1. Develop an appropriate 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, such as logical backup for less than 100g and physical backup for more than 100g. Backup file retention time. This can be determined by the disk size and is generally retained for at least 7 days. Backup execution time. It is usually placed during the business trough, such as performing backup operations in the early hours of the morning. Backup interval. One preparation a day is generally recommended, and the backup interval can be extended if the system is not very important. Whether there is a slave library. If there is a slave library, it is recommended to backup it on the slave library to reduce the pressure on the master database.

2.Linux system backup script

Under the Linux system, we can use crontab scheduled tasks to execute backup scripts, if you do not know crontab, you can refer to the following introduction to learn quickly.

Crontab is a command, commonly found in Unix and Unix-like operating systems, that sets instructions that are executed periodically.

Format:

* command

Time-sharing, day, month and week order

Column 1 represents minutes 1: 59 per minute is represented by * or * / 1.

The second column represents the hour 1: 23 (0 means 0 o'clock)

The third column represents the date 1: 31

The fourth column represents the month from January to December.

The fifth column identification number is 0006 (0 means Sunday)

Column 6 commands to run

Crontab-e edits the scheduled task settings under this user

Crontab-l lists all scheduled tasks under this user

Let's formally write the backup script. Without saying too much nonsense, we will first give you the script template:

#! / bin/bash#-# FileName: mysql_backup.sh # Describe: Used for database backup# Revision: 1.The Date: 2020swap 08 / 1 login Author: wang# sets the login username and password for mysql (fill in based on the actual situation) mysql_user = "root" mysql_password = "yourpassword" mysql_host = "localhost" mysql_port = "3306" backup_dir = / data/mysql_backupdt=date +'% Y%m%d_%H%M'echo "Backup Begin Date:" $(date + "% Y-%m-%d% H:%M:%S") # backup all data Library mysqldump-h$mysql_host-P$mysql_port-u$mysql_user-p$mysql_password-R-E-- all-databases-- single-transaction > $backup_dir/mysql_backup_$dt.sqlfind $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 the actual situation, such as backing up a library, changing the retention time, and so on. After writing the script, you should pay attention to debugging, and then you can deploy it. For example, if we plan to backup at 2 am every day, we can set up scheduled tasks like this.

# pay attention to script execution permissions and modify 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 you need to set up scheduled tasks to be executed regularly. For example, we can create a MySQLdata_Bak directory under E disk, and create a mysql_backup directory under this directory to store backup files. Mysql_bak.bat is a backup script. The script content is as follows (backup files from 7 days ago are automatically deleted):

Rem auther:wangrem date:20200811rem * MySQL backup start*@echo offforfiles / 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 onrem * MySQL backup end*

The above script is for reference only and can be changed slightly according to your own environment. Similarly, after the completion of script debugging, you can join the planned task, if you do not understand the Windows planning task, you can Baidu, it is also more convenient and simple.

4. Backup availability check

In addition to backup, it is very important to verify the availability of backup data. Imagine how uncomfortable it would be when you need to recover your data and suddenly find that the backup data in the past is invalid. After writing a backup script and adding it to a scheduled task, many friends just check that the scheduled task is executed, and they no longer pay attention to the files in the backup directory, and often find that there is something wrong with the backup data when they need to use backup files.

At present, there is no very convenient way to check the data of backup files, but we often pull backup files out regularly to do backup recovery drills. for example, doing backup recovery drills once a month can effectively improve the availability of backup files. I have peace of mind.

Therefore, do not think that with a backup will be foolproof, usually should also check whether the backup script execution is correct, whether the content of the production backup script is available, it is best to do regular recovery drills.

About what the MySQL backup script is written to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.