MySQL Database full standby and add-on, incremental data recovery cases, and scheduled cleanup of Binlog logs

One, MySQL full-scale backup and incremental backup1, the full amount of backup command:

• /application/mysql/bin/mysqldump-uroot-p123456--lock-all-tables-a-b-f--master-data=2--single-transaction-- Events|gzip >/opt/mysqlbackup/allbackup/allbackup.sql.gz*

如上一段代码所示，其功能是将所有数据库全量备份。其中 MySQL 用户名为：root ，密码为：123456。备份的文件路径为：/opt/Mysql_Backup/all_backup，当然这个路径是按照个人意愿修改的。备份的文件压缩包名为 all_backup.sql.gz参数 --lock-all-tables：锁定所有数据库;参数 -A：备份所有库；参数 -B：指定多个库，增加建库语句和 use 语句；参数 -F：刷新 binlog 日志；参数 --master-data=0|1|2：        0: 不记录        1：记录为CHANGE MASTER语句        2：记录为注释的CHANGE MASTER语句；参数 --single-transaction：适合 innodb 事务数据库备份；参数 --events：导出事件；参数 gzip：备份压缩文件。

2, full-scale backup script:

• #!/bin/bash
  . /etc/init.d/functions
  User=root
  Password= "123456"
  Backuptools=/application/mysql/bin/mysqldump
  Backupdir=/opt/mysqlbackup
  allbackup= $BackupDir/allbackup
  Mkdir-p $AllBackup
  Echo ' ========== ' $ (date + "%y-%m-%d%h:%m:%s") ' ========== ' "Backup Start" >> $AllBackup/allbackup.log
  $BackupTools-u$user-p$password-a-b-f--master-data=2--single-transaction--events|gzip > $AllBackup/allbackup$ ( Date +%y%m%d). sql.gz
  If [$?-eq 0]
  Then
  Echo ' ========== ' $ (date + "%y-%m-%d%h:%m:%s") ' ========== ' "Backup Complete" >> $AllBackup/allbackup.log
  Action "Mysql full backup is OK"/bin/true
  Else
  Action "Mysql full backup isn't OK"/bin/false
  Fi

3. Restore full backup command:

Cd/opt/mysqlbackup/allbackup
Gzip-d allbackup.sql.gz
mysql-uroot-p123456 < Allbackup.sql
Or:

4. Incremental backup

首先在进行增量备份之前需要查看一下配置文件，查看 logbin 是否开启，因为要做增量备份首先要开启 logbin 。首先，进入到 myslq 命令行，输入如下命令：

Mysql> Show variables like '%logbin% ';
+---------------------------------+-------+
| Variablename | Value |
+---------------------------------+-------+
| Logbin | On |
| logbintrustfunctioncreators | OFF |
| Sqllogbin | On |
+---------------------------------+-------+

If the binlog is turned on, if it is not, execute the following command:

Vim/etc/my.cnf
Open:
[Mysqld]
Log-bin=mysql-bin

To view the currently used mysql_bin.000 log file:

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlogdodb | Binlogignoredb |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 533039 | | |
+------------------+----------+--------------+------------------+

The file name for the log currently being logged is mysql-bin.000019.

Incremental backup Script

#!/bin/bash
Export Lang=enus.utf-8
backupdir=/opt/mysqlbackup/binlogbackup/
bindir=/application/mysql/data/
Logfile=/opt/mysqlbackup/binlog.log
Binfile=/application/mysql/data/mysql-bin.index
Mkdir-p $BackupDir
/application/mysql/bin/mysqladmin-uroot-p123456 Flush-logs
Counter=wc -l $BinFile|awk ‘{print $1}‘
Nextnum=0
For file incat $BinFile
Do
Base=basename $file
nextnum=expr $NextNum + 1
If [$NextNum-eq $Counter]
Then
Echo $base skip! >> $LogFile
Else
dest= $BackupDir/$base
If [-e $dest]
Then
Echo $base exist! >> $LogFile
Else
CP $BinDir/$base $BackupDir
echo $base copying >> $LogFile
Fi
Fi
Done
echo date +"%Y年%m月%d日 %H:%M:%S" Backup succ!>> $LogFile

Second, incremental data recovery Case 1, scenario overview

    a、MySQL数据库每日零点自动全备    b、某天上午10点，小明莫名其妙地drop了一个数据库    c、我们需要通过全备的数据文件，以及增量的binlog文件进行数据恢复

