SQL介绍
PolarDB-X提供了兼容MySQL的SQL语法,可以对binlog文件进行查看和操作。
多流binlog的SQL兼容
如上所列的SHOW BINARY LOGS、SHOW BINLOG EVENTS、SHOW MASTER STATUS是最常用的3个SQL命令,适配MySQL binlog的很多生态工具在程序代码中也多有使用。
针对多流binlog,PolarDB-X对这3个SQL命令进行了语法扩展,可以通过指定with选项,针对特定的流或流组进行操作。但众多的生态工具并不支持该扩展语法,推进生态工具适配PolarDB-X的扩展语法也需要较长的实施周期(Alibaba canal已经实现了适配),对此,PolarDB-X提供了一种将账号和binlog流进行绑定的机制,来实现0改造即可使用binlog多流服务。
使用限制
大于等于5.4.19的版本才支持该绑定机制。
如果不想开启绑定机制,在PolarDB-X命令行执行
set global ENABLE_EXTRACT_STREAM_NAME_FROM_USER = false
。
使用方法
通过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 | +--------+-----------------+-------------------------------+----------+
为每条流创建一个绑定账号并授予合适的权限,账号的命名规则为: 流名称 + _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'@'%';
使用创建的账号登录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