使用SQL语句操作PolarDB-X的binlog

更新时间:2024-05-10 02:37:22

SQL介绍

PolarDB-X提供了兼容MySQLSQL语法,可以对binlog文件进行查看和操作。

多流binlogSQL兼容

如上所列的SHOW BINARY LOGS、SHOW BINLOG EVENTS、SHOW MASTER STATUS是最常用的3SQL命令,适配MySQL binlog的很多生态工具在程序代码中也多有使用。

针对多流binlog,PolarDB-X对这3SQL命令进行了语法扩展,可以通过指定with选项,针对特定的流或流组进行操作。但众多的生态工具并不支持该扩展语法,推进生态工具适配PolarDB-X的扩展语法也需要较长的实施周期(Alibaba canal已经实现了适配),对此,PolarDB-X提供了一种将账号和binlog流进行绑定的机制,来实现0改造即可使用binlog多流服务。

使用限制

  • 大于等于5.4.19的版本才支持该绑定机制。

  • 如果不想开启绑定机制,在PolarDB-X命令行执行set global ENABLE_EXTRACT_STREAM_NAME_FROM_USER = false

使用方法

  1. 通过show binary stream查看所有的流信息。

    show binary streams;
    +--------+-----------------+-------------------------------+----------+
    | GROUP  | STREAM          | FILE                          | POSITION |
    +--------+-----------------+-------------------------------+----------+
    | group1 | group1_stream_0 | group1_stream_0_binlog.000438 |     6690 |
    | group1 | group1_stream_1 | group1_stream_1_binlog.000440 |     6690 |
    | group1 | group1_stream_2 | group1_stream_2_binlog.000452 |     6690 |
    | group1 | group1_stream_3 | group1_stream_3_binlog.000449 |     6690 |
    +--------+-----------------+-------------------------------+----------+
  2. 为每条流创建一个绑定账号并授予合适的权限,账号的命名规则为: 流名称 + _cdc_user后缀,如下所示

    CREATE USER IF NOT EXISTS 'group1_stream_0_cdc_user'@'%' identified by '123456';
    CREATE USER IF NOT EXISTS 'group1_stream_1_cdc_user'@'%' identified by '123456';
    CREATE USER IF NOT EXISTS 'group1_stream_2_cdc_user'@'%' identified by '123456';
    CREATE USER IF NOT EXISTS 'group1_stream_3_cdc_user'@'%' identified by '123456';
    
    grant ALL PRIVILEGES on *.* to 'group1_stream_0_cdc_user'@'%';
    grant ALL PRIVILEGES on *.* to 'group1_stream_1_cdc_user'@'%';
    grant ALL PRIVILEGES on *.* to 'group1_stream_2_cdc_user'@'%';
    grant ALL PRIVILEGES on *.* to 'group1_stream_3_cdc_user'@'%';
  3. 使用创建的账号登录PolarDB-X,并执行SHOW BINARY LOGS、SHOW BINLOG EVENTS、SHOW MASTER STATUS,对返回内容进行验证,返回内容如果对应的是当前登录账号中包含的binlog流的信息,则符合预期。

示例

SHOW BINARY LOGS

  • 使用普通账号执行show binary logs

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show binary logs;
    +---------------+-----------+
    | LOG_NAME      | FILE_SIZE |
    +---------------+-----------+
    | binlog.000001 |       260 |
    | binlog.000002 |      6031 |
    | binlog.000003 |      3046 |
    | binlog.000004 |      6827 |
    | binlog.000005 |      3046 |
    | binlog.000006 |    612906 |
    | binlog.000007 |   3440227 |
    | binlog.000008 |  10486619 |
    | binlog.000009 |   6825583 |
    | binlog.000010 |      3245 |
    +---------------+-----------+
  • 使用普通账号加with选项执行show binary logs

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show binary logs with 'group1_stream_0';
    +-------------------------------+-----------+
    | LOG_NAME                      | FILE_SIZE |
    +-------------------------------+-----------+
    | group1_stream_0_binlog.000001 |       276 |
    | group1_stream_0_binlog.000002 |      8634 |
    | group1_stream_0_binlog.000003 |      9629 |
    | group1_stream_0_binlog.000004 |    615708 |
    | group1_stream_0_binlog.000005 |   8775293 |
    | group1_stream_0_binlog.000006 |     10027 |
    | group1_stream_0_binlog.000007 |      9430 |
    | group1_stream_0_binlog.000008 |     13437 |
    | group1_stream_0_binlog.000009 |     10049 |
    | group1_stream_0_binlog.000010 |      9629 |
    +-------------------------------+-----------+
  • 使用绑定账号执行show binary logs

    select user();
    +------------------------------------+
    | USER()                             |
    +------------------------------------+
    | group1_stream_0_cdc_user@127.0.0.1 |
    +------------------------------------+
    1 row in set (0.00 sec)
    
    show binary logs;
    +-------------------------------+-----------+
    | LOG_NAME                      | FILE_SIZE |
    +-------------------------------+-----------+
    | group1_stream_0_binlog.000001 |       276 |
    | group1_stream_0_binlog.000002 |      8634 |
    | group1_stream_0_binlog.000003 |      9629 |
    | group1_stream_0_binlog.000004 |    615708 |
    | group1_stream_0_binlog.000005 |   8775293 |
    | group1_stream_0_binlog.000006 |     10027 |
    | group1_stream_0_binlog.000007 |      9430 |
    | group1_stream_0_binlog.000008 |     13437 |
    | group1_stream_0_binlog.000009 |     10049 |
    | group1_stream_0_binlog.000010 |      9629 |
    +-------------------------------+-----------+

