Top

NSD RDBM1 DAY05

  1. 案例1：使用binlog日志恢复数据

1 案例1：使用binlog日志恢复数据

1.1 问题

利用binlog恢复库表，要求如下：

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：启用binlog日志

1）调整/etc/my.cnf配置，并重启服务

  1. [root@dbsvr1 ~]# vim  /etc/my.cnf
  2. [mysqld]
  3. server_id=1  //定义server_id
  4. log-bin=mysql-bin  //定义日志名
  5. binlog_format=”mixed”     //定义日志格式                           
  6. [root@dbsvr1 ~]# systemctl  restart mysqld.service //重启服务

2）确认binlog日志文件

新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：

  1. [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
  2. /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index

其中mysql-bin.index文件记录了当前保持的二进制文件列表：

  1. [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
  2. mysql-bin.000001

重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：

  1. [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
  2. /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
  3. /var/lib/mysql/mysql-bin.000002
  4.  
  5. [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
  6. mysql-bin.000001
  7. mysql-bin.000002

步骤二：利用binlog日志重做数据库操作

1）执行数据库表添加操作

创建db1·库tb1表，表结构自定义：

  1. mysql> CREATE DATABASE db1;
  2. Query OK, 1 row affected (0.05 sec)
  3.  
  4. mysql> USE db1;
  5. Database changed
  6. mysql> CREATE TABLE tb1(
  7.     -> id int(4) NOT NULL,name varchar(24)
  8.     -> );
  9. Query OK, 0 rows affected (0.28 sec)

插入3条表记录：

  1. mysql> INSERT INTO tb1 VALUES
  2.     -> (1,'Jack'),
  3.     -> (2,'Kenthy'),
  4.     -> (3,'Bob');
  5. Query OK, 3 rows affected (0.12 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0

确认插入的表记录数据：

  1. mysql> SELECT * FROM tb1;
  2. +----+--------+
  3. | id | name   |
  4. +----+--------+
  5. |  1 | Jack   |
  6. |  2 | Kenthy |
  7. |  3 | Bob    |
  8. +----+--------+
  9. 3 rows in set (0.00 sec)

2）删除前一步添加的3条表记录

执行删除所有表记录操作：

  1. mysql> DELETE FROM tb1;
  2. Query OK, 3 rows affected (0.09  sec)

确认删除结果：

  1. mysql> SELECT * FROM tb1;
  2. Empty set (0.00 sec)

步骤三：通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

1）查看mysql-bin.000002日志内容

  1. [root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
  2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #170412 12:05:32 server id 1  end_log_pos 123 CRC32 0x6d8c069c  Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup
  7. # Warning: this binlog is either in use or was not closed properly.
  8. ROLLBACK/*!*/;
  9. BINLOG '
  10. jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  11. AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  12. AZwGjG0=
  13. '/*!*/;
  14. # at 123
  15. #170412 12:05:32 server id 1  end_log_pos 154 CRC32 0x17f50164  Previous-GTIDs
  16. # [empty]
  17. # at 154
  18. #170412 12:05:59 server id 1  end_log_pos 219 CRC32 0x4ba5a976  Anonymous_GTID  last_committed=0        sequence_number=1
  19. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  20. # at 219
  21. #170412 12:05:59 server id 1  end_log_pos 310 CRC32 0x5b66ae13  Query   thread_id=3     exec_time=0     error_code=0
  22. SET TIMESTAMP=1491969959/*!*/;
  23. SET @@session.pseudo_thread_id=3/*!*/;
  24. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  25. SET @@session.sql_mode=1436549152/*!*/;
  26. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  27. /*!\C utf8 *//*!*/;
  28. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  29. SET @@session.lc_time_names=0/*!*/;
  30. SET @@session.collation_database=DEFAULT/*!*/;
  31. CREATE DATABASE db1
  32. /*!*/;
  33. # at 310
  34. #170412 12:06:23 server id 1  end_log_pos 375 CRC32 0x2967cc28  Anonymous_GTID  last_committed=1        sequence_number=2
  35. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  36. # at 375
  37. #170412 12:06:23 server id 1  end_log_pos 502 CRC32 0x5de09aae  Query   thread_id=3     exec_time=0     error_code=0
  38. use `db1`/*!*/;
  39. SET TIMESTAMP=1491969983/*!*/;
  40. CREATE TABLE tb1(
  41. id int(4) NOT NULL,name varchar(24)
  42. )
  43. /*!*/;
  44. # at 502
  45. #170412 12:06:55 server id 1  end_log_pos 567 CRC32 0x0b8cd418  Anonymous_GTID  last_committed=2        sequence_number=3
  46. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  47. # at 567
  48. #170412 12:06:55 server id 1  end_log_pos 644 CRC32 0x7e8f2fa0  Query   thread_id=3     exec_time=0     error_code=0
  49. SET TIMESTAMP=1491970015/*!*/;
  50. BEGIN
  51. /*!*/;
  52. # at 644
  53. #170412 12:06:55 server id 1  end_log_pos 772 CRC32 0x4e3f728e  Query   thread_id=3     exec_time=0     error_code=0                            //插入表记录的起始时间点 
  54. SET TIMESTAMP=1491970015/*!*/;
  55. INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob')
  56. /*!*/;
  57. # at 772
  58. #170412 12:06:55 server id 1  end_log_pos 803 CRC32 0x6138b21f  Xid = 10
  59.                                                       //确认事务的时间点 
  60. COMMIT/*!*/;
  61. # at 803
  62. #170412 12:07:24 server id 1  end_log_pos 868 CRC32 0xbef3f472  Anonymous_GTID  last_committed=3        sequence_number=4
  63. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  64. # at 868
  65. #170412 12:07:24 server id 1  end_log_pos 945 CRC32 0x5684e92c  Query   thread_id=3     exec_time=0     error_code=0
  66. SET TIMESTAMP=1491970044/*!*/;
  67. BEGIN
  68. /*!*/;
  69. # at 945
  70. #170412 12:07:24 server id 1  end_log_pos 1032 CRC32 0x4c1c75fc         Query   thread_id=3     exec_time=0     error_code=0            //删除表记录的时间点
  71. SET TIMESTAMP=1491970044/*!*/;
  72. DELETE FROM tb1
  73. /*!*/;
  74. # at 1032
  75. #170412 12:07:24 server id 1  end_log_pos 1063 CRC32 0xccf549b2         Xid = 12
  76. COMMIT/*!*/;
  77. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  78. DELIMITER ;
  79. # End of log file
  80. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  81. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2） 执行指定Pos节点范围内的sql命令恢复数据

根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：

  1. [root@dbsvr1 ~]# mysqlbinlog \
  2.     --start-datetime="2017-04-12 12:06:55" \ 
  3.     --stop-datetime="2017-04-12 12:07:23" \
  4.     /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
  5. Enter password:                                     //验证口令

3）确认恢复结果

  1. mysql> SELECT * FROM db1.tb1;
  2. +----+--------+
  3. | id | name   |
  4. +----+--------+
  5. |  1 | Jack   |
  6. |  2 | Kenthy |
  7. |  3 | Bob    |
  8. +----+--------+
  9. 3 rows in set (0.00 sec)