Multi-master Cluster Edition - PolarDB - Alibaba Cloud Documentation Center

PolarDB for MySQL Multi-master Cluster (Database/Table) clusters are developed based on a multi-master architecture that contains multiple primary nodes and read-only nodes. To improve the overall concurrent read and write capabilities of clusters, this architecture supports concurrent data writes to databases or data objects from different compute nodes and allows you to dynamically switch the primary nodes of databases within seconds. The following types of data object are supported: table, view, trigger, event, stored procedure, and function. This topic describes how to use Multi-master Cluster (Database/Table) clusters.

Prerequisites

A Multi-master Cluster (Database/Table) cluster is purchased. For more information, see Purchase an Enterprise Edition cluster and Purchase a subscription cluster.
A privileged account is created. For more information, see Create and manage a database account.
You are connected to the cluster. For more information, see Connect to a cluster.
PolarDB for MySQL 8.0 supports Multi-master Cluster (Database/Table) clusters.

Limits

The data of each database or data object can be written from only one node. You can read or write data only from a node that has database or data object assigned. By default, the operations are performed based on databases. To perform the operations on data objects, you must execute corresponding statements.
You can query only the data within one primary node. If you execute an SQL statement to query data from databases or data objects on multiple primary nodes, the system reports an error. We recommend that you change the endpoints of all databases or data objects to the endpoint of one primary node before you query data.
Only the cluster endpoint is supported. The primary endpoint is not supported.
Switch the database endpoint based on isolation levels:
- If database isolation is used, you need to switch the endpoint of databases.
- If data object isolation is used, you need to switch the endpoint of data objects.

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

When database isolation is used, the data of each database can be written from only one node.
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 value of loose_innodb_mm_default_master_id is 0, the system randomly specifies a primary node to create a database.

Example: Create database db1 on the RW1 node.

CREATE DATABASE db1 POLARDB_WRITE_NODE 1;

Change 1 in the preceding statement to 2 to create database db1 on the RW2.

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 database db1 that is created 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 database endpoint

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 database db1 to the RW2 node.

ALTER DATABASE db1 POLARDB_WRITE_NODE 2;

Note

In most cases, switching the endpoint is time-consuming. The following factors affect the execution time of the switching operation:

The number of tables in the database. The greater the number, the slower the switching speed.
The workload of DML statements of the database during the switching. The heavier the workload, the slower the switching speed.

Switch the isolation level from database to data object

By default, database isolation is used for a multi-master cluster. By using this isolation level, all data objects in a database can be accessed only on one primary node. To allow access to data objects in a database from multiple primary nodes, you can execute the following statement to change the isolation level from database to data object.

ALTER DATABASE name TO TABLE_LOCK POLARDB_WRITE_NODE master_id;

In this statement, name is the name of the database, and master_id is the endpoint of the data object.

Example: Switch the isolation level of database db1 from database to data object and set the endpoint to the RW2 node.

ALTER DATABASE db1 TO TABLE_LOCK POLARDB_WRITE_NODE 2;

Note

In most cases, switching the isolation level is time-consuming. The following factors affect the execution time of the switching operation:

The number of data objects in the database. The greater the number, the slower the switching speed.
The workload of DML statements of the database during the switching. The heavier the workload, the slower the switching speed.

Switch the isolation level from data object to database

After you configure the isolation level of a database to data object, you can execute the following statement to change the isolation level back to database for management purposes.

ALTER DATABASE name TO DB_LOCK POLARDB_WRITE_NODE master_id;

In this statement, name is the name of the database, and master_id is the endpoint of the database.

Example: Switch the isolation level of the database db1 from data object to database and set the endpoint to the RW1 node.

ALTER DATABASE db1 TO DB_LOCK POLARDB_WRITE_NODE 1;

Note

In most cases, switching the isolation level is time-consuming. The following factors affect the execution time of the switching operation:

The number of data objects in the database. The greater the number, the slower the switching speed.
The workload of DML statements of the database during the switching. The heavier the workload, the slower the switching speed.

Switch the endpoint of a data object

After the isolation level of a multi-primary cluster is switched to the data object, a database may contain multiple object types, including table, view, trigger, function, procedure, and event. You can execute the following statement to switch the endpoint of the objects:

ALTER obj_type name POLARDB_WRITE_NODE master_id;

Valid values of obj_type: TABLE, VIEW, TRIGGER, FUNCTION, PROCEDURE, and EVENT. name is the name of the data object.

Example 1: Switch the endpint of table t1 in database db1 to the RW3 node.

ALTER TABLE db1.t1 POLARDB_WRITE_NODE 3;

Example 2: Switch the endpoint of view t2 in the current database to the RW2 node.

ALTER VIEW t2 POLARDB_WRITE_NODE 2;

Example 3: Switch the endpoint of function f1 and function f2 in database db2 to the RW1 node.

ALTER FUNCTION db2.f1, db2.f2 POLARDB_WRITE_NODE 1;

