数据库学习之十二：mysql主从复制

十二、mysql主从复制

1、主从复制原理

1.主从复制的前提：

1.1两台mysql实例（多台物理机，或者多实例）

1.2主库要开启二进制日志

1.3主库要提供复制相关用户，replication slave,一个比较特殊的权限。

grant replication slave on * . * to ‘10.0.0.%‘ identified by ‘123‘;

1.4从库需要将和主库相差的数据，进行追加

一般情况下可以人为备份主库数据，恢复到从库上。

1.5应该从恢复之后的时间点，开始自动从主库获取二进制日志开始应用

需要人为告诉从库，从哪里开始自动开始复制二进制日志(file+position)，另外还需要告诉从库user,password,ip,port

2.复制中的线程及文件

2.1主库

dump（IO） thread:在复制过程中，主库发送二进制日志的线程。

2.2从库

IO thread:向主库请求二进制日志，并且接受二进制日志的线程。

SQL thread:执行请求过来的二进制的线程

2.3在主库的文件

binlog文件，主库的二进制日志

2.4从库文件

relaylog：中继日志，存储请求过来的二进制日志。

master.info:

? 1.从库连接主库的重要参数（user，password，ip，port）

? 2.上次获取过的主库二进制日志的位置

relay-log.info

? 存储从库SQL线程已经执行过的relaylog日志位置。

3.主从复制的工作原理

3.1从库，IO线程，读取master.info中的信息，获取到连接参数（user，password，ip，port），和上次用过的主库的binlog的位置（mysqlbin-0000,position）。

3.2IO线程使用连接到主库，拿着上次从主库获取到的binlog的位置，问主库有没有比这个更新的二进制日志。

3.3主库查询二进制日志，并对比从库发送过来的位置信息，如果有新的二进制日子，就通过dump thread发送给我从库。

3.4从库通过IO线程，接受主库发来的二进制日志，存储到TCP/IP缓存中，并且返回ACK确认给主库，这时主库认为复制完成了，可以继续其他工作了。

3.5从库更新master.info，二进制日志的为新的位置信息。

3.6从库IO线程会将TCP/IP缓存中的日志，存储到relay-log中继日志文件中。

3.7从库SQL线程，读取relay-log.info，获取到上次执行到的relay-log日志位置，以这个位置为起点，往后继续执行中继日志。

3.8SQL线程执行完成所有relay之后，会更新relay-log.info信息为新位置信息。

到此位置，一次完成的复制过程完成。

4、搭建主从复制

1、准备环境
思路：
1、两个以上节点（多实例）
3307：master
3308：slave1
3309：slave2
2、主库binlog开启，从库开启relay-log（默认在数据目录下生成）
vim /data/3307/my.cnf
log-bin=/data/3307/mysql-bin
binlog_format=row

3、server-id不同
[ data]# cat /data/3307/my.cnf |grep server-id
server-id=3307
[ data]# cat /data/3308/my.cnf |grep server-id
server-id=3308
[ data]# cat /data/3309/my.cnf |grep server-id
server-id=3309
4、关闭数据库的自动域名解析
每个节点都加入以下配置：
skip-name-resolve
5、启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

6、主库创建复制账户
连接到主库：
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to ‘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.000003‘, 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.203‘,
  MASTER_USER=‘repl‘,
  MASTER_PASSWORD=‘123‘,
  MASTER_PORT=3307,
  MASTER_LOG_FILE=‘mysql-bin.000003‘,
  MASTER_LOG_POS=325;
开启主从（开启IO和SQL线程）：
start slave;
9、查看主从状态：
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主从重要状态信息介绍
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 


IO线程故障：
    1、主库连接不上
       user、password、port、ip 错误
	   解决方案：
		stop  slave;	
		reset slave all;  
		change master to	
		start slave;
       防火墙
       网络不通
       skip-name-resolve
	   stop  slave;
	   start slave;
	   
    2、主库二进制日志丢失或损坏
		解决方案：
		stop  slave;	
		reset slave all;  
		重新备份恢复
		change master to	
		start slave;

5、SQL线程故障

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           ---->只能控制普通用户

6、主从异常--主从延时过长

show slave status \G
Seconds_Behind_Master:0

默认的主从复制机制是异步的一个过程。

主库原因:
1、主库做修改操作之后，才会记录二进制日志。
sync_binlog=0/1

If the value of this variable is greater than 0, 
the MySQL server synchronizes its binary log to disk (using fdatasync()) 
after sync_binlog commit groups are written to the binary log. 
The default value of sync_binlog is 0, which does no synchronizing to disk—in this case,
the server relies on the operating system to flush the binary log‘s contents from time to time as for any other file. 
A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. 
However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast)
---------------------
    1：表示：每次事务commit，刷新binlog到磁盘
    0：系统决定binlog什时候刷新到磁盘
2、主库的压力特别大（大事务、多事务）
3、从库数量多，导致dump线程繁忙
-------------------
从库原因：
1、relay-log写入慢
2、SQL线程慢（主从硬件差异比较大）
-----------------------------
尽可能的避免主从延时
1、sync_binlog=1
2、大事务拆成小事务，多事务进行分离
3、使用多级主从，分库分表架构
4、将binlog放到ssd或者flash上，高性能存储
5、将relay放到ssd或者flash上
6、尽量选择和主库一致硬件和配置

7、主从复制高级功能--半同步复制

出发点：保证主从数据一致性的问题，安全的考虑

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，这次半同步就完成了。

8、主从复制高级特性--延时从库

会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份
	
我们配置的是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;

9、主从复制高级功能--复制过滤

主库方面控制（不建议使用）：
    白名单:只记录白名单中列出的库的二进制日志
     binlog-do-db
    黑名单：不记录黑名单列出的库的二进制日志
     binlog-ignore-db
	  
从库方面控制：
show slave status\G;查看相关参数。
白名单：只执行白名单中列出的库或者表的中继日志   

--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数据库的数据

10、主从复制新特性--GTID复制

GTID
5.6新特性
GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
每一台mysql实例中，都会有一个唯一的uuid，标识实例的唯一性
auto.cnf，存放在数据目录下

重要参数：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1


gtid-mode=on			            --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true		 --强制GTID的一致性
log-slave-updates=1					--slave更新是否记入日志

-----------------
构建1主2从的GTID复制环境：

3台虚拟机，
db02 克隆两台虚拟机环境，分别命名为db01、db03，在生产中准备3台真实的物理机，不用多实例

要求：

1、IP地址、主机名
	 db01:10.0.0.51/24   
	 db03:10.0.0.53/24
2、清理所有之前3306的相关数据，只留软件

db01:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db02:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db03:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

3、准备配置文件
规划：
	主库： 10.0.0.51/24
	从库1: 10.0.0.52/24
	从库2：10.0.0.53/24
主库：
加入以下配置信息
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 ‘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;