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

Characteristics of incremental backup

The advantage of incremental backup is that there is no duplicate data, the backup quantity is small, and the time is short. The disadvantage is also obvious that all incremental backups need to be restored after the last full backup and full backup, and it is cumbersome to restore all the increments one by one.

MySQL does not provide a direct incremental backup method, but incremental backup can be achieved indirectly through the binary of MySQL. The significance of binary logs for backups is as follows:

(1) the binary log holds all updates or possibly updates to the database.

(2) the binary log starts to be recorded after starting the MySQL server, and the new log file is recreated after the file reaches the size set by max_binlog_size or after receiving the flush logs command.

(3) only need to execute the flush logs method to recreate the new log, generate the binary file sequence, and save these logs to a safe place in time to complete the incremental backup of a period of time.

Incremental backup operation on the basis of table info in database school

Mysql > select * from info

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

+-- +

One incremental backup

1. To achieve incremental backup, you must first turn on the binary function. Add a log-bin=mysql-bin statement to the mysql configuration file, and then restart the service

[root@bogon data] # vim / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / enable binary function

[root@localhost ~] # systemctl restart mysqld.service / / restart the mysql service to generate binaries

[root@localhost ~] # cd / usr/local/mysql/data/

[root@localhost data] # ls

Auto.cnf ib_logfile0 mysql performance_schema

Ib_buffer_pool ib_logfile1 mysql-bin.000001 school / / binary file sequence

Ibdata1 ibtmp1 mysql-bin.index sys

two。 Use the mysqldump command to make a full backup of the table info in the database school

[root@localhost data] # mysqldump-uroot-p school info > / opt/info.sql

Enter password:

[root@localhost data] # cd / opt/

[root@localhost opt] # ls

Info.sql mysql-5.7.17 rh / / Table info backup file

3. Insert data into the info table, perform flush-logs operations, and generate new binary incremental backup files

[root@localhost data] # mysql-uroot-p / / enter mysql database

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Mysql > use school; / / enter the library school

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > insert into info (name,score) values ('test01',88); / / insert information

Query OK, 1 row affected (0.37 sec)

Mysql > insert into info (name,score) values ('test02',76)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from info

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 |

| | 4 | test02 | 76.00 | |

+-- +

Mysql > quit

Bye

[root@localhost data] # mysqladmin-uroot-p flush-logs / / perform incremental backup

Enter password:

[root@localhost data] # ls

Auto.cnf ib_logfile0 mysql mysql-bin.index sys

Ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema

Ibdata1 ibtmp1 mysql-bin.000002 school / / generate a new binary incremental backup file

4. Although a new binary incremental backup file mysql-bin.000002 is generated, the data changes are saved in the number 000001 at this time, and 000002 is an empty log file. Use the mysqlbinlog command to view the contents of the binary file.

