1、主从复制准备
1.1 前提
- 准备2个以上地mysql实例，一主一从。
- 每台实例server_id不同
- 主库方面创建专用地复制用户
- 主库必须开启二进制日志
- 从库需要初始化数据，保证和主库数据在一个时间点上一致
1.2 准备
3307配置文件添加binlog配置
vim /data/3307/my.cnf
log_bin=/data/3307/data/mysql-bin
binlog_format=row启动多实例
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3309/my.cnf &验证
netstat -lnp|grep 330主库创建复制用户
mysql -uroot -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';全备主库数据，恢复到从库
mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql查看binlog文件和位置点
vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=325;全备导入从库
mysql -uroot -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/full.sql开启主从复制
mysql -uroot -S /data/3308/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.51', ---->主库IP
MASTER_USER='repl', ---->主库复制用户
MASTER_PASSWORD='123', ---->主库复制用户密码
MASTER_PORT=3307, ----->主库端口
MASTER_LOG_FILE='master2-bin.000001', ----->需要复制的二进制日志名
MASTER_LOG_POS=325, ---->需要复制的二进制日志复制起点
MASTER_CONNECT_RETRY=10; ---->重连重试次数
启动复制线程
start slave;
查看主从状态
show slave status\G
slave_IO_Running：yes
slave_SQL_Running：yes
2、主从复制介绍
2.1 什么是主从复制？
基于主库二进制日志实时恢复到从库
2.2 主从复制存在的原因
- 辅助备份：解决物理损坏
- 演变高可用架构：在主库发生故障时，自动进行故障转移，对应用透明
2.3 主从复制原理
- 主从复制前提
- 多台节点
- server_id要不同
- 主库开启binlog
- 主库提供专门的复制用户
- 主库备份导入从库，跟上主库的数据位置
- 通知从库：复制用户、密码、IP、port、复制文件和起点
- 开启复制
- 文件
- Master：
binlog：记录主库的数据变化 - Slave：
relaylog：中继日志，存储从主库请求过来的二进制日志的存储位置
master.info：存储用户，密码，IP，port，记录上次请求过的binlog位置
relay-log.info：记录了上次SQL线程执行过的relaylog的位置点
- 线程：
- Master：
dump线程：主库发送二进制日志给从库的线程 - Slave：
IO线程：请求binlog，并接收binlog的线程
SQL线程：执行relaylog日志的线程
主从复制原理
- 从库IO线程，查看master.info信息，获取IP，port，user，password，file，position位置点
- 通过IP，port，user，password，连接到主库
- 拿着 file（mysql-bin.000003），pos(120)，请求主库
- 主库判断如果有新的binlog(mysql-bin.000003，800)，通过Dump线程从3号文件的120开始发送二进制日志事件
- 从库IO线程，接收binlog日志
- 接收到的binlog日志存到TCP/IP缓存
- IO线程回复一个ACK确认给dump线程，主库收到后，主库此次复制工作就完成了
- IO线程通知更新master.info文件，file，position被更新为最新请求的值
- 同时TCP/IP的缓存数据，写入relay-log中
- SQL线程，读取relay-log.info，获取到上次已经执行过的位置信息
- SQL执行最新的relay-log日志内容，数据重做
- 再次更新relay-log.info
- 已经复制过的relay-log，会被自动清理
简单来说就是三个步骤：
1、master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件(binary log events);
2、slave将master 的binary log events 拷贝到它的中继日志( relay log);
3、slave重做中继日志中的事件，将改变应用到自己的数据库中。mysql复制是异步的且串行化的
2.4 主从复制监控
主库：
show master status; //主库和从库的文件和位置信息应该是一致
show processlist;从库：
show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: master-bin.000004 //主库的binlog日志文件
Read_Master_Log_Pos: 918 //binlog日志文件位置点
Relay_Log_File: db01-relay-bin.000001 //relay log 文件
Relay_Log_Pos: 4 //relay log 位置点
Relay_Master_Log_File: master-bin.000004 //relaylog 日志文件对应的主库的binlog文件名
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
启动关闭线程
stop slave;
start slave;
stop slave io_thread; //单独关闭IO线程
stop slave sql_thread; //单独关闭sql线程监控线程具体报错信息
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
2.5 主从复制故障
- IO线程故障
- 连接master故障
user，password，IP，port，网络不通，防火墙，master没启动，master连接数到达上限，反向解析(skip-name-resolve)
# 用户密码错误，尝试登录主库
mysql -urepl -p123 -h 10.0.0.51 -P3307
# 解决方法：
show slave status\G //查看故障点的binlog位置
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 918
# 停止复制
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.000004',
MASTER_LOG_POS=918, //从故障时间点的位置开始复制主库
MASTER_CONNECT_RETRY=10;
start slave;
show slave status\G
- 接收binlog故障
binlog日志文件找不到、损坏、与主库断节
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
reset master //二进制日志从头开始。禁用
解决方法：
# 从库停库
stop slave;
reset slave all;
# 使用备份恢复，重新初始化数据；如果主库数据没有变化，则不用
set sql_log_bin=0;
source /tmp/full.sql
# 重新复制
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=120,
MASTER_CONNECT_RETRY=10;
start slave;
-
SQL线程故障
（1）读写relay log.info
（2）relay log 损坏，断节，找不到
（3）接收到的SQL无法执行；根本原因是从库有写入操作，从库所有的数据都应该来自于主库。
解决方法：
1、写入操作撤回
2、跳过错误的方法是有风险的，最安全的方法重新构建主从。
3、彻底解决方法是将从库设置为只读库
从库设置只读权限
# 会话级别设置：
set global read_only=1;
# 永久设置：
vim /etc/my.cnf
[mysqld]
read_only=1
# 以上两条只对普通用户生效
# 设置管理员只读权限
vim /etc/my.cnf
[mysqld]
innodb_read_only=1
会话级别无法设置，只能重启生效
- 主从复制延时过高
定义：主库做了一个操作，从库很久都没有追上
- 原因1 主库写binlog不及时
解决方法：控制binlog从内存写入到磁盘的控制参数：
每次事务提交会立即刷新binlog到磁盘（双一标准的其中一个）
sync_binlog=1每次事务提交不立即写入磁盘，靠操作系统判断什么时候写入
sync_binlog=0
原因2 dump线程压力大
原因3 IO线程阻塞
大事务------->拆成多个小事务
事务量大------->group commit，5.6以后的参数，可以同时提交原因4 SQL线程慢
- 默认只有一个SQL线程，从库中的事务都是一个一个来执行的
- 如果主库的并发事务数很多，大事务，都会造成从库延时
5.6 多线程复制（多SQL线程），有局限性，针对不同库的事务进行并发；
大事务只能在主库拆成小事务。
- 查看从库延时时间
show slave status\G
Seconds_Behind_Master：8888
