MySQL 主从复制

1.主库操作

1.主库配置server_id
2.主库开启binlog
3.主库授权从库连接的用户
4.查看binlog信息
5.导出所有数据

2.从库操作

1.从库配置server_id（跟主库不一致）
2.确认主库授权的用户可以连接主库
3.同步主库数据
4.配置主库信息（change master to）
5.开启slave

3.主从复制原理

1）图解

2）文字描述

1.主库配置server_id和开启binlog
2.主库授权从库连接的用户
3.主库查看binlog信息，与服务器信息
4.从库配置跟主库不一致server_id
5.配置主从，通过change master to高速从库主库的信息：ip、用户、密码、端口、binlog位置点、binlog名字
6.从库开启IO线程和sql线程
7.从库连接主库以后，IO线程会向主库的dump线程发起询问，询问是否有新数据
8.dump线程被询问，去查找新数据，并将新数据返回给IO线程
9.IO线程拿到数据先写入TCP缓存
10.TCP缓存将数据写入中继日志，并返回给IO线程一个ACK
11.IO线程收到ACK会记录当前位置点到master.info
12.sql线程会读取relay-log，执行从主库获取的sql语句
13.执行完以后将执行到的位置点，记录到relay-log.info

4.主从中涉及到的文件或者线程

1）主库

1.binlog：主库执行的sql语句
2.dump线程：对比binlog是否更新，获取新的binlog

2）从库

1.IO线程：连接主库，询问新数据，获取新数据
2.SQL线程：执行从主库哪来的sql语句
3.relay-log：中继日志，记录从主库拿过来的binlog
4.master.info：记录主库binlog信息，会随着同步进行更新
5.relay-log.info：记录sql线程执行到了那里，下次从哪里开始执行

三、主从复制的搭建

1.主库操作

1）配置

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/service/mysql/data/mysql-bin

[root@db03 ~]# /etc/init.d/mysqld start

2）授权一个用户

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.03 sec)

3）查看binlog信息

mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000003 |      326 |
+------------------+----------+

4）导出所有数据

( 锁表
flush tables with read lock;
解表
主从同步配置完成再解锁
unlock tables;)
# 备份数据
mysqldump -uroot -p -A --master-data=2 --single-transaction > /tmp/full.sql

[root@db03 data]# scp /tmp/full.sql 172.16.1.52:/tmp/

2.从库操作

1）配置

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

[root@db02 ~]# /etc/init.d/mysqld start

2）验证主库用户

[root@db02 ~]# mysql -urep -p -h172.16.1.53

3）同步数据

[root@db02 ~]# mysql -uroot -p123 < /tmp/full.sql

4）配置主从

mysql> change master to
    -> master_host='172.16.1.53',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=326;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

5）开启线程

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

6）查看主从

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.主从数据库出错

1）IO线程出错

mysql> show slave status\G
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
            
mysql> show slave status\G
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
            
#排查思路
1.网络
	[root@db02 ~]# ping 172.16.1.53
2.端口
	[root@db02 ~]# telnet 172.16.1.53 3306
3.防火墙
4.主从授权的用户错误
5.反向解析
	skip-name-resolve
6.UUID或server_id相同

2）SQL线程出错

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

#原因：
1.主库有的数据，从库没有
2.从库有的数据，主库没有

#处理方式一：自欺欺人
1.临时停止同步
mysql> stop slave;
2.将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
3.开启同步
mysql> start slave;

#处理方式二：掩耳盗铃
1.编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007

#处理方式三：正解
重新同步数据，重新做主从

配置延时复制

1.配置

# 配置延时主从
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=424,
    -> master_delay=180;
# 添加 master_delay=180; 180等于180秒
# 开启线程
mysql> start slave;
# 关闭延时从库
mysql> stop slave;
mysql> change master to master_delay=0;
mysql> start slave;
## 延时从库恢复数据时不要关闭主库的binlog，实际上从库还是会执行主库执行错的语句，只不过又执行了重建语句

2.利用延时从库恢复数据(实例)

#思考
总数据量级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;

半同步主从复制

