mysql的binlog说明

主从复制依赖于二进制日志文件，简称为binlog

binlog里面有存放了偏移信息

mysql主库binlog信息查看命令

[root@master ~]#  mysql -u root -p123456   #登录到mysql里

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is

Server version: 5.7.-log MySQL Community Server (GPL)

Copyright (c) , , Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master logs;                #binlog列表查看，也可以直接到/data/mysql查看

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin. |        |

| master-bin. |        |

+-------------------+-----------+

 rows in set (0.00 sec)

mysql> show master status;   #记录目前的binlog+偏移信息

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin. |       |              |                  |                   |       #398即是偏移

+-------------------+----------+--------------+------------------+-------------------+

创建一个库和表，观察日志

mysql> create database darren;

Query OK,  row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| darren             |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

 rows in set (0.00 sec)

mysql> use darren;

Database changed

mysql> create table test (id int);

Query OK,  rows affected (0.01 sec)

mysql> show tables;

+------------------+

| Tables_in_darren |

+------------------+

| test             |

+------------------+

查看binlog日志

mysql> mysql> show binlog events in 'master-bin.000002';  #全部查看

+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+

| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                 |

+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+

| master-bin. |    | Format_desc    |          |          | Server ver: 5.7.-log, Binlog ver:                                                                                 |

| master-bin. |  | Previous_gtids |          |          |                                                                                                                      |

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                 |

| master-bin. |  | Query          |          |          | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                 |

| master-bin. |  | Query          |          |          | create database darren                                                                                               |

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                 |

| master-bin. |  | Query          |          |          | use `darren`; create table test (id int)                                                                             |

+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+

mysql> mysql> show binlog events in 'master-bin.000002' limit ;   只查看前两个语句

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                  |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| master-bin. |    | Format_desc    |          |          | Server ver: 5.7.-log, Binlog ver:  |

| master-bin. |  | Previous_gtids |          |          |                                       |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

 rows in set (0.00 sec)

mysql> show binlog events in 'master-bin.000002' from ;     #查看偏移量是398以后的语句

+-------------------+-----+----------------+-----------+-------------+------------------------------------------+

| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                     |

+-------------------+-----+----------------+-----------+-------------+------------------------------------------+

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |  | Query          |          |          | create database darren                   |

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |  | Query          |          |          | use `darren`; create table test (id int) |

+-------------------+-----+----------------+-----------+-------------+------------------------------------------+

mysql> mysql> show binlog events in 'master-bin.000002' from  limit ;   #查看偏移量为398的前一条

+-------------------+-----+----------------+-----------+-------------+--------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+-------------------+-----+----------------+-----------+-------------+--------------------------------------+

| master-bin. |  | Anonymous_Gtid |  |  | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

+-------------------+-----+----------------+-----------+-------------+--------------------------------------+

直接使用命令查看二进制文件

[root@master ~]# cd /data/mysql/

[root@master mysql]# mysqlbinlog master-bin.000002

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at

# :: server id   end_log_pos  CRC32 0x7ae93041     Start: binlog v , server v 5.7.-log created  :: at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

z8scXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADPyxxdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AUEw6Xo=

'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0x81e643cc     Previous-GTIDs

# [empty]

# at

# :: server id   end_log_pos  CRC32 0x0a3cc640     Anonymous_GTID    last_committed=    sequence_number=    rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0xbd85ba24     Query    thread_id=    exec_time=    error_code=

SET TIMESTAMP=/*!*/;

SET @@session.pseudo_thread_id=/*!*/;

SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;

SET @@session.sql_mode=/*!*/;

SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;

SET @@session.lc_time_names=/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

/*!*/;

# at

# :: server id   end_log_pos  CRC32 0x674f9414     Anonymous_GTID    last_committed=    sequence_number=    rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0x2106de6f     Query    thread_id=    exec_time=    error_code=

SET TIMESTAMP=/*!*/;

create database darren

/*!*/;

# at

# :: server id   end_log_pos  CRC32 0xbf9b983e     Anonymous_GTID    last_committed=    sequence_number=    rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0x773a1598     Query    thread_id=    exec_time=    error_code=

use `darren`/*!*/;

SET TIMESTAMP=/*!*/;

create table test (id int)

/*!*/;

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*/;

增删改查，然后查看binlog

mysql> insert into test values ();    #写入一个数据，有事务的提交

Query OK,  row affected (0.01 sec)

mysql> select * from test;              #查一个数据

+------+

| id   |

+------+

|     |

+------+

 row in set (0.00 sec)

mysql> update mysql> update test set id  ;  #改一个数据，有事务的提交

Query OK,  row affected (0.00 sec)

Rows matched:   Changed:   Warnings: 

mysql> select * from test;

+------+

| id   |

+------+

|     |

+------+

 row in set (0.00 sec)

mysql> delete from test;                    #删除数据，有事务的提交

Query OK,  row affected (0.00 sec)

mysql> show binlog events in 'master-bin.000002' from ;    #查看日志

+-------------------+------+----------------+-----------+-------------+------------------------------------------+

| Log_name          | Pos  | Event_type     | Server_id | End_log_pos | Info                                     |

+-------------------+------+----------------+-----------+-------------+------------------------------------------+

| master-bin. |   | Query          |          |          | create database darren                   |

