mysql 主从复制

一、mysql复制原理

• 从库的I/O thread 线程会读取master info 文件 获取主库的 user,password port信息然后还会获取上次获取主库二进制日志的位置 如3640 就是00003这个文件640这个位置，主库收到从库的请求后，会验证用户名密码等的合法性，然后问主库你有没有比上次00003文件640这个位置更加新的二进制日志，然后主库就会查看自己的binglog日志，如果发现比640这个新，如已经到达31080这个位置了，主库就会把00003号文件的640这个位置到1080这个位置的binglog日志截断，通过dump(i/o)线程返回给从库的I/O thread线程，到从库之后，它会先存到tcp/ip缓存当中(tcp/ip cached)，然后从库会立即发送一个ack给主库，主库收到ack后就认为这个过程已经完成，就可以去干别的事情了，此时从库会更新mast info的信息，把binglog的位置信息更新到1080，下次就从1080开始往下找，然后再把tcp/ip缓存的日志写入到relaylog当中，最后sql thread线程会读取relay-log.info,获取到上次执行binglog日志的位置信息，比如发现上次以及执行到640这个位置了，sql就会读取relaylog从640的位置开始执行二进制日志，当执行完后，最后更新relay-log.info文件，记录最后执行的位置，最后，relaylog会自动把已经执行过的二进制日志清理掉这样一次复制就完成了

• 复制中的线程及文件
  2.1、主库
  Dump（IO） thread：在复制过程中，主库发送二进制日志的线程
  2.2、从库
  IO thread：向主库请求二进制日志，并且接受二进制日志的线程
  SQL thread：执行请求过来的二进制的线程
  2.3、主库
  binlog文件：主库的二进制日志
  2.4、从库
  relaylog:中继日志，存储请求过来的二进制日志
  master.info:
  1、从库连接主库的重要参数（user,passwd,ip,port）
  2、上次获取过的主库二进制日志的位置
  relay-log.info
  存储从库SQL线程已经执行过的relaylog日志位置

  二、mysql 主从搭建

• 主从复制前提

  1、两台以上MySQL实例（可以是多台物理机，也可是mysql实例）
  2、主库要开启二进制日志
  3、主库要提供复制相关的用户需要用到 replication slave一个比较特殊的权限
  4、从库需要将和主库相差的数据进行追加，一般情况下认为备份数据库，恢复到从库上
  5、从库应该从恢复后的时间点开始自动从主库获取二进制日志开始自动同步主库数据，我们需要告诉从库，从哪儿开始复制二进制日志进行学习

• 主从复制搭建实战
  1、环境准备
  两个MySQL实例
  3307：master
  3308：slave
  2、开启主库binlog,从库开启relay-log(默认在数据目录下生成)
  vim /data/3307/my.cnf
  log-bin=/data/3307/mysql-bin
  binlog_format=row
  3、server-id不同
  [root@db02 data]# cat /data/3307/my.cnf |grep server-id
  server-id=3307
  [root@db02 data]# cat /data/3308/my.cnf |grep server-id
  server-id=3308
  4、关闭数据库自动域名解析（没个节点实例都加）
  skip-name-resolve
  5、启动多实例
  mysqld_safe --defaults-file=/data/3307/my.cnf &
  mysqld_safe --defaults-file=/data/3308/my.cnf &
  6、主库创建复制账户连接到主库
  mysql -S /data/3307/mysql.sock
  grant replication slave on . to repl@'10.0.0.%' identified by '123';
  7、从库数据追加

（1）不需要追加的情况
主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog(mysql-bin.000001)开头位置（120）

（2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制

mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql
sed -n '22p' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325
恢复到从库：
mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql
8、从库开启主库
mysql -S /data/3308/mysql.sock

help change master to

CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=325;
开启主从（开启IO/SOL线程）
start slave
9、查看主从状态
show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主从重要状态信息介绍

show slave status\G
Slave_IO_Running: Yes（io线程状态）
Slave_SQL_Running: Yes（sql线程状态）
Last_IO_Errno: 0（io线程异常状态码）
Last_IO_Error: （io线程异常详细信息）
Last_SQL_Errno: 0（sql线程状态码）
Last_SQL_Error: （sql线程异常详细信息）

三 、 主从复制常见异常解决思路

• IO线程故障
  1、主库连接不上
  检查user，password，port，IP，网络，防护墙
  stop slave;
  reset slave all;(清空配置)
  chagen master to
  start slave

2、主库二进制文件丢失或损坏
解决方案；
stop slave;
reset slave all;
从新备份恢复
change master to
start slave;

• SQL线程故障
  执行relaylog日志新新的事件
  1、删除，修改对象的操作时，没有这个对象
  2、创建对象时，对象已存在
  3、主键冲突
  从库做写入操作，会导致以上问题出现

处理方法跳过这个错误
stop slave;
set global sql_slave_skip_counter=1;
start slave;
/etc/my.cnf
slave-skip-errors=1032,1062,1007
但是，以上操作有的时候时候是有风险的，最安全的方法是从新构建新的主从
如何预防？
修改从库为只读库
set global read_only=1;
vim /etc/my.cnf
read_only=1(这个参数只能控制普通用户)

• 主从延时过长
  show slave status \G
  Seconds_Behind_Master:0

默认的主从复制是异步的过程

主库原因
1、主库做修改操作之后，才会记录二进制日志
2、主库的压力特别大（大事务，多事物）
3、从库数量多，导致domp线程繁忙
从库原因：
1、relay-log写入慢
2、sql线程慢（主从硬件差异较大）
解决思路
主库
1、sync_binlog=1(1表示只要主库做了一次commit，二进制日志就会立刻刷新到磁盘，如果等于0要根据系统binlog决定)
2、大事物拆分成小事物，多事物进行分离
3、使用多级主从，分库分表架构
4、将binlog放在ssd或者flash上，高性能存储
从库
1、将relay放到ssd或者flash上
2、尽量选择和主库一样的硬件配置

四、主从复制其他特性

• 半同步复制
  1、出现原因
  保证数据一致性问题，安全考虑
  默认情况下MySQL主从备份是通过异步的方式进行备份，当从库向主库请求二进制日志，从库会把请求过来的二进制日志存到tcp/ip缓存当中，返回一个ack告诉主库接收到了二进制日志，试想一下当这个时候从库突然断电了，存到tcp/ip缓存当中的二进制日志就会丢失，而主库还也以为从库正常接收到了二进制日志，从库的数据就会丢失，半同步的实现方法就是当从库接收到二进制日志后必须要存到relaylog落地到磁盘当中从库才会发送ack给主库，这样就保证了数据的一致性
  半同步复制是在5.5出现的概念，但5.5的半同步复制性能太差不建议使用，在5.6以后出现group commit 组提交功能，来提升开启版同步复制的性能，5.7 增强半同步复制的新特性：after sync;
• 具体实现

加载插件

主:
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;

重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';

补充：
rpl_semi_sync_master_timeout | 10000
默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制
如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。

• 从库延时
  防止数据库的逻辑损坏，假设在主库不小心误删除了一个表，这个事件会同样记录到二进制日志当中发送到从库，这时候从库不会立即执行这个 操作，比如配置了延时3小时，从库会在3小时后才执行删除表的操作，这样就给我们运维人员一些缓冲的时间，延时从库是对sql线程的延时
  会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份

• 配置实现
  SQL_thread的延时

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 60;

mysql>start slave;

mysql> show slave status \G
SQL_Delay: 300

取消延时：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

• 复制过滤
  出现原因，有得时候我们不想复制主库的所有数据，只想复制一个库或者一个库某些表
  主库方面控制（不建议使用）：
  白名单:只记录白名单中列出的库的二进制日志
  binlog-do-db
  黑名单：不记录黑名单列出的库的二进制日志
  binlog-ignore-db

从库方面控制：
白名单：只执行白名单中列出的库或者表的中继日志

--replicate-do-db=test （哪个库）
--replicate-do-table=test.t1 （哪个库的哪个表）
--replicate-wild-do-table=test.x* （模糊的匹配）

黑名单：不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table

只复制world数据库的数据
在从库配置
vi /etc/my.cnf
replicate-do-db=world
重启从库

查询：show slave status \G
replicate-do-db = world

• 主从复制新模式-GTID复制

• GTID是从5.6之后新的复制特性，以前的复制模式是当主库发生了任何方式的变化，会已事件的形式记录到binlog日志当中，每个事件生成一个position号，然后从库去获取这些二进制事件，而GTID是把每个完整的事物单独的生成一个全局唯一的编号，这样就简化了主从复制的配置，实现过程相对也简单写
  它的官方定义如下：
  GTID = source_id ：transaction_id
  7E11FA47-31CA-19E1-9E56-C43AA21293967:29
  前半段为UUID，后半段为事物的编号，存放在auto.cnf下

• 配置实现
  1、重要参数
  gtid-mode=on （是否开启GTID）
  enforce-gtid-consistency=true （强制GTID的一致性）
  log-slave-updates=1 (slave更新是否写入日志)
  2、规划
  主库： 10.0.0.51/24
  从库1: 10.0.0.52/24
  从库2：10.0.0.53/24
  3、写入配置文件
  主库：
  加入以下配置信息

db01:10.0.0.51/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave1：
db02:10.0.0.52/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave2：
db02:10.0.0.53/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

三台节点分别初始化数据：

/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/

分别启动三个节点mysql：

/etc/init.d/mysqld start

测试启动情况：
mysql -e "show variables like 'server_id'"

master:51
slave:52,53

51:
grant replication slave on . to repl@'10.0.0.%' identified by '123';

52\53:

change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;

start slave;