How to use a Multi-master Cluster (Limitless)-PolarDB(PolarDB)-阿里云帮助中心

The PolarDB for MySQL Multi-master Cluster (Limitless) feature upgrades a cluster from a single-writer, multi-reader architecture to a multi-writer, multi-reader architecture. This feature allows concurrent data writes to different databases or data objects on different compute nodes. It also supports dynamic scheduling of databases and data objects across nodes with switches that complete in seconds, which significantly improves the overall concurrent read and write capability of the cluster. Data objects include tables, views, triggers, events, stored procedures, and functions. This topic describes how to use a Multi-master Cluster (Limitless).

Prerequisites

You have purchased a Multi-master Cluster (Limitless) cluster. For more information, see Custom Purchase and Purchase a subscription cluster.
You have created a privileged account. For more information, see Create a privileged account.
You have connected to the database cluster. For more information, see Connect to a database cluster.
PolarDB for MySQL kernel version 8.0 or later supports Multi-master Cluster (Limitless).

Limitations

Data for each database or data object can be written only through a single primary node. Nodes that do not have an assigned database or data object cannot perform read or write operations. By default, operations are performed at the database level. To perform operations at the data object level, you must use a specific syntax to switch the mode.
Queries across primary nodes are not supported. If a query involves databases or data objects that reside on different primary nodes, the system reports an error. We recommend moving the endpoints for all involved databases or data objects to a single primary node before running the query.
Only cluster endpoints, are provided. Primary endpoints are not supported.
Switching an endpoint is subject to the following conditions:
- If you use the database isolation level, you must switch the database endpoint.
- If you use the data object isolation level, you must switch the data object endpoint.

Specify a primary node for a database

Create a database on a specified primary node. The syntax is as follows:

CREATE DATABASE name [POLARDB_WRITE_NODE master_id];

Note

When using the database isolation level, data for each database can be written only through a single primary node.
If you omit [POLARDB_WRITE_NODE master_id] in the syntax, the system uses the value of the loose_innodb_mm_default_master_id parameter to specify the primary node for creating the database. When the value of the loose_innodb_mm_default_master_id parameter is 0, the system randomly selects a primary node to create the database. You can go to the PolarDB console and navigate to the Settings and Management > Parameters page to view and modify cluster or node parameters.
To view the distribution of databases across nodes, see Query the database distribution.

Example: Create a database named db1 on primary node 1.

CREATE DATABASE db1 POLARDB_WRITE_NODE 1;

To create the database db1 on primary node 2, change 1 to 2 in the preceding statement.

Delete a database

To delete a database created on a specified primary node, use the following syntax:

DROP DATABASE name;

Example: Delete the database db1 that was created on primary node 1.

DROP DATABASE db1;

You do not need to specify the primary node when deleting a database.

Switch a database endpoint

To switch the database endpoint to another primary node, use the following syntax:

ALTER DATABASE name POLARDB_WRITE_NODE master_id;

Example: Switch the endpoint of the database db1 to primary node 2.

ALTER DATABASE db1 POLARDB_WRITE_NODE 2;

Note

Switching an endpoint can be a time-consuming operation. The execution time depends on the following factors:

The more tables the database contains, the slower the switch.
The heavier the DML workload on the database during the switch, the slower the operation.

Switch to data object isolation

By default, the isolation level for a multi-primary cluster is the database level. This means all data objects within the same database can be accessed only on a single primary node. If you want all data objects in the same database to be accessible through multiple primary nodes, you need to change the database isolation level to the data object isolation level. The syntax is as follows:

ALTER DATABASE name TO TABLE_LOCK POLARDB_WRITE_NODE master_id;

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

Example: Change the isolation level for the database db1 to the data object isolation level and set its endpoint to primary node 2.

ALTER DATABASE db1 TO TABLE_LOCK POLARDB_WRITE_NODE 2;

Note

Switching the isolation level can be a time-consuming operation. The execution time depends on the following factors:

The more objects the database contains, the slower the switch.
The heavier the DML workload on the database during the switch, the slower the operation.

Switch to database isolation

If a database is set to the data object isolation level, you can revert it to the database isolation level for easier management. Use the following statement:

ALTER DATABASE name TO DB_LOCK POLARDB_WRITE_NODE master_id;

