mysql binlog相关命令

2017-12-25 mysql 评论字数统计: 971(字) 阅读时长: 5(分)

以myslq 5.7为例

开启bin-log

log-bin=mysql-bin
server-id=1

1.查看状态

mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |
| log_bin_basename                       | /var/lib/mysql/mysql-bin       |
| log_bin_index                          | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysqld.log            |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | OFF                            |
| log_slave_updates                      | OFF                            |
| log_slow_admin_statements              | OFF                            |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)

binlog 操作

1.创建一个数据库

mysql> create database wordpress;
Query OK, 1 row affected (0.01 sec)

2.查看binlog 日志

   [root@centos ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
 …………  


  #171225 15:29:14 server id 1  end_log_pos 328 CRC32 0x9be9c989     Query    thread_id=7    exec_time=0    error_code=0@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    create database wordpress
    /*!*/;
    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是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中

注: server id 1 数据库主机的服务号；
end_log_pos 328 pos点
thread_id=7 线程号

3.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：

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

选项解析：
IN ‘log_name’ 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)

mysql> show binlog events IN 'mysql-bin.000001'
    -> ;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219 | Query          |         1 |         328 | create database wordpress             |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)

mysql> show binlog events IN 'mysql-bin.000001'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.20-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 328
       Info: create database wordpress
4 rows in set (0.00 sec)

ERROR:
No query specified

查询第一个（最早）的binlog 日志

mysql> show binlog events\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.20-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 328
       Info: create database wordpress
4 rows in set (0.00 sec)

ERROR:
No query specified

指定pos点查询

mysql> show binlog events in 'mysql-bin.000001' from 219
    -> ;
+------------------+-----+------------+-----------+-------------+---------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                      |
+------------------+-----+------------+-----------+-------------+---------------------------+
| mysql-bin.000001 | 219 | Query      |         1 |         328 | create database wordpress |
+------------------+-----+------------+-----------+-------------+---------------------------+
1 row in set (0.00 sec)

从指定pos 查询10条

mysql> show binlog events in 'mysql-bin.000001' from 219 limit 10\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 328
       Info: create database wordpress
1 row in set (0.00 sec)

ERROR:
No query specified

恢复相关命令

mysqlbinlog --stop-position=154 --database=wordpress mysql-bin.000002 | mysql -uroot -ppasswd -v wordpress

常用选项：
–start-position=953 起始pos点
–stop-position=1437 结束pos点
–start-datetime=”2013-11-29 13:18:54” 起始时间点
–stop-datetime=”2013-11-29 13:21:53” 结束时间点
–database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

不常用选项：
-u –user=name Connect to the remote server as username.连接到远程主机的用户名
-p –password[=name] Password to connect to remote server.连接到远程主机的密码
-h –host=name Get the binlog from server.从远程主机上获取binlog日志
–read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

本文链接： https://www.awen.me/posts/28122.html

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

阿文运维 & CSDN 专家作者

喜欢新事物，关注云计算行业，对新的技术有追求，喜欢写作和coding