MySQL数据库 主从同步

主从复制介绍

什么是主从复制

• 将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。
• 一句话表示就是，主数据库做什么，从数据库就跟着做什么。

为何要做主从
1. 为实现服务器负载均衡/读写分离做铺垫，提升访问速度

1、什么是读写分离
有了主从保持数据一致作为大前提，我们便可以可以分离读写操作，其中Master负责写操作的负载，
也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。

2、读写分离的作用
读写分离可以大大提高读取的效率。
1） 在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右，
也就是说写操作非常少，大量的数据操作是集中在读的操作。我们可以制作一主多从，因为写操作很少，所以由一个主库负责即可，
而大量的读操作则分配给多个从库，这样占据比例最大的读操作的压力就被负载均衡了，因此读效率会得到了极大的提升。

2）熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。
我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，
这从另一个方面有效的提高了读的效率，保证了系统的性能及高可用性。

3、具体做法
方案一：
就是主库写，从库读。

方案二：
主库负责写，还有部分读，从库只负责读，而且是读操作的主力。
即当主服务器比较忙时，部分查询请求会自动发送到从服务器中，以降低主服务器的工作负荷。
# 主从复制的时候，从服务器复制主服务器数据会存在网络延迟；
# 主服务器接收用户写请求是并行的，而从服务器会复制主服务器是串行的方式，并行数据量比较大的时候，主从复制本身存在延迟。
# 主库写操作比较多，数据量比较大的时候，用户读取数据可以先读取主库中写操作缓存的数据；方案二效率更高，保证了用户体验。

2. 通过复制实现数据的异地备份，保障数据安全

可以定期的将数据从主服务器上复制到从服务器上，这实现了数据的异地备份。

在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备上，
当备份作业运行时就会影响到服务器的正常运行，有时候会明显的降低服务器的性能。
同时，将备份数据存放在本地，也不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，
此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

3. 提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。
主库宕机后，从库尚可用，即当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。
然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员会采用两种手段。

1. 从库临时取代主库，只用来读
主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制，
例如仍然只能够进行数据的查询，而不能够进行数据的更新、删除等操作。这主要是基于从数据的安全性考虑。

2. 从库永久取代主库，负责读和写
从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。
此时可以实现对数据的查询、更新、删除等操作。为此就需要做好数据的安全性工作。
即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患。

主从复制的原理

主从复制整体上来说，复制有3个步骤：

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）。

2. slave的io线程将master的binary log events拷贝到它的中继日志(relay log)；

3. slave的sql线程解析中继日志中的事件并在从库执行，保持与主库一致。
   

主从复制详解

从库准备

1. 从库 change master to 时，ip、port、user、password、binlog position写入到master.info进行记录
2. 从库 start slave 时，会启动IO线程和SQL线程

同步的过程

1. 从库的IO线程，读取master.info信息，获取主库信息并连接主库
2. 主库接收从库的链接请求后，会生成一个准备binlog dump的线程，来响应从库
3. 主库一旦有新的日志生成，会发送“信号”给主库的binlog dump线程，然后binlog dump线程会读取binlog日志的更新
4. TP(传送)给从从库的IO线程
5. IO线程将收到的日志存储到了TCP/IP 缓存
6. 写入TCP/IP缓存后，立即返回ACK给主库 ，此时主库工作完成
7. IO线程更新master.info文件binlog 文件名和postion
8. IO线程将缓存中的数据，存储到relay-log日志文件，此时io线程工作完成
9. 从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点
10. 从库SQL线程基于从步骤9中获取到的起点，去中继日志relay-log.000001获取后续操作，在从库回放relay-log
11. SQL线程回放完成之后，会更新relay-log.info文件，把当前操作的位置记入，作为下一次操作的起点。
12. relay-log会有自动清理的功能。
    

主从复制日志的格式

这里的日志格式就是指二进制日志的三种格式

binlog_format=statement 
binlog_format=row 
binlog_format=mixed

