PolarDB for MySQL Multi-master Cluster (Database/Table) Edition increases the number of primary nodes from which you can write data to databases. The architecture supports concurrent data writes to databases from different primary nodes. The architecture also helps you dynamically switch the primary nodes of databases within seconds to improve the overall concurrent read and write capabilities of clusters. This topic describes how to use Multi-master Cluster (Database/Table) Edition.

Prerequisites

Limits

  • The data of each database can be written from only one node. You can read or write data only from a node that has database assigned.
  • You can query only the data within one primary node. If you execute an SQL statement to query data from databases on multiple primary nodes, the system reports an error. We recommend that you change the endpoints of all databases to the endpoint of one primary node before you query data.
  • Only the cluster endpoint is supported. The primary endpoint is not supported.

Specify the primary node when you create a database

You can execute the following statement to create a database on a specified primary node:
CREATE DATABASE name [POLARDB_WRITE_NODE master_id];
Note
  • The data of each database can be written from only one node. You can read or write data only from a node that has database assigned.
  • If [POLARDB_WRITE_NODE master_id] is omitted in the preceding statement, the primary node on which you can create a database is specified by referencing the loose_innodb_mm_default_master_id value. If the loose_innodb_mm_default_master_id parameter is set to 0, a primary node is selected randomly on which you can create a database.
Example: Create the db1 database on the RW1 node.
CREATE DATABASE db1 POLARDB_WRITE_NODE 1;
To create the db1 database on RW2, replace 1 with 2 in the preceding statement.

Delete a database on a specified primary node

You can execute the following statement to delete a database on a specified primary node:
DROP DATABASE name;
Example: Delete the db1 database on the RW1 node.
DROP DATABASE db1;
When you delete a database, you do not need to specify the POLARDB_WRITE_NODE parameter.

Switch the primary node

You can execute the following statement to switch the endpoint of the database to another primary node:
ALTER DATABASE name POLARDB_WRITE_NODE master_id;
Example: Switch the endpoint of the db1 database to the RW2 node.
ALTER DATABASE db1 POLARDB_WRITE_NODE 2;

Specify the primary node in which an SQL statement is executed

Important This feature takes effect only for SQL statements that are not used to query data, such as statements that are used to query information_schema or status variables. If you want to use SQL statements such as SELECT * FROM table1 to query data, you do not need to specify a primary node. The database proxy automatically selects the required primary node.
Execute the following statement to send an SQL statement to a specified primary node:
ALTER SESSION POLARDB_WRITE_NODE master_id;
Example: Query the value of the innodb_buffer_pool_size variable on the RW1 node.
ALTER SESSION POLARDB_WRITE_NODE 1; # Send the SQL statement to the RW1 node. 
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; # Query the value of the innodb_buffer_pool_size variable on the RW1 node.
Note If you do not specify a primary node when you execute an SQL statement, the database proxy randomly selects a primary node to execute the SQL statement.
Execute the following statement to unlock the primary node in which the specified SQL statement is executed:
RESET SESSION POLARDB_WRITE_NODE;

Query the information about a node

  • Execute the following statement to query the database distribution on a primary node:
    ALTER SESSION POLARDB_WRITE_NODE master_id;
SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO;
    Example: Query the database distribution on the RW1 node.
    ALTER SESSION POLARDB_WRITE_NODE 1;
SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO;
    Sample result:
    +-----------------------+---------------------+----------+--------------+-----------+-------------+
| table_name            | table_id            | space_id | s_lock_count | lock_mode | current_lsn |
+-----------------------+---------------------+----------+--------------+-----------+-------------+
| mysql/global_ddl_lock | 1043956280258737140 |        0 |            0 | SLS_X     |    24311657 |
| db2                   |   27980076883382651 |        0 |            0 | SLS_X     |    36087702 |
| db1                   |   27980076883383418 |        0 |            0 | SLS_X     |    34339564 |
| mysql                 | 3381631115268247737 |        0 |            0 | SLS_IX    |           0 |
+-----------------------+---------------------+----------+--------------+-----------+-------------+
4 rows in set (0.00 sec)
    Each row in the preceding result displays the information about a database, although the column name is table_name. mysql/global_ddl_lock and mysql are the internal databases of MySQL, not custom databases.
  • Execute the following statement to query the distribution of all databases in the cluster:
    Note You can query database information only by using a privileged account, but not a custom account. 
    SELECT * FROM INFORMATION_SCHEMA.INNODB_CC_GLOBAL_LOCK_INFO;
    Sample result:
    +-----------+-----------------------+---------------------+-----------+-------------+
| master_id | table_name            | table_id            | lock_mode | current_lsn |
+-----------+-----------------------+---------------------+-----------+-------------+
|         2 | db5                   |   27980076883382398 | SLS_X     |    18866566 |
|         1 | mysql/global_ddl_lock | 1043956280258737140 | SLS_X     |    24311657 |
|         1 | db1                   |   27980076883383418 | SLS_X     |    34339564 |
|         1 | db2                   |   27980076883382651 | SLS_X     |    36087702 |
|         2 | db4                   |   27980076883383165 | SLS_X     |    18855954 |
|         1 | mysql                 | 3381631115268247737 | SLS_IX    |           0 |
+-----------+-----------------------+---------------------+-----------+-------------+
6 rows in set (0.00 sec)
    Each row in the preceding result displays the information about a database, although the column name is table_name. The result indicates that the databases are on the primary nodes specified by the master_id parameter. mysql/global_ddl_lock and mysql are the internal databases of MySQL, not custom databases.

Query the metadata of a database

Execute the following statement to query the information about tables on all primary nodes:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
Execute the following statement to query the information about the primary node in which the SQL statement is executed:
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES;

Configure binary logging

Multi-master Cluster (Database/Table) Edition is fully compatible with the binary logging feature of MySQL. The architecture integrates operation logs on all primary nodes in a cluster to generate globally unified and logical binary logs.

You can specify loose_polar_log_bin to enable the binary logging feature for your Multi-master Cluster (Database/Table) Edition cluster and specify binlog_expire_logs_seconds to set the retention period of binary logs of the Multi-master Cluster (Database/Table) Edition cluster. For more information, see Enable binary logging.

Note Multi-master Cluster (Database/Table) Edition does not support global transaction identifiers (GTIDs). Multi-master Cluster (Database/Table) Edition can be used as the source and destination of Data Transmission Service (DTS) for one-way or two-way data synchronization.