主从复制介绍
一、MySQL主从复制介绍
1.1.1 MySQL主从复制介绍
- 主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。
- MySQL中复制的优点包括：
- 横向扩展解决方案 -在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
- 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从上运行备份服务而不会破坏相应的主数据。
分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不影响主服务器的性能。 - 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问服务器。
{{uploading-image-943327.png(uploading...)}}
- 搭建主从复制(这里使用多实例搭建主从复制)
主从复制的前提
* 至少2个实例
* 不同的server_id
* 主库需要开启二进制日志
* 主库需要授权一个专用复制用户
* 主库数据备份
* 开启专用复制线程
多实例搭建地址：https://www.cnblogs.com/woaiyunwei/p/13039619.html
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309
[root@db01 ~]# ss -luntp|grep 330
tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=1376,fd=20))
tcp LISTEN 0 80 [::]:3307 [::]:* users:(("mysqld",pid=1450,fd=19))
tcp LISTEN 0 80 [::]:3308 [::]:* users:(("mysqld",pid=1485,fd=22))
tcp LISTEN 0 80 [::]:3309 [::]:* users:(("mysqld",pid=1584,fd=22))
主库：3307
[root@db01 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock
检查是否开启binlog
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| log_bin | ON |
| log_bin_basename | /data/3307/mysql-bin |
| log_bin_index | /data/3307/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------+
6 rows in set (1.72 sec)
检查server-id号
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
在主库上创建用于主从复制的用户（管理员）
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
将主库的数据备份
[root@db01 ~]# mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -R -E --triggers --master-data=2 --single-transaction >/tmp/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# ll /tmp/full.sql
-rw-r--r-- 1 root root 792898 Jun 26 22:30 /tmp/full.sql
将数据恢复到从库（3308）
[root@db01 ~]# mysql -uroot -p123456 -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/full.sql
配置从库复制的信息（添加主库的chang master to 信息）
mysql> help change master to; （查看帮助命令）
CHANGE MASTER TO
MASTER_HOST='master2.example.com', （ip或主机名）
MASTER_USER='replication', （用户）
MASTER_PASSWORD='password', （密码）
MASTER_PORT=3306, （端口）
MASTER_LOG_FILE='master2-bin.001', （二进制日志）
MASTER_LOG_POS=4, （position 起始值）
MASTER_CONNECT_RETRY=10; （表示重连的时间间隔，slave_net_timeout超时后，立刻重连，后续重连的时间间隔由 CHANGE MASTER TO 命令的MASTER_CONNECT_RETRY 参数指定。默认值60s。）
修改如下：
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=443,
MASTER_CONNECT_RETRY=10;
可以在备份中找到当前用的二进制文件和position起始号
[root@db01 ~]# cat /tmp/full.sql | head -30
-- MySQL dump 10.13 Distrib 5.7.26, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=443;
#这里就是二进制文件和position初始号
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
将配置命令复制到从库执行
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.51',
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=443,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
在从库启动复制线程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
这些没有报错就是正常的
如果 change master to 信息输入错误，咋办？
mysql> stop slave;
mysql> reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=443,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
- 主从复制的原理
主从复制工作过程（原理）
名词认识
文件：
主库：binlog
从库：
relay-log 中继日志
master.info 主库信息文件
relay-log.info 中继日志应用信息
线程：
主库：
binlog_dump_thread 二进制日志投递线程
mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 4 | rep | 10.0.0.51:36667 | NULL | Binlog Dump | 1884 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
从库:
IO_Thread : 从库IO线程 ： 请求和接收binlog
SQL_Thread: 从库的SQL线程 ： 回放日志
mysql> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| 2 | root | localhost | mysql | Query | 0 | starting | show processlist |
| 3 | system user | | NULL | Connect | 2178 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 2178 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
工作原理
（1）从库执行 change master to 语句，会立即将主库信息记录到master.info中
（2）从库执行 start slave语句，会立即生成IO_T和SQL_T
（3）IO_T 读取master.info文件，获取到主库信息
（4）IO_T 连接主库，主库会立即分配一个DUMP_T,进行交互
（5）IO_T 根据master.info binlog信息，向DUMP_T请求最新的binlog
（6）主库DUMP_T,经过查询，如果发现有新的，截取并反回给从库IO_T
（7）从库IO_T会收到binlog，存储在TCP/IP缓存中,在网络底层返回ACK
（8）从库IO_T会更新master.info ,重置binlog位置点信息
（9）从库IO_T会将binlog，写入到relay-log中
（10）从库SQL_T 读取Relay-log.info 文件，获取上次执行过的位置点
（11）SQL_T按照位置点往下执行relaylog日志
（12）SQL_T执行完成后，重新更新relay-log.info
（13）relaylog定期自动清理的功能。
细节：
主库发生了信息的修改，更新二进制日志完成后，会发送一个“信号”给Dump_T,Dump_T通知给IO_T线程
1.1.2 主从复制监控及故障分析处理
- 主库
mysql> show processlist;
+----+------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| 4 | rep | 10.0.0.51:36667 | NULL | Binlog Dump | 58827 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
Master has sent all binlog to slave; waiting for more updates
监控到binblogdump 无报错就代表主库基本正常
- 从库
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 443
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 443
Relay_Log_Space: 526
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: 7
Master_UUID: a64409a6-a586-11ea-95ee-000c290e8d03
Master_Info_File: /data/3308/data/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)
主库的信息（master.info）：
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51 主库的IP
Master_User: rep复制用户名
Master_Port: 3307主库的端口
Connect_Retry: 10断连之后重试次数
Master_Log_File: mysql-bin.000002 已经获取得到binlog的文件名
Read_Master_Log_Pos: 443 已经获取得到binlog的位置号
从库的relaylog的信息(relay-log.info)：
Relay_Log_File: db01-relay-bin.000002 从库已经运行过的relaylog的文件名
Relay_Log_Pos: 320 从库已经运行过的relaylog的位置点
从库复制线程工作状态：
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:
从库延时主库的时间(重点监控):
Seconds_Behind_Master: 0 从库延时主库的时间（秒为单位）
从库线程报错详细信息：
Last_IO_Errno: 0 IO报错的号码
Last_IO_Error: IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL线程报错的具体原因
延时从库：
SQL_Delay: 0 延时从库设定的时间
SQL_Remaining_Delay: NULL 延时操作剩余时间
GTID复制信息：
Retrieved_Gtid_Set: 接收到的GTID的个数
Executed_Gtid_Set: 执行了的GTID的个数
主从故障的分析及处理
从库复制线程工作状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库线程报错详细信息：
Last_IO_Errno: 0 IO报错的号码
Last_IO_Error: IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL线程报错的具体原因
- 主从故障I/O解决
IO线程故障
（1）连接主库连接不上
connecting
原因：
网络不通
防火墙
IP 不对
port 不对
用户，密码不对
skip_name_resolve
链接数上限
[root@db01 ~]# mysql -urep -p123456 -h 10.0.0.51 -P 3308 --->端口问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server
[root@db01 ~]# mysql -urep -p123456 -h 10.0.0.52 -P 3307 --->ip问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)
[root@db01 ~]# mysql -urep -p1234 -h 10.0.0.51 -P 3307 --->密码问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
[root@db01 ~]# mysql -urepl -p123456 -h 10.0.0.51 -P 3307 --->用户名问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES)
如何处理？
stop slave;
reset slave all ;
change master to
....
...
。
start slave;
请求新的binlog
IO线程No的状态分析：
原因一： 日志名不对
从库信息：
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
对比备份的位置号。
原因二：日志损坏，日志不连续
演示：
主库：
mysql -S /data/3307/mysql.sock
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> reset master;
mysql> create database dd;
Query OK, 1 row affected (0.01 sec)
mysql> create database dd1;
Query OK, 1 row affected (0.00 sec)
mysql> create database dd2;
Query OK, 1 row affected (0.00 sec)
从库：
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/mysql-bin.000006' at 154, the last byte read from '/data/3307/mysql-bin.000006' at 154.'
处理：
mysql -S /data/3308/mysql.sock
stop slave;
reset slave all ;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
（3）写relaylog
（4）更新master.info
（5）server_id重复
- 主从故障I/O解决
SQL线程故障
原因一：
读relay-log.info
读relay-log ，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从
原因二：
为什么一条SQL语句执行不成功？
1. 主从数据库版本差异较大
2. 主从数据库配置参数不一致（例如：sql_mode等）
3. 想要创建的对象已经存在
4. 想要删除或修改的对象不存在
5. 主键冲突
6. DML语句不符合表定义及约束时
归根结底是从库写入了。
方法一：
stop slave;
set global sql_slave_skip_counter = 1; (跳过刚才得报错)
/etc/my.cnf
slave-skip-errors = 1032,1062,1007 （生产中不要建议设置，数据安全有很大隐患）
方法二：万全的解决 （推荐用法）
设置从库只读，防止写入
使用中间件做成读写分离的架构（推荐）
read_only=on （从库开启只读 只能管普通用户）
- 主从延时原因分析
从库延时主库的时间:
Seconds_Behind_Master: 0 从库延时主库的时间（秒为单位） #注意这个时间也有点不准确
主库方面：
日志写入不及时
sync_binlog=1;
主库并发业务较高
“分布式”架构
从库太多
级联主从
对于Classic Replication ：
主库是有能力并发运行事务的，但是在Dump_T在传输日志的时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。
怎么处理：
group commit。
mysql> show variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
mysql> set global binlog_group_commit_sync_delay=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 1 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
从5.6开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。
gtid-mode=on
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
sync_binlog=1;
从库方面
Classic Replication
SQL 线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
在5.6中出现了database级别的多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS ，真正实现了事务级别的并发SQL
- 从库人为设置延时
数据损坏
物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。
设计理念
对SQL线程进行延时设置
如何设置：企业生产中 延时多久合适？
一般企业，延时3-6小时
设置如下：
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 10800; （单位秒）
mysql>start slave;
mysql> show slave status G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
如何使用延时从库
思路模拟故障：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
发现问题了：
1. 停止SQL线程，停止主库业务。
2. 模拟SQL手工恢复relaylog到drop之前的位置点
3. 截取relaylog日志，找到起点（relay-log.info）和终点(drop 操作)
4. 恢复截取的日志，验证数据可用性。
开始处理：
停从库的SQL线程
mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;
找relaylog的起点和终点
起点：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
终点：
mysql> show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1149 | Query | 7 | 2036 | drop database delay
#截取relaylog是截取（开始到结束都是在右边截取）右边的比如：1149 左边的不要截取
截取日志
[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
恢复
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/relay.sql
7.过滤复制
主库：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 765 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
控制参数：（了解，不推荐在主库使用）
binlog_do_db （只记录某个库，数据库白名单列表）
binlog_ignore_db （不记录某个库，数据库黑名单列表）
从库参数：（推荐在从库使用）
Replicate_Do_DB: （只复制某个库，数据库白名单列表） #这个用的最多
Replicate_Ignore_DB: （不复制某个库，数据库黑名单列表） #这个用的最多
Replicate_Do_Table: （只复制某个库下的表，数据库白名单列表）
Replicate_Ignore_Table: （不复制某个库下的表，数据库黑名单列表）
Replicate_Wild_Do_Table: #更高级别的应用，通配符，应用到哪一类表的
Replicate_Wild_Ignore_Table: #更高级别的应用，通配符，应用到哪一类表的
例子：从库只同步
[root@db01 ~]# vim /data/3308/my.cnf
replicate_do_db=xyz #注意参数一定要小写。多个库用,号隔开
[root@db01 ~]# systemctl restart mysqld3308
mysql> show slave statusG;
Replicate_Do_DB: xyz
主库创建数据库测试：
mysql> create database xyz;
Query OK, 1 row affected (0.00 sec)
mysql> create database ces;
Query OK, 1 row affected (0.00 sec)
从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| xyz |
+--------------------+
6 rows in set (0.05 sec)
从库只同步了xyz，而没有同步ces，过滤复制配置成功
- 半同步复制
加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#INSTALL PLUGIN是mysql加载插件的命令
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status'
半主从复制和传统复制的区别：
半主从复制是以插件形式提供的功能
主库线程：ack_receiver
从库线程：ACK_send
主库的 ack_receiver线程只有收到从库发来的ACK_send确认，主库的事务才能commit成功，
从库的ACK_send只有等relaylog落地才能发送ACK，主库接受从库ACK的时间为10s，
如果超时没有收到，会自动替换为异步复制。
- GTID复制
主库开启GTID复制
gtid-mode=on
enforce-gtid-consistency=true
从库开启强制刷新从库二进制日志（高可用MHA环境必须开启，级联复制也要开启）
log-slave-updates=1
准备三台mysql虚拟机环境
db01配置：
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
basedir = /application/mysql/
datadir = /application/mysql/data/
socket = /tmp/mysql.sock
server_id = 51
port = 3306
log_error = /application/mysql/data/mysql.err
log_bin = /application/mysql/log_bin/mysql-bin
#secure-file-priv= /tmp
autocommit=0
innodb_flush_log_at_trx_commit= 1
innodb_flush_method= O_DIRECT
innodb_log_buffer_size= 128M
innodb_log_file_size= 256M
innodb_log_files_in_group = 3
gtid_mode=on
enforce_gtid_consistency= true
log_slave_updates=1
expire_logs_days= 15
slow_query_log= 1
slow_query_log_file= /application/mysql/slow/slow.log
long_query_time= 0.1
log_queries_not_using_indexes
[client]
socket = /tmp/mysql.sock
[mysql]
promtp=db01 [\d]>
db02配置：
[root@db02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /application/mysql/
datadir = /application/mysql/data/
socket = /tmp/mysql.sock
server_id = 52
port = 3306
log_error = /application/mysql/data/mysql.err
log_bin = /application/mysql/log_bin/mysql-bin
#secure-file-priv= /tmp
autocommit=0
innodb_flush_log_at_trx_commit= 1
innodb_flush_method= O_DIRECT
innodb_log_buffer_size= 128M
innodb_log_file_size= 256M
innodb_log_files_in_group = 3
gtid_mode=on
enforce_gtid_consistency= true
log_slave_updates=1
expire_logs_days= 15
slow_query_log= 1
slow_query_log_file= /application/mysql/slow/slow.log
long_query_time= 0.1
log_queries_not_using_indexes
slave_skip_errors = 1032,1062,1007
[client]
socket = /tmp/mysql.sock
[mysql]
promtp=db02 [\d]>
db03配置：
[root@db03 ~]# cat /etc/my.cnf
[mysqld]
basedir = /application/mysql/
datadir = /application/mysql/data/
socket = /tmp/mysql.sock
server_id = 53
port = 3306
log_error = /application/mysql/data/mysql.err
log_bin = /application/mysql/log_bin/mysql-bin
#secure-file-priv= /tmp
autocommit=0
innodb_flush_log_at_trx_commit= 1
innodb_flush_method= O_DIRECT
innodb_log_buffer_size= 128M
innodb_log_file_size= 256M
innodb_log_files_in_group = 3
gtid_mode=on
enforce_gtid_consistency= true
log_slave_updates=1
expire_logs_days= 15
slow_query_log= 1
slow_query_log_file= /application/mysql/slow/slow.log
long_query_time= 0.1
log_queries_not_using_indexes
slave_skip_errors = 1032,1062,1007
[client]
socket = /tmp/mysql.sock
[mysql]
promtp=db03 [\d]>
初始化三个数据库：
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/appliaction/mysql --datadir=/application/mysql/data
[root@db03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/appliaction/mysql --datadir=/application/mysql/data
启动mysql
systemctl start mysqld
设置密码
mysqladmin -uroot password '123456'
在主库创建replication复制账号
db01 [(none)]>grant replication slave on *.* to rep@'10.4.7.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
db01 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
设置从库change master to信息（因为都是新库所以不用备份恢复主库数据，如果主库时生产库，就需要将备份恢复到从库才开始设置从库的change master to）
重要参数： MASTER_AUTO_POSITION = {0|1} （0不开启，1开启 GTID复制）
注意GTID号和position复制的参数是不一样的
db02：
db02 [(none)]>CHANGE MASTER TO
-> MASTER_HOST='10.4.7.51',
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db02 [(none)]>start slave;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)]>show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
db03：
db03 [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.4.7.51',
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db03 [(none)]>start slave;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)]>show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db03-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主库穿件测试test库测试是否成功
db01 [(none)]>create database test;
Query OK, 1 row affected (0.01 sec)
db01 [(none)]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 | 313 | | | 55799216-ba11-11ea-810e-000c290e8d03:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
从库查看
db02：
db02 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db02 [(none)]>show slave statusG;
Retrieved_Gtid_Set: 55799216-ba11-11ea-810e-000c290e8d03:1
Executed_Gtid_Set: 55799216-ba11-11ea-810e-000c290e8d03:1
Auto_Position: 1
db03：
db03 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db03 [(none)]>show slave statusG;
Retrieved_Gtid_Set: 55799216-ba11-11ea-810e-000c290e8d03:1
Executed_Gtid_Set: 55799216-ba11-11ea-810e-000c290e8d03:1
Auto_Position: 1
使用GTID号做主从复制就不需要position了