bin log日志详解

• 其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

主从复制的方式

MySQL的主从复制有两种复制方式，分别是异步复制和半同步复制

异步复制原理介绍

我们之前介绍的就是异步复制，即客户端线程提交一个写操作，写入主库的bin log日志后就立即返回，并不需要等待从库完成同步操作，而主库的dump线程会监测bin log日志的变量然后主动将更新推送给从库。MySQL 主从复制默认是异步的模式

异步复制的问题
MYSQL5.5之前版本的主从复制都是异步的，而在异步复制中，主库执行完操作后，写入bin log日志后，就返回客户端。这一动作就结束了，主库并不会验证从库有没有收到bin log日志、以及收到的bin log是否完整，那如果主库提交一个事务并写入bin log中后，当从库还没有从主库得到bin log时，主库宕机了或因磁盘损坏等故障导致该事务的bin log丢失了，那从库就不会得到这个事务，也就造成了从库与主库数据不一致的问题，我们也就无法使用备库来继续提供数据一致的服务了，半同步复制可以解决该问题。

半同步复制原理介绍

从MYSQL5.5开始，支持半同步复制，在一定程度上保证提交的事务已经传给了至少一个备库。

1、一个事务操作的完成需要记完两份日志，即主从的binlog是同步的

半同步复制，当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Bin log并成功写入Relay-log中才返回客户端。
所以这样就保证了一个事务至少有两份日志，一份保存在主库的Bin log，另一份保存在其中一个从库的Relay-log中，
从而保证了数据的安全性和一致性。

2、半同步即并非完全同步

半同步复制的"半"体现在，虽然主从库的Bin log是同步的，但主库不会等待从库的sql线程执行完Relay-log后才返回，
而是确认从库的io线程接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，
这个延时就是从库sql线程执行Relay-log的时间。所以只能称为半同步。

3、半同步复制超时则会切换回异步复制，正常后则切回半同步复制

在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，
导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），
如果这个时间过后还无法推送到从库，那MySQL会自动从半同步复制切换为异步复制，
当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

异步复制部署

前提：主从数据库版本一致！！！

主库停服务 搭建主从同步

主库：192.168.15.51

1. 在主库上创建一个用于复制的账号，并赋予replication slave权限，这里必须 *.* 不能指定库授权，因为replication slave是全局的
mysql> grant replication slave on *.* to "nana"@"%" identified by 'Na123***';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2. 修改主库配置文件，开启主库的Binlog，并设置server-id
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#节点ID，确保唯一
server-id = 1
#开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
sync_binlog = 1
#binlog日志格式
binlog_format = row
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db = mysql
# 表中自增字段每次的偏移量
auto-increment-offset = 1
# 表中自增字段每次的自增量
auto-increment-increment = 1
#跳过从库错误
slave-skip-errors = all

3. 重启主库
[root@db01 ~]# systemctl restart mysqld

4. 备份主库，备份时锁表保证备份一致
# 锁表
mysql> FLUSH TABLES WITH READ LOCK;

[root@db01 ~]# mysqldump -uroot -p"Na123***" -A -E -r --triggers --master-data=2 --single-transaction > /tmp/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

5. 将备份文件发送给从库
[root@db01 ~]# scp /tmp/all.sql root@192.168.15.7:/tmp

从库：192.168.15.7

1. 测试复制账号
[root@web01 ~]# mysql -unana -pNa123*** -h 192.168.15.51

2. 导入初始数据
[root@web01 ~]# mysql -uroot -pNa123*** < /tmp/all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

