【DB宝64】MySQL主从之1主2从异步复制搭建及同步测试

-- 设置主从的网络环境
docker pull mysql:5.7.30
docker network create --subnet=172.72.0.0/24 mysql-network


-- 删除之前的容器
docker rm -f MSS5730M33650 MSS5730S33651 MSS5730S33652

-- 创建参数文件路径
mkdir -p /lhrmysqltest3/master/conf.d
mkdir -p /lhrmysqltest3/slave1/conf.d
mkdir -p /lhrmysqltest3/slave2/conf.d


-- 申请主库环境
docker run -d --name MSS5730M33650 \
   -h master -p 33650:3306 --net=mysql-network --ip 172.72.0.50 \
   -v /lhrmysqltest3/master/conf.d:/etc/mysql/conf.d  \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.30

-- 申请从库1环境
docker run -d --name MSS5730S33651 \
   -h slave1 -p 33651:3306 --net=mysql-network --ip 172.72.0.51 \
   -v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.30

-- 申请从库2环境
docker run -d --name MSS5730S33652 \
   -h slave2 -p 33652:3306 --net=mysql-network --ip 172.72.0.52 \
   -v /lhrmysqltest3/slave2/conf.d:/etc/mysql/conf.d \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.30


-- 配置主库参数
cat > /lhrmysqltest3/master/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033650
log-bin = 
binlog_format=row
skip-name-resolve
gtid-mode=ON
enforce-gtid-consistency=on
report_host=172.72.0.50
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1
EOF

-- 配置从库1参数
cat > /lhrmysqltest3/slave1/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033651
log-bin = 
binlog_format=row
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.51
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1
EOF


-- 配置从库2参数
cat > /lhrmysqltest3/slave2/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033652
log-bin = 
binlog_format=row
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.52
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1
EOF

-- 重启3台环境
docker restart MSS5730M33650
docker restart MSS5730S33651
docker restart MSS5730S33652
docker ps

-- 登陆
docker exec -it MSS5730M33650 bash
docker exec -it MSS5730M33650 mysql -uroot -plhr

-- 查询
mysql -uroot -plhr -h192.168.66.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid"

-- 主库创建复制用户repl
mysql -uroot -plhr -h192.168.66.35 -P33650
grant replication slave on *.* to repl@'%' identified by 'lhr';
select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 

create database lhrdb;
use lhrdb;
create table mytb1(id int,name varchar(30));
insert into mytb1 values(1,'a'),(2,'b');
select * from mytb1;


-- 导出时不能加--set-gtid-purged=off参数
mysqldump -uroot -plhr -h192.168.66.35 -P33650 --single-transaction --hex-blob --routines --events --triggers \
--master-data=2  --databases lhrdb --default-character-set=utf8  --max_allowed_packet=512M > /tmp/salve.sql


-- 查询
show master status ;
show slave hosts;
select @@hostname,@@server_id,@@server_uuid;

MySQL [lhrdb]> show master status ;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000004 |     1085 |              |                  | 1bc15639-bc5b-11eb-b1eb-0242ac480032:1-9 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.06 sec)

MySQL [lhrdb]> show slave hosts;
Empty set (0.06 sec)

MySQL [lhrdb]> select @@hostname,@@server_id,@@server_uuid;
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| master     |   573033650 | 1bc15639-bc5b-11eb-b1eb-0242ac480032 |
+------------+-------------+--------------------------------------+
1 row in set (0.05 sec)

-- 从库1导入数据
mysql -uroot -plhr -h192.168.66.35 -P33651
-- 重置master，否则执行导入脚本时报错：ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
reset master;

mysql -uroot -plhr -h192.168.66.35 -P33651 < /tmp/salve.sql

-- 配置同步
change master to
master_host='172.72.0.50',
master_port=3306,
master_user='repl',
master_password='lhr',
master_auto_position=1;

-- 启动复制进程
start slave; 
show slave status \G;

SELECT * FROM lhrdb.mytb1;

-- 主库：
insert into mytb1 values(3,'c'),(4,'d');

-- S2
mysql -uroot -plhr -h192.168.66.35 -P33652

-- 修改参数，配置同步
change master to master_host='172.72.0.50',
master_port=3306,
master_user='repl',
master_password='lhr',
master_auto_position=1;

start slave; 
show slave status \G;

SELECT * FROM lhrdb.mytb1;


