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 | +-------------------------------+------+-------------+------------+-------------+-----------------------------------------------------------------------+