For testing purposes, this test is run using Docker

Reference documentation

Version Description

Ubuntu 20.04 Docker 19.03.13 MySQL: 8.0.26 DB1: master service master DB2: slave service slaveCopy the code

MySQL replication

  • Database replication is essentially data synchronization. The MySQL database performs incremental data synchronization based on the binary log. The binary log records all modifications to the MySQL database.

MySQL Replication type

Asynchronous replication

  • GTID replication is asynchronous. The Master does not care whether the Slave receives binary logs, so the Master and Slave do not have any dependencies. Asynchronous replication provides the best performance. If services do not have high requirements for data consistency, they can tolerate data loss, even a large amount of data, when a fault occurs. Therefore, asynchronous replication is recommended

The installation

Pull MySQL Docker Image

Docker pull mysql: 8.0.26Copy the code

Get the default my.cnf

  • Start the mysql
Docker run -d -p 3306:3306 --name mysql-get-conf -e MYSQL_ROOT_PASSWORD="root" mysql:8.0.26 docker run -d -p 3306:3306 --name mysql-get-conf -e MYSQL_ROOT_PASSWORD="rootCopy the code
  • Copy the configuration file to the local computer
docker cp mysql-get-conf:/etc/mysql/my.cnf ./
Copy the code
  • Delete the container that obtained the configuration
docker rm -f mysql-get-conf
Copy the code
  • Copying a Configuration File
Mkdir /home/dong/db1 /home/dong/db2 cp my.cnf /home/dong/db1 cp my.cnf /home/dong/db2Copy the code

Start MySQL container

  • Docker network configuration
Create docker network create db-netCopy the code
  • Start the
docker run --name db1 -v /home/dong/db1/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -d --network db-net Mysql: 8.0.26 docker run - name db2 -v/home/dong/db2 / my CNF: / etc/mysql/my MYSQL_ROOT_PASSWORD = root CNF - e - d - network The db -.net mysql: 8.0.26Copy the code
  • Viewing the Health status
Dong @ DESKTOP: ~ $docker ps | grep mysql 84 e7e0887bc6 mysql: 8.0.26 "docker - entrypoint. S..." 28 seconds ago Up 27 seconds 3306/ TCP, 33060/ TCP DB2 C6989e437e61 mysql:8.0.26 "docker-entryPoint. 34 seconds ago Up 33 seconds 3306/tcp, 33060/tcp db1Copy the code
  • Other commands
Docker exec -it db1 mysql -uroot -prootCopy the code

Modify the configuration

Master Service Configuration

# vim /home/dong/db1/my.cnf # Add the following configuration server_id = 1 # In binary log replication mode to be unique gtid_mode = ON # Enable GTID mode enforce_gtid_consistency Binlog_gtid_simple_recovery = ON # Controls how binary log files are repeated during the search for GTID when MySQL starts or restarts. Relay_log_recovery = ON # Use the trunk log to recover during startupCopy the code
  • Server_id description: dev.mysql.com/doc/refman/…
  • Gtid_mode description: dev.mysql.com/doc/refman/…
  • Enforce_gtid_consistency description: dev.mysql.com/doc/refman/…
  • Binlog_gtid_simple_recovery description: dev.mysql.com/doc/refman/…
  • Relay_log_recovery description: dev.mysql.com/doc/refman/…

Restart the service

# # to restart the docker restart db1 view the docker ps | grep mysqlCopy the code

Create a user to synchronize data

Docker exec -it db1 mysql -uroot -proot Here we use mysql_native_password authentication, MySQL8 default authentication requires SSL, CREATE USER REPL_user @'%' IDENTIFIED WITH mysql_native_password BY 'repl*User123'; Grant replication slave on *.* to 'repl_user'@'%'; Flush privileges;Copy the code

Create test data

CREATE DATABASE 'blog' DEFAULT CHARACTER SET UTf8MB4 COLLATE UTF8MB4_unicode_CI; Use blog; CREATE TABLE 'user' (' id 'int NOT NULL AUTO_INCREMENT,' username 'VARCHar (32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Insert into user (username) values("test1"); insert into user (username) values("test2"); Select * from user;Copy the code

Look at the master status

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000003 |     2955 |              |                  | 81e99d09-f5f5-11eb-8a5a-0242ac120002:1-11 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
Copy the code

Back up data on the primary server

Mysqldump -uroot -proot --single-transaction --databases blog > / TMP /db.sql # Copy to local docker cp db1:/ TMP /db.sql./ # db.sql GTID_PURGED=/* @@global.GTID_PURGED=/*! @@global. 80000 '+'*/ '81e99d09-f5f5-11eb-8a5a-0242ac120002:1-11';Copy the code

Secondary server configuration

configuration

# vim /home/dong/db2/my.cnf [mysqld] server_id = 2 # In binary log replication mode, gtid_mode = ON # Enables GTID mode enforce_gtid_consistency Binlog_gtid_simple_recovery = ON # Controls how binary log files are repeated during the search for GTID when MySQL starts or restarts. Relay_log_recovery = ON # Use the trunk log to recover during startupCopy the code

restart

docker restart db2
Copy the code

Import data

Docker cp db. SQL db2:/ TMP # Log in to MySQL docker exec -it db2 MySQL -uroot -proot # Import source/TMP /db.sqlCopy the code

Set the synchronous

  • CHANGE the MASTER TO illustrate: dev.mysql.com/doc/refman/…
Mysql > CHANGE MASTER TO MASTER_HOST = 'db1', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = 'repl*User123', MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 8 warnings (0.07 SEC) # mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 SEC) # check status mysql > show slave status \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. Row * * * * * * * * * * * * * * * * * * * * * * * * * * * Slave_IO_State: Waiting for source to send event Master_Host: db1 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 196 Relay_Log_File: d8a9b6ad089b-relay-bin.000002 Relay_Log_Pos: 365 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 196 Relay_Log_Space: 581 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 81e99d09-f5f5-11eb-8a5a-0242ac120002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 81e99d09-f5f5-11eb-8a5a-0242ac120002:1-11 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 Warning (0.00 SEC)Copy the code

test

Mysql > insert into blog.user (username) values("test3"); mysql> insert into blog.user (username) values("test3"); Query OK, 1 row affected (0.02sec) # Select * from blog.user; mysql> select * from blog.user; +----+----------+ | id | username | +----+----------+ | 1 | test1 | | 2 | test2 | | 3 | test3 | +----+----------+ 5 rows In the set (0.00 SEC)Copy the code

Other Common Commands

# stop slave stop slave; Reset slave; Reset master; Salve show slave hosts; # Check the current connection show processList; Select Host, User, plugin from User; Grant @'%';Copy the code