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