• MySQL的binlog操作


    1. MySQL的binlog有三种模式: statement, row and mixed, 从5.1开始支持row, 默认是row模式

    2. 设置参数

    # 要配置在mysqld下
    [mysqld]
    binlog_format=ROW

    设置binlog过期清理时间 expire_logs_days 

    # Should be unique
    server-id = 1
    log-bin = master-bin
    # The number of days for automatic binary log file removal
    expire_logs_days = 14
    # 0:log&flush per 1s; 1:log&flush per commit; 2:log per commit & flush per 1s
    innodb_flush_log_at_trx_commit=0
    # The number of binary log commit groups to collect before sync to disk.
    sync_binlog = 10

    3. 查看binlog是否开启及其参数

    mysql> show variables like 'log_bin%';
    +---------------------------------+---------------------------------+
    | Variable_name                   | Value                           |
    +---------------------------------+---------------------------------+
    | log_bin                         | ON                              |
    | log_bin_basename                | /var/lib/mysql/master-bin       |
    | log_bin_index                   | /var/lib/mysql/master-bin.index |
    | log_bin_trust_function_creators | OFF                             |
    | log_bin_use_v1_row_events       | OFF                             |
    +---------------------------------+---------------------------------+
    5 rows in set (0.01 sec)
    
    mysql> show variables like 'binlog%';
    +-----------------------------------------+--------------+
    | Variable_name                           | Value        |
    +-----------------------------------------+--------------+
    | binlog_cache_size                       | 32768        |
    | binlog_checksum                         | CRC32        |
    | binlog_direct_non_transactional_updates | OFF          |
    | binlog_error_action                     | ABORT_SERVER |
    | binlog_format                           | ROW          |
    | binlog_group_commit_sync_delay          | 0            |
    | binlog_group_commit_sync_no_delay_count | 0            |
    | binlog_gtid_simple_recovery             | ON           |
    | binlog_max_flush_queue_time             | 0            |
    | binlog_order_commits                    | ON           |
    | binlog_row_image                        | FULL         |
    | binlog_rows_query_log_events            | OFF          |
    | binlog_stmt_cache_size                  | 32768        |
    +-----------------------------------------+--------------+
    13 rows in set (0.00 sec)

    4. 查看服务器的binlog状态

    mysql> show binary logs;
    +-------------------+-----------+
    | Log_name          | File_size |
    +-------------------+-----------+
    | master-bin.000008 |    321270 |
    | master-bin.000009 |      1026 |
    | master-bin.000010 |     10103 |
    | master-bin.000011 | 544150473 |
    | master-bin.000012 |   4155620 |
    | master-bin.000013 |  21550593 |
    +-------------------+-----------+
    6 rows in set (0.00 sec)
    
    mysql> show master logs;
    +-------------------+-----------+
    | Log_name          | File_size |
    +-------------------+-----------+
    | master-bin.000008 |    321270 |
    | master-bin.000009 |      1026 |
    | master-bin.000010 |     10103 |
    | master-bin.000011 | 544150473 |
    | master-bin.000012 |   4155620 |
    | master-bin.000013 |  21550593 |
    +-------------------+-----------+
    6 rows in set (0.00 sec)
    
    mysql> show master status;
    +-------------------+----------+--------------+------------------+-------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+-------------------+
    | master-bin.000013 | 21551221 |              |                  |                   |
    +-------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    5. 查看binlog内容

    # 查看指定binlog文件的内容语法:
    SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

    例如

    mysql> show binlog events limit 0, 5;
    +-------------------+-----+----------------+-----------+-------------+------------------------------------------+
    | Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
    +-------------------+-----+----------------+-----------+-------------+------------------------------------------+
    | master-bin.000008 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.14-log, Binlog ver: 4    |
    | master-bin.000008 | 123 | Previous_gtids |         1 |         154 |                                          |
    | master-bin.000008 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
    | master-bin.000008 | 219 | Query          |         1 |         290 | BEGIN                                    |
    | master-bin.000008 | 290 | Query          |         1 |         385 | use `yic`; DELETE FROM `yic`.`u_session` |
    +-------------------+-----+----------------+-----------+-------------+------------------------------------------+
    5 rows in set (0.00 sec)
    mysql> show binlog events in 'master-bin.000013' limit 0, 5;
    +-------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +-------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | master-bin.000013 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.14-log, Binlog ver: 4 |
    | master-bin.000013 | 123 | Previous_gtids |         1 |         154 |                                       |
    | master-bin.000013 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    | master-bin.000013 | 219 | Query          |         1 |         290 | BEGIN                                 |
    | master-bin.000013 | 290 | Table_map      |         1 |         373 | table_id: 111 (yic.u_session_log)     |
    +-------------------+-----+----------------+-----------+-------------+---------------------------------------+
    5 rows in set (0.00 sec)

    注意: 都是按时间正序排列.

    6. 使用mysqlbinlog查看binlog

    # 提取指定的binlog日志  (参数需要用绝对路径)
    mysqlbinlog /opt/data/APP01bin.000001
    # 根据位置提取
    mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001
    # 根据开始结束时间提取
    mysqlbinlog --start-datetime="2014-12-15 20:15:23" --stop-datetime="2014-12-15 20:30:23" /opt/data/APP01bin.000002 --result-file=extra02.sql
    # 对多个binlog文件, 提取指定数据库, 并转换字符集
    mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql
    # 提取远程的binlog, 并输出到本地
    mysqlbinlog -u someone -p -P3306 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql  

    mysqlbinlog 查看row模式的binlog

    # 使用--base64-output=decode-rows -v参数, 就能看懂了
    mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/master-bin.000013|more
  • 相关阅读:
    NOI2013
    【FINAL】NOI
    【jsoi】第一季 [略]精简题解
    浏览器缓存机制--小总结
    UC和QQ两个主流浏览器 * 点击触发微信分享到朋友圈或发送给朋友的功能(转载)
    webpack加载postcss,以及autoprefixer的loader
    HTTP协议中的短轮询、长轮询、长连接和短连接,看到一篇文章有感
    webpack2.0 css文件引入错误解决及图片输出在根目录配置问题
    webpack+vue 我的视角(持续更新)
    手机端图像编辑上传-cropper
  • 原文地址:https://www.cnblogs.com/milton/p/6825217.html
Copyright © 2020-2023  润新知