2. Main ideas

    a、利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件增量的部分    b、用mysqlbinlog命令将上述的binlog文件导出为sql文件，并剔除其中的drop语句    c、通过全备文件和增量binlog文件的导出sql文件，就可以恢复到完整的数据

3. Process

4. Operation Process

1), Analog data

*createtablestudent (

idInt (one) not nullautoincrement,

namechar () is not NULL,

agetinyint (2) not Nulldefault ' 0 ',

PRIMARY KEY ( id ),

KEY indexname ( name )

) Engine=innodbautoincrement=8defaultcharset=utf8

Mysql>insertstudentvalues (1, ' Zhangsan ', 20);

Mysql>insertstudentvalues (2, ' Lisi ', 21);

Mysql>insertstudentvalues (3, ' Wangwu ', 22);
*

2), fully prepared command

/application/mysql/bin/mysqldump-uroot-p123456--lock-all-tables-a-b-f--master-data=2--single-transaction-- Events|gzip >/opt/mysqlbackup/allbackup/allbackup.sql.gz

3), continue inserting data

*mysql>insertstudentvalues (6, ' xiaoming ', 20);

Mysql>insertstudentvalues (6, ' Xiaohong ', 20);

At this time, the test database was deleted by mistake

mysql>dropdatabasetest;
*
At this time, the user writes the data in Binlog, and needs to be restored.

4), to view the new Binlog file after the full preparation

cd/opt/mysqlbackup/allbackup/
Ls
Allbackup20180831.sql.gz
Gzip-d allbackup20180831.sql.gz
grep Change Allbackup20180831.sql
--Change MASTER to masterlogfile= ' mysql-bin.000003 ', masterlogpos=107;

This is the full-time Binlog file location, which is the 107 rows of mysql-bin.000003, so the data in the Binlog file before the file is already contained in this fully-prepared SQL file.

5), move the Binlog file, and read the SQL to remove the drop statement

CP mysql-bin.000003/tmp/
mysqlbinlog-d Test mysql-bin.000003 > Bin.log
Use Vim to edit files and reject drop statements

The Binlog file must be moved out before the full data is restored, or it will continue to be written to binlog during the recovery process, resulting in a somewhat confusing portion of the incremental recovery data

6), Recover data

Mysql-uroot-p < All_backup_20180831.sql < Full recovery >

Mysql-uroot-p-E "select * from Test.student;"

+----+----------+-----+

|id|name |age|

+----+----------+-----+

| 1|zhangsan| 20|

| 2|lisi | 21|

| 3|wangwu | 22|

+----+----------+-----+

At this point, the full-time data is restored.

Then use the 003bin.sql file to recover the full standby time to delete the database between the new data

Mysql-uroot-p test</tmp/bin.sql < incremental binlog statement recovery >

</span># mysql-uroot-p-E "select * from Test.student;"

+----+----------+-----+

|id|name |age|

+----+----------+-----+

| 1|zhangsan| 20|

| 2|lisi | 20|

| 3|wangwu | 20|

| 4|xiaoming| 20|

| 5|xiaohong| 20|

+----+----------+-----+

Complete

5. Summary

a、适合人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复b、恢复条件要全备和增量的所有数据c、恢复时建议对外停止更新，即禁止更新数据库d、先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库

Third, scheduled cleanup Binlog log

The recent disk growth was very fast and found that the Binlog log occupies a large disk resource. We use manual cleanup and set up automatic cleanup later.

View the specified delete log

mysql >show binary logs; 查看多少binlog日志，占用多少空间。mysql> PURGE MASTER LOGS TO ‘mysql-bin.002467‘; 删除mysql-bin.002467以前所有binlog，这样删除可以保证*.index信息与binlog文件同步。

Manual cleanup

    mysql>PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY); 手动删除5天前的binlog日志

Automatic Setup Cleanup

mysql> set global expire_logs_days = 5; 把binlog的过期时间设置为5天; mysql> flush logs; 刷一下log使上面的设置生效，否则不生效。

To ensure that it is still valid after MySQL restart, this parameter setting is also added in MY.CNF

expire_logs_days = 5

MySQL Database full standby and add-on, incremental data recovery cases, and scheduled cleanup of Binlog logs