1.  开启 
    //mysqld.cnf
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

     

  2. 查询最新的日志 
    mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1053 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

     

    SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
  3. 写入，会生成一个最新的日志 
    mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

     

  4. 清空 
    mysql> reset master;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

     

  5. 查看binlog日志（row模式) 
    sudo mysqlbinlog /var/log/mysql/mysql-bin.000003 --base64-output=decode-rows -v|more

# at 339
#190514  0:56:40 server id 1  end_log_pos 385 CRC32 0xc5bc8e18     Delete_rows: table id 108 fla
gs: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=4
###   @2='Doken'
# at 385
#190514  0:56:40 server id 1  end_log_pos 416 CRC32 0xf0631419     Xid = 56
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

     

  6. 恢复数据 
    sudo mysqlbinlog /var/log/mysql/mysql-bin.000003 | mysql -uroot -p test
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