13 数据库主从

主从复制介绍

什么是主从复制

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

为何要做主从

1、为了实现服务器负载均衡/读写分离做铺垫，提升访问速度
2、通过复制实现数据的异地备份，保障数据安全
3、提高数据库系统的可用性

主从复制的原理

# 从库准备 
（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会有自动清理的功能。

主从复制的方式

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

异步复制

客户端线程提交一个写操作，写入主库的binlog日志后就会立即返回，不需要等待从库完成同步操作，从而主库的dump线程会检测binlog日志的变量然后主动将更新推送给从库

主库停服部署

别搞事，数据库版本要一致。

服务器	ip
主库db01	192.168.15.201
从库db02	192.168.15.202

主库

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 修改主库配置文件，开启主库的binlog并设置server-id
# binlog
[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



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

# 备份主库，备份时锁表保证数据一致
mysql> FLUSH TABLES WITH READ LOCK; 
mysql> UNLOCK TABLES; 			# 解锁所有表

# 备份主库完整sql文件
[root@db01 ~]# mysqldump -uroot -pZkh0928! -A -E --triggers --master-data=2 --single-transaction > /root/all.sql

# 将完整的备份文件发给从库
[root@db01 ~]# scp ./all.sql root@192.168.15.202:/root/

从库

# 测试账号
[root@db02 ~]# mysql -ubaim0 -pxxx! -h192.168.15.201

# 导入数据
mysql> source /root/all.sql

# 修改从库的配置文件
[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.%

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

# 主库查看以下binlog日志的名字与位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

# 从库登陆mysql设置
change master to 
master_host='192.168.15.201', -- 库服务器的IP 
master_port=3306, -- 主库端口 
master_user='baim0', -- 主库用于复制的用户 
master_password='Zkh0928!', -- 密码 
master_log_file='mysql-bin.000001', -- 主库日志名 
master_log_pos=154; -- 主库日志偏移量,即从何处开始复制

# 开启slave线程
start slave;

# 验证启动
mysql> show slave statusG

IO线程起不来的报错https://blog.csdn.net/weixin_40816738/article/details/100054450

主库不停服制作主从

# 模拟插数据脚本
for i in `seq 1 1000000` 
do 
mysql -uroot -pZkh0928! -e "insert db1.t1 values($i)"; 
sleep 1;
done

主库

# 步骤同上一小结，最后加上下面这一步

# 查看binlog日志的起始点
[root@db01 ~]# head -50 ./all.sql | grep 'MASTER_LOG_POS'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=56696;

从库

# 步骤同上一小结，更改binlog日志的起始点

# 从库登陆mysql设置，注意设置binlog日志的起始点
change master to 
master_host='192.168.15.201', -- 库服务器的IP 
master_port=3306, -- 主库端口 
master_user='baim0', -- 主库用于复制的用户 
master_password='Zkh0928!', -- 密码 
master_log_file='mysql-bin.000001', -- 主库日志名 
master_log_pos=56696; -- 主库日志偏移量,即从何处开始复制

# 开启slave线程
start slave;

# 验证启动
mysql> show slave statusG

# 清空主从配置
reset slave all;

延迟从库

开启了主从

# 从库
1.停止主从 mysql> stop slave; 
2.设置延时为180秒 mysql> CHANGE MASTER TO MASTER_DELAY = 180; 
3.开启主从 mysql> start slave; 
4.查看状态 mysql> show slave status G SQL_Delay: 180 
5.主库创建数据，会看到从库值变化，创建的库没有创建 SQL_Remaining_Delay: 170

没有开启主从复制的情况下

1.修改主库，从库配置文件 
server_id 
开启binlog 
2.保证从库和主库的数据一致 
3.执行change语句 
change master to 
master_host='172.16.1.50',
master_user='rep',
master_password='123', 
master_log_file='mysql-bin.000001', 
master_log_pos=2752,
master_delay=180;

延时从库停止方法

1.停止主从
mysql> stop slave;
2.设置延时为0 
mysql> CHANGE MASTER TO MASTER_DELAY = 0; 
3.开启主从 mysql> start slave; 
#注：做延时从库只是为了备份，不提供服务

半同步复制

基本介绍

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

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

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

开启方法

确认mysql服务器是否支持动态增加插件

mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+

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

# 插件一般默认在MySQL安装目录/lib/plugin下，可以去查看一下是否存在 
ls /usr/lib64/mysql/plugin/ | grep semisync

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

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

# 安装完成后，在plugin表中查看一下
select * from mysql.plugin;

主库

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

过滤复制

企业开发环境介绍

# 开发环境
开发自己玩的环境

# 测试环境
性能测试
功能测试

# 预发布环境
# beta，内测不删档
1、只是一台服务器
2、没有真实的流量
3、连的时线上数据库

# 灰度环境
1、一台或者多台线上主机
2、链接的是线上数据库
3、真实流量

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

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

过滤复制的两种方式

黑名单

# 不记录黑名单列出的库的二进制日志
#参数 
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语句