《MySQL数据库》MySQL主从复制搭建与原理

前言

主从复制：两台或者更多的数据库实例，通过二进制日志，实现数据同步。为什么需要主从复制，主从复制的作用是什么，答：为了预防灾难。

搭建

第一步：准备多实例环境。如何创建多实例见：

第二步：确保每一个实例的server_id 不同。

检查各自实例图中（my.cnf）的配置是否不同。

第三步：主库检查binlog 是否开启

第四步：主库创建复制用户

grant replication slave on *.* to repl@'%' identified by 'repl';

第五步：主库和从库在主从之前要保证数据结构一致。

主库备份，恢复到从库：https://www.cnblogs.com/jssj/p/13514597.html

第六步：主从连接（重点）

从库中执行以下语句，告知从库连接信息，同步开始点等（我这里有两个从库就两个从库都登入执行） 

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',                 -- 主库ip
  MASTER_USER='repl',                      -- 主从专用用户
  MASTER_PASSWORD='repl',                  -- 用户密码
  MASTER_PORT=3307,                        -- 主库ip
  MASTER_LOG_FILE='mysql-bin.000001',      --复制开始点的binlog文件
  MASTER_LOG_POS=653,                      --binlog 文件中的开始点
  MASTER_CONNECT_RETRY=10;                 -- 重连次数
  MASTER_DELAY = 300;                      -- 从库SQL线程延时时间设置，（一般一主二从的时候，一从不加延时，一从加延时，这样逻辑误操作可以及时修复）

第七步：从库中启动专用主从线程

start slave ;      -- 启动主从

stop slave;     -- 关闭主从

扩展：

start slave sql_thread  -- 单独启动从库sql线程
start slave io_thread    -- 单独启动从库I/O线程
stop slave sql_thread   -- 单独停从库sql线程
stop slave io_thread     -- 单独停从库I/O线程

第八步：验证主从复制是否正常

查看从库线程

mysql -uroot -S /usr/local/mysql/data/3308/mysql.sock -e "show slave statusG | grep Running"

还有就是在主库上创建数据库，创建表等操作，看看从库是否和主库一致。

如果搭建失败：

执行：（没有问题别执行），然后重新执行以上步骤。  reset slave all  表示重置主从配置信息。

mysql -uroot -S /usr/local/mysql/data/3308/mysql.sock -e "stop slave; reset slave all;"

好了，到这里我们已经搭建完毕。

原理

文件部分

从库文件（以下文件都默认放在数据文件目录下）：

主机名-relay-bin.000001      -- 默认叫这个名字，从主库接收到的binlog信息记录在这里

主机名-relay-bin.000002      -- 默认叫这个名字，从主库接收到的binlog信息记录在这里

主机名-relay-bin.index          -- 默认叫这个名字，从主库接收到的binlog信息记录在这里

master.info                            --从库的配置信息： CHANGE MASTER TO ... 的信息。

relay-log.info                         --存储接收的binlog

mysql> select @@master_info_repository;     -- 设置从库配置信息存放的方式：文件/表

mysql> select @@relay_log_info_repository;    -- relay-log存放方式：文件/表，默认文件

mysql> select @@relay_log_purge;      -- relay_log是否开启删除已经被使用过的relay_log

主库文件就是binlog 已经再之前文档讲过，这里不在说明https://www.cnblogs.com/jssj/p/13472394.html

线程：

主库下执行以下命令，查询线程：

show processlist;

图中可以看到两个 Binlog Dump的主从线程。 

从库线程：搭建的第八步已经给出。两个线程一个I/O,一个SQL。

主从复制的原理图：

额外补充： 主库有变化会通过从库，从库就会及时去主库获取新的变化，MySQL的主从复制是通过这种方式来保证实时性的。

参数

mysql> show slave hosts;     -- 查看主库中被连接的从库信息

mysql> show slave status G;    --查看从库信息

重点字段说明

change master to 的配置信息

Master_Host: 127.0.0.1                             
Master_User: repl                                      
Master_Port: 3307                                     
Connect_Retry: 10                                    
Master_Log_File: mysql-bin.000004         
Read_Master_Log_Pos: 154                    

Relay_Log_File的执行情况：

Relay_Log_File: iZm5e5v2zi93osbr5z21fvZ-relay-bin.000005      
Relay_Log_Pos: 367                                                                      
Relay_Master_Log_File: mysql-bin.000004

