mysql中高阶玩法系列（八）

朴灿烈づ我的快乐病毒、 2022-09-06 10:11 118阅读 0赞

### 目录 ###

*  列出服务器上的二进制日志文件
 *  master服务器的二进制日志文件的状态信息
 *  显示二进制日志中的事件
 *  列出了在 table 缓存中当前打开的非临时的表
 *  巨人的肩膀
 *  VChat

--------------------

先定义一张表，在之后的讲解中的所有例子，都是基于这张表做的演示，那么就定义一张最常见的用户表吧。

CREATE TABLE `user` (
        `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
        `pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
        `created_at` INT(8) UNSIGNED NOT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_created_at` (`created_at`)
    )  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI;

插入几条数据，方便之后快乐的玩耍

INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A', '555', '1559318400');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('2', '牛B', '555', '1559318400');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('3', '牛C', '555', '1573441871');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('4', '牛X', '555', '1558329240');

![在这里插入图片描述][20190725224136243.]  
![在这里插入图片描述][20190725230908971.]

# 列出服务器上的二进制日志文件 #

show binary logs

+------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000081 |       177 |
    | mysql-bin.000082 |       154 |
    +------------------+-----------+

MySQL的二进制日志可以说是MySQL最重要的日志了，它记录的是 已经提交commit 的各种DML和DDL语句(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的

一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:

其一：主从复制，MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

其二：基于时间点的恢复，自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000\*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件

# master服务器的二进制日志文件的状态信息 #

查看master状态，即正在写入的二进制文件一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

show master status

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>File</td> 
   <td>当前正在写入的binlog文件</td> 
  </tr> 
  <tr> 
   <td>Position</td> 
   <td>当前正在写入的位置</td> 
  </tr> 
  <tr> 
   <td>Binlog_Do_DB</td> 
   <td>只记录指定数据库的二进制文件</td> 
  </tr> 
  <tr> 
   <td>Binlog_Ignore_DB</td> 
   <td>不记录指定数据库的二进制文件</td> 
  </tr> 
  <tr> 
   <td>Executed_Gtid_Set</td> 
   <td>记录当前执行的GTID，显示在主库上执行全局事务标识符</td> 
  </tr> 
 </tbody> 
</table>

# 显示二进制日志中的事件 #

可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

mysql> show binlog events in 'mysql-bin.000082'\G
    *************************** 1. row ***************************
       Log_name: mysql-bin.000082
            Pos: 4
     Event_type: Format_desc
      Server_id: 1
    End_log_pos: 123
           Info: Server ver: 5.7.21-log, Binlog ver: 4
    *************************** 2. row ***************************
       Log_name: mysql-bin.000082
            Pos: 123
     Event_type: Previous_gtids
      Server_id: 1
    End_log_pos: 154
           Info: 
    2 rows in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Log_name</td> 
   <td>查询的binlog日志文件名</td> 
  </tr> 
  <tr> 
   <td>Pos</td> 
   <td>在binlog的起始点</td> 
  </tr> 
  <tr> 
   <td>Event_type</td> 
   <td>描述事件类型的标识符<br>1. <code>UNKNOWN</code>：这个事件类型应该永远不会出现。它从不会写入binlog中。如果binlog中的事件没法被识别成其他已知事件的话，就会被标识为UNKNOWN<br>2. <code>START</code>：该事件是写在每个二进制文件开头的一个描述性的事件<br>3. <code>QUERY</code>： (1)事务开始时，在binlog中有一个QUERY_EVENT类型的BEGIN。(2)在statement格式中，具体执行的SQL语句会保存在该事件中。(3)对于ROW格式的binlog，所有DDL操作以文本的形式记录在该事件中。<br>4. <code>STOP</code>：当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止<br>5. <code>ROTATE</code>：binlog文件中的最后一个event，记录下一个binlog的信息<br>6. <code>INTVAR</code>：该事件在语句使用了AUTO_INCREMENT 列或者LAST_INSERT_ID()函数时写入，它只会在QUERY_EVENT事件前写入，并且在基于row模式的日志记录中不会出现<br>7. <code>LOAD</code>：执行LOAD DATA INFILE 语句时产生此事件，即从外部文件导入数据<br>8. <code>SLAVE</code>：该事件还未被使用<br>9. <code>CREATE_FILE</code>：<br>10. <code>APPEND_BLOCK</code>：<br>11. <code>EXEC_LOAD</code>：<br>12. <code>DELETE_FILE</code>：<br>13. <code>NEW_LOAD</code>：<br>14.<code>RAND</code>：执行包含RAND()函数的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下<br>15.<code>USER_VAR</code>：执行包含了用户变量的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下<br>16.<code>FORMAT_DESCRIPTION</code>：binlog文件的第一个event，记录版本号等元数据信息<br>17. <code>XID</code>：二阶段提交xid记录。在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务<br>18. <code>BEGIN_LOAD_QUERY</code>：<br>19. <code>EXECUTE_LOAD_QUERY</code>：<br>20. <code>TABLE_MAP</code>：row模式下记录表源数据，对读取行记录提供规则参考&lt;<br>21.<code>INCIDENT</code>：该事件在主库中发生异常，提醒从库主库可能发生会导致数据不一致的事件<br>22. <code>HEARTBEAT</code>：这个事件是由master发给slave的，让slave知道master还活着。这类事件不会再binlog或relay log中出现。他们由master的dump事件线程产生，然后直接发给了slave。slave收到后，校验事件内容后，直接抛弃这个事件，而不会写到relay log中<br>23. <code>IGNORABLE</code>：<br>24. <code>ROWS_QUERY</code>：记录原始的 SQL，row格式binlog DML也会记录在Rows_query事件中<br>25. <code>WRITE_ROWS</code>：对应insert操作，包含了要插入的数据<br>26. <code>UPDATE_ROWS</code>：对应update操作，不仅包含了修改后的数据，还包含了修改前的值<br>27. <code>DELETE_ROWS</code>：对应delete操作，仅仅需要指定删除的主<br>28. <code>GTID</code>：事务的GTID值，在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID<br>29. <code>ANONYMOUS_GTID</code>：如果没有开启GTID模式，事件类型会是ANONYMOUS_GTID_EVENT<br>30. <code>PREVIOUS_GTIDS</code>：上一个binlog结束的gtid值</td> 
  </tr> 
  <tr> 
   <td>Server_id</td> 
   <td>标识是由哪台服务器执行的</td> 
  </tr> 
  <tr> 
   <td>End_log_pos</td> 
   <td>在binlog的结束点</td> 
  </tr> 
  <tr> 
   <td>Info</td> 
   <td>执行的sql语句</td> 
  </tr> 
 </tbody> 
</table>

每个binlog事件都以一个binlog事件头开始，然后是一个binlog事件类型特定的数据部分  
![在这里插入图片描述][73c51bb9268d49759273b5da1f78c9ea.png]

> GTID即全局事务ID，GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID。

# 列出了在 table 缓存中当前打开的非临时的表 #

show open tables

+--------------------+------------------------------------------------------+--------+-------------+
    | Database           | Table                                                | In_use | Name_locked |
    +--------------------+------------------------------------------------------+--------+-------------+
    | performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
    | performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
    | performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |
    | performance_schema | replication_connection_status                        |      0 |           0 |
    | mysql              | time_zone_leap_second                                |      0 |           0 |
    | mysql              | columns_priv                                         |      0 |           0 |
    | performance_schema | metadata_locks                                       |      0 |           0 |
    | performance_schema | status_by_user                                       |      0 |           0 |
    +--------------------+------------------------------------------------------+--------+-------------+

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Database</td> 
   <td>数据库名称</td> 
  </tr> 
  <tr> 
   <td>Table</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>In_use</td> 
   <td>表示有多少线程正在使用某张表，有可能已经给这个表上锁，或者等待获得锁</td> 
  </tr> 
  <tr> 
   <td>Name_locked</td> 
   <td>显示表名称是否被锁定，0往往是没有锁定，1为写锁, 写锁基本发生在delete table或rename table时</td> 
  </tr> 
 </tbody> 
</table>

> 具体应用：
> 
>  *  查看表是否在使用  
>     show open tables where in\_use > 0 ;

# 巨人的肩膀 #

> 从他人的工作中汲取经验来避免自己的错误重复，正如我们是站在巨人的肩膀上才能做出更好的成绩。

[https://docs.pingcap.com/zh/tidb/stable/mysql-schema][https_docs.pingcap.com_zh_tidb_stable_mysql-schema]  
[http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/preface.com.coder114.cn.html][http_www.searchdoc.cn_rdbms_mysql_dev.mysql.com_doc_refman_5.7_en_preface.com.coder114.cn.html]  
[https://www.kancloud.cn/curder/mysql/355287][https_www.kancloud.cn_curder_mysql_355287]  
[http://www.jiangxinlingdu.com/mysql/2019/06/07/binlog.html][http_www.jiangxinlingdu.com_mysql_2019_06_07_binlog.html]  
[https://segmentfault.com/a/1190000040042493][https_segmentfault.com_a_1190000040042493]  
[https://cloud.tencent.com/developer/article/1508857][https_cloud.tencent.com_developer_article_1508857]  
[https://dev.mysql.com/doc/internals/en/event-meanings.html][https_dev.mysql.com_doc_internals_en_event-meanings.html]  
[https://gitbook.curiouser.top/origin/mysql-binlog.html][https_gitbook.curiouser.top_origin_mysql-binlog.html]  
[https://weikeqin.com/2019/09/05/mysql-lock-table-solution/][https_weikeqin.com_2019_09_05_mysql-lock-table-solution]

# VChat #

一个没有哆啦A梦和静香的IT码农，不专业Gopher  
![在这里插入图片描述][20201206221123294.png]

[20190725224136243.]: /images/20220829/13a2be6f819e4666b81b8413df63c64c.png
[20190725230908971.]: /images/20220829/67c23660f3d942279f0629f1cb81c878.png
[73c51bb9268d49759273b5da1f78c9ea.png]: /images/20220829/205739d097c14d71a29ad454f852e2d5.png
[https_docs.pingcap.com_zh_tidb_stable_mysql-schema]: https://docs.pingcap.com/zh/tidb/stable/mysql-schema
[http_www.searchdoc.cn_rdbms_mysql_dev.mysql.com_doc_refman_5.7_en_preface.com.coder114.cn.html]: http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/preface.com.coder114.cn.html
[https_www.kancloud.cn_curder_mysql_355287]: https://www.kancloud.cn/curder/mysql/355287
[http_www.jiangxinlingdu.com_mysql_2019_06_07_binlog.html]: http://www.jiangxinlingdu.com/mysql/2019/06/07/binlog.html
[https_segmentfault.com_a_1190000040042493]: https://segmentfault.com/a/1190000040042493
[https_cloud.tencent.com_developer_article_1508857]: https://cloud.tencent.com/developer/article/1508857
[https_dev.mysql.com_doc_internals_en_event-meanings.html]: https://dev.mysql.com/doc/internals/en/event-meanings.html
[https_gitbook.curiouser.top_origin_mysql-binlog.html]: https://gitbook.curiouser.top/origin/mysql-binlog.html
[https_weikeqin.com_2019_09_05_mysql-lock-table-solution]: https://weikeqin.com/2019/09/05/mysql-lock-table-solution/
[20201206221123294.png]: /images/20220829/f94459ebcce146658ccd2b2704bbe829.png