MySQL主从复制
主从复制介绍
1、主从复制基于binlog来实现的
2、主库发生新的操作，都会记录binlog
3、从库取得主库的binlog进行回放
4、主从的过程时异步
主从复制的搭建
主从复制搭建前提
1、2个或以上的数据库实例
2、主库要开启二进制日志
3、server_id要不同，区分不同的节点
4、主库需要建立专用的复制用户
5、从库通过备份主库，恢复主库的部分数据
6、人为告诉从库一些复制信息（ip port user passwd，二进制日志起点）
7、从库开启专门的复制线程
搭建
- 准备多实例
[root@test01 3307]# cd /data01/3307/data/
[root@test01 data]# ll
total 122920
-rw-r-----. 1 mysql mysql 56 Dec 1 20:52 auto.cnf
-rw-r-----. 1 mysql mysql 356 Dec 1 21:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 6 20:53 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Dec 6 20:53 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Dec 1 20:52 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Dec 6 20:53 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Dec 1 20:52 mysql
drwxr-x---. 2 mysql mysql 8192 Dec 1 20:52 performance_schema
drwxr-x---. 2 mysql mysql 8192 Dec 1 20:52 sys
-rw-r-----. 1 mysql mysql 6 Dec 6 20:53 test01.pid
[root@test01 data]# rm -rf *
[root@test01 data]# cd ..
[root@test01 3307]# ls
data my.cnf mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.log mysql.sock mysql.sock.lock
[root@test01 3307]# rm -rf mysql-bin.*
[root@test01 3307]# mysqld --initialize-insecure --user=mysql --basedir=/aplication/mysql --datadir=/data01/3307/data
[root@test01 3307]# systemctl start mysqld3307
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
[root@test01 3307]# mysql -S /data01/3307/mysql.sock -e "select @@port"
+--------+
| @@port |
+--------+
| 3307 |
+--------+
- 检查配置文件
二进制日志是否开启
两个节点的server_id
- 主库创建复制用户
[root@test01 3307]# mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to repl@'192.168.184.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| repl | 192.168.184.% |
| root | 192.168.184.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
5 rows in set (0.00 sec)
- 备份主库并恢复到从库
#主库备份数据
[root@test01 3307]# mysqldump -uroot -p1 -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql
#从库导入数据
```bash
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/full.sql
- 告知从库关键复制信息
登入从库
help change master to --查看change master to用法
CHANGE MASTER TO
MASTER_HOST='192.168.184.128', --主库主机名
MASTER_USER='repl', --主库用户名
MASTER_PASSWORD='123', --主库用户名的密码
MASTER_PORT=3306, --主库端口
MASTER_LOG_FILE='master2-bin.004',
MASTER_LOG_POS=449,
--MASTER_LOG_FILE和MASTER_LOG_POS从备份文件中的22行就可以看见
--例如：
--[root@test01 3307]# sed -n '22p' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000004', MASTER_LOG_POS=449;
MASTER_CONNECT_RETRY=10; --主从断开后10次重连
实际操作：
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.184.128',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.004',
-> MASTER_LOG_POS=449,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
#开启复制线程（IO，SQL）
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
6、检查主从复制状态
db01 [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: --详细错误信息
主从复制原理
主从复制中涉及的文件
主库
binlog
从库
relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog应用的信息
主从复制中涉及的线程
主库：
binlog_dump Thread：DUMP_T
从库：
SLAVE_IO_THREAD：IO_T
SLAVE_SQL_THREAD：SQL_T
主从复制工作原理
主从复制监控
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
##########################主库有关信息(master.info)###########
Master_Host: 192.168.184.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql_bin.000005 #主库的binlog信息
Read_Master_Log_Pos: 154 #主库的binlog信息
###############从库relay应用信息有关的(relay.info)############
Relay_Log_File: test01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000005
########################从库线程运行状态（排错）##############
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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
SQL_Remaining_Delay: NULL
######################GTID复制有关的状态信息################
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
主从复制故障
从库：
-
IO_T线程故障
- 连接主库
网络，连接信息错误或变更了，防火墙，连接数上限
排查方案：
1、使用复制用户手动登录主数据库
解决：
1、stop slave
2、reset slave all；
3、重新change master to更新信息
4、start slave- 请求binlog
主库没开binlog、binlog损坏，不存在、主库执行reset master
主库执行reset master的解决方法：
主库查看binlogr日志的时间点，执行show master status;
从库执行：
1、stop slave
2、reset slave all；
3、重新change master to更新信息
4、start slave- 存储binlog到relaylog
查看relaylog权限等问题
重启主从，stop slave、start slave -
SQL_T线程故障
- relay-log损坏
回放relaylog
主要是SQL执行失败导致
场景模拟：在从库建立了test数据库，后来又在主库建了test数据库，在主库的test库中建表，发现从库没得表
问题描述：在从库同步主库的命令的时，发现测试表已经存在
解决方法：
删除从库的测试表
把握一个原则，尽量已主库为准‘，进行反操作
或者重新构建主从
主从延时监控及原因
主库方面原因
- binlog写入不及时
sync_binlog=1 优化参数，1秒就写入binlog日志
- 默认情况下dump_t是串行传输binlog
在并发事务量大，由于dump_t是串行工作的，导致传送日志慢
解决方案：
必须GTID，使用Group commit方式，可以支持DUMP_T并行
从库方面原因
- 传统复制
如果主库并发事务量很大，或者出现大事务，由于从库是单SQL线程，导致不管传的日志有多少，只能一次执行一个事务
5.7版本中有了增强的GTID，增加了新型的并发SQL线程模式（logical_clock）,MTS技术
- 主从硬件差异大
- 主从的参数配置
- 主从和从库索引不一致
- 版本差异
主从延时的监控
主库方面
主库：
mysql> show master status \G
*************************** 1. row ***************************
File: mysql_bin.000005
Position: 154
从库
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
从库方面
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
#拿了多少
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
#执行了多少
Relay_Log_File: test01-relay-bin.000002
Relay_Log_Pos: 320
[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave status \G"
*************************** 1. row ***************************
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
#对比Exec_Master_Log_Pos和 Read_Master_Log_Pos看执行了多少语句，来判断是否延时没拍成看是否是SQL_T的延时