原 【DB宝64】MySQL主从之1主2从异步复制搭建及同步测试
[toc]
一、MySQL主从复制简介
1.1、MySQL主从复制简介
MySQL主从复制（MySQL Replication）是指从一个MySQL主服务器(master)将数据拷贝到另一台或多台MySQL从服务器(slaves)的过程。将主数据库的DDL和DML操作通过二进制日志（binlog）传到从服务器（slave）上，然后在从服务器上对这些日志重新执行，从而使得主从服务器的数据保持同步。
MySQL从3.23版本开始提供复制的功能。
MySQL的Replication是一个多MySQL数据库做主从同步的方案，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。
1.2、主从复制的好处
主从复制有以下几方面的好处：
- 数据备份(Data Backup)
只是简单的对数据库进行备份，降低数据丢失的风险， - 线下统计
用于报表等对数据时效性要求不高的场合。 - 负载均衡(Load Balance)、读写分离
主要用在MySQL集群，解决单点故障或做故障切换；以降低单台服务器的负载和风险，如实现读写分离，可以使得服务器访问负荷比较均衡。 - 数据分发(Data DistributIOn)、灾备
主要用于多数据中心或异地备份，实现数据分发与同步。 - 高可用和数据容错(High Availability and Failover)
MySQL自带的健康监控和检测，根据配置的时间间隔，可以检测主库是否正常工作，一旦发现主库宕机或无法正常工作，就会选择到最好的一个备库上。
1.3、MySQL主从复制流程
1.4、主从拓扑结构
二、1主2从异步复制搭建
2.1、MySQL环境初始化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | -- 设置主从的网络环境 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" |
2.2、主库配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- 主库创建复制用户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; |
执行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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) |
2.3、从库1配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 从库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'); |
2.4、从库2配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 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'); |
2.5、主从查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 主库 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') ; |
2.5.1、"show slave status"介绍
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | 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: |
SHOW SLAVE STATUS会返回以下字段：
- Slave_IO_State
SHOW PROCESSLIST输出的State字段的拷贝。SHOW PROCESSLIST用于从属I/O线程。如果线程正在试图连接到主服务器，正在等待来自主服务器的时间或正在连接到主服务器等，本语句会通知您
slave I/O线程的状态，有以下几种：
1) waiting for master update
这是connecting to master状态之前的状态
2) connecting to master
I/O线程正尝试连接到master
3) checking master version
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。
4) registering slave on master
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。
5) requesting binlog dump
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。在这个状态下，I/O线程向master发送请求，请求binlog，位置从指定的binglog 名字和binglog的position位置开始。
6) waiting to reconnect after a failed binlog dump request
如果因为连接断开，导致binglog的请求失败，I/O线程会进入睡眠状态。然后定期尝试重连。尝试重连的时间间隔，可以使用命令"change master to master_connect_trt=X;"改变。
7) reconnecting after a failed binglog dump request
I/O进程正在尝试连接master
8) waiting for master to send event
说明，已经成功连接到master，正等待二进制日志时间的到达。如果master 空闲，这个状态会持续很长时间。如果等待的时间超过了slave_net_timeout(单位是秒)的值，会出现连接超时。在这种状态下，I/O线程会人为连接失败，并开始尝试重连
9) queueing master event to the relay log
此时，I/O线程已经读取了一个event，并复制到了relay log 中。这样SQL 线程可以执行此event
10) waiting to reconnect after a failed master event read
读取时出现的错误(因为连接断开)。在尝试重连之前，I/O线程进入sleep状态，sleep的时间是master_connect_try的值(默认是60秒)
11) reconnecting after a failed master event read
I/O线程正尝试重连master。如果连接建立，状态会变成"waiting for master to send event"
12) waiting for the slave sql thread to free enough relay log space
这是因为设置了relay_log_space_limit，并且relay log的大小已经整张到了最大值。I/O线程正在等待SQL线程通过删除一些relay log，来释放relay log的空间。
13) waiting for slave mutex on exit
I/O线程停止时会出现的状态，出现的时间非常短。
Master_Host:
主库的ip地址
Master_User
被用于连接主服务器的当前用户。这个是master上面的一个用户。用来负责主从复制的用户，创建主从复制的时候建立的（具有reolication slave权限）。
- Master_Port
当前的主服务器端口，master服务器的端口，一般是3306。
- Connect_Retry
--master-connect-retry选项的当前值。连接中断后，重新尝试连接的时间间隔。默认值是60秒。
- Master_Log_File
I/O线程当前正在读取的主服务器二进制日志文件的名称。
- Read_Master_Log_Pos
在当前的主服务器二进制日志中，I/O线程已经读取的位置。
- Relay_Log_File
SQL线程当前正在读取和执行的中继日志文件的名称。
- Relay_Log_Pos
在当前的中继日志中，SQL线程已读取和执行的位置。（Relay_Log_File下的Relay_Log_Pos其实一一对应着Relay_Master_Log_File的Exec_Master_Log_Pos。）
- Relay_Master_Log_File
由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。当前slave SQL线程读取并执行的relay log的文件中多数近期事件，对应的主服务器二进制日志文件的名称。（说白点就是SQL线程从relay日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中）
- Slave_IO_Running
I/O线程是否被启动并成功地连接到主服务器上。
- Slave_SQL_Running
SQL线程是否被启动。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
啥版本呀？
5.7.30版本，5.7通用