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
- A cluster of Multi-master Cluster (Database/Table) Edition is purchased. For more information, see Purchase a pay-as-you-go cluster and Purchase a subscription cluster.
- A privileged account is created. For more information about how to create an ECS instance, see Create a privileged account.
- You are connected to the cluster. For more information about how to create an ECS instance, see Connect to a cluster.
- Only PolarDB for MySQL 8.0 supports Multi-master Cluster (Database/Table) Edition.
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
CREATE DATABASE name [POLARDB_WRITE_NODE master_id];
- 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.
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
DROP DATABASE name;
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
ALTER DATABASE name POLARDB_WRITE_NODE master_id;
db1
database to the RW2 node.ALTER DATABASE db1 POLARDB_WRITE_NODE 2;
Specify the primary node in which an SQL statement is executed
SELECT * FROM table1
to query data, you do not need to specify a primary node. The database proxy automatically selects the required primary node. ALTER SESSION POLARDB_WRITE_NODE master_id;
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.
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.
Sample result:ALTER SESSION POLARDB_WRITE_NODE 1; SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO;
Each row in the preceding result displays the information about a database, although the column name is table_name.+-----------------------+---------------------+----------+--------------+-----------+-------------+ | 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)
mysql/global_ddl_lock
andmysql
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.
Sample result:SELECT * FROM INFORMATION_SCHEMA.INNODB_CC_GLOBAL_LOCK_INFO;
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.+-----------+-----------------------+---------------------+-----------+-------------+ | 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)
mysql/global_ddl_lock
andmysql
are the internal databases of MySQL, not custom databases.
Query the metadata of a database
SELECT * FROM INFORMATION_SCHEMA.TABLES;
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.