mysql主从复制
1.mysql复制简介
复制是 MySQL的一项功能，允许服务器将更改从一个实例复制到另一个实例。
• 主服务器将所有数据和结构更改记录到二进制日志中。
• 从属服务器从主服务器请求该二进制日志并在本地应用其内容。
2.mysql主从复制工作原理
1.从库，从IO线程，读取master.info中的信息，获取数据库的连接参数(userpasswdipport），与上次请求过的binlog的位置(例子：mysql-bin.000003，position=640)
2.IO线程连接主库，拿着binlog位置信息，问主库有没有比这个更新的二进制日志
3.主库查询二进制日志，对比从库发过来的位置信息，如果有新的二进制日志，会通过dump thread发给从库
4.从库通过IO线程，接收到主库发过来的二进制日志，会存储到TCP/IP缓存中，并返回ACK确认给主库，主库收到ACK之后就认变复制完成，继续其它工作
5.从库会更新master.info文件，二进制日志更新到最新的位置
6.从库IO线程会将TCP/IP缓存中的日志，存储到relaylog中继日志文件中
7.从库SQL线程，读取relaylog.info,获取到上次执行到的relaylog日志位置，以这个位置为起点，往后继续执行中继日志
8.从库SQL线程执行完所有relaylog后，会更新relaylog.inof信息为最新位置信息
9.到此为之，一次完整的复制完成
3.mysql主从复制搭建
1、主从复制的前提
1.1 两台以上mysql实例
多台物理机
多个mysql实例
1.2 主库要开启二进制日志
1.3 主库要提供复制相关的用户
replication slave，一个比较特殊的权限
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
1.4 从库需要将和主库相差的数据，进行追加
一般情况下可以人为备份主库数据，恢复到从库上
1.5 从应该从恢复之后的时间点，开始自动从主库获取新的二进制日志开始应用
我们需要人为告诉从库，从哪开始自动开始复制二进制日志(file+position)，另外还需要告诉从库user,passwd,port,ip
change master to
2、复制中的线程及文件
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日志位置
3.1 mysql主从复制搭建过程
准备环境
两个以上节点（多实例）
3307：master
3308：slave1
3309：slave2
第一步：各个节点配置，skip-name-resolve：关闭数据库的自动域名解析
[root@db02 3307]# cat my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
binlog_format=row
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log
skip-name-resolve
[root@db02 data]# cat 3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
skip-name-resolve
[root@db02 data]# cat 3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log
skip-name-resolve
第二步：启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
第三步：主库创建复制账户
连接到主库：
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
第四步：从库数据的追加
（1）不需要追加的情况
主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog（mysql-bin.000001）的开头位置（120）
（2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制
以下针对第二种方式：
备份主库
mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/backup/full.sql
sed -n '22p' /backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000010', MASTER_LOG_POS=325
恢复到从库
mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /backup/full.sql
第五步：从库开启主库
mysql -S /data/3308/mysql.sock #连接到3308
CHANGE MASTER TO
MASTER_HOST='10.0.0.52', #主库数据库地址
MASTER_USER='repl', #主库授权的账号
MASTER_PASSWORD='123', #数据库密码
MASTER_PORT=3307, #主库的端口
MASTER_LOG_FILE='mysql-bin.0000010', #主库的binlog文件
MASTER_LOG_POS=325; #binlog位置
开启主从（开启IO和SQL线程）：
start slave;
查看主从状态：
show slave statusG
查看主从状态信息介绍
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
第六步：测试，在主数据库中新建数据库,在从库查看状态
create databases oldoby;
3.2 mysql主从复制故障解决方法
3.2.1 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;
3.2.2 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 ---->只能控制普通用户
3.2.3 主从异常——主从延时过长
show slave status G
Seconds_Behind_Master:0 #查看同步时间
默认的主从复制机制是异步的一个过程。
主库原因:
1、主库做修改操作之后，才会记录二进制日志。
sync_binlog=0/1
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、尽量选择和主库一致硬件和配置
4.主从复制高级功能——半同步复制
半同步：保证主从数据一致性的问题，安全的考虑
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';
查看同步时间
show variables like '%rpl_sem%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制
如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。
5.主从复制高级功能——延时从库
为了防止逻辑损坏会专门找一个节点，配置成延时节点，一般情况下这个节点会被用备份
设置延时，一般的情况下会设置3-6小时个延时时间，为会防止逻辑损坏
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_DELAY = 120; #设置延时时间
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
SQL_Delay: 120
取消延时：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;
6.主从复制高级功能——复制过滤
主要是为了不复制一些不需要的库或表到从库
主库方面控制（不建议使用）：
白名单:只记录白名单中列出的库的二进制日志
binlog-do-db
黑名单：不记录黑名单列出的库的二进制日志
binlog-ignore-db
从库方面控制：
白名单：只执行白名单中列出的库或者表的中继日志
--replicate-do-db=test #只复制test库
--replicate-do-table=test.t1 #只复制test库下面的t1表
--replicate-wild-do-table=test.x* #只复制test库下面以x开头的表
黑名单：不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
例子：只同步oldboy库到从库
在3308中的my.cnf配置文件中加入
replicate-do-db=oldboy
然后重启数据库
mysqladmin -S /data/3308/mysql.sock shutdown
mysql -S /data/3308/mysql.sock
在主库上建数据库
mysql> create database oldboy1;
在从库上查看，发现没有同步过来
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| test |
+--------------------+
在主库上的oldboy进行添加数据
mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | li |
| 2 | aa |
| 3 | a1a |
+------+------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(4,'12');
Query OK, 1 row affected (0.00 sec)
在从库上查看，发现已经同步过来了
mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | li |
| 2 | aa |
| 3 | a1a |
| 4 | 12 |
+------+------+
4 rows in set (0.00 sec)
7.主从复制新特性-GTID复制
7.1 /etc/my.cnf中添加配置
gtid-mo --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
7.2 GTID搭建过程
三台机器：
db01:172.16.1.51(master)
db02:172.16.1.52(slave01)
db02:172.16.1.53(slave02)
7.2.1 编写配置文件
1.编写master主机配置文件(172.16.1.51)
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/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=/application/mysql/tmp/mysql.sock
[root@db01 ~]#
2.编写slave1主机配置文件(172.16.1.52)
[root@db02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
server_id=52
socket=/application/mysql/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/application/mysql/tmp/mysql.sock
3.编写slave1主机配置文件(172.16.1.53)
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
server_id=53
socket=/application/mysql/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/application/mysql/tmp/mysql.sock
4.重启数据库
/etc/init.d/mysqld restart
7.2.2 配置复制环境
1.在master主库上配置(51)
grant replication slave on *.* to repl@'172.16.1.%' identified by '123';
2.在从库上配置(5152)
change master to master_host='172.16.1.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;
3.启动slave
start slave;
4.查看从库状态
show slave statusG
7.2.3 测试GTID环境
1.在主库上新建数据库
create database oldboy;
2.在从库上查看有没有复制成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)