[root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000001 / / View the log file numbered 000001

# at 348 / / No garbled code is generated

# 180904 16:45:20 server id 1 end_log_pos 398 CRC32 0x398bd3e5 Write_rows: table id 118 flags: STMT_END_F

# INSERT INTO `room`.`info`

# SET

# @ 1 information inserted in table 3 / / info

# @ 2misttest01'

# @ 3cm 88.00

# at 398

# 180904 16:45:20 server id 1 end_log_pos 429 CRC32 0xd2d905fe Xid = 63

Com _ MIT _ blank /

# at 623

# 180904 16:45:32 server id 1 end_log_pos 673 CRC32 0x4164c313 Write_rows: table id 118 flags: STMT_END_F

# INSERT INTO `room`.`info`

# SET

# @ 1mm 4

# @ 2misttest02'

# @ 3pm 76.00

# at 673

# 180904 16:45:32 server id 1 end_log_pos 704 CRC32 0x8287a8b1 Xid = 64

Com _ MIT _ blank /

two. Incremental backup recovery

Incremental recovery is more tedious than full recovery, each incremental backup is a separate individual, the data is not duplicated, and needs to be controlled more accurately.

Recovery steps for losing all data after a full backup

After a full backup and an incremental backup, all data is lost, and you need to restore the full backup and all incremental backup files one by one. Here we demonstrate the restore operation of the info table in the library school.

(1) data loss of table info in simulation database school, and delete info table using drop

[root@localhost] # mysql-uroot-p

Enter password:

Mysql > use school

Mysql > select * from info

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 |

| | 4 | test02 | 76.00 | |

+-- +

4 rows in set (0.00 sec)

Mysql > drop table info; / / Delete info table

Query OK, 0 rows affected (0.01 sec)

Mysql > select * from info

ERROR 1146 (42S02): Table 'school.info' doesn't exist

(2) use the mysql command to restore the full backup first.

[root@localhost] # mysql-uroot-p school

< /opt/info.sql //恢复school 库中 info 表，备份文件为/opt/info.sql Enter password: [root@localhost ~]# mysql -uroot -p -e 'use school;show tables;select * from info;' //不进入数据库查看info 表的恢复情况 Enter password: +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ +----+------+-------+ | id | name | score | +----+------+-------+ | 1 | tom | 89.00 | //完全备份操作已经恢复 | 2 | lili | 92.00 | +----+------+-------+ （3）被删除的表中数据又可以查询出来，说明完全恢复是成功的。接下来使用二进制文件进行增量恢复操作，需要注意的是恢复的顺序，要恢复最先生成的二进制文件，然后依次执行 [root@localhost ~]# cd /usr/local/mysql/data/ [root@localhost data]# ls auto.cnf ib_logfile0 mysql mysql-bin.000003 school ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.index sys ibdata1 ibtmp1 mysql-bin.000002 performance_schema [root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p //使用二进制文件恢复 Enter password: [root@localhost data]# mysql -uroot -p -e 'use school;select * from info;' //查看恢复情况 Enter password: +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | tom | 89.00 | | 2 | lili | 92.00 | | 3 | test01 | 88.00 | | 4 | test02 | 76.00 | +----+--------+-------+ 三。基于时间点与位置的恢复 利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这是完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作前的状态，然后跳过操作语句，在恢复后面操作的语句。 假定需要往数据库中插入两条数据，但由于误操作，两条语句中间删除了一条数据，而这条数据是不应该删除的。 由于误操作，在添加完 test04 后 将 'tom' 给误删除，有添加了test 05. （1）基于时间点的恢复，就是讲某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据恢复。 使用 mysqlbinlog 加上 --stop-datetime 选项，表示在哪个时间点结束，后面误操作的语句不执行。 使用 mysqlbinlog 加上 --start-datetime 选项，表示执行后面的语句，结合使用跳过误操作语句，完成恢复。需要注意的是，二进制文件中保存的日期格式需要调整为 " -" 分割。 mysql>

Select * from info

+-- +

| | id | name | score | |

+-- +

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 |

| | 4 | test02 | 76.00 | |

| | 5 | test04 | 80.00 |

| | 6 | test05 | 90.00 |

+-- +

5 rows in set (0.00 sec)

[root@localhost data] # ls / / binary files for incremental backups with incorrect operations

Auto.cnf ib_logfile0 mysql mysql-bin.000003 performance_schema

Ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 school

Ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.index sys

[root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000003 / / View incremental backup binaries

# at 2272 / / location point, the last location that can be executed

# 180904 20:26:49 server id 1 end_log_pos 2322 CRC32 0x50dbd722 Write_rows: table id 2222flags: STMT_END_F / / time step

# INSERT INTO `operations `.`info` / / correct operation statements

# SET

# @ 1: 5

# @ 2misttest04'

# @ 300080.00

# at 2322

# 180904 20:26:49 server id 1 end_log_pos 2353 CRC32 0x9ffdf83c Xid = 93

Com _ MIT _ blank /

# at 2547

# 180904 20:27:19 server id 1 end_log_pos 2594 CRC32 0xaed6d12c Delete_rows: table id 222 flags: STMT_END_F

# DELETE FROM `operations `.`info` / misoperation statements

# WHERE

# @ 1room1

# @ 2roomtom'

# @ 3percent 89.00

# at 2594 / / location that can be executed next time

# 180904 20:27:19 server id 1 end_log_pos 2625 CRC32 0x84e5fdb3 Xid = 94

Com _ MIT _ blank /

# at 2819

# 180904 20:32:27 server id 1 end_log_pos 2869 CRC32 0x5babb6b2 Write_rows: table id 222 flags: STMT_END_F

# INSERT INTO `operations `.`info` / / correct operation statements

# SET

# @ 1: 6

# @ 2mistress 05'

# @ 399 90.00

# at 2869

# 180904 20:32:27 server id 1 end_log_pos 2900 CRC32 0x73283b1b Xid = 95

Com _ MIT _ blank /

(2) Delete info table, advanced full backup and restore, incremental backup and restore in sequence

Mysql > use school; / / enter school database

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > drop table info; / / Delete info table

Query OK, 0 rows affected (0.35 sec)

Mysql > quit

Bye

[root@localhost data] # mysql-uroot-p school < / opt/info.sql / / perform a full backup first

Enter password:

[root@localhost data] # mysql-uroot-p-e 'use school;show tables;select * from info;'Enter password: / / View full backup info table

+-+

| | Tables_in_school |

+-+

| | info |

+-+

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

+-- +

[root@localhost data] # mysqlbinlog-- no-defaults mysql-bin.000001 | mysql- uroot-pabc123 / / restore the first incremental backup

Mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost data] # mysql-uroot-p-e 'use school;show tables;select * from info;'

Enter password:

+-+

| | Tables_in_school |

+-+

| | info |

+-+

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 |

| | 4 | test02 | 76.00 | |

/ / delete 'tom' statement error operation time

[root@localhost data] # mysqlbinlog-- no-defaults-- stop-datetime='2018-09-04 20 no-defaults 27 stop-datetime='2018 19' / usr/local/mysql/data/mysql-bin.000003 | mysql- uroot-p

Enter password:

[root@localhost data] # mysql-uroot-p-e 'use school;show tables;select * from info;'

Enter password:

+-+

| | Tables_in_school |

+-+

| | info |

+-+

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 |

| | 4 | test02 | 76.00 | |

| | 5 | test04 | 80.00 |

+-- +

/ / the next correct operation time point

[root@localhost data] # mysqlbinlog-- no-defaults-- start-datetime='2018-09-04 20:32:27'/ usr/local/mysql/data/mysql-bin.000003 | mysql- uroot-p

Enter password:

[root@localhost data] # mysql-uroot-p-e 'use school;show tables;select * from info;'Enter password:

+-+

| | Tables_in_school |

+-+

| | info |

+-+

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 92.00 |

| | 3 | test01 | 88.00 | / / the info table is restored correctly |

| | 4 | test02 | 76.00 | |

| | 5 | test04 | 80.00 |

| | 6 | test05 | 90.00 |

+-+-

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.