MySQL主从复制基础

主从复制（Replication）

一、主从介绍

1.介绍

两台或以上数据库实例，通过二进制日志，实现数据的“同步”关系

1. 基于二进制日志复制的
2.主库的修改操作会记录二进制日志
3.从库会请求新的二进制日志并回放,最终达到主从数据同步
4. 主从复制核心功能:
辅助备份,处理物理损坏                   
扩展新型的架构:高可用,高性能,分布式架构等

2.主从复制前提（搭建过程）

时间同步
至少2台以上实例，要有不同角色换分，server_id
主库开binlog
网络畅通
开启专用复制线程  
“补课”
确认复制起点

#总结
1.需要2台以上数据库实例，时间同步，网络畅通，Server_id不同，区分不同角色（主库，从库）
2.主库开启binlog，建立专用版复制用户
3.从库需要提前“补课”
4.从库：主库的链接信息，确认复制起点
5.从库：开启专用的复制线程

二、主从搭建（一台机器）

1.搭建

1.1实例准备

[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3307.cnf --user=root &
[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3308.cnf --user=root &
[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3309.cnf --user=root &

1.2检查关键信息

[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "show variables like 'server_id';"
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock -e "show variables like 'server_id';"
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock -e "show variables like 'server_id';"

1.3主库binlog

[root@db02 ~]# mysql -uroot -p -S /data/mysql/3307/data/mysql.sock -e "select @@log_bin";
Enter password: 
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

vim /data/mysql/conf/my.3307.conf
sync_binlog=1

1.4主库建立复制用户

[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"; 

[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "select user,host from mysql.user"
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

1.5主库备份恢复到从库

--主库备份
[root@db02 ~]# mysqldump -uroot -p -S /data/mysql/3307/data/mysql.sock -A --master-data=2 --single-transaction >/tmp/all.sql

--恢复到从库：
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock </tmp/all.sql

[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock </tmp/all.sql

1.6告知从库复制信息

--查看主库binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      154 |              |                  |                   |
    
mysql> help change master to

mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

--和上面参数对应
[root@db02 ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=444;

1.7 主库的链接信息，确认复制起点

[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
  
[root@db02 ~]# mysql -uroot -p -S /data/mysql/3309/data/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

1.8 从库：开启专用的复制线程（3308，3309）

mysql> start slave;

1.9 验证主从状态

--命令行查看线程状态（两个yes说明成功）：
[root@db02 ~]# mysql -u root -p123 -S /data/mysql/3308/data/mysql.sock -e "show slave status\G" | grep Running

[root@db02 ~]# mysql -u root -p123 -S /data/mysql/3309/data/mysql.sock -e "show slave status\G" | grep Running

1.10 如果搭建不成功，可以执行以下命令

从第一步开始重新操作（没有问题就不要执行）
清空主从环境
mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock -e "stop slave;reset slave all;"
mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock -e "stop slave;reset slave all;"

2.主从复制的原理

2.1 主从中涉及到的资源

1）文件

主库：
binlog文件
从库：
--relay-log文件：  
作用：存储接受的binlog，默认是在从库的数据目录下的
cd /data/3308/data
db02-relay-bin.000001
db02-relay-bin.000002

---手工定义的方法
mysql> show variables like '%relay%';
relay_log_basename  | /service/mysql/data/db02-relay-bin 
--master-info文件： 
作用：连接主库的信息，已经接收binlog位置点信息，默认存储在从库的数据路径下
---手工定义的方法
master_info_repository=FILE/TABLE
relay.info  :记录从库会放到relay-log的位置点 

2）线程

主库：
Binlog_dump_Thread:
--作用：用来接收从库请求，并且投递binlog
mysql> show processlist;

从库：
IO线程：请求日志，接收binlog日志
SQL线程：回放日志

2.2 主从复制原理画图说明

2.3 主从复制文字说明

1. S：Change master to IP，Port，USER，PASSWORD，binlog位置信息写入到M.info中
2. S：连接主库
3. M：分配Dump_T线程，专门和S_IO通信
show processlist;
4. S：IO线程：IO线程请求新日志
5. M：Dump_T 接收请求，截取日志，返回给S_IO
6. S：IO线程接收到binlog，日志存放在TCP/IP，此时，网络层面会返回一个ACK给主库Dump，主库工作完成
7. S：IO将binlog最终写入到replay中，并更新M.info
8. S：SQL线程读R.info，获取上次执行到的位置点
9. S：SQL线程向后执行新的relay-log，再次更新R.info
--小细节
1. S：realy-log 参数：realy_log_purge=ON，定期删除应用过的relay_log
2. M：Dump线程实时监控中主库的binlog变化，如果有新变化，发信号给从库

三、主从监控

1.主库方面

mysql> show processlist;
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         9 |      | 3309 |         7 | e72eed9b-9926-11eb-8dcc-000c296d3dea |
|         8 |      | 3308 |         7 | e278859b-9926-11eb-8c10-000c296d3dea |
+-----------+------+------+-----------+--------------------------------------+

2.从库方面

mysql> show  slave status \G

1. 主库相关信息：来自于M.info
Master_Host:10.0.0.52
Master_User：repl
Master_Port：3307
Connect_Retry：10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1025


2. 从库的relay-log的执行情况，来自于R.info，一般用来判断主从延时
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 901
Relay_Master_Log_File: mysql-bin.000001
Seconds_Behind_Master：0（延时主库多少秒）

3. 从库的线程状态及具体报错信息
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
Last_IO_Errno：0
Last_IO_Error：
Last_SQL_Errno：0
Last_SQL_Error：

4. 过滤复制相关信息
Replicate_Do_DB：
Replicate_Ignore_DB：
Replicate_Do_Table：
Replicate_Ignore_Table：
Replicate_Wild_Do_Table：
Replicate_Wild_Ignore_Table：

5. 延时从库（人为配置）的配置信息：
SQl_Delay：0
SQL_Remaining_Delay：NULL

6. GTID相关复制信息
Retrieved_Gtid_set:
Executed_Gtid_Set:

四、主从故障分析及处理

1.监控方法

mysql> show  slave status \G
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
Last_IO_Errno：0
Last_IO_Error：
Last_SQL_Errno：0
Last_SQL_Error：

2.IO线程

正常状态：
Slave_IO_Running:Yes

非正常状态：
Slave_IO_Running:NO
Slave_IO_Running:Connecting

1）IO线程故障原因

连接主库：
	（1）网络，防火墙
	（2）用户，密码，授权：replication slave
	（3）主库连接数上限：（最大连接数是151）
	mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
	（4）版本不统一 5.7 native 8.0 sha2

2）故障模拟

主从的线程管理：
mysql> start slave;               #启动所有线程
mysql> stop slave;                #关闭所有线程
mysql> start slave sql_thread;    #单独启动sql线程
mysql> start slave io_thread;     #单独启动IO线程

解除从库身份：
mysql> reset slave all;
mysql> show slave status \G

模拟故障
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

mysql> start slave;
mysql> show slave status \G
线程信息会显示NO

3）通用的故障处理思路

1.手动通过复制用户连接主库
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
mysql -urepl -p123 -h 10.0.0.52 -P 3307
报错：
1045       --->  用户或者密码错误
2003（113） ---> ip地址错误
2003（111） ---> 端口错误
1040       ---> 连接数上限

2. 故障修复
mysql> stop slave;
mysql> slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

4）请求日志，接收日志

1. 故障原因
主库二进制日志不完整：损坏，不连续...
从库请求的起点问题...
主从的server id （server_uuid）相同
relaylog 问题

2. 模拟故障
在主库中运行
mysql> reset master;
如果业务繁忙期间做，有可能会导致数据库夯住
如果要恢复主从，需要重新搭建主从
===============================
生产中如果要 reset master;
1. 找业务不繁忙的时间，停业务5分钟
2. 等待从库重放所有主库日志
3. 主库 reset master;
4. 从库重新同步主库日志
mysql> stop slave;
mysql> slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
    
mysql> start slave;
===============================
    
第三方工具：
pt工具

3.SQL线程故障

1）主要做什么工作？

回放relay-log中的日志，可以理解为执行relay-log SQL

2）SQL线程故障本质

为什么SQL线程执行不了SQL语句

原因整理：
创建的对象已经存在了
需要操作的对象不存在
约束冲突
以上问题：大几率出现在从库写入或者双主结构中容易出现
SQL_MODE，参数，版本

3）故障模拟

1. 先在从库建库
create database hhh;
2. 再去主库再建一个库
create database hhh;
3. 检查从库SQL线程状态
Slqve_SQL_Running：NO
会报错：

4）处理故障

（1）思路1：一切以主库为准，将从库进行反操作一下，重启线程
mysql> drop database hhh;
mysql> start slave;
（2）思路2：以从库为准，跳过此次复制错误
方法一：
mysql> stop slave; 
mysql> set global sql_slave_skip_counter = 1;
注意：如果要用此种方法，一定要保证此时故障数据是完全一致时，才能采用

第三方工具：
pt工具：帮助我们检查主从数据一致，并可以修复主从不一致情况
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
mysql> start slave;

方法二：暴力方法，遇到自动跳过
vim /etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

（3）思路3：重新搭建主从：备份恢复+重新构建

扩展

1. 从库只读
mysql> select @@read_only;         #普通用户只读
mysql> select @@super_read_only;   #普通管理员只读

2. 中间件

4.什么是主从延时？

主库发生了操作，从库很久才跟上来

5.关于主从延时怎么监控？

--粗略评估
mysql> show slave status;
Seconds_Behind_Master：0     #从库落后于主库的时间

--准确评估
日志量：主从binlog位置点：从relay执行的位置点
---主库查看
mysql> show master status;
+------------------+----------+--------------+-------------
| File             | Position | Binlog_Do_DB | Binlog_Ignor
+------------------+----------+--------------+-------------
| mysql-bin.000001 |     1329 |              |             
+------------------+----------+--------------+------------

--从库查看
ll /data/3308/data/relay.log.info
7                                   #来自于哪个主库
./db02-relay-bin.000002
1205
mysql-bin.000001
1329
0
0
1

可以得出：有或者没有延时的情况，等于0不代表没有延时
评估主从延时更加精确的指标是，延时了多少日志量
主库执行的日志量，从库执行的日志的对比

6.如何计算延时的日志量

show master status;
cat /data/3308/data/relay.log.info

7. 主从复制延时的原因

1）主库

1. 外部：网络，硬件配置，主库业务繁忙，从库太多
--主库业务繁忙
1）拆分业务（分布式）：组件分离，垂直，水平
2）大事务的拆分：比如，1000W业务，拆分为20次执行

2. 内部
1）二进制日志更新
sync_binlog=1
2）5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行的，所以会导致，事务量，大事务时会出现比较严重延时
--解决方案：
5.6+版本，出现了gtid，事务在主从的全局范围内就有了唯一性标志
5.7+版本，就可以实现并发传输binlog
但是，即使有这么多的优秀特性，我们依然需要尽可能的减少大事务，以及锁影响

ps：怎么判断是主库传输不及时

1.Seconds_behind_master
2.主库：show master status; show slave status \G

2）从库

1. 外部
	网络，从库配置低，参数设定
2. 内部
	IO线程：写relay-log ---> IO性能
	SQL线程：SQL默认在非GTID模式下是串行的
	解决方案：
		1）开启GTID
		2）串行改并行
			5.6+GTID：database级别，基于库级别SQL线程并发
			5.7+GTID：Logic_clock 逻辑时钟。保证了同库级别下的事务顺序问题，所以可以理解为基于事务级别的并发回放，MTS
	
即使有以上的自带的优化机制，我们还是要注意对于大事务的处理的问题，锁的问题

优化数据库的根本就是优化人，大事务和锁都是需要我们额外关注的

五、主从复制搭建实战（多台机器）

1.主库操作:

给主库个密码 mysqladmin -uroot password '123'

2.创建主从复制用户

#登录数据库
[root@db01 ~]# mysql -uroot -p123
#创建rep用户作为从库连接用的用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';
	这里必须 *.* 不能指定库授权，因为 replication slave 是全局的

3）查看主库binlog信息

mysql> show master status;

2. 从库操作:

1）修改配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
#主库server-id为1，从库不等于1
server_id=2

#重启数据库
[root@db02 ~]# /etc/init.d/mysqld restart

2）配置主从同步

#登陆数据库
[root@db02 ~]# mysql -uroot -poldboy123

#执行change master to 语句
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=1357;

#开启IO和SQL线程
start slave;

3.添加数据库主从（数据库运行中做主从）

1.准备纯净的从库环境
2.主库全备数据
	#如果不打点备份
	mysqldump -uroot -p123 -A -R --triggers > /backup/full.sql
	#打点备份
	mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction > /backup/full.sql
3.将热备数据传达从库
	scp /backup/full.sql 172.16.1.52:/tmp
4.模拟主库运行，有数据写入
	insert into .....
5.导入全备数据
	#导入不打点的数据的话，接下来主从，位置点只能去主库查看 show master status; 这个是不准确的
	#如果导入的是打点的数据，那么接下来主从的同步位置点可以从全被sql里面找到，是准确的
6.查看sql文件中的位置点（如果是打点备份的话）
	[root@db02 scripts]# head -50 /tmp/dadian.sql|grep 'MASTER_LOG_POS'
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1544;
7.从库配置主从
mysql> change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1357;
8.开启SQL线程和IO线程
	start slaves;
9.查看主从状态
	show slave status;

4.验证多个从库server_id相同，主从是否有问题

1.配置第二个从库
	[root@db01 ~]# vim /etc/my.cnf
	[mysqld]
	server_id=2
2.启动数据库
	systemctl start mysql
3.将主库的全备发送到从库
	scp /tmp/full.sql root@172.16.1.51:/tmp	
4.在从库执行sql文件
	mysql < /tmp/full.sql
5.查看sql文件中的位置点
	[root@db02 scripts]# head -50 /tmp/dadian.sql|grep 'MASTER_LOG_POS'
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1544;
6.在从库上执行change master
	change master to
	master_host='172.16.1.50',
	master_user='rep',
	master_password='123',
	master_log_file='mysql-bin.000001',
	master_log_pos=2100;
7.开启SQL线程和IO线程
	start slave;
8.查看主从复制的状态
	show slave status\G

问题

1.主库出了问题怎么办？

物理方面：
1.看主库是否能ssh上
2.检查一下binlog是否完整
3.手工追加日志到最新位置
4.从库替代主库工作

逻辑方面：drop
只能通过备份去恢复

2.从库怎么称当主库

1.修复最新状态
2.取消从库身份
3.清空binlog日志信息