linux系统mysql主从复制（2）
1.什么是主从复制
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。
2.主从复制的作用
1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。
3.主从复制的原理
从库I/O线程连接主库，请求主库中更新的bin-log。I/O线程接收到主库binlog dump 进程发来的更新之后，保存在本地relay-log中。
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
主从复制的前提
1）两台或两台以上的数据库实例
2）主库要开启二进制日志
3）主库要有复制用户
4）主库的server_id和从库不同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
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
4.主从复制搭建
主库操作：
修改主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id = 1
log-bin=/usr/local/mysql/data/mysql-bin
binlog_format=row
#server-id：用来标识一个唯一的实例，要保证主库唯一
#log_bin：二进制日志文件的路径，mysql用户对该路径必须具有读写权限
#binlog_format：表示二进制日志内容的记录方式，row记录sql的执行过程
#重启mysql
[root@db01 ~]# /etc/init.d/mysqld restart
创建主从复制用户
#登录数据库
[root@db01 ~]# mysql -uroot -p123
#创建主从复制用户
mysql> grant replication slave on *.* to wyf@'10.0.0.%' identified by '123';
查看主库的二进制日志文件及位置点
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 317
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
全备主库数据拷贝到从库
[root@db01 ~]# mysqldump -uroot -p123 -A --single-transaction --master-data=2 > /backup/full.sql
[root@db01 ~]# scp /backup/full.sql 10.0.0.51:/tmp/full.sql
从库操作：
修改从库配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id =5
relay_log=/usr/local/mysql/data/relay-log
relay_log_index = /usr/local/mysql/data/relay-log-index
#server_id：主库server-id为1，从库不等于1
#relay_log：中继日志位置
#重启mysql
[root@db02 ~]# /etc/init.d/mysqld restart
将主库数据导入从库
[root@db02 ~]# mysql -uroot -p123 </tmp/full.sql
登录从库执行change master to 语句
[root@db02 ~]# mysql -uroot -p123
mysql> change master to
-> master_host='10.0.0.50',
-> master_port=3306,
-> master_user='wyf',
-> master_password='123',
-> master_log_file='mysql-bin.000007',
-> master_log_pos=317;
#master_host:主库的主机名或者IP地址
#master_port:主库的端口号，必须为整数，不能加引号，否则会提示错误
#master_user:在主库上添加的复制用户名称
#master_password:在主库上添加的复制用户密码
#master_log_file:主库当前的二进制日志文件名称
#master_log_pos:主库当前的二进制文件位置点，整数，不可加引号，否则会提示错误
启动主从同步
mysql> start slave;
查看同步状况
mysql> show slave status\G
mysql> show slave status\G
***************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.50
Master_User: wyf
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 327
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注意：上述结果中Slave_IO_Running和Slave_SQL_Running都为Yes表示主从同步成功，如果为Connecting...，可以等待一会再次查看，如果为No，表示同步失败；
开启主从的另外一种方法是分别开启SQL线程和IO线程，如下：
mysql> start slave IO_THREAD;
mysql> start slave SQL_THREAD;
5.主从复制基本故障处理
IO线程
从库的IO线程无法连接，通过"show slave status\G"可以查看到具体的错误信息
原因1：在主库上创建的用户授权错误，导致从库无法远程连接主库
解决办法1：在主库上通过"show grants for 'user'@'ip';"查看授权是否正确，如果错误，重新授权即可
原因2：可能网络问题，ping不通等，例如由于主库的防火墙拦截导致从库无法连接主库
解决办法2：关闭主库的防火墙，或者在主库所在服务器添加防火墙规则，允许从库的tcp连接
原因3：执行change master to语句时执行错误，比如主库ip，用户，密码，端口等。
解决方法：mysql> stop slave;停止主从复制，删除之前执行的语句mysql> reset slave all ;重新执行change master to语句。
SQL线程
在从库上执行了创建库或者表的操作，然后在主库上又执行了一遍，导致同步错误，如下：
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
原因：从库上创建了库，主库上再次创建，从库会将主库上的创建过程再次应用到从库，导致从库上创建同名的库，发生错误
解决办法：停止从库，然后设置sql_slave_skip_count，使其跳过同步主库创建库的操作，从下一个操作开始同步，如下：
方法一：
#临时停止从库同步
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
方法三：
给从库设置只读
#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
[root@db01 ~]# vim /etc/my.cnf
read_only=1
方法四：重新备份数据库，恢复到从库
6.延时从库
6.1 延时从库的作用
1.用来在数据库误操作后，快速的恢复数据。比如有人误操作的表，那么这个操作在延迟时间内，从库的数据并没有发生变化，可以用从库的数据进行快速恢复。
2.用来做延迟测试，比如你做好的数据库读写分离，把从库作为读库，那么你想知道当数据产生延迟的时候到底会发生什么。那么你就可以使用这个特性也模拟延迟。
3.用于老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进行查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。
6.2 延时从库的设置方法
#停止主从复制
mysql>stop slave;
#设置延时为180秒。单位为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制。
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;
6.3 利用延时从库恢复数据
模拟故障
先关闭从库延时：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;
模拟主库数据：
mysql> create database aaa;
mysql> \u aaa
mysql> mysql> create table bbb(id int);
mysql> insert into bbb values(1),(2),(3);
开启从库延时500秒：
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 500;
mysql>start slave;
模拟主库产生数据并出现删库故障：
mysql> insert into bbb values(4),(5),(6);
mysql> drop database aaa;
从库关闭SQL线程：
mysql> stop slave sql_thread;
截取relay-log：
起点：
[root@db02 ~]# cd /usr/local/mysql/data/
[root@db02 data]# cat relay-log.info
mysql-bin.000008
592
终点：
[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002
# at 480
mysqlbinlog --start-position=283 --stop-position=480 /usr/local/mysql/data/db02-relay-bin.000002 >/tmp/relay.sql
将截取的relay-log传至主库
scp /tmp/relay.sql 172.16.1.50:/tmp/
把主库被删的库导出传至主库
mysqldump -uroot -p123 -B aaa -R --triggers --single-transaction>/tmp/aaa.sql
在主库上把从库导出的数据导入
mysql> source /tmp/aaa.sql
source /tmp/relay.sql
查看主库数据未丢失
mysql> select * from bbb;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
最后从库开启SQL线程即可
mysql> start slave sql_thread;
7.半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能， 主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收完毕，这一过程，也就意味着有可能出现当主服务器或从服务器端发生故障的时候，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。
简介：
从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；
半同步复制开启方法
主库操作：
#登录数据库
[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%';
从库操作：
#登录数据库
[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)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有
8.复制过滤
主库：
白名单:只记录白名单中列出的库的二进制日志
binlog-do-db
黑名单：不记录黑名单列出的库的二进制日志
binlog-ignore-db
从库：
白名单：只执行白名单中列出的库或者表的中继日志
replicate-do-db=test
replicate-do-table=test.t1 #只执行test库中的t1表的中继日志
replicate-wild-do-table=test.t* #执行test库中的以t开头的表的中继日志
黑名单：不执行黑名单中列出的库或者表的中继日志
replicate-ignore-db
replicate-ignore-table
replicate-wild-ignore-table
配置复制过滤
[root@db02 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加
replicate-do-db=test #从库只执行对test库的中继日志
#重启MySQL
[root@db02 ~]# /etc/init.d/mysqld restart
