All Products
Search

This Product

    PolarDB:Execute SQL statements to manage PolarDB-X binary logs

    Document Center

    PolarDB:Execute SQL statements to manage PolarDB-X binary logs

    Last Updated:Mar 28, 2026

    PolarDB for Xscale (PolarDB-X) supports a set of MySQL-compatible SQL statements for viewing and managing binary log files. In multi-stream mode, most ecosystem tools for MySQL binary log consumption — such as change data capture (CDC) connectors and replication tools — cannot use the extended WITH clause syntax that PolarDB-X provides. To let these tools work with specific log streams without code changes, PolarDB-X introduces an account binding mechanism: bind a database account to a specific log stream, and standard statements issued by that account automatically target the bound stream.

    Supported statements

    StatementDescription
    SHOW BINARY STREAMSLists all binary log streams in the instance
    SHOW BINARY LOGSLists all binary log files, with optional stream filtering
    SHOW BINLOG EVENTSShows events in a binary log file, with optional stream filtering
    SHOW MASTER STATUSShows the current binary log file and position, with optional stream filtering
    FLUSH LOGSRotates log files

    How it works

    In multi-stream mode, PolarDB-X extends these three statements with a WITH clause so you can target a specific log stream or stream group:

    SHOW BINARY LOGS WITH 'group1_stream_0';
SHOW BINLOG EVENTS WITH 'group1_stream_0' LIMIT 5;
SHOW MASTER STATUS WITH 'group1_stream_0';

    Most ecosystem tools do not support this extended syntax. CloudCanal is an exception. Rather than requiring changes to each tool, PolarDB-X provides the account binding mechanism: bind a database account to a specific binary log stream, and standard statements issued by that account automatically target the bound stream.

    A typical multi-stream workflow uses these statements in sequence:

    1. Run SHOW BINARY STREAMS to discover all streams and their current log positions.

    2. Run SHOW BINARY LOGS WITH '<stream>' to list log files for a specific stream.

    3. Run SHOW MASTER STATUS WITH '<stream>' to confirm the current write position in that stream.

    Limitations

    • The account binding mechanism requires PolarDB-X V5.4.19 or later.

    • If you do not want to enable the binding mechanism, run the following command in the PolarDB-X command-line interface (CLI):

      set global ENABLE_EXTRACT_STREAM_NAME_FROM_USER = false

    Set up account binding

    The following steps create one database account per binary log stream, bind each account to its stream by using the required naming format, and verify that the binding works.

    1. List all binary log streams in the instance.

      SHOW BINARY STREAMS;

      The output lists each stream with its current log file and position:

      +--------+-----------------+-------------------------------+----------+
| 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. Create one account per stream. PolarDB-X determines the stream binding from the account name, which must follow the format <stream_name>_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';

    3. Grant the required privileges to each account.

      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'@'%';

    4. Log in with a binding account and verify that the statements return data for the bound stream only.

      SELECT USER();
-- Expected: group1_stream_0_cdc_user@127.0.0.1

SHOW BINARY LOGS;
-- Expected: log files for group1_stream_0 only

SHOW MASTER STATUS;
-- Expected: current position in group1_stream_0

      If the results match the bound stream, the binding is working correctly.

    Examples

    The following examples show each statement in three contexts:

    • Standard account: returns aggregated data across all streams

    • Standard account with `WITH` clause: targets one specific stream

    • Binding account: automatically targets the bound stream without the WITH clause

    SHOW BINARY LOGS

    Standard account — aggregated view across all streams

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

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

    Standard account with `WITH` clause — single stream

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

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

    Binding account — same result without `WITH` clause

    SELECT USER();
+------------------------------------+
| USER()                             |
+------------------------------------+
| group1_stream_0_cdc_user@127.0.0.1 |
+------------------------------------+

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

    Standard account — aggregated view across all streams

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| FILE          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
+---------------+----------+--------------+------------------+-------------------+
| binlog.001219 |     4899 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

    Standard account with `WITH` clause — single stream

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

SHOW MASTER STATUS WITH 'group1_stream_0';
+-------------------------------+----------+--------------+------------------+-------------------+
| FILE                          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
+-------------------------------+----------+--------------+------------------+-------------------+
| group1_stream_0_binlog.000442 |    10840 |              |                  |                   |
+-------------------------------+----------+--------------+------------------+-------------------+

    Binding account — same result without `WITH` clause

    SELECT USER();
+------------------------------------+
| USER()                             |
+------------------------------------+
| group1_stream_0_cdc_user@127.0.0.1 |
+------------------------------------+

SHOW MASTER STATUS;
+-------------------------------+----------+--------------+------------------+-------------------+
| FILE                          | POSITION | BINLOG_DO_DB | BINLOG_IGNORE_DB | EXECUTED_GTID_SET |
+-------------------------------+----------+--------------+------------------+-------------------+
| group1_stream_0_binlog.000443 |     1118 |              |                  |                   |
+-------------------------------+----------+--------------+------------------+-------------------+

    SHOW BINLOG EVENTS

    Standard account — aggregated view across all streams

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

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

    Standard account with `WITH` clause — single stream

    SELECT USER();
+-------------------------+
| USER()                  |
+-------------------------+
| polardbx_root@127.0.0.1 |
+-------------------------+

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

    Binding account — same result without `WITH` clause

    SELECT USER();
+------------------------------------+
| USER()                             |
+------------------------------------+
| group1_stream_0_cdc_user@127.0.0.1 |
+------------------------------------+

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