mysql8.0 Master-Slave Architecture Patterns [0 to 1 Architecture Series].

pre-conditions

1. Prepare 3,4,5 virtual machines
2. Naked installation of mysql 8.0

master-slave architecture

Two common modes of "one master multi-slave" and "cascade replication" are basically very simple, are dependent on the binlog log file for synchronization, the binlog log will record DDL and some DDL statements, synchronization from the library will be re-executed these statements to achieve master-slave synchronization. The slave library will re-execute these statements during synchronization to achieve master-slave synchronization.

Step 1:

Configure the master/slave server'sserver_idIf you are not sure about the mysql server server_id, use the command SHOW VARIABLES LIKE 'server_id';can be queried, the value of server_id is not strictly defined, it is sufficient to ensure global uniqueness, the server_id value is 1 by default.

debian server edit command is as follows: nano /etc/mysql/, add the following node, assuming that the master library set 1, set 2 from the database, save and restart the databasesystemctl restart mysql

[mysqld]
server-id=1 #master-repository-configuration-join

[mysqld]
server-id=2 #join from repository configuration

Step 2 (not required):

Execution on the host show master status;Displays the latest log file and current location.

File The name of the binary log file currently being written to.

Position The offset of the current binary log file, indicating the next position to be written to.

Databases configured on the Binlog_Do_DB master server that need to be logged to the binary log.

Databases configured on the Binlog_Ignore_DB master server that do not need to be logged to the binary log.

Executed_Gtid_Set In GTID mode, this property represents the set of global transaction identifiers that have been executed

Step 3:

The slave server connects to the master server for master-slave synchronization and executes the following code on top of the slave server:

CHANGE REPLICATION SOURCE to
 SOURCE_HOST='192.168.3.107',
 SOURCE_USER='root',
 SOURCE_PASSWORD='1234',
 SOURCE_LOG_FILE='binlog.000009',
 SOURCE_LOG_POS=0;

SOURCE_HOST: address of the master library connection
SOURCE_USER: account number
SOURCE_PASSWORD: password
SOURCE_LOG_FILE: the current binary file name of the master library
SOURCE_LOG_POS: offset of the current binary log file

After the execution is complete, you can pass theshow REPLICA status;You can view the status of the replication cluster and start, pause, and delete the cluster with the following commands.

show REPLICA status; #view
start REPLICA; #Start
stop REPLICA; #Pause
RESET REPLICA ALL; #Delete

By setting the SOURCE_HOST address to connect to a master or a slave, you can easily realize a master-multiple-slave, or a master-slave-slave chained architecture.

GTID mode (recommended)

In master-slave replication mysql provides a GTID mode thatSHOW GLOBAL VARIABLES LIKE 'gtid_mode';View the status of Gtid mode. gtid is a new log format. gtid has a unique GTID for each transaction that ensures the uniqueness of the transaction across the replication topology. the main difference with the traditional log format is that it is based on the transaction whereas traditional logs are based on the log location.

RESET MASTER;Reset the binlog log of the master library, note that once the log is reset all log data will be lost, it will start from 00001, edit thenano /etc/mysql/The server_id of each database is sorted down by 1-5 and needs to be configured for both the master and slave databases.

[mysqld]
server-id=1
gtid_mode=on
enforce_gtid_consistency=true

gtid_mode: enable gtid mode.

enforce_gtid_consistency: enforces GTID consistency, which helps to improve data reliability and consistency and prevent data loss or corruption.

Execute the following code from the library:

CHANGE REPLICATION SOURCE to 
 SOURCE_HOST='192.168.3.107',
 SOURCE_USER='root',
 SOURCE_PASSWORD='123',
 SOURCE_AUTO_POSITION=1;

You will notice that SOURCE_LOG_FILE and SOURCE_LOG_POS will no longer need to be filled in, as one of the benefits of gtid is that it will automatically locate the log files and locations, reducing configuration complexity. The rest of the cluster management is consistent with the traditional commands.

#Common Commands for Master Vaults
show master status #Check the status of the master
reset master; #reset master binlog

#Slave commands
show replica status; #Check the status of the slave replica
start replica; #Start synchronization
stop replica; #Pause synchronization
reset replica all; #Reset (delete) all synchronizations
select * from performance_schema.replication_applier_status_by_worker; #View information about the synchronization thread (mainly used to view specific error messages)

#Other
show variables like 'server_id'; #View server_id
show global variables like 'gtid_mode'; #See the gtid status.