| master-bin. |   | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |   | Query          |          |          | use `darren`; create table test (id int) |

| master-bin. |   | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |   | Query          |          |          | BEGIN                                    |

| master-bin. |   | Table_map      |          |          | table_id:  (darren.test)              |

| master-bin. |   | Write_rows     |          |          | table_id:  flags: STMT_END_F          |

| master-bin. |   | Xid            |          |          | COMMIT /* xid=44 */                      |

| master-bin. |   | Anonymous_Gtid |          |         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |  | Query          |          |         | BEGIN                                    |

| master-bin. |  | Table_map      |          |         | table_id:  (darren.test)              |

| master-bin. |  | Update_rows    |          |         | table_id:  flags: STMT_END_F          |

| master-bin. |  | Xid            |          |         | COMMIT /* xid=49 */                      |

| master-bin. |  | Anonymous_Gtid |          |         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |

| master-bin. |  | Query          |          |         | BEGIN                                    |

| master-bin. |  | Table_map      |          |         | table_id:  (darren.test)              |

| master-bin. |  | Delete_rows    |          |         | table_id:  flags: STMT_END_F          |

| master-bin. |  | Xid            |          |         | COMMIT /* xid=52 */                      |

+-------------------+------+----------------+-----------+-------------+------------------------------------------+

#错误sql不会写入binlog

mysql主库binlog清空，测试教学环境用，线上环境慎用。或者初搭建mysql主从用

mysql> reset master;

Query OK,  rows affected (0.01 sec)

mysql> show master logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin. |        |

+-------------------+-----------+

 row in set (0.00 sec)

mysql> show binlog events in 'master-bin.000001' ;

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                  |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| master-bin. |    | Format_desc    |          |          | Server ver: 5.7.-log, Binlog ver:  |

| master-bin. |  | Previous_gtids |          |          |                                       |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

mysql主库刷新binlog，一般来说也是测试环境用

mysql> flush logs;               #新增一个日志

Query OK,  rows affected (0.01 sec)

mysql> show master logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin. |        |

| master-bin. |        |

+-------------------+-----------+

 rows in set (0.01 sec)

mysql> use darren;

Database changed

mysql> insert into test values ();

Query OK,  row affected (0.01 sec)

mysql> show master logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin. |        |

| master-bin. |        |

+-------------------+-----------+

 rows in set (0.00 sec)

mysql> show binlog events in 'master-bin.000001' ;

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| master-bin. |  | Format_desc |  |  | Server ver: 5.7.-log, Binlog ver:  |

| master-bin. |  | Previous_gtids |  |  | |

| master-bin. |  | Rotate |  |  | master-bin.;pos= |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

mysql> show binlog events in 'master-bin.000002' ;   #查看日志内容

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                  |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

| master-bin. |    | Format_desc    |          |          | Server ver: 5.7.-log, Binlog ver:  |

| master-bin. |  | Previous_gtids |          |          |                                       |

| master-bin. |  | Anonymous_Gtid |          |          | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |

| master-bin. |  | Query          |          |          | BEGIN                                 |

| master-bin. |  | Table_map      |          |          | table_id:  (darren.test)           |

| master-bin. |  | Write_rows     |          |          | table_id:  flags: STMT_END_F       |

| master-bin. |  | Xid            |          |          | COMMIT /* xid=62 */                   |

+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

测试数据删除，让两个数据库的内容保持一致

mysql> show binlog events in 'master-bin.000002 ^C;

mysql> drop database darren;

Query OK,  row affected (0.02 sec)

mysql> reset master;

Query OK,  rows affected (0.01 sec)

最新文章

  1. flag+文件操作
  2. 第3.2 使用案例1：股票期货stock portfolio 21050917
  3. 编程之美2014挑战赛 复赛 Codehunt平台试题答案
  4. I-MooFest(POJ 1990)
  5. 使用 sp_executesql
  6. JavaWeb中登陆功能
  7. C++编写操作系统（1）：基于 EFI 的 Bootloader
  8. mvn创建web项目
  9. 转载Eclipse中Maven WEB工程tomcat项目添加调试
  10. 关于自定义的 XIB cell上的 button如何在控制器里实现点击方法
  11. 在Selenium Webdriver中使用XPath Contains、Sibling函数定位
  12. Wincc flexable的按钮组态
  13. error：com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
  14. python字符串与数字类型转化
  15. sql 按年月日统计
  16. jxl应用事例
  17. 另类P、V操作问题-详细图解
  18. Linux编程 15 文件权限(用户管理 useradd,userdel,usermod,passwd,chpasswd,chsh, chfn,chage)
  19. web前端识别文字转语音
  20. box-shadow阴影 三面显示

热门文章

  1. 基本的Sql编写注意事项
  2. iOS 9整理
  3. @loj - 2196@「SDOI2014」Lis
  4. 微博第三方登录时，域名使用错误报错， Laravel \ Socialite \ Two \ InvalidStateException No message
  5. 不插字段，直接利用OracleSpatial计算
  6. 从 SGD 到 Adam —— 深度学习优化算法概览(一) 重点
  7. Python type hints 之 Optional，Union
  8. Laravel 修改默认日志文件名称和位置
  9. codedecision P1112 区间连续段 题解 线段树
  10. 通俗理解tf.name_scope()、tf.variable_scope()