In this statement, name specifies the database name, and master_id specifies the database endpoint.

Example: Change the isolation level for the database db1 back to the database isolation level and set its endpoint to primary node 1.

ALTER DATABASE db1 TO DB_LOCK POLARDB_WRITE_NODE 1;

Note

Switching the isolation level can be a time-consuming operation. The execution time depends on the following factors:

The more objects the database contains, the slower the switch.
The heavier the DML workload on the database during the switch, the slower the operation.

Switch a data object endpoint

When a cluster is at the data object isolation level, a database can contain various object types, including table, view, trigger, function, stored procedure, and event. To switch the endpoint for these objects, use the following syntax:

ALTER obj_type name POLARDB_WRITE_NODE master_id;

In this statement, obj_type can be TABLE, VIEW, TRIGGER, FUNCTION, PROCEDURE, or EVENT. name is the name of the data object.

Example 1: Switch the endpoint for the table t1 in the database db1 to primary node 3.

ALTER TABLE db1.t1 POLARDB_WRITE_NODE 3;

Example 2: Switch the endpoint for the view t2 in the current database to primary node 2.

ALTER VIEW t2 POLARDB_WRITE_NODE 2;

Example 3: Switch the endpoint for the functions f1 and f2 in the database db2 to primary node 1.

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

Note

Switching an endpoint can be a time-consuming operation. The execution time depends on the following factors:

The heavier the DML workload on the data object during the switch, the slower the operation.
Objects with dependencies may become invalid if they are not located on the same primary node.
For example, a view named VIEW1 depends on a table named t1. If the endpoint for VIEW1 is on primary node 1 and the endpoint for t1 is on primary node 2, an error occurs when you query VIEW1 on primary node 1. Similarly, calls to a function, stored procedure, or event fail if the objects they reference are on different primary nodes. A trigger also fails to modify a table if their endpoints are on different nodes.
If a foreign key constraint exists between two tables, such as t1 and t2, altering the endpoint of one table automatically alters the endpoint of the other.

Specify a primary node for SQL

Important

This feature applies only to metadata queries, such as queries on information_schema or status variables. If you need to query data, for example, by running SELECT * FROM table1, you do not need to specify a primary node, as the database proxy automatically routes the query to the correct primary node.

To send an SQL statement to a specific primary node, run the following SQL statement to lock the session to that node:

ALTER SESSION POLARDB_WRITE_NODE master_id;

Example: Query the value of the innodb_buffer_pool_size variable on primary node 1.

ALTER SESSION POLARDB_WRITE_NODE 1;   # Send the SQL statement to primary node 1.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  # Query the value of innodb_buffer_pool_size on primary node 1.

Note

If you do not specify a primary node when you run an SQL statement, the database proxy randomly selects a primary node to execute the statement.

Run the following command to unlock the session from the specified primary node:

RESET SESSION POLARDB_WRITE_NODE;

Query the database distribution

In the PolarDB console, go to the Settings and Management > Database Management page to view the distribution of all databases across the cluster.

Run the following commands to query the database distribution on a specific 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 primary node 1.

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

The following output 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 is named table_name, each row in the result contains information about a database or a data object. In this example, sbtest, test, and test2 use the database isolation level. The objects `test3/f1` (a function), `test3/e1` (an event), and `test3/v1` (a view) use the data object isolation level. The result may also contain an object named mysql/global_ddl_lock with an object type of table. This is for internal use and can be ignored.

Run the following command to query the distribution of all databases in the cluster:

Note

This query can only be run by using a privileged account.

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

The following output 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 is named table_name, each row in the result contains information about a database or data object and its corresponding primary node. The result may also contain an object named mysql/global_ddl_lock with an object type of table. This is for internal use and can be ignored.

Configure the binary log

Multi-master Cluster (Limitless) is fully compatible with the MySQL binary log. It consolidates operation logs from all primary nodes in the cluster to generate a globally unified and logically ordered binary log.

You can use loose_polar_log_bin to enable the binary log feature for a Multi-master Cluster (Limitless) and binlog_expire_logs_seconds to set the retention period for the binary logs of the Multi-master Cluster (Limitless). For more information, see Enable binary logs.

Note

Multi-master Cluster (Limitless) can be used as a source and destination for Data Transmission Service (DTS) to perform one-way or two-way data synchronization.