Configuring Secondary Databases

Configure and start parallel replication.

         
              /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

Stop the secondary database and set parallel replication.

stop slave;
set global binlog_transaction_dependency_tracking=COMMIT_ORDER;
set global transaction_write_set_extraction=XXHASH64;
set global binlog_transaction_dependency_tracking=WRITESET;
set global slave_parallel_type='LOGICAL_CLOCK';
set global slave_parallel_workers=96;

**Table 1** Parallel replication parameters
Parameter	Description	Remarks
transaction_write_set_extraction	Specifies the hash algorithm used for detecting transaction dependencies. This parameter is required if writeset is used for parallel replication.	The value can be OFF, XXHASH64, or MURMUR32. You need to set this parameter for MySQL 5.7, and do not need to set it for MySQL 8.0 because the default value for MySQL 8.0 is XXHASH64. Before setting this parameter, you need to set binlog_transaction_dependency_tracking to COMMIT_ORDER.
binlog_transaction_dependency_tracking	Indicates how to determine the transaction dependency.	The values are as follows: COMMIT_ORDER (default value): indicates that group commit in MySQL 5.7 is used to determine the transaction dependency. WRITESET: indicates that writesets are used to determine transaction dependency. WRITESET_SESSION: indicates that writesets are used to determine transaction dependency. However, transactions in the same session have different last_committed values.
slave_parallel_type	Indicates whether to enable parallel replication.	The default value DATABASE indicates that the schema-level parallel playback (parallel replication based on databases) in MySQL 5.6 is used. The value LOGICAL_LOCK indicates that the parallel playback based on group commit (parallel replication based on group commit) is used, and transactions in the same group will be replayed on secondary databases in parallel.
slave_parallel_workers	Specifies the DOP of SQL threads.	If secondary databases do not bear any service pressure, you are advised to set this parameter to the number of CPU cores. In this document, the server has two Kunpeng 920 5250 processors, that is, 96 cores in total. If secondary databases bear the service pressure, you are advised to adjust the value based on the command output of the SELECT GTID_SUBSET(LAST_SEEN_TRANSACTION,@@GLOBAL.GTID_EXECUTED) from performance_schema.replication_applier_status_by_worker; command without affecting the service pressure. If the command output is 0, certain transactions are not processed. In this case, you can increase the value of slave_parallel_workers. If the command output is not 0, all transactions have been replayed.

Start parallel replication.
Start secondary databases for the slave_parallel_workers parameter to take effect.
```
start slave;
```

To disable parallel replication, disable it first for the primary database and then for secondary databases.

Disable parallel replication for the primary database.

set global binlog_transaction_dependency_tracking=COMMIT_ORDER;
set global transaction_write_set_extraction=XXHASH64;
set global slave_parallel_type='DATABASE';
set global slave_parallel_workers=0;

Run the following commands to check whether parallel replication is disabled successfully:

show variables like '%transaction_write_set_extraction%';
show variables like '%binlog_transaction_dependency_tracking%';
show variables like '%slave_parallel_type%';
show variables like '%slave_parallel_workers%';

In the command output, the values XXHASH64, COMMIT_ORDER, DATABASE, and 0 indicate that parallel replication is disabled successfully.

Disable parallel replication for secondary databases.
1. Disable parallel replication for secondary databases.
```
stop slave;
set global binlog_transaction_dependency_tracking=COMMIT_ORDER;
set global transaction_write_set_extraction=XXHASH64;
set global slave_parallel_type='DATABASE';
set global slave_parallel_workers=0;
start slave;
```
  Or
```
stop slave;
set global slave_parallel_type='DATABASE';
start slave;
```
2. Run the following commands to check whether parallel replication is disabled successfully:
```
show variables like '%transaction_write_set_extraction%';
show variables like '%binlog_transaction_dependency_tracking%';
show variables like '%slave_parallel_type%';
show variables like '%slave_parallel_workers%';
```
  In the command output, the values XXHASH64, COMMIT_ORDER, DATABASE, and 0 indicate that parallel replication is disabled successfully.
  
  Query all threads (I/O threads and SQL threads).
```
show processlist;
```
  There is one I/O thread (ID 35) and one SQL thread (ID 36). The I/O thread receives the binlog of the primary database, and the SQL thread resolves the binlog and executes SQL statements.

Check whether parallel replication is started successfully.
1. Check whether parallel replication takes effect.
```
show variables like '%transaction_write_set_extraction%';
show variables like '%binlog_transaction_dependency_tracking%';
show variables like '%slave_parallel_type%';
show variables like '%slave_parallel_workers%';
```
  If the values in the command output are XXHASH64, WRITESET, LOGICAL_CLOCK and 96 in sequence, parallel replication takes effect.
  
  If certain values are inconsistent, you need to log out of the database and log in to the database again for the values to take effect.
2. Query threads.
  Query all threads (I/O threads and SQL threads).
```
show processlist;
```
  SQL coordinator thread: parses the binlog and distributes it to SQL threads for execution.
Query the status of secondary databases.
1. Run the following command:
```
show slave status\G;
```
  If the values of the following two parameters are Yes, secondary databases are started successfully.
  
  Slave_IO_Running: Yes
  
  Slave_SQL_Running: Yes
  
  If the error message "The slave I/O thread stops because master and slave have equal MySQL server UUIDs..." is displayed, rectify the fault by referring to "The slave IO thread stops" Displayed During the MySQL Primary-Secondary Replication Verification and check the status of the secondary database.
2. Exit secondary databases.
```
exit
```

Parent topic: Parallel Replication