1. Asynchronous replication: This is the default replication mode of MySQL. In this mode, the master repository will return the result to the client immediately after executing the transaction submitted by the client, and does not care whether the slave repository has received and processed it. The advantage of this mode is that it is simple to implement, but the disadvantage is that if the master repository crashes, the committed transactions may not be transmitted to the slave repository, resulting in inconsistent data.
2, full-synchronous replication: in this mode, the master library after the execution of a transaction, you need to wait for all slave libraries have executed the transaction before returning to the client. The advantage of this mode is high data consistency, but the disadvantage is that the performance impact is greater, because you need to wait for all the slave library confirmation.
3, semi-synchronous replication: this mode between asynchronous replication and full synchronous replication. After the master library in the execution of the client submitted transactions, will wait for at least one slave library to receive and write the relay log before returning to the client. This mode improves data security, but will cause some delay.
4, enhanced semi-synchronous replication: this mode in the semi-synchronous replication on the basis of further enhanced data security. The master library will write each transaction to the binlog, waiting for the slave library to refresh the disk before returning to the client. The advantage of this model is to further reduce the risk of data loss, but the disadvantage is that it may increase the transaction delay.
5, group replication: this mode is an advanced replication technology, support for multi-master multi-slave architecture, applicable to read and write half of the scenario. It ensures data consistency and high availability through mutual communication and voting mechanisms between nodes in the group.
This time we will use two machines, node1 and node2, with node1 as the master and node2 as the slave.
1, get myql mirror
# Pull mysql images on node1 and node2 respectively, which have mysql version 8.4.3.
docker pull mysql:8
2、Get the default configuration file
Start the container, copy the configuration file locally, (node1 and node2 are synchronized)
# Create a mysql directory under /opt/.
# Start mysql temporarily
docker run -d \
--name mysql-master \\
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:8
# Copy the data and configuration directory locally
Execute on node1
docker cp mysql-master:/etc/ /opt/mysql/
On node2, run
docker cp mysql-master:/etc/ /opt/mysql/
3. Configure the startup master library
1, in node1 modify the main library configuration file
Execute vim on node1 and edit the following configuration
[mysqld]
#mysql service-id, ensure unique in the whole cluster environment, value range: 1 - 2 to the 32nd power-1, default is 1
server-id=1
#Read-only or not, 1 means the current server is read-only (can't do add, delete or change operations), 0 means read-write.
read-only=0
# Set the format of the log, default value ROW. row (record row data) statement (record sql) mixed (mixed mode)
binlog_format=STATEMENT
# Binary log name, default binlog
log-bin=master_binlog
# Set the databases to be replicated, default replicate all databases
# binlog-do-db=test
# Set the databases that do not need to be replicated
# binlog-ignore-db=test
2. Start the master library on node1
docker run -d \
--name mysql-master \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /opt/mysql/mysql_data:/var/lib/mysql \
-v /opt/mysql/:/etc/ \
--restart=always \
--privileged=true \
mysql:8
3. Create a master and backup account:
# Create an account dedicated to replication
CREATE USER 'backups'@'%' IDENTIFIED BY 'backups'.
# Grant this account the privileges required for replication
GRANT REPLICATION SLAVE ON *. * TO 'backups'@'%'; # Grant this account the privileges required for replication GRANT REPLICATION SLAVE ON *.
Get replication source binary log coordinates
To configure the replica to start the replication process at the correct point, you need to note the current coordinates of the source in the binary log. To obtain the source binary log coordinates, start a session on the source by connecting to the source using a command-line client and refresh all tables and block write statements by executing the following statement
# Flush all tables and block write statements
mysql> FLUSH TABLES WITH READ LOCK;
# Determine the name and location of the current binary log file
mysql> SHOW BINARY LOG STATUS\G
Record the File and Position, which are needed when configuring the synchronization source from the slave library
3. Configure the startup slave library
1. Configure the slave library and start
Run vim on node2 and edit the following configuration
[mysqld]
# server-unique-id, the id must be different for each server, if you configure other slaves, take care to change the ids
server-id=2
# relay log name, default xxxxxxxxxxxxxx-relay-bin
# relay-log=relay-bin
# read-only or not, 1 for read-only, 0 for read/write. For the slave library, we only need to query from the slave library, we don't need to write from the slave library > so we can just set read-only to 1 directly.
read-only=1
#name of the binary file
#log-bin=binlog
# Set the log format, default value ROW. row (log row data) statement (log sql) mixed (mixed mode)
binlog_format=STATEMENT
2. Start the slave library:
docker run -d \
--name mysql-slave \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /opt/mysql/mysql_data:/var/lib/mysql \
-v /opt/mysql/:/etc/ \
--restart=always \
--privileged=true \
mysql:8
3. Use a replicated user to request the server's public key
This step is to avoidERROR：Authentication plugin ‘caching_sha2_password‘ reported error:Authenticationcrap
mysql -u backups -pbackups -h 10.10.10.11 -P3306 --get-server-public-key
After execution, you need to exit the current mysql terminal and use the root account to set the following settings
Setting the Synchronization Source for a Slave Library
mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='10.10.10.11',
-> SOURCE_USER='backups',
-> SOURCE_PASSWORD='backups',
-> SOURCE_LOG_FILE='master_binlog.000003',
-> SOURCE_LOG_POS=697;
Managing the Synchronized Replication Status of Slave Libraries
# Pause replication on replica
mysql> STOP REPLICA.
# Start replication on the replica
mysql> START REPLICA;
# Check the status of a single replica
SHOW REPLICA STATUS\G
Replica_IO_State: current state of the replica
Replica_IO_Running: whether the I/O (receiver) thread that reads the source binary log is running. Typically Yes, unless you have not started replication or have explicitly stopped replication, then you want this thread to be in the running state STOP REPLICA.
Replica_SQL_Running: whether the SQL thread that executes events in the relay log is running. As with I/O threads, this should normally be Yes.
Last_IO_Error, Last_SQL_Error: the last error logged by the I/O (receiver) and SQL (application) threads when processing the relay log. Ideally, these should be blank, indicating no errors.
Seconds_Behind_Source: the number of seconds the replicating SQL (application) thread is behind in processing the source binary log. A large (or increasing) number may indicate that the replica is unable to process events from the source in a timely manner. With a value of 0, Seconds_Behind_Source can usually be interpreted to mean that the replica has caught up with the source; however, in some cases this is not entirely true. For example, this can happen if the network connection between the source and the replica is down, but the replication I/O (receiver) thread has not yet noticed this, i.e., the set time period replica_net_timeout has not yet elapsed.Seconds_Behind_Source does not accurately reflect the situation. Seconds_Behind_Source displays 0 when the replica SQL (application) thread is catching up with I/O; however, when the replica I/O (receiver) thread is still queuing new events, Seconds_Behind_Source may display a larger value until the replica application thread finishes executing the new events. This is especially likely to occur when the event has an old timestamp; in this case, if you perform SHOW REPLICA STATUS multiple times in a relatively short period of time, you may see this value repeatedly go back and forth between 0 and a relatively large value.
The detailed execution flow is shown in the following figure
After executing SHOW REPLICA STATUS\G in the slave library, look at the following Replica_IO_State: Waiting for source to send event, Replica_IO_Running: Yes, Replica_SQL_Running: Yes, which means that the replication status is normal. state is normal
Execute SHOW PROCESSLIST in the master library \G.
Check the list of running processes to check the status of the connected replica. The replica connection contains the Command field Binlog Dump
SHOW PROCESSLIST \G.
The master library sees this process, indicating that the master library Binlog Dump process is started
4. Testing
1. Connect the master and slave libraries
2, in the main library to create the test library and user table, and insert three data
# establishteststorehouse
create database test;
use test;
# existteststorehouse中establishusera meter (measuring sth)
create table user(
id int(11) primary key not null auto_increment,
name varchar(50) not null,
sex varchar(1),
age int(11)
)engine=innodb default charset=utf8mb4;
# Insert three pieces of data
insert into user(id,name,sex,age) values(null,'John Doe', '1',18),(null,'the fourth child in the family','0',19),(null,'Wang May (1905-1975), Mao *'s fifth wife','1',20);
3. Check if the slave libraries on node2 are synchronized
At this point, the master-slave synchronization has been completed, if you need a master and multiple slaves, you only need to deploy a few more slave libraries, in accordance with the above deployment of slave libraries on the way!
If you feel the writing is still readable, you can follow the public number and the latest news will be pushed to you