SHOW MASTER STATUS

  • 使用普通账号执行show master status

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | FILE          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.001219 |     4899 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
  • 使用普通账号加with选项执行show master status

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show master status with 'group1_stream_0';
    +-------------------------------+----------+--------------+------------------+-------------------+
    | FILE                          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
    +-------------------------------+----------+--------------+------------------+-------------------+
    | group1_stream_0_binlog.000442 |    10840 |              |                  |                   |
    +-------------------------------+----------+--------------+------------------+-------------------+
  • 使用绑定账号执行show master status

    select user();
    +------------------------------------+
    | USER()                             |
    +------------------------------------+
    | group1_stream_0_cdc_user@127.0.0.1 |
    +------------------------------------+
    1 row in set (0.00 sec)
    
    show master status;
    +-------------------------------+----------+--------------+------------------+-------------------+
    | FILE                          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
    +-------------------------------+----------+--------------+------------------+-------------------+
    | group1_stream_0_binlog.000443 |     1118 |              |                  |                   |
    +-------------------------------+----------+--------------+------------------+-------------------+

SHOW BINLOG EVENTS

  • 使用普通账号执行show binlog events

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show binlog events limit 5;
    +---------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | LOG_NAME      | POS  | EVENT_TYPE  | SERVER_ID  | END_LOG_POS | INFO                                                                  |
    +---------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | binlog.000001 |    4 | Format_desc | 3189545694 |         123 | Server ver: 5.6.29-TDDL-5.4.19-SNAPSHOT, Binlog ver: 4                |
    | binlog.000001 |  123 | Rows_query  | 3189545694 |         216 | CTS::718558471351435270417166499290336542720000000000000000::FlushLog |
    | binlog.000001 |  216 | Rotate      | 3189545694 |         260 | binlog.000002;pos=4                                                   |
    +---------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
  • 使用普通账号加with选项执行show binlog events

    select user();
    +-------------------------+
    | USER()                  |
    +-------------------------+
    | polardbx_root@127.0.0.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    show binlog events with 'group1_stream_0' limit 5;
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | LOG_NAME                      | POS  | EVENT_TYPE  | SERVER_ID  | END_LOG_POS | INFO                                                                  |
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | group1_stream_0_binlog.000001 |    4 | Format_desc | 3189545694 |         123 | Server ver: 5.6.29-TDDL-5.4.19-SNAPSHOT, Binlog ver: 4                |
    | group1_stream_0_binlog.000001 |  123 | Rows_query  | 3189545694 |         216 | CTS::718558434551031404817166495609667010560000000000000000::FlushLog |
    | group1_stream_0_binlog.000001 |  216 | Rotate      | 3189545694 |         276 | group1_stream_0_binlog.000002;pos=4                                   |
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
  • 使用绑定账号执行show binlog events

    select user();
    +------------------------------------+
    | USER()                             |
    +------------------------------------+
    | group1_stream_0_cdc_user@127.0.0.1 |
    +------------------------------------+
    1 row in set (0.00 sec)
    
    show binlog events limit 5;
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | LOG_NAME                      | POS  | EVENT_TYPE  | SERVER_ID  | END_LOG_POS | INFO                                                                  |
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+
    | group1_stream_0_binlog.000001 |    4 | Format_desc | 3189545694 |         123 | Server ver: 5.6.29-TDDL-5.4.19-SNAPSHOT, Binlog ver: 4                |
    | group1_stream_0_binlog.000001 |  123 | Rows_query  | 3189545694 |         216 | CTS::718558434551031404817166495609667010560000000000000000::FlushLog |
    | group1_stream_0_binlog.000001 |  216 | Rotate      | 3189545694 |         276 | group1_stream_0_binlog.000002;pos=4                                   |
    +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+

  • 本页导读 (1)
  • SQL介绍
  • 多流binlog的SQL兼容
  • 使用限制
  • 使用方法
  • 示例
  • SHOW BINARY LOGS
  • SHOW MASTER STATUS
  • SHOW BINLOG EVENTS