Detailed explanation of Docker's Mysql master-slave replication

This article mainly shares with you the Mysql master-slave replication based on Docker, hoping to help everyone.

Environment Introduction

Master server: JD Cloud CentOS

Slave server: Vultr Cloud CentOS

In order to facilitate testing, Docker is used to run Mysql and Docker is started. The command is as follows:

docker run --name mysql-server-01 -p 3306:3306 -v "$PWD"/conf.d:/etc/mysql/conf.d -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw  -d mysql:latest

The above maps the mysql configuration directory and data directory to the host machine.

Configuring Master

For convenience, a special directory is created to operate the master database. The structure is as follows

mysql
└── server01
    ├── conf.d
    ├── data
    └── start.sh

Create my.cnf in cond.d to configure master

[mysqld]log-bin=mysql-binserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1

Then you can start the container.

After starting, enter the container to operate mysql

# 进入容器
docker exec -it 容器ID bash

# 进入msyql客户端
mysql -u root -p

# 新建salve用户并配置权限，格式如下
mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

# 查看master状态，这里file和position后面配置salve会用到mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |     1092 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

Configure the slave server

slave my. The cnf configuration file is as follows (for simplicity, many parameters are omitted here and should not be used in production environments):

[mysqld]server-id=2

Then start the container, Enter the mysql client.

# 配置master到slave
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',    ->     MASTER_USER='replication_user_name',    ->     MASTER_PASSWORD='replication_password',    ->     MASTER_LOG_FILE='recorded_log_file_name',    ->     MASTER_LOG_POS=recorded_log_position;

The above MASTER_LOG_FILE and MASTER_LOG_POS are the file and position seen in the previous show master stuas

Then start the slave

START SLAVE;

Check the running status:

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: xxx.com                  Master_User: slave1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 1092               Relay_Log_File: ca210e4caaaa-relay-bin.000002                Relay_Log_Pos: 813        Relay_Master_Log_File: mysql-bin.000001             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: 1092              Relay_Log_Space: 1027              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: 0Master_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: 5c2073ab-0ad6-11e8-a871-0242ac110005             Master_Info_File: /var/lib/mysql/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave 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:                 Auto_Position: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)

Both Slave_IO_Running and Slave_SQL_Running above are YES, indicating that the master and slave have been configured.

Summary

There are three common architectures of mysql

• Single instance architecture

• Master-slave replication

• MHA High Availability Architecture

One of the master-slave replication methods is based on the above binary log replication method, and the other one is based on the global transaction identifier. (GTID) method.

Related recommendations:

Detailed explanation of MySQL master-slave replication process_Mysql instance detailed explanation

##About MySQL5. 7.18 Detailed explanation of master-slave replication to build a master-slave instance

Mycat read-write separation is implemented based on MySQL master-slave replication

The above is the detailed content of Detailed explanation of Docker's Mysql master-slave replication. For more information, please follow other related articles on the PHP Chinese website!