-- 测试同步
insert into mytb1 values(5,'e'),(6,'f');

-- 主库
show slave hosts;
show master status;

-- 从库
show slave status;




-- 线程查询
SELECT *
FROM performance_schema.threads a
WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;

MySQL [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.72.0.50
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 1085
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 417
        Relay_Master_Log_File: master-bin.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: 1085
              Relay_Log_Space: 625
              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: 573033650
                  Master_UUID: 1bc15639-bc5b-11eb-b1eb-0242ac480032
             Master_Info_File: mysql.slave_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: 1bc15639-bc5b-11eb-b1eb-0242ac480032:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

Master_Log_File= Relay_Master_Log_File
Read_Master_Log_Pos= Exec_Master_Log_Pos

-- 在主库查询：
MySQL [lhrdb]> show slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host        | Port | Master_id | Slave_UUID                           |
+-----------+-------------+------+-----------+--------------------------------------+
| 573033651 | 172.72.0.51 | 3306 | 573033650 | 41618dbd-7bea-11eb-9d95-0242ac480033 |
| 573033652 | 172.72.0.52 | 3306 | 573033650 | 43036dd9-7bea-11eb-8dcb-0242ac480034 |
+-----------+-------------+------+-----------+--------------------------------------+
2 rows in set (0.05 sec)

-- PROCESSLIST_COMMAND为Binlog Dump表示非GTID复制，Binlog Dump GTID表示MySQL 5.6.5版本新增的基于GTID复制
MySQL [lhrdb]> SELECT *
    -> FROM performance_schema.threads a
    -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------------------------------------+---------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                             | PROCESSLIST_INFO                                        | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------------------------------------+---------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
|        28 | thread/sql/one_connection | FOREGROUND |              2 | repl             | 172.72.0.51      | NULL           | Binlog Dump GTID    |              583 | Master has sent all binlog to slave; waiting for more updates | SET @slave_uuid= '41618dbd-7bea-11eb-9d95-0242ac480033' |                1 | NULL | YES          | YES     | TCP/IP          |           76 |
|        29 | thread/sql/one_connection | FOREGROUND |              3 | repl             | 172.72.0.52      | NULL           | Binlog Dump GTID    |              582 | Master has sent all binlog to slave; waiting for more updates | SET @slave_uuid= '43036dd9-7bea-11eb-8dcb-0242ac480034' |                1 | NULL | YES          | YES     | TCP/IP          |           77 |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------------------------------------+---------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
2 rows in set (0.05 sec)

MySQL [lhrdb]> SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;
+----+------+-------------------+------+------------------+------+---------------------------------------------------------------+------+
| ID | USER | HOST              | DB   | COMMAND          | TIME | STATE                                                         | INFO |
+----+------+-------------------+------+------------------+------+---------------------------------------------------------------+------+
|  3 | repl | 172.72.0.52:35566 | NULL | Binlog Dump GTID | 1340 | Master has sent all binlog to slave; waiting for more updates | NULL |
|  2 | repl | 172.72.0.51:34004 | NULL | Binlog Dump GTID | 1341 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+-------------------+------+------------------+------+---------------------------------------------------------------+------+
2 rows in set (0.05 sec)



-- 在从库查询：
MySQL [(none)]> SELECT *
    -> FROM performance_schema.threads a
    -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND='Binlog Dump' ;
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME                 | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                      | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
|        26 | thread/sql/slave_io  | FOREGROUND |              1 | NULL             | NULL             | NULL           | Connect             |              199 | Waiting for master to send event                       | NULL             |                1 | NULL | YES          | YES     | NULL            |           73 |
|        27 | thread/sql/slave_sql | FOREGROUND |              2 | NULL             | NULL             | NULL           | Connect             |              199 | Slave has read all relay log; waiting for more updates | NULL             |                1 | NULL | YES          | YES     | NULL            |           74 |
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
2 rows in set (0.06 sec)

MySQL [(none)]> SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command='Binlog Dump';
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
| ID | USER        | HOST | DB   | COMMAND | TIME | STATE                                                  | INFO |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
|  2 | system user |      | NULL | Connect |  312 | Slave has read all relay log; waiting for more updates | NULL |
|  1 | system user |      | NULL | Connect |  312 | Waiting for master to send event                       | NULL |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
2 rows in set (0.05 sec)