Several ways of regular backup of MySQL database (very comprehensive)

In the process of operating data, it may cause data errors, or even database crashes, and effective scheduled backups can well protect the database. This article mainly describes several methods for regular MySQL database backup.

1. mysqldump command to backup data

MySQL provides a convenient tool mysqldump to export database data and files from the command line. We can directly export the database content through the command line. First, let's briefly understand the usage of the mysqldump command:

#MySQLdump常用
mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

2. Examples of common operations of mysqldump

1. Back up the data and structure of all databases

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2. Back up the structure of all databases (add -d parameter)

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

3. Back up all database data (add -t parameter)

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4. Back up the data and structure of a single database (database name mydb)

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

5. Back up the structure of a single database

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

6. Back up the data of a single database

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

7. Back up the data and structure of multiple tables (the separate backup method of data and structure is the same as above)

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

8. Back up multiple databases at once

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

3. Restore MySQL backup content

There are two ways to restore, the first is in the MySQL command line, the second is to use the SHELL line to complete the restore

1. In the system command line, enter the following to restore:

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2. After logging in to the mysql system, use the source command to find the file in the corresponding system to restore:

mysql> source /data/mysqlDump/mydb.sql

In Linux, BASH scripts are usually used to write the content that needs to be executed, and the crontab command is executed regularly to realize automatic log generation.

The following code function is for mysql backup, with crontab, the backup content is the daily mysql database records in the past month (31 days).

Write BASH to maintain a fixed number of backup files

In Linux, use vi or vim to write the script content and name it: mysql_dump_script.sh

#!/bin/bash

#保存备份个数，备份31天数据
number=31
#备份保存路径
backup_dir=/root/mysqlbackup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=mysqldump
#用户名
username=root
#密码
password=TankB214
#将要备份的数据库
database_name=edoctor

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then     
    mkdir -p $backup_dir;
fi

#简单写法 mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份，只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

The main meaning of the above code is as follows:

1. First set up various parameters, such as number the maximum number of backups that need to be backed up, backup path, user name, password, etc.

2. Execute the mysqldump command to save the backup file, and print the operation to log.txt in the same directory to mark the operation log.

3. Define the file that needs to be deleted: Get the ninth column, that is, the file name column, through the ls command, and then define the file that needs to be deleted with the latest operation time.

4. Define the number of backups: add wc -l to count the number of lines in the file ending with sql through the ls command.

5. If the file exceeds the limit size, delete the earliest created sql file

Use crontab to periodically execute backup scripts

In Linux, the tasks that are executed periodically are generally handled by the cron daemon [ps -ef|grep cron] . Cron reads one or more configuration files, these configuration files contain the command line and its call time.
The cron configuration file is called "crontab", which is short for "cron table".

cron service

Cron is a timed execution tool under Liunx, which can run jobs without manual intervention.

service crond start    //启动服务
service crond stop     //关闭服务
service crond restart  //重启服务
service crond reload   //重新载入配置
service crond status   //查看服务状态 

crontab syntax

The crontab command is used to install, delete, or list tables used to drive cron background processes. The user puts the sequence of commands that need to be executed into the crontab file for execution. Each user can have his own crontab file. The crontab file under /var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command.

How to enter the command and time to be executed in the crontab file. Each line in the file includes six fields, the first five fields are the time when the command is executed, and the last field is the command to be executed.
Use spaces or tabs to separate each field.

The format is as follows:

minute hour day-of-month month-of-year day-of-week commands 
合法值 00-59 00-23 01-31 01-12 0-6 (0 is sunday) 

除了数字还有几个个特殊的符号就是"*"、"/"和"-"、","，*代表所有的取值范围内的数字，"/"代表每的意思,"/5"表示每5个单位，"-"代表从某个数字到某个数字,","分开几个离散的数字。

-l 在标准输出上显示当前的crontab。 
-r 删除当前的crontab文件。 
-e 使用VISUAL或者EDITOR环境变量所指的编辑器编辑当前的crontab文件。当结束编辑离开时，编辑后的文件将自动安装。 

Create cron script

• The first step: write a cron script file, named mysqlRollBack.cron.
  echo "xgmtest....." >> xgmtest.txt means that every 15 minutes, execute the print command
• Step 2: Add timed tasks. Execute the command "crontab crontest.cron". Get it done
• The third step: "crontab -l" check whether the timing task is successful or check whether the corresponding cron script is generated under /var/spool/cron

Note: This operation is to directly replace the crontab under the user, not to add

Regularly execute the written timing task script (remember to give the shell script execution permission first)

0 2 * * * /root/mysql_backup_script.sh

Then use the crontab command to periodically instruct the timing script

crontab mysqlRollback.cron

Then use the command to check whether the scheduled task has been created:

Attached examples of the use of crontab:

1. Everyday at 6 o'clock in the morning

0 6 * * * echo "Good morning." >> /tmp/test.txt //注意单纯echo，从屏幕上看不到任何输出，因为cron把任何输出都email到root的信箱了。

2. Every two hours

0 */2 * * * echo "Have a break now." >> /tmp/test.txt

3. Every two hours between 11 p.m. and 8 a.m. and 8 a.m.

0 23-7/2，8 * * * echo "Have a good dream" >> /tmp/test.txt

4. On the 4th of every month and every Monday to Wednesday at 11 o'clock in the morning

0 11 4 * 1-3 command line

5.1 at 4 a.m. on 1st

0 4 1 1 * command line SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root //如果出现错误，或者有数据输出，数据作为邮件发给这个帐号 HOME=/

6. Execute the scripts in /etc/cron.hourly every hour

01 * * * * root run-parts /etc/cron.hourly

7. Execute the scripts in /etc/cron.daily every day

02 4 * * * root run-parts /etc/cron.daily

8. Execute the scripts in /etc/cron.weekly every week

22 4 * * 0 root run-parts /etc/cron.weekly

9. Execute the scripts in /etc/cron.monthly every month

42 4 1 * * root run-parts /etc/cron.monthly

Note: The "run-parts" parameter is removed. If you remove this parameter, you can write the name of a script to be run instead of the folder name.

10. Execute commands at 4, 5, and 6 pm every day at 5 min, 15 min, 25 min, 35 min, 45 min, and 55 min.

5，15，25，35，45，55 16，17，18 * * * command

11. Every Monday, Wednesday, and Friday at 3:00 PM, the system enters the maintenance state and restarts the system.

00 15 * * 1，3，5 shutdown -r +5

12. At 10 minutes per hour, at 40 minutes, execute the innd/bbslin command in the user directory:

10，40 * * * * innd/bbslink

13. Execute the bin/account command in the user directory at 1 minute per hour:

1 * * * * bin/account

The following is the screenshot effect of my test every minute, and the corresponding code is as follows:

* * * * * /root/mysql_backup_script.sh

Effect screenshot:

The log.txt records the detailed log of the backup operation:

Source: cnblogs.com/letcafe/p/mysqlautodump.html