3. 修改从库配置文件，增加server-id，注意这个值是唯一的，要区别于主库和其他从库
[root@web01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

# 忽略数据同步的表
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
# 从库也可以开启binlog，但通常关闭
# log-bin=mysql-bin

4. 重启从库
systemctl restart mysqld

5. 配置复制
5.1 先去主库查看一下binlog日志名与位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.2 然后在从库进行配置
[root@web01 ~]# mysql -uroot -pNa123***

mysql> change master to
    -> master_host='192.168.15.51', 		-- 库服务器的IP
    -> master_port=3306, 				-- 主库端口	
    -> master_user='nana', 				-- 主库用于复制的用户
    -> master_password='Na123***', 		-- 密码
    -> master_log_file='mysql-bin.000001', 	-- 主库日志名
    -> master_log_pos=154; 				-- 主库日志偏移量,即从何处开始复制
Query OK, 0 rows affected, 2 warnings (0.02 sec)

6. 从库启动slave线程，并检查
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7. 查看主从复制服务配置是否完成
mysql> show slave status\G
#      Slave_IO_Running: Yes			IO线程
#      Slave_SQL_Running: Yes			SQL线程

8. 切换到主库,解锁主库锁住的表
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

测试

- 在主库中进行操作
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

- 在从库中进行操作
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

主库运作状态 搭建主从同步

主库：192.168.15.51

1. 在主库上创建一个用于复制的账号，并赋予replication slave权限，这里必须 *.* 不能指定库授权，因为replication slave是全局的
mysql> grant replication slave on *.* to "nana"@"%" identified by 'Na123***';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2. 修改主库配置文件，开启主库的Binlog，并设置server-id
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 节点ID，确保唯一
server-id = 1
# 开启mysql的binlog日志功能
log-bin = mysql-bin
# 控制数据库的binlog刷到磁盘上去 , 0不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
sync_binlog = 1
# binlog日志格式
binlog_format = row
# binlog过期清理时间
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 100m
# binlog缓存大小
binlog_cache_size = 4m
# 最大binlog缓存大小
max_binlog_cache_size = 512m
# 不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db = mysql
# 表中自增字段每次的偏移量
auto-increment-offset = 1
# 表中自增字段每次的自增量
auto-increment-increment = 1
# 跳过从库错误
slave-skip-errors = all

3. 重启主库
[root@db01 ~]# systemctl restart mysqld

4. 创建数据
create database db01;
create table db01.t1(id int);
insert db01.t1 values(666),(666),(666);

5. 模拟主库一直在插入数据
[root@db01 ~]# vim a.sh
#!/bin/bash
for i in `seq 1 1000000`
do
mysql -uroot -pNa123*** -e "insert db01.t1 values($i)";
sleep 1;
done

[root@db01 ~]# bash a.sh

6. 主库全量备份数据(热备)
# 打点备份
[root@db01 ~]# mysqldump -uroot -pNa123*** -A -R --triggers --master-data=2 --single-transaction > /tmp/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 不打点备份
# mysqldump -uroot -p123 -A -R --triggers > /tmp/all.sql
# 导入不打点的数据的话，接下来主从，位置点只能去主库查看 show master status; 而数据在不停地往主库里插入，该命令查看的只是那一刻binlog的记录的位置，基于该位置同步数据会丢失所获得位置点往前一直到全备的数据，以及操作从库过程中新写入主库的数据。
# 如果导入的是打点的数据，那么全量备份的起始点可以从备份文件中获得，从库可以以此为同步的基准点，去主库获取全量备份之后的数据。

7. 将热备数据传送给从库
[root@db01 ~]# scp /tmp/all.sql 192.168.15.7:/tmp

从库：192.168.15.7

1. 测试复制账号
[root@web01 ~]# mysql -unana -pNa123*** -h 192.168.15.51

2. 修改从库配置文件，增加server-id，注意这个值是唯一的，要区别于主库和其他从库
[root@web01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

# 忽略数据同步的表
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
# 从库也可以开启binlog，但通常关闭
# log-bin=mysql-bin

3. 重启从库
[root@web01 ~]# systemctl restart mysqld

4. 在从库导入全备数据
[root@web01 ~]# mysql -uroot -pNa123*** < /tmp/all.sql

5. 查看sql文件中的位置点（如果是打点备份的话）
[root@web01 ~]# head -50 /tmp/all.sql|grep 'MASTER_LOG_POS'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4221;

6. 从库配置同步
mysql> change master to
    -> master_host='192.168.15.51',
    -> master_port=3306,
    -> master_user='nana',
    -> master_password='Na123***',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=4221;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

7. 开启SQL线程和IO线程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

8. 查看主从状态
mysql> show slave status\G
#      Slave_IO_Running: Yes			IO线程
#      Slave_SQL_Running: Yes			SQL线程

9. 测试主从同步是否完成
# 从服务器数据和主服务器数据一致即主从同步配置完成 
mysql> select * from db01.t1;

主从复制基本故障处理

# 清空主从配置
reset slave all;

IO线程问题

1.检测网络:
[root@db01 ~]# ping 172.16.1.51

2.检测端口:
[root@db01 ~]# telnet 172.16.1.51 3307
# 阿里云机器有的禁ping
[root@db01 ~]# tcping 172.16.1.51 3306

3.检测账号,密码:
# 根据授权命令
grant replication slave on *.* to "nana"@'172.16.1.5%' identified by '123';
# 连接测试
mysql -unana -p123 -h127.0.0.1 -P 3307

4.查看binlog是否存在
show master status;

5.反向解析
# 没有跳过反向解析
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password:YES)
# 配置
skip_name_resolve

SQL线程问题

1.主库有的数据，从库没有
  主库:a库
  从库:没有a库

2.从库有的数据，主库没有
  从库:a库
  主库:要创建a库

3.主库与从库数据库结构有差别

# 处理方法一：
# 临时停止同步
mysql> stop slave;
# 将同步指针向下移动一个（可重复操作）解决不了根本问题
mysql> set sql_slave_skip_counter=1;
# 开启同步
mysql> start slave;

# 处理方法二：
# 编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
# 在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007
	1007:对象已存在

	1032:无法执行DML

	1062:主键冲突,或约束冲突

# 处理方法三：
1. 要求,主从复制之前,主库和从库的数据保证一致.
2. 在从库上设置 只读:set read-only=1; 
（做读写分离时使用，但是做MHA会出现提升为主库时，主库只读，后面会讲专门做读写分离的 Atlas）

搭建延迟从库

我们说用延时从库可以做备份，主库执行删除的时候，从库还没有删除，可以把表数据拿出来恢复回去，企业中一般会延时3-6小时。

1. 已经有主从同步的情况下

1.停止主从(从库操作)
mysql> stop slave;

2. 设置延时120秒(从库操作)
mysql> change master to master_delay=120;

3. 开启主从(从库操作)
mysql> start slave;

4. 切换至主库创建数据库(切换至主库进行测试)
mysql> create database db02;

4. 查看状态(从库操作)
mysql> show slave status\G
#          SQL_Delay: 120				
#          SQL_Remaining_Delay: 118		主库创建数据，会看到从库值变化，创建的库还剩118秒创建创建

2. 没有主从同步的情况下

1.修改主库，从库配置文件
	server_id
	开启binlog
	
2.保证从库和主库的数据一致

3.执行change语句
# 跟制作主从同步一样的操作,添加一个master_delay参数即可
change master to
master_host='192.168.15.51',
master_port=3306,
master_user='nana',
master_password='Na123***',
master_log_file='mysql-bin.000001',
master_log_pos=4221;
master_delay=120;    

3. 延时从库停止方法

1. 停止主从
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

2. 设置延时为0
mysql> change master to master_delay=0;
Query OK, 0 rows affected (0.00 sec)

3. 开启主从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#注: 做延时从库只是为了备份，不提供服务

思考: 延时IO线程延时了还是SQL线程延时了

1. 开启延时线程	
stop slave;
change master to master_delay=120;
start slave;
  
2. 去主库创建一个库
create database test;
  
3. 查看从库的relaylog看看有没有内容
[root@web01 mysql]# cd /var/lib/mysql 
[root@web01 mysql]# mysqlbinlog --base64-output=decode-rows -vvv mysql-relay-bin.000002
# 在延迟的时间段内，relay log日志里面记录了create database test，说明从库已经IO线程是正常接收到了主库的数据。

总结：

• 延时从库是在SQL线程做的手脚，IO线程已经把数据放到relay-log里了.
• SQL线程在执行的时候，会延迟你设定的时间长度.

使用延时从库恢复数据

场景

总数据量级500G，正常备份去恢复需要1.5-2小时 
1. 配置延时3600秒 
mysql> change master to master_delay = 3600;

2. 主库 
mysql> drop database db; 
   
3. 怎么利用延时从库，恢复数据？

环境准备

1.进行每日的全备
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction >/backup/full.sql
  
2.调整延时从库延时时间为60分钟
stop slave;
change master to master_delay = 3600;
start slave;

3.主库写入新数据
create database test01;
use test01;
create table t1(id int);
insert into t1 values(1),(2),(3),(4);

create database test02;
use test02;
create table t2(id int);
insert into t2 values(1),(2),(3),(4);

模拟删除数据

1. 删除一个库，可以是之前的，也可以是刚创建的
# 因为刚我们看了，只要我执行了，从库已经写到了relay-log，跟我数据库里的数据关系不大。
drop database db01;		

使用延时从库恢复数据

1. 停止从库sql线程
mysql> stop slave sql_thread;

2. 查看状态
mysql> show slave status\G

3. 备份从库数据
# -B 可以指定数据库名,多个数据库可以用逗号分隔,这边使用全量备份也可以 
[root@web01 mysql]# mkdir /backup
[root@web01 mysql]# mysqldump -uroot -p123 -B db01 > /backup/congku.sql

4. 截取一下relay-log
# 确认起点，查看relay-log.info即可
[root@web01 mysql]# cd /var/lib/mysql
[root@web01 mysql]# cat relay-log.info 
./mysql-relay-bin.000002
320

# 确认终点，找到drop语句之前
[root@web01 mysql]# mysqlbinlog --base64-output=decode-rows -vvv mysql-relay-bin.000002
# 截取数据
[root@web01 mysql]# mysqlbinlog --start-position=320 --stop-position=974 mysql-relay-bin.000002 > /tmp/db01.sql

5. 将从库全备的数据与relaylog截取数据拷贝到主库
[root@db01 ~]# scp /tmp/db01.sql 192.168.15.51:/tmp/
[root@db01 ~]# scp /backup/congku.sql 192.168.15.51:/tmp/

6.将数据导入主库
# 导入前不要停掉bin log日志
[root@db01 ~]# mysql -uroot -pNa123*** < /tmp/db01.sql
[root@db01 ~]# mysql -uroot -pNa123*** < /tmp/congku.sql

7.开启从库的sql线程
mysql> start slave sql_thread;

# 主库那边执行到删除的时候没关系，因为还有创建的部分，他会再次把数据创建回来。

半同步复制部署

半同步模式是作为MySQL5.5的一个插件来实现的，主从库使用的插件不一样

主库：192.168.15.51

1. 在主库上创建一个用于复制的账号，并赋予replication slave权限，这里必须 *.* 不能指定库授权，因为replication slave是全局的
mysql> grant replication slave on *.* to "nana"@"%" identified by 'Na123***';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2. 修改主库配置文件，开启主库的Binlog，并设置server-id
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#节点ID，确保唯一
server-id = 1
#开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
sync_binlog = 1
#binlog日志格式
binlog_format = row
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db = mysql
# 表中自增字段每次的偏移量
auto-increment-offset = 1
# 表中自增字段每次的自增量
auto-increment-increment = 1
#跳过从库错误
slave-skip-errors = all

3. 重启主库
[root@db01 ~]# systemctl restart mysqld

4. 备份主库，备份时锁表保证备份一致
# 锁表
mysql> FLUSH TABLES WITH READ LOCK;

[root@db01 ~]# mysqldump -uroot -p"Na123***" -A -E -r --triggers --master-data=2 --single-transaction > /tmp/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

5. 将备份文件发送给从库
[root@db01 ~]# scp /tmp/all.sql root@192.168.15.7:/tmp

从库：192.168.15.7

1. 测试复制账号
[root@web01 ~]# mysql -unana -pNa123*** -h 192.168.15.51

2. 导入初始数据
[root@web01 ~]# mysql -uroot -pNa123*** < /tmp/all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

3. 修改从库配置文件，增加server-id，注意这个值是唯一的，要区别于主库和其他从库
[root@web01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

# 忽略数据同步的表
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
# 从库也可以开启binlog，但通常关闭
# log-bin=mysql-bin

4. 重启从库
systemctl restart mysqld

5. 配置复制
5.1 先去主库查看一下binlog日志名与位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.2 然后在从库进行配置
[root@web01 ~]# mysql -uroot -pNa123***

mysql> change master to
    -> master_host='192.168.15.51', 		-- 库服务器的IP
    -> master_port=3306, 				-- 主库端口	
    -> master_user='nana', 				-- 主库用于复制的用户
    -> master_password='Na123***', 		-- 密码
    -> master_log_file='mysql-bin.000001', 	-- 主库日志名
    -> master_log_pos=154; 				-- 主库日志偏移量,即从何处开始复制
Query OK, 0 rows affected, 2 warnings (0.02 sec)

6. 从库启动slave线程，并检查
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7. 查看主从复制服务配置是否完成
mysql> show slave status\G
#      Slave_IO_Running: Yes			IO线程
#      Slave_SQL_Running: Yes			SQL线程

8. 切换到主库,解锁主库锁住的表
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

分别在主从库上安装对应插件

1. 先确认主从的MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

2. 分别在主从库上安装对用插件
# 插件一般默认在MySQL安装目录/lib/plugin下，可以去查看一下是否存在
# 主库的插件是semisync_master.so，从库是semisync_slave.so
[root@web01 ~]# ls /usr/lib64/mysql/plugin/ | grep semisync
semisync_master.so
semisync_slave.so

# 主库
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

# 从库
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

3. 安装完成后，在plugin表（系统表）中查看一下
# 主库
mysql> select * from mysql.plugin;
+----------------------+----------------------+
| name                 | dl                   |
+----------------------+----------------------+
| rpl_semi_sync_master | semisync_master.so   |
| validate_password    | validate_password.so |
+----------------------+----------------------+
2 rows in set (0.00 sec)

# 从库
mysql> select * from mysql.plugin;
+---------------------+----------------------+
| name                | dl                   |
+---------------------+----------------------+
| rpl_semi_sync_slave | semisync_slave.so    |
| validate_password   | validate_password.so |
+---------------------+----------------------+
2 rows in set (0.00 sec)

在主库开启半同步复制

# 启动插件
mysql> set global rpl_semi_sync_master_enabled=1;
# 设置超时
mysql> set global rpl_semi_sync_master_timeout=30000;

# 修改配置文件（不用重启库，因为上面已经开启了）
[root@db01 ~]# vim /etc/my.cnf
# 在[mysqld]标签下添加如下内容
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

在从库开启半同步复制

# 启动插件
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

确认半同步复制是否成功开启

# 在主库上查看半同步复制的状态
mysql> show status like '%semi_sync%';
#ON表示半同步复制打开，OFF表示关闭
| Rpl_semi_sync_master_status                | ON    |

# 这个数字表示主库当前有几个事务说通过半同步复制到从库的
| Rpl_semi_sync_master_yes_tx                | 1     |

# 表示有几个事务不是通过半同步复制到从库的
| Rpl_semi_sync_master_no_tx                 | 0     |

企业诸多环境介绍

实际情况中，企业中的环境可能有很多，如下所示 :

1. 开发环境

开发开发完自己测试

2. 测试环境

1. 性能测试
2. 功能测试

3. 预发布环境（beta，内测不删档）

1. 只是一台服务器
2. 没有真实的流量
3. 连的是线上数据库

疑问：如果有一个待上线需求，需要改动数据库表结构，怎么处理？

先把预发布环境使用的数据库切换为测试环境使用的数据库
然后有针对性的测试下数据库的变更是否会影响线上当前代码程序的运行，测试通过后再上线

4. 灰度环境

1. 一台或多台线上主机
2. 链接的是线上数据库
3. 真实流量

灰度发布，又称金丝雀发布。
金丝雀发布这一术语源于煤矿工人把笼养的金丝雀带入矿井的传统。矿工通过金丝雀来了解矿井中一氧化碳的浓度，
如果一氧化碳的浓度过高，金丝雀就会中毒，从而使矿工知道应该立刻撤离。

灰度发布发生在预发布环境之后，生产环境之前。
对应到软件开中，则是指在发布新的产品特性时通过少量的用户试点确认新特性没有问题，确保无误后推广到更大的用户使用群体。

生产环境一般会部署在多台机器上，以防某台机器出现故障，这样其他机器可以继续运行，不影响用户使用。
灰度发布会发布到其中的几台机器上，验证新功能是否正常。如果失败，只需回滚这几台机器即可。

5. 沙盒环境

沙盒环境又称测试环境和开发环境，是提供给开发者开发和测试用的环境。
沙盒通常严格控制其中的程序所能访问的资源，比如，沙盒可以提供用后即回收的磁盘及内存空间。
在沙盒中，网络访问、对真实系统的访问、对输入设备的读取通常被禁止或是严格限制。

也就是说所谓的沙盒测试就是在产品未上线前在内部环境或网络下进行的测试，
此时在正常的线上环境是无法看到或查询到该产品或项目的，只有产品在测试环境下无问题上传到生产环境之后，用户才能看到该产品或功能。

6. 生产环境

除了生产和预发布其他的环境都是虚拟机测试用的。

测试环境有很多游戏，我就想一个从库同步一种游戏，还有合服，建新服，其实就是一个库或者一个表而已。

考虑到环境过多为了节省资源，我们会有只同步某一个库的需求，这就用到了黑名单与白名单。

过滤同步的两种方式

黑名单

不记录黑名单列出的库的二进制日志

# 参数
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t% 支持通配符,t开头的表

注意：
replicate-ignore-table依赖参数replicate-ignore-db
即如果想忽略某个库下的某张表，需要一起配置
replicate-ignore-db=test
replicate-ignore-table=test.t1

白名单

只执行白名单中列出的库或者表的中继日志

# 参数：
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t%

注意：
replicate-do-table依赖参数replicate-do-db
即如果想只接收某个库下的某张表，需要一起配置
replicate-do-db=test
replicate-do-table=test.t1

主从库设置黑白名单的影响

1、黑白名单对主库的影响是：是否记录binlog日志
在主库上设置白名单:只记录白名单设置的库或者表、相关的SQL语句到binlog中
在主库上设置黑名单:不记录黑名单设置的库或者表、相关的SQL语句到binlog中

2、黑白名单对从库的影响是：sql线程是否执行io线程拿到的binlog
IO线程一定会拿到所有的binlog，但
如果在从库上设置白名单:SQL线程只执行白名单设置的库或者表相关的SQL语句
如果在从库上设置黑名单:SQL线程不执行黑名单设置的库或者表相关的SQL语句

配置过滤同步

- 在从库配置白名单
# 在从库配置白名单
[root@web01 ~]#  vim /etc/my.cnf
replicate-do-db=test
replicate-wild-do-table=test.t%

# 重启从库
[root@web01 ~]# systemctl restart mysqld

- 在主库创建库和表进行测试
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

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

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

- 在从库查看同步的数据
mysql> use test;
Database changed

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