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

How the global binary log feature supports data replication

  • Global binary logs in PolarDB-X are generated based on physical binary logs stored in data nodes. The distributed transaction feature is disabled for global binary logs. Only the non-distributed transaction feature is enabled for global binary logs. Global binary logs are compatible with the format of MySQL binary log files. The global 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.
  • When you use the global binary log feature, physical binary logs are not summarized in a simple manner. The Gather operator and the MergeSort operator of the feature ensure the integrity and order of distributed transactions. This helps ensure high data consistency. For example, in scenarios in which money is transferred, you can connect to a MySQL server that is associated with PolarDB-X. In this case, you can obtain the account balances that are consistent between a table in the MySQL server and a table in the corresponding source server at each point in time.
  • In PolarDB-X, various protocols and algorithms are used to ensure the capabilities of global binary logs. This helps ensure that instance modifications do not affect data replication in a negative manner. For example, if you perform a failover for high availability (HA), add nodes, delete nodes, scale out a PolarDB-X instance, or execute distributed DDL statements, data replication is not affected in a negative manner.

Limits

  • If global transaction identifiers (GTIDs) are enabled for your PolarDB-X instance, you cannot replicate data.
  • You can merge distributed transactions 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

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, statements that are used for MySQL replication are supported.

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 from 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 = {01}
       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 = {01}
       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 = {01}
       MASTER_DELAY = interval
       MASTER_COMPRESSION_ALGORITHMS = 'value'
       MASTER_ZSTD_COMPRESSION_LEVEL = level
       MASTER_SSL = {01}
       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 = {01}
       MASTER_TLS_VERSION = 'protocol_list'
       MASTER_TLS_CIPHERSUITES = 'ciphersuite_list'
       MASTER_PUBLIC_KEY_PATH = 'key_file_name'
       GET_MASTER_PUBLIC_KEY = {01}
       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