Note

In most cases, switching the endpoint is time-consuming. The following factors affect the execution time of the switching operation:

The workload of DML statements of the data objects during the switching. The heavier the workload, the slower the switching speed.
Data objects may be associated with each other. If the endpoints of associated objects are not on the same primary node, the objects may be invalid.
For example, view 1 depends on table t1, but the endpoint of view 1 is on the RW1 node while the endpoint of table t1 is on the RW2 node. The system reports an error when you access view 1 on the RW1 node. Similarly, if the endpoints of the objects that are referenced by data objects of the function, procedure, or event type are not correctly configured, such objects cannot be accessed. If the endpoint of a trigger object and the endpoint of the table to which the trigger object is associated are not on the same node, the table object cannot be updated by the trigger object.
If a foreign key constraint exists between table t1 and table t2, when the endpoint of one table is changed, the endpoint of the other table is automatically changed.

Specify the primary node in which an SQL statement is executed

Important

This feature is only applicable to the statements not to query data, such as those 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 innodb_buffer_pool_size 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 innodb_buffer_pool_size 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 WHERE LOCK_MODE = 'SLS_X';

Example: Query the database distribution on the RW1 node.

ALTER SESSION POLARDB_WRITE_NODE 1;
SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

An output similar to the following one is returned:

 SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+
| table_name | table_id            | space_id | s_lock_count | lock_mode | object   | current_lsn | hold_thread | hold_start_time     | hold_total_time |
+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+
| test3/f1   | 9149389368458135753 |        0 |            0 | SLS_X     | function |    28076635 |          17 | 2024-07-10 21:35:20 |             214 |
| test3/e1   | 9149389368458332874 |        0 |            0 | SLS_X     | event    |    28077248 |          17 | 2024-07-10 21:35:30 |             204 |
| test3/v1   | 9149389368457234649 |        0 |            0 | SLS_X     | view     |    28075972 |          17 | 2024-07-10 21:35:08 |             226 |
| sbtest     | 2107518311328629409 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-07 23:04:41 |          254053 |
| test       | 7190879906290573778 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-10 11:20:57 |           37077 |
| test2      | 3381728963524265351 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-10 11:13:09 |           37545 |
+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+

6 rows in set (0.00 sec)

Although the column name is table_name, each row in the preceding result displays the information about a database or data object. In the result, sbtest, test, and test2 use database isolation. function test3.f1, event test3.e1, and view test3.v1 use data object isolation. In addition, a table object that is named mysql/global_ddl_lock may be included in the result. This information is for internal use of MySQL and can be ignored.

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 WHERE LOCK_MODE = 'SLS_X';

An output similar to the following one is returned:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CC_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+
| master_id | table_name | table_id            | lock_mode | object    | current_lsn | hold_start_time     | hold_total_time |
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+
|         1 | test3/v1   | 9149389368457234649 | SLS_X     | view      |    28075972 | 2024-07-10 21:35:08 |             754 |
|         2 | test5/t1   | 9149389447232697561 | SLS_X     | table     |     7256175 | 2024-07-10 21:46:36 |              66 |
|         1 | test2      | 3381728963524265351 | SLS_X     | db        |    28034927 | 2024-07-10 11:13:09 |           38073 |
|         2 | test4      | 3381728963524272009 | SLS_X     | db        |     7255352 | 2024-07-10 21:46:27 |              75 |
|         1 | test3/f1   | 9149389368458135753 | SLS_X     | function  |    28076635 | 2024-07-10 21:35:20 |             742 |
|         1 | test3/e1   | 9149389368458332874 | SLS_X     | event     |    28077248 | 2024-07-10 21:35:30 |             732 |
|         1 | test       | 7190879906290573778 | SLS_X     | db        |    28034927 | 2024-07-10 11:20:57 |           37605 |
|         2 | test5/p1   | 9149389447233473757 | SLS_X     | procedure |     7257051 | 2024-07-10 21:46:45 |              57 |
|         1 | sbtest     | 2107518311328629409 | SLS_X     | db        |    28034927 | 2024-07-07 23:04:41 |          254581 |
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+

9 rows in set (0.00 sec)

Although the column name is table_name, each row in the preceding result displays the information about a database or data object, indicating that the database or data object is on the corresponding primary node. In addition, a table object that is named mysql/global_ddl_lock may be included in the result. This information is for internal use of MySQL and can be ignored.

Configure binary logging

Multi-master Cluster (Database/Table) 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 configure the loose_polar_log_bin parameter to enable the binary logging feature of Multi-master Cluster (Database/Table). You can configure the binlog_expire_logs_seconds parameter to set the retention period of binary logs for Multi-master Cluster (Database/Table). For more information, see Enable binary logging.

Note

Multi-master Cluster (Database/Table) can be used as the source and destination of Data Transmission Service (DTS) for one-way or two-way data synchronization.