Exec_Master_Log_Pos: 154

从库线程状态：
Slave_IO_Running: Yes              -- no 或者 connecting 都表示不正常。  网络，端口，防火墙，用户密码 ，权限replication slave，连接数上限，版本不一致等。
Slave_SQL_Running: Yes         

mysql> select @@max_connections;      -- 最大连接数

从库线程报错信息：

Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制相关信息：

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

主从延时

Seconds_Behind_Master: 0     -- 单位为秒（主二进制文件的事件和从库获取二进制文件的事件差，所以这个时间并不能说明主从没有延时）

延时从库的配置信息：

SQL_Delay: 0                          -- 通过主从配置的时候设置的 MASTER_DELAY; 

SQL_Remaining_Delay: NULL

GTID相关复制信息：从库显示的就是主库的GTID 和从库执行到的GTID。

Retrieved_Gtid_Set:
Executed_Gtid_Set:

故障

主从不一致的时候，会出现从库SQL线程down掉。

先看参数： 报错内容

Last_SQL_Errno: 0
Last_SQL_Error:

处理方式一：

stop slave;                 --  停主从
set global sql_slave_skip_counter = 1;     -- 设置参数跳过本次主从同步操作。 
start slave;   

处理方式二：

从库反操作一下：比如从库中已经存在主键记录，先把主键记录删除，重启start slave; 让其再同步一次。

处理方式三（统一处理一下重复报错，不推荐）：

在MySQL参数配置文件：/etc/my.cnf 中设置

slave-skip-errors = 1032,1062,1007      -- 常见错误代码:1007:对象已存在；1032:无法执行DML；1062:主键冲突,或约束冲突

终极方式：重新搭建主从。

一般情况：MySQL主从搭建从库是不需要有其他的操作的，也是为了减少不必要的主从问题，所以会设置一个参数：

mysql> select @@read_only;     -- 1：只读，0：不是 （针对普通用户）

mysql> select @@super_read_only;   -- 1：只读，0：不是 （针对普通管理员用户）

主从延时

主从延时监控：

主库binlog执行到的位置点：

从库relaylog执行到的位置点，

是否存在差异，差异越大延时越严重。

主从延时原因：

1. 网络太慢。 

2. 硬件性能。

3. 主库业务繁忙。

4. 从库太多。

5. 5.6版本没有开启GTID（串行传输日志）。  -- 开启GTID，或者升级5.7 ，5.7默认支持并发传输日志。

6. 锁也会导致延时。

7.从库SQL线程串行执行，效率低，导致延时， 需要开启多个SQL线程来保证效率（必须开启GTID）.并且5.7 版本还有逻辑时钟保证并发执行。 MTS

恢复

1. 如果是物理损坏， 主从非常简单的就可以恢复数据。 直接将从库数据导出，导入主库， 或者直接将从库当成主库使用。 

stop slave;      -- 停掉主从服务
reset slave all;     -- 去掉主从配置

然后就可以临时当主库使用。

2. 如果数逻辑损坏，比如drop了数据库。

上面的情况下，我们就需要使用延时从库的功能了， 因为该从库是延时执行操作的， 故主库出现问题的时候从库是正常的。  所以可以通过从库恢复数据：

CHANGE MASTER TO MASTER_DELAY = 300;   -- 设置延时

1. 登录从库数据库停从库sql线程：

stop slave sql_thread

2. 查看relay.info 的位置点是否主库一致，表示日志文件已经同步：

3. 恢复从库：

截取relay_bin log位置点的起点

show slave status G;

截取relay_bin log位置点的终点

找到使用的relay-bin 文件，然后使用下面的命令找到终点

show relaylog events in 'iZm5e5v2zi93osbr5z21fvZ-relay-bin.000002'    -- 查看relaybinlog 文件

获取pos字段就可以了，

起点，终点都有了，然后截取命令：

mysqlbinlog --start-position=634 --stop-position=861  /usr/local/mysql/data/3309/data/iZm5e5v2zi93osbr5z21fvZ-relay-bin.000002 > /usr/local/mysql/relay.sql

恢复从库数据：

