mysql binlog

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)

    

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
   
   sudo mysqlbinlog -D --stop-position=2133 /var/log/mysql/mysql-bin.000001 | mysql -uroot -p

   // 从sql恢复
   // -v view 
   // -f force 发生错误继续执行
   sudo mysql -uroot -p db_name -v -f < bak.sql