Day.8 备份还原 - 备份资料 (MYSQL binlog )-上

复习: 在昨天讲解了如何使用mysqldump备份是数据和binlog相关过滤方法，今天来实际模拟一下流程进行。

资料复原准备

使用binlog复原资料的流程，假设我们今天因为异动操作误删了表中资料要如何复原资料？

• DB:user

• TABLE:user_powers

• --DATA SQL资料参考--

 (资料库)
 CREATE DATABASE user;
 ----------------------------------------------------
 (资料表)
 CREATE TABLE `user_powers` (
  `user_id` varchar(50) NOT NULL COMMENT '会员ID',
  `nick_name` varchar(20) NOT NULL COMMENT '会员名称',
  `group_id` int(11) DEFAULT NULL COMMENT '会员所属平台',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP    COMMENT '异动时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 ----------------------------------------------------
 (资料)
  INSERT INTO `user_powers` VALUES ('1021211','ken',119201,'2021-08-10 08:33:56'),('1232123','siang',19200,'2021-08-10 08:33:51'),('18675543','kitty',19200,'2021-08-10 08:33:51'),('23122141','kiki',1242000,'2021-08-12 09:01:54'),('24122441','snow',119201,'2021-08-10 08:33:51'),('4356231','lisa',19200,'2021-08-10 08:33:56'),('64121211','kevin',389110,'2021-08-10 08:33:51'),('7622441','ben',213123,'2021-08-10 08:33:56'),('91311233','alex',389110,'2021-08-10 08:33:56'),('91675543','kay',123321,'2021-08-10 08:33:56'),('98212321','sara',119201,'2021-08-10 08:33:51');
 ----------------------------------------------------
 (完成後数据)
mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 1232123  | siang     |    19200 | 2021-08-10 08:33:51 |
| 18675543 | kitty     |    19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 4356231  | lisa      |    19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
11 rows in set (0.00 sec)
 

1.假设上面数据为今天备份完的内容

重要*3
在下面的指令中加上 --master-data=2 参数作用会纪录此备份内容是到哪一个binlog(POS点&档名)，方便後续复原动作。

• 备份DB: user (数据含相关SQL排成文件 procedure,events...这是 後面会做介绍~)

# mysqldump -uroot -p1234  --routines --events --single-transaction --master-data=2 --flush-logs user > user_backup_20210817.sql

• 检视此备份档执行的binlog纪录

# grep -i "CHANGE MASTER TO MASTER_LOG_FILE" user_backup_20210817.sql

• 在备份完後我们未做任何资料上更动，查看mysql目前的binlog纪录位置，可以看到确实为备份档案的纪录位置。

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |      3068 |
| mysql-bin.000013 |       154 |   <-目前位置
+------------------+-----------+
3 rows in set (0.00 sec)

--补充--

环境备份:基本上生产环境下备份都是以全库备为主，而有些重要的表资讯可能会有备份单库or表的需求。

mysqldump还原注意: 备份时如果不是用指定到表的方式备份 ex.mysqldump -u root -p db_name tbl_name ... 的话在恢复时是没办法直接以恢复单一TABLE的方式还原资料倒回去的，可采用过滤备份资料把表相关资料语法另外捞出来做复原。

保险操作: 将备份档和binlog资料在临时库上做复原动作，根据备份档纪录的binlog(POS&档名)找後续执行的binlog内容进行复原，确认资料没问题後再将表倒回生产库中。
避免出错造成二次伤害的状况!!!

2.由於正常服务是持续运行的，备份完当然会有资料持续的更新写入。

• 准备备份完後异动的资料，产新binlog档。 (ex.有多个binlog纪录档的资料要恢复)

mysql> INSERT INTO `user_powers` VALUES ('98112111','leo',19201,'2021-08-13 12:33:56'),('38921112','shawn',19200,'2021-08-12 10:33:51');

mysql> INSERT INTO `user_powers` VALUES ('72197123','eric',19200,'2021-08-15 01:33:51');

mysql> FLUSH LOGS;

mysql> UPDATE user_powers SET nick_name = 'siangx' WHERE user_id = 1232123;

mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 1232123  | siangx    |    19200 | 2021-08-17 02:08:05 |
| 18675543 | kitty     |    19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 38921112 | shawn     |    19200 | 2021-08-12 10:33:51 |
| 4356231  | lisa      |    19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 72197123 | eric      |    19200 | 2021-08-15 01:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98112111 | leo       |    19201 | 2021-08-13 12:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
14 rows in set (0.00 sec)

3.执行误删数据动作

mysql> delete from user_powers where group_id = 19200;

mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98112111 | leo       |    19201 | 2021-08-13 12:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
9 rows in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |      3068 |
| mysql-bin.000013 |       816 |
| mysql-bin.000014 |       849 |
+------------------+-----------+
4 rows in set (0.00 sec)

总结-(备份完时位置在mysql-bin.000013-(154)的地方，所以异动过後的资料纪录於 mysql-bin.000013 & mysql-bin.000014)

下集预告: 完成以上测试资料准备後，明天开始进行後续复原数据流程