mysql> set sql_log_bin = 0;    -- 关闭binlog日志
mysql> source /usr/local/mysql/relay.sql;     -- 导入sql脚本（通过binlog截取出来的）
mysql> set sql_log_bin = 1;    -- 开启binlog日志

检查数据：

4. 恢复主库：

参考：https://www.cnblogs.com/jssj/p/13514597.html  的恢复章节。 导出从库数据库文件，导入主库。

5. 恢复主从：

从新设置主从：参考本文第一部分。

过滤主从

图中 数据库C 不需要主从复制。

1. 主库设置不同步，不产生binlog 即可（不推荐）

其中：binlog_Do_DB  是包含哪些库需要生成binlog日志;      binlog_ignore_DB 忽略掉一些数据库产品binlog日志

2. 从库设置SQL线程不执行不需要复制的数据库（推荐）。

[root@db01 ~]# vim my.cnf           -- 打开参数文件设置
replicate_do_db=test                -- 需要写入从库的数据库
replicate_do_db=test1               -- 需要写入从库的数据库

重启数据库实例生效。

扩展：

replicate_do_db=test                -- 需要写入从库的数据库
replicate_ignore_db=test1         -- 需要忽略写入从库的数据库

replicate_do_table=test.test      -- 需要写入从库的数据库的表
replicate_ignore_db=test1.test  -- 需要忽略写入从库的数据库的表

replicate_wild_do_db=test.t*         -- 需要写入从库的数据库的模糊的表
replicate_wild_ignore_db=test1.t*  -- 需要忽略写入从库的数据库模糊的表

半同步主从

在上面的主从复制的框架中有一个问题，就是主库不关心从库是否接收到数据，写入磁盘，容易出现从库和主库不一致的情况。

已经属于历史功能，基本已经不使用了。

原理：

1. 主库执行新的事务,commit时,更新 show master statusG ,触发一个信号给
2. binlog dump 接收到主库的 show master statusG信息,通知从库日志更新了
3. 从库IO线程请求新的二进制日志事件
4. 主库会通过dump线程传送新的日志事件,给从库IO线程
5. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程
6. ACK_receiver线程触发一个事件,告诉主库commit可以成功了
7. 如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  -- 主库加载插件
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;     -- 从库设置半同步

STOP SLAVE IO_THREAD;     -- 停止I/O线程
START SLAVE IO_THREAD;    -- 启动I/O线程

show status like 'Rpl_semi_sync_master_status';    -- 主库查看半同步状态
show status like 'Rpl_semi_sync_slave_status';      -- 从库查看半同步状态

GTID 主从复制（推荐）

搭建GTID主从要注意一点：MySQL的data文件需要清理之后，从新搭建。

my.cnf的配置文件如下：

cat > /usr/local/mysql/data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64
datadir=/usr/local/mysql/data/3307data
socket=/usr/local/mysql/data/3307/mysql.sock
log_error=/usr/local/mysql/data/3307/mysql.log
port=3307
server_id=7
log_bin=/usr/local/mysql/log/3307/mysql-bin
secure-file-priv=/tmp
binlog_format=row
autocommit=0
gtid-mode=on                  #开启GTID
enforce-gtid-consistency=true    #开启GTID
log-slave-updates=1          #主从一至
[mysql]
prompt=db01 [\d]>
EOF

重新初始化数据：

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64  --datadir=/usr/local/mysql/data/3307/data

启动实例：

mysqld --defaults-file=/usr/local/mysql/data/3309/my.cnf &

重新构建主从：主库

grant replication slave on *.* to repl@'%' identified by 'repl';

从库执行命令（和普通主从有区别）：

-- 主从配置信息
change master to 
master_host='127.0.0.1',
MASTER_PORT=3307,  
master_user='repl',
master_password='repl' ,
MASTER_AUTO_POSITION=1;     -- 主动获取主库的的位置点，根据从库的relay-bin.info去判断
-- 开启主从
start slave;

原理：

GTID 备份数据，然后复制到从库，从库启动后会自动判断到那些GTID被执行过，可以自动获取下一个GTID. 需要结合--set-gtid-purged。 默认自动开启。

总结

数据安全非常重要，所以数据库保证数据的安全是必须要实现的，这也是为什么会出现主从复制的原因，备份恢复操作比较麻烦，而且物理损坏修复也比较麻烦。主从演变到现在已经比较靠谱和完善了。