MySQL 主从复制

1- 主从复制介绍

(1) 主从复制基于binlog来实现的
(2) 主库发生新的操作,都会记录binlog
(3) 从库取得主库的binlog进行回放
(4) 主从复制的过程是异步

2- 主从复制的前提 (搭建主从复制)
(1) 2个或以上的数据库实例
(2) 主库需要开启二进制日志
(3) server_id要不同,区分不同的节点
(4) 主库需要建立专用的复制用户 (replication slave)
(5) 从库应该通过备份主库,恢复的方法进行"补课"
(6) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
(7) 从库应该开启专门的复制线程

环境准备（多实例）---参考多实例搭建
配置文件需要开启二进制日志
以3307为例：

公司配置文件
[mysqld]
user=mysql
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=41
port=3306
log-bin=/data/mysql/mybinlog
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
max_connections=100000
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
#skip-grant-tables
#不区分大小写
lower_case_table_names=1
[mysql]
socket=/tmp/mysql.sock
prompt=db01 [\\d]>
[mysqldump]
socket=/tmp/mysql.sock

 

?

1 2 3 4 5 6 7 8 9

[mysqld] basedir=/application/mysql datadir=/data/mysql3307/data socket=/data/mysql3307/mysql.sock log_error=/data/mysql3307/mysql_error.log server_id=7 log_bin=/data/binlog3307/mysql-bin binlog_format=row port=3307

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

主库创建复制用户 mysql -uroot -p123 -S /data/mysql3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"   主库进行全备 mysqldump -uroot -p123 -S /data/mysql3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql   从库导入主库的全备数据 mysql -S /data/mysql3308/mysql.sock mysql> set sql_log_bin=0; mysql> source /tmp/full.sql     在从库上执行：告诉从库信息 help change master to [root@db01 ~]# mysql -S /data/3308/mysql.sock   CHANGE MASTER TO MASTER_HOST='主机地址', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_PORT=端口号, MASTER_LOG_FILE='二进制日志名', show master status; 查看二进制日志名和备份位置编号 MASTER_LOG_POS=备份位置, MASTER_CONNECT_RETRY=10;

?

1 2 3 4 5 6 7 8 9 10 11 12

CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10;   从库开启复制线程(IO,SQL) [root@db01 ~]# mysql -S /data/3308/mysql.sock mysql> start slave;

?

1 2 3 4 5 6 7 8 9

检查主从复制状态 [root@db01 ~]# mysql -S /data/3308/mysql.sock mysql> show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes   测试主从复制 主库: mysql -uroot -p123 -S /data/3307/mysql.sock -e "create database test" 从库: mysql -S /data/3308/mysql.sock -e "show databases"

2- 主从复制原理 *****

主库:
binlog
从库:
relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog应用的信息

----- 主从复制中涉及的线程
　　主库:
　　Binlog_Dump Thread : DUMP_T
　　从库:
　　SLAVE_IO_THREAD : IO_T
　　SLAVE_SQL_THREAD : SQL_T

----- 主从复制监控 ****

命令:
show slave status \G

主库有关的信息(master.info):
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
*******************************
和主库的show master status状态位置
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 609
*******************************

从库relay应用信息有关的(relay.info):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004

从库线程运行状态(排错)
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

 

---   主从复制故障解决步骤：
1. stop slave;
2. reset slave all;
3. help change master to
4. start slave;

遇到的问题

Slave_IO_Running: 为no或者为Connecting
Slave_SQL_Running: Yes

处理方法是：检测是否能远程到数据库上，和help change master to 上二进制文件和log编号是否与master主库是否一致
用show master status; 可以查看，无法远程到数据库一般是权限问题

问题
该IP下连接数据库报错（其他机器可以正常连接）
Host '192.168.xx.xx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

原因
一般是 max_connect_errors = 10 报错 ，要调高错误连接的数量

远程端：mysql -uroot -p
show variables like  'max_connect_errors';
set global max_connect_errors=1000;
flush hosts; 

 问题2

mysql主从不同步，提示Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction

?

1 2 3

mysql> show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: No

　

 

 

 根据提示查看对应表： select * from performance_schema.replication_applier_status_by_worker\G

 

 

 mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql_log/binlog/mysql-bin.000392 | grep -A 20 “332866446” --color

从图上看数据更新导致主从数据不一致导致线程同步失败，查看数据每30秒更新一次的定时任务select * from  `xxl_job`.`xxl_job_registry` where id=111；

 

 

 

 解决方案 把定时任务停止后，把主库数据同步到从库保持数据一致性，重新启动主从

?

1 2 3 4 5 6 7 8 9

stop slave ;   CHANGE MASTER TO   MASTER_HOST='172.16.179.23',   MASTER_USER='repl_user',   MASTER_PASSWORD='123456',   MASTER_PORT=3306,  <strong> MASTER_LOG_FILE='mysql-bin.000392', 保持和主库一致   MASTER_LOG_POS=765480074,          </strong><em id="__mceDel"><strong>保持和主库一致</strong></em>  MASTER_CONNECT_RETRY=10; start slave ;

　　

合理处理方法:
把握一个原则,一切以主库为准进行解决.如果出现问题,尽量进行反操作，最直接稳妥办法,重新构建主从

为了很程度的避免SQL线程故障
(1) 从库只读
read_only
super_read_only
(2) 使用读写分离中间件
atlas
mycat
ProxySQL
MaxScale

3- 主从延时监控及原因 *****

--- 主库方面原因
(1) binlog写入不及时
sync_binlog=1
(2) 默认情况下dump_t 是串行传输binlog *****
在并发事务量大时或者大事务,由于dump_t 是串型工作的,导致传送日志较慢
如何解决问题?
必须GTID,使用Group commit方式.可以支持DUMP_T并行，开启双一标准
(3) 主库极其繁忙
慢语句
锁等待
从库个数
网络延时
--- 从库方面原因
(1) 传统复制(Classic)中 *****
如果主库并发事务量很大,或者出现大事务
由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务.
5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database)
5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),MTS技术
(2) 主从硬件差异太大
(3) 主从的参数配置
(4) 从库和主库的索引不一致
(5) 版本有差异

4- 主从延时的监控

show slave status\G
Seconds_Behind_Master: 0

主库方面原因的监控

主库:
mysql> show master status ;
File: mysql-bin.000001
Position: 1373

从库
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1373

从库方面原因监控:

拿了多少:
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 691688
执行了多少:
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 690635
Exec_Master_Log_Pos: 691000
Relay_Log_Space: 690635