MySQL Incremental backup and recovery

MySQL incremental backup and recovery skill targets

• Understanding the concept of MySQL incremental backup
• Understanding MySQL Incremental backup and recovery methods

1: The concept of incremental backup full backup each time you take all the files back up one side of the space is large, Incremental backup solves this problem it can be resolved after your full backup if you make a change or a file that needs to be backed up is not a big deal with incremental backup 2: Features of incremental backups (1) binary files save all updates or may update database operations (2) binary files start logging after starting the MySQL service (3) just perform a refresh Ability to create new log incremental backups The following is a case in a production environment, which is an incremental backup operation 1. Requirements Description

A company user information database is client, the User Resource table is User_info

Requirements are as follows

(1): Please make a full backup for the company every week

(2): Incremental backup for the company every day

(3): Newly added user information is as follows

ID	name	Zifei
1	Wang-1	10
2	Wang-2	20

Implementing incremental backups

#1: Make a full backup of the following original tables first refresh before backup [[email protected] data] mysqladmin-u root-p flush-logsmysql> select * from User_ info;+------+---------+-------+| ID | name |    Zifei |+------+---------+-------+| 1 |    zhang-1 |    10 | | 2 |    zhang-2 |    20 | | 3 |     zhang-3 | 5 |+------+---------+-------+3 rows in Set (0.00 sec) [[email protected] data] mysqldump-u root-p client User_info & Gt /usr/local/mysql/data/client-user_info.sqlenter password[[email protected] Data] lsclient-user_info.sql# 2: Incremental backup after adding wang-1 information mysql> insert INTO User_info (Id,name,zifei) VALUES (4, ' wang-1 ', 10); Query OK, 1 row Affected (0.00 sec) [[email protected] data] mysqladmin-uroot-p flush-logsenter password: #上面的 Command refresh saved in mysql-bin.000001 [[email protected] data]# ls # mysql-bin.000002 Yes mysql-bin.000001 save generates an empty log waiting for the next incremental backup mysql-bin.000001mysql-bin.000002 #3: Incremental backup after adding wang-2 information mysql> INSERT INTO User_info (Id,name,zifei) VALUES (5, ' wang-2 ', 20); Query OK, 1 row Affected (0.00 sec) [[email protected] data] mysqladmin-uroot-p flush-logsenter password: [[email protected] data]# lsmysql-bin.000002 mysql-bin.000003

After each incremental backup, it generates an empty binary file for the next incremental backup, Recovery is the inverse of the recovery is to say that you recover mysql-bin.000002 is actually recovering the operation you did last time MySQL incremental recovery 1: Join you put the original table in wang-1,wang-2 delete need to restore this delta file to do the following actions

#下表中wang -1,wang-2 has been removed mysql> select * from user_info;+------+---------+-------+| ID | name |    Zifei |+------+---------+-------+| 1 |    zhang-1 |    10 | | 2 |    zhang-2 |    20 | | 3 |     zhang-3 | 5 |+------+---------+-------+5 rows in Set (0.00 sec) #恢复wang -1[[email protected] data] Mysqlbinlog--no-defaults mysql-bin.000002 | Mysql-u root-p #先恢复mysql-bin.000002 Let's see mysql> select * from user_info;+------+---------+-------+| ID | name |    Zifei |+------+---------+-------+| 1 |    zhang-1 |    10 | | 2 |    zhang-2 |    20 | | 3 |     zhang-3 |    5 | | 5 |    wang-2 | |+------+---------+-------+4 rows in Set (0.00 sec) #恢复的是我最后一次操作备份wang-2 Data # recovery mysql-bin.000001 look at [[email . Protected] data]# Mysqlbinlog--no-defaults mysql-bin.000001 | Mysql-u root-pmysql> SELECT * from user_info;+------+---------+-------+| ID | name |    Zifei |+------+---------+-------+| 1 |    zhang-1 |    10 | | 2 |    zhang-2 |    20 | | 3 |     zhang-3 |    5 | | 4 |  wang-2 |  10 | | 5 |    wang-2 | |+------+---------+-------+4 rows in Set (0.00 sec)

View binaries If you use the following statement directly, he will not see you. The statement that you operate will automatically encrypt you

[[email protected] data] mysqlbinlog --no-defaults mysql-bin.000001BINLOG ‘ #下面加密的哪一行就是你在数据库进行操作PCE7WxMBAAAAOgAAAEkIAAAAANsAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAMDDwMCHgAH7bXH3w==PCE7WyABAAAAMwAAAHwIAAAAANsAAAAAAAEAAgAD//gFAAAABndhbmctMhQAAABJK6gI‘/*!*/;# at 2172#想要解密这个加密文件用下列语句[[email protected] data] mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001### DELETE FROM `client`.`user_info`### WHERE #下面就是之前看不到的加密的部分###   @1=5###   @2=‘wang-2‘###   @3=20

MySQL Incremental backup and recovery