1.半同步复制概念

从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.性能差，影响主库效率
2.半同步复制，有一个超时时间，超过这个时间恢复主从复制

2.配置半同步

1）主库操作

#登录数据库
[root@db01 ~]# mysql -uroot -p123
#查看是否有动态支持
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

3）额外参数

rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。

rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

过滤主从复制

1.过滤复制的方式

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      305 |   白名单      |    黑名单        |                   |
+------------------+----------+--------------+------------------+-------------------+

1）白名单

#从库
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t*
#主库
binlog-do-db=test
binlog-do-table=test.t1
binlog-wild-do-table=test.t*

2）黑名单

#从库
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t*
#主库
binlog-ignore-db=test
binlog-ignore-table=test.t1
binlog-wild-ignore-table=test.t*

2.配置过滤复制

1）主库创建两个库

mysql> create database wzry;
mysql> create database lol;

2）第一台从库配置

[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry

[root@db02 data]# systemctl restart mysqld

#查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wzry

3）配置第二台从库

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol

[root@db03 ~]# systemctl restart mysqld

#查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: lol

4）验证过滤复制

#1.主库操作
mysql> use wzry
Database changed

mysql> create table cikexintiao(id int);
Query OK, 0 rows affected (0.26 sec)

mysql> use lol
Database changed

mysql> create table fuleierzhuode(id int);
Query OK, 0 rows affected (0.01 sec)

#第一台从库查看
mysql> use wzry
Database changed

mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao    |
+----------------+
1 row in set (0.00 sec)

mysql> use lol
Database changed

mysql> show tables;
Empty set (0.00 sec)

#第二台从库查看
mysql> use wzry
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> use lol
Database changed

mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| fuleierzhuode |
+---------------+
1 row in set (0.00 sec)

3.配置过滤多个库

1）方法一：

[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry,lol

2）方法二：

[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry
replicate-do-db=lol

4.过滤复制配置在主库

1.配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
binlog-do-db=wzry

2.查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 | wzry         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.在主库的wzry库和lol库添加数据

4.从库查看数据，只能看到wzry库的数据

5.过滤复制总结

#配置在从库时
1.配置白名单：IO线程将主库的数据拿到了relay-log，但是sql线程只执行白名单配置的数据库相关语句
1.配置黑名单：IO线程将主库的数据拿到了relay-log，但是sql线程只不执行黑名单配置的数据库相关语句

#配置在主库时
1.配置白名单：binlog只记录白名单相关的sql语句
2.配置黑名单：binlog只不记录黑名单相关的sql语句

基于GTID主从复制

1.什么是GTID

1.全局事务标识符
2.组成：UUID + TID    
	   f03a53e0-cd46-11ea-a2c4-000c292c767e:1

2.GTID主从复制的优点

1.GTID同步时开启多个SQL线程，每一个库同步时开启一个线程
2.binlog在rows模式下，binlog内容比寻常的主从更加简洁
3.GTID主从复制会记录主从信息，不需要手动配置binlog和位置点

3.GTID主从复制的缺点

1.备份时更加麻烦，需要额外加一个参数 --set-gtid=on
2.主从复制出现错误，没有办法跳过错误

4.搭建GTID主从复制

1）配置三台数据库

#配置第一台主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin

#配置第一台从库
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

#配置第二台从库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3

2）查看是否开启GTID

mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery     | OFF       |
| enforce_gtid_consistency        | OFF       |
| gtid_executed                   |           |
| gtid_mode                       | OFF       |
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+
8 rows in set (0.00 sec)

3）开启GTID

#主库配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency
log-slave-updates

#从库1的配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency
log-slave-updates

#从库2的配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency
log-slave-updates

4）扩展

#配置log-slave-updates参数的场景
1.基于GTID的主从复制
2.双主架构+keepalived
3.级联复制
4.MHA

5）主库创建用户

mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';

6）主库数据同步到从库

mysqldump -uroot -p -R --triggers --master-data=2 --single-transaction -A > /tmp/full.sql
scp
mysql < full.sql

7）从库配置主从

mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)