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:Jun 03, 2024

    Overview

    PolarDB-X provides the following SQL statements that are compatible with MySQL. You can view and manage binary log files by using these SQL statements.

    Compatibility with binary logging in multi-stream mode

    The SHOW BINARY LOGS, SHOW BINLOG EVENTS, and SHOW MASTER STATUS statements are most commonly used. Multiple ecological tools that are compatible with MySQL binary logs are also used in the code.

    For binary logging in multi-stream mode, PolarDB-X extends the syntax of these three SQL statements. You can add a WITH clause when you use these SQL statements to manage specific log streams or stream groups. However, multiple ecological tools do not support the extended syntax except CloudCanal. It takes a long time to develop the adaption of ecological tools to the PolarDB-X extended syntax. In this case, PolarDB-X provides a mechanism that binds accounts to binary log streams. This way, you can use binary logging in multi-stream mode without adaption of ecological tools.

    Limits

    • The binding mechanism supports only PolarDB-X instances whose version is V5.4.19 or later.

    • If you do not want to enable the binding mechanism, run the set global ENABLE_EXTRACT_STREAM_NAME_FROM_USER = false command in the PolarDB-X CLI.

    Usage

    1. Execute the SHOW BINARY STREAMS statement to view all binary log streams of a PolarDB-X instance. 

      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. Create an account for each binary log stream, bind the account to the stream, and then grant the required permissions to the account. The account name is specified in the Stream name + _cdc_user format. The following sample code provides an example on the binding mechanism.

      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. Use a binding account to log on to the PolarDB-X instance, execute the SHOW BINARY LOGS, SHOW BINLOG EVENTS, and SHOW MASTER STATUS statements on the instance, and then verify the returned result. If the returned result corresponds to the information about the binary log stream that is bound to the account, the binding mechanism works as expected.

    Examples

    SHOW BINARY LOGS

    • Use a standard account to execute the SHOW BINARY LOGS statement.

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

    • Use a standard account and a WITH clause to execute the SHOW BINARY LOGS statement.

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

    • Use a binding account to execute the SHOW BINARY LOGS statement.

      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

    • Use a standard account to execute the SHOW MASTER STATUS statement.

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

    • Use a standard account and a WITH clause to execute the SHOW MASTER STATUS statement.

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

    • Use a binding account to execute the SHOW MASTER STATUS statement.

      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

    • Use a standard account to execute the SHOW BINLOG EVENTS statement.

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

    • Use a standard account and a WITH clause to execute the SHOW BINLOG EVENTS statement.

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

    • Use a binding account to execute the SHOW BINLOG EVENTS statement.

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