MySQL主从复制
主从复制简介

1：高可用
2：辅助备份
3：分担负载
复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。
1：主服务器将所有数据和结构更改记录到二进制日志中。
2：从属服务器从主服务器请求该二进制日志并在本地应用其内容。
3：IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog
4：SQL：从relaylog中将sql语句翻译给从库执行
二.主从复制原理
主从复制的前提
1：两台或两台以上的数据库实例
2：主库要开启二进制日志
3：主库要有复制用户
4：主库的server_id和从库不同
5：从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置：
6：从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
IP：10.0.0.51
Port：3306
User：rep
Password：qmf123
logFile：mysql-bin.000002
Pos：120
7：从库要开启相关线程：IO、SQL
8：从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9：从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来
三.主从复制涉及到的文件和线程
主库：
1：主库binlog：记录主库发生过的修改事件
2：dump thread：给从库传送（TP：二进制日志线程
从库：
1：relay-log（中继日志：：存储所有主库TP过来的binlog事件
2：master.info：存储复制用户信息，上次请求到的主库binlog位置点
3：IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
4：SQL thread：执行主库TP过来的日志
主从复制原理
1：通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2：从库通过start slave命令开启复制必要的IO线程和SQL线程
3：从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4：从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5：主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6：从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7：将TCP/IP缓存中的内容存到relay-log中
8：SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info
主从复制搭建实战
主库操作:
1：修改配置文件
#编辑mysql配置文件 [root@db01 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1，从库不等于1 server_id =1 #开启binlog日志 log_bin=mysql-bin
2：创建主从复制用户
#登录数据库 [root@db01 ~]# mysql -uroot -poldboy123 #创建rep用户 mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';
3：从库操作:
#修改db02配置文件 [root@db02 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1，从库不等于1 server_id =5 #重启mysql [root@db02 ~]# /etc/init.d/mysqld restart #记录主库binlog及位置点 mysql> show master status; #登陆数据库 [root@db02 ~]# mysql -uroot -poldboy123 #执行change master to 语句 mysql> change master to -> master_host='10.0.0.51', -> master_user='rep', -> master_password='oldboy123', -> master_auto_position=1;
四.主从复制基本故障处理
IO线程
连接主库
1：user password ip port
2：网络：不通，延时高，防火墙
请求binlog
1：binlog不存在或者损坏
更新relay-log和master.info
SQL线程
1：relay-log出现问题
2：从库做写入了
操作对象已存在（create：
操作对象不存在（insert update delete drop truncate alter：
约束问题、数据类型、列属性
处理方法一：
#临时停止同步 mysql> stop slave; #将同步指针向下移动一个（可重复操作： mysql> set global sql_slave_skip_counter=1; #开启同步 mysql> start slave;
处理方法二：
#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf #在[mysqld]标签下添加以下参数 slave-skip-errors=1032,1062,1007
但是以上操作都是有风险存在的
处理方法三：
1：重新备份数据库，恢复到从库
2：给从库设置为只读
#在命令行临时设置 set global read_only=1; #在配置文件中永久生效 read_only=1
五.延时从库
普通的主从复制可能存在不足
1：逻辑损坏怎么办？
2：不能保证主库的操作，从库一定能做
3：高可用？自动failover？
4：过滤复制
企业中一般会延时3-6小时
延时从库配置方法
#停止主从 mysql>stop slave; #设置延时为180秒 mysql>CHANGE MASTER TO MASTER_DELAY = 180; #开启主从 mysql>start slave; #查看状态 mysql> show slave status \G SQL_Delay: 60
3.延时从库停止方法
#停止主从 mysql> stop slave; #设置延时为0 mysql> CHANGE MASTER TO MASTER_DELAY = 0; #开启主从 mysql> start slave;
思考问题：
总数据量级500G，正常备份去恢复需要1.5-2小时
1：配置延时3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;
2：主库
drop database db;
3：怎么利用延时从库，恢复数据？
提示：
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001';
处理的思路：
1：停止SQL线程
mysql> stop slave sql_thread;
2：截取relaylog到误删除之前点
relay-log.info 获取到上次运行到的位置点，作为恢复起点
分析relay-log的文件内容，获取到误删除之前position
模拟故障：
1：关闭延时
mysql -S /data/3308/mysql.sock mysql> stop slave; mysql> CHANGE MASTER TO MASTER_DELAY = 0; mysql> start slave;
2：模拟数据
mysql -S /data/3307/mysql.sock source /root/world.sql use world; create table c1 select * from city; create table c2 select * from city;
3：开启从库延时5分钟
mysql -S /data/3308/mysql.sock show slave status \G mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 300; mysql>start slave; mysql -S /data/3307/mysql.sock use world; create table c3 select * from city; create table c4 select * from city;
4：破坏，模拟删库故障。(以下步骤在5分钟内操作完成。)
mysql -S /data/3307/mysql.sock drop database world;
5：从库，关闭SQL线程
mysql -S /data/3308/mysql.sock stop slave sql_thread;
6：截取relay-log
起点： cd /data/3308/data/ cat relay-log.info ./db01-relay-bin.000002 283 终点： mysql -S /data/3308/mysql.sock show relaylog events in 'db01-relay-bin.000002' db01-relay-bin.000002 | 268047 mysqlbinlog --start-position=283 --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
恢复relay.sql
1：取消从库身份
mysql> stop slave; mysql> reset slave all;
2：恢复数据
mysql> set sql_log_bin=0; mysql> source /tmp/relay.sql mysql> use world mysql> show tables;
六.半同步复制
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous：的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机：，这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。
半同步复制（Semi synchronous Replication：则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；
半同步复制开启方法
1：安装（主库）
#登录数据库 [root@db01 ~]# mysql -uroot -poldboy123 #查看是否有动态支持 mysql> show global variables like 'have_dynamic_loading'; #安装自带插件 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so'; #启动插件 mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; #设置超时 mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; #修改配置文件 [root@db01 ~]# vim /etc/my.cnf #在[mysqld]标签下添加如下内容（不用重启库） [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 检查安装： mysql> show variables like'rpl%'; mysql> show global status like 'rpl_semi%';
2：安装（从库）
#登录数据库 [root@mysql-db02 ~]# mysql -uroot -poldboy123 #安装slave半同步插件 mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so'; #启动插件 mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; #重启io线程使其生效 mysql> stop slave io_thread; mysql> start slave io_thread; #编辑配置文件（不需要重启数据库： [root@mysql-db02 ~]# vim /etc/my.cnf #在[mysqld]标签下添加如下内容 [mysqld] rpl_semi_sync_slave_enabled =1
注：相关参数说明
rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms：,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。
测试半同步
#创建两个数据库，test1和test2 mysql> create database test1; Query OK, 1 row affected (0.04 sec) mysql> create database test2; Query OK, 1 row affected (0.00 sec) #查看复制状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | #此行显示2，表示刚才创建的两个库执行了半同步 | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.06 sec) #从库查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test1 | | test2 | +--------------------+ #关闭半同步（1:开启 0:关闭： mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0; #查看半同步状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | #状态为关闭 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec) #再一次创建两个库 mysql> create database test3; Query OK, 1 row affected (0.00 sec) mysql> create database test4; Query OK, 1 row affected (0.00 sec) #再一次查看半同步状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | #此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4 | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有
七.过滤复制
主库：
白名单:只记录白名单中列出的库的二进制日志
binlog-do-db
黑名单：不记录黑名单列出的库的二进制日志
binlog-ignore-db
从库：
白名单：只执行白名单中列出的库或者表的中继日志
--replicate-do-db=test
--replicate-do-table=test.t1
--replicate-wild-do-table=test.t2
黑名单：不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
复制过滤配置：
[root@db01 data]# vim /data/3307/my.cnf #在[mysqld]标签下添加 replicate-do-db=world #关闭MySQL mysqladmin -S /data/3307/mysql.sock shutdown #启动MySQL mysqld_safe --defaults-file=/data/3307/my.cnf &
测试复制过滤：
第一次测试：
1：主库：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use world mysql> create table t1(id int);
2：从库查看结果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use world mysql> show tables;
第二次测试：
1：主库：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use test mysql> create table tb1(id int);
2：从库查看结果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use test mysql> show tables;
