第三个模块 MySQL5.7 通过binlog日志恢复数据

论mysql5.7.13性能优化之索引优化

第一步：配置MySQL打开binlog日志

# vim /etc/mysql/mysql.conf.d/mysqld.cnf 

• 找到下面两行，将前面的#去掉

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log # binlog日志目录在/var/log/mysql/目录下
binlog_do_db            = nst #这里是你要binlog日志记录的数据库名字(nst 是我的数据库名)

• 重启MySQL

sudo service mysql restart

• 查看binlog是否生效

# mysql -u root -p nst

mysql> show master status; # 可以看到有记录
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 | nst          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

---

第二步：数据测试并进行恢复

• 查看binlog日志文件

# cd /var/log/mysql/
# sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本对sql语句加密了，使用--base64-output=decode-rows 查看=

• 进行如下三步操作:

  • 创建数据表test

  mysql> create table test(
      -> id int,
      -> name char(64)
      -> );
  

  • 插入数据

  mysql> insert into test values(1, 'feng'),(2, 'scort');
  

  • 删除表

  mysql> drop table test;
  

• 现在test表没有了，我想恢复test表！那我们就使用binlog日志文件来恢复

  • 查看binlog日志文件，记录下要恢复的开始位置和结束位置

  # cd /var/log/mysql/
  # sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本对sql语句加密了，使用--base64-output=decode-rows 查看
  
  
  -----内容如下-----
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  # at 4
  #170814  8:08:13 server id 1  end_log_pos 123 CRC32 0x77cdc807  Start: binlog v 4, server v 5.7.19-0ubuntu0.16.04.1-log created 170814  8:08:13
  # Warning: this binlog is either in use or was not closed properly.
  # at 123
  #170814  8:08:13 server id 1  end_log_pos 154 CRC32 0x6912ae9a  Previous-GTIDs
  # [empty]
  # at 154
  #170814  8:25:22 server id 1  end_log_pos 219 CRC32 0x8e9ef4db  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 219
  #170814  8:25:22 server id 1  end_log_pos 334 CRC32 0x91d186ce  Query   thread_id=228   exec_time=0 error_code=0
  use `nst`/*!*/;
  SET TIMESTAMP=1502670322/*!*/;
  SET @@session.pseudo_thread_id=228/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=1436549152/*!*/;
  SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  /*!\C utf8 *//*!*/;
  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  DROP TABLE `test` /* generated by server */
  /*!*/;
  # at 334
  #170814  8:25:50 server id 1  end_log_pos 399 CRC32 0xe931e689  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 399
  #170814  8:25:50 server id 1  end_log_pos 513 CRC32 0xe5e319b0  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670350/*!*/;
  create table test(
  id int,
  name char(64)
  )
  /*!*/;
  # at 513
  #170814  8:26:32 server id 1  end_log_pos 578 CRC32 0x817100ec  Anonymous_GTID  last_committed=2    sequence_number=3   rbr_only=yes
  /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 578
  #170814  8:26:32 server id 1  end_log_pos 649 CRC32 0x489ac5f1  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670392/*!*/;
  BEGIN
  /*!*/;
  # at 649
  #170814  8:26:32 server id 1  end_log_pos 698 CRC32 0x29773e5b  Table_map: `nst`.`test` mapped to number 369
  # at 698
  #170814  8:26:32 server id 1  end_log_pos 754 CRC32 0x05cff49c  Write_rows: table id 369 flags: STMT_END_F
  ### INSERT INTO `nst`.`test`
  ### SET
  ###   @1=1
  ###   @2='feng'
  ### INSERT INTO `nst`.`test`
  ### SET
  ###   @1=2
  ###   @2='scort'
  # at 754
  #170814  8:26:32 server id 1  end_log_pos 785 CRC32 0xf408e2ea  Xid = 7852
  COMMIT/*!*/;
  # at 785
  #170814  8:27:08 server id 1  end_log_pos 850 CRC32 0xbca456aa  Anonymous_GTID  last_committed=3    sequence_number=4   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 850
  #170814  8:27:08 server id 1  end_log_pos 965 CRC32 0x649ac834  Query   thread_id=228   exec_time=0 error_code=0
  SET TIMESTAMP=1502670428/*!*/;
  DROP TABLE `test` /* generated by server */
  /*!*/;
  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  DELIMITER ;
  # End of log file
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  

  at 334处看到了创建表的sql，这个位置是开始位置;
  最后一条记录commit的地方at 785,这个地方就是结束位置

  • 开始恢复

  # sudo mysqlbinlog --start-position=334 --stop-position=785 mysql-bin.000005 | mysql -u root -p nst
  

  • 登录库存查看结果,恢复成功

  # mysql -u root -p nst
  
  mysql> show tables;
  +-------------------------+
  | Tables_in_nst           |
  +-------------------------+
  | test                    |
  +-------------------------+
  
  # 恢复成功
  mysql> select * from test;
  +------+-------+
  | id   | name  |
  +------+-------+
  |    1 | feng  |
  |    2 | scort |
  +------+-------+