SQL statements for data replication

This topic describes the SQL statements that can be used to replicate data between MySQL servers based on binary log files.

How the binary log feature supports data replication

• Binary log files in PolarDB-X are generated based on physical binary logs stored in data nodes. The distributed transaction feature is disabled for binary logs. Only the non-distributed transaction feature is enabled for binary logs. Binary logs can be parsed by MySQL. The binary log feature allows you to execute mysqldump statements to replicate data. You can replicate transaction logs in a PolarDB-X instance in the same manner in which you replicate transaction logs in a standalone MySQL database.

• In PolarDB-X, the binary log feature supports two types of binary logs: binary logs in single-stream mode and binary logs in multi-stream mode. For more information, see Binary log feature.

• In PolarDB-X, various protocols and algorithms are used to ensure the stability and reliability of binary logs:

  • If the high-availability (HA) switchover occurs in CDC or the number of CDC nodes changes, the recovery time objective (RTO) of binary logs is less than 30 seconds.

  • If you scale up or down the data nodes, only one link restart is triggered, and the RTO of binary logs is less than 20 seconds.

  • If DDL changes occur in a PolarDB-X instance, the binary logs are not blocked and can be recorded online.

Limits

• If the global transaction identifier (GTID) mode is enabled for your PolarDB-X instance, data replication is not supported.

• If you want to replicate binary logs generated in single-stream mode, distributed transactions can be merged only when the transaction policy is set to Timestamp Oracle (TSO). TSO helps ensure strong consistency.

SQL statements supported by the source instance for data replication

• SHOW BINARY STREAMS

• SHOW BINARY LOGS

• SHOW BINLOG EVENTS

• SHOW MASTER STATUS

SQL statements supported by the destination instance for data replication

• If the destination instance is a native MySQL server or a MySQL server that is consistent with a native MySQL server in SQL syntax, the SQL statements that are used for MySQL replication are supported.

• If you want to replicate binary logs generated in multi-stream mode, you must create a replication link for each stream and regard each log stream as a standalone MySQL database.

Note If the destination instance is a PolarDB-X instance, statements that are used for MySQL replication are not supported.

• The following sample code provides an example on how to configure information about the source instance in the destination instance:

  CHANGE MASTER TO option [, option] ... [ channel_option ]
  
  option: {
      MASTER_BIND = 'interface_name'
    | MASTER_HOST = 'host_name'
    | MASTER_USER = 'user_name'
    | MASTER_PASSWORD = 'password'
    | MASTER_PORT = port_num
    | PRIVILEGE_CHECKS_USER = {'account' | NULL}
    | REQUIRE_ROW_FORMAT = {0|1}
    | REQUIRE_TABLE_PRIMARY_KEY_CHECK = {STREAM | ON | OFF}
    | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {OFF | LOCAL | uuid}
    | MASTER_LOG_FILE = 'source_log_name'
    | MASTER_LOG_POS = source_log_pos
    | MASTER_AUTO_POSITION = {0|1}
    | RELAY_LOG_FILE = 'relay_log_name'
    | RELAY_LOG_POS = relay_log_pos
    | MASTER_HEARTBEAT_PERIOD = interval
    | MASTER_CONNECT_RETRY = interval
    | MASTER_RETRY_COUNT = count
    | SOURCE_CONNECTION_AUTO_FAILOVER = {0|1}
    | MASTER_DELAY = interval
    | MASTER_COMPRESSION_ALGORITHMS = 'value'
    | MASTER_ZSTD_COMPRESSION_LEVEL = level
    | MASTER_SSL = {0|1}
    | MASTER_SSL_CA = 'ca_file_name'
    | MASTER_SSL_CAPATH = 'ca_directory_name'
    | MASTER_SSL_CERT = 'cert_file_name'
    | MASTER_SSL_CRL = 'crl_file_name'
    | MASTER_SSL_CRLPATH = 'crl_directory_name'
    | MASTER_SSL_KEY = 'key_file_name'
    | MASTER_SSL_CIPHER = 'cipher_list'
    | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
    | MASTER_TLS_VERSION = 'protocol_list'
    | MASTER_TLS_CIPHERSUITES = 'ciphersuite_list'
    | MASTER_PUBLIC_KEY_PATH = 'key_file_name'
    | GET_MASTER_PUBLIC_KEY = {0|1}
    | NETWORK_NAMESPACE = 'namespace'
    | IGNORE_SERVER_IDS = (server_id_list)
  }
  
  channel_option:
      FOR CHANNEL channel
  
  server_id_list:
      [server_id [, server_id] ... ]
  

• The following sample code provides an example on how to enable data replication:

  START {SLAVE | REPLICA}
  

• The following sample code provides an example on how to stop data replication:

  STOP {SLAVE | REPLICA}
  

• The following sample code provides an example on how to reset data replication. Take note that you must stop your data replication task before you execute the following statement:

  RESET {SLAVE | REPLICA} [ALL] [channel_option]
  
  channel_option:
      FOR CHANNEL channel