Mysql主从配置（一主一从）

目标：主从的原理，怎么实现主从

主从复制的基本原理

---

复制的类型

• 异步复制（Asynchronous Replication）
• 半同步复制（Semisynchronous Replication）
• 组复制（Group Replication）

1.异步复制

• 原理简单
• 对网络延迟要求比较小
• 不保证日志被传送到了备库，可能会丢失数据

异步复制的原理

2.半同步复制

• 原理简单
• 对网络延迟有一定要求，最好在同一机房
• 可以保证日志被传送到了备库，不易丢失数据
• rpl_semi_sync_master_timeout 该参数可以设置主从超时时间（默认值：10秒），退化成异步复制（如果备库无法连接，也不会长时间影响主库）
• 半同步是兼顾数据安全与效率的最好选择

半同步复制的原理

3. 组复制

• 原理比较复杂
• 需要依赖共识算法
• 实际应用比较少
• 是数据库走向原生分布式的示范

组复制的原理

---

实现一主一从

异步复制

1.mysql.cnf配置

配置完成后需要重启数据库

主库配置

 -- 主库
[mysqld]
secure_file_priv=/home # mysqldump备份后的路径，你可以设置到你想要的目录
server-id = 1 # 配置服务器ID，2个数据库server-id不一致即可
log_bin = /var/lib/mysql/binlog # 开启binlog，目录/var/lib/mysql/就会有binlog.000001这样的文件

从库配置

 -- 从库
[mysqld]
secure_file_priv=/home # mysqldump备份后的路径，你可以设置到你想要的目录
server-id = 2 # 配置服务器ID，2个数据库server-id不一致即可
log_bin = /var/lib/mysql/binlog # 开启binlog，目录/var/lib/mysql/就会有binlog.000001这样的文件

2.主从数据库配置

主库-查看binlog状态，参数用于后续从库同步使用

 -- 主库
-- 查看binlog读取的位置
SHOW MASTER STATUS;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
binlog.000033	1381

主库-全表加读锁，防止binlog备份时被更新

 -- 主库
-- 给主库加上全表读锁
FLUSH TABLES WITH READ LOCK;

主库-全表备份

 # 主库
# mysql5.6给全库备份
mysqldump -uroot -p123456 --all-databases --master-data > dbdump.sql

# mysql8.0给全库备份
mysqldump -uroot -p123456 --all-databases --source-data > dbdump.sql

mysqldump -uroot -p123456 --all-databases --triggers --routines --events  --source-data > dbdump.sql

主库-备份完成后，解锁全表

 -- 主库
-- 解锁
UNLOCK TABLES；

主库-把主库备份的文件复制到从库

 # 主库
# 通过scp传输到从库
scp dbdump.sql root@192.ip.0.0:/home/

# 因为我是开发环境用的是docker
docker cp mysql8:/home/dbdump.sql mysql/app/dbdump.sql
docker mysql/app/dbdump.sql mysql8-slave1:/home/dbdump.sql

从库-导入备份数据

 -- 从库
-- 从库还原
source /home/dbdump.sql

从库-查询状态

 -- 从库
-- 查询一下从库状态
show slave status;

-- 如果已经配置好,可以停止，再重置（如果没有则不需要重置）
stop slave;
reset slave;

从库-配置

 -- 从库
-- 配置从库 mysql5.6
CHANGE MASTER TO
  MASTER_HOST = '<主服务器的主机名>',
  MASTER_PORT = <主服务器的端口号>,
  MASTER_USER = '<复制账户用户名>',
  MASTER_PASSWORD = '<复制账户密码>',
  MASTER_LOG_FILE = '<主服务器的二进制日志文件名>',
  MASTER_LOG_POS = <主服务器的二进制日志位置>;

-- 示例
CHANGE MASTER TO
  MASTER_HOST = 'mysql8', -- 没有使用docker的填写ip即可
  MASTER_PORT = 3306 ,
  MASTER_USER = 'root',
  MASTER_PASSWORD = '123456',
  MASTER_LOG_FILE = 'binlog.000032', -- 上面主库查询出来的数据
  MASTER_LOG_POS = 157; -- 上面主库查询出来的数据

-- 示例 mysql8.0
-- GET_MASTER_PUBLIC_KEY=1, 这个是为了绕过SSL/HTTPS错误
CHANGE REPLICATION SOURCE TO
  GET_MASTER_PUBLIC_KEY=1,
  SOURCE_HOST = 'mysql8',
  SOURCE_PORT = 3306 ,
  SOURCE_USER = 'root',
  SOURCE_PASSWORD = '123456',
  SOURCE_LOG_FILE = 'binlog.000032', -- 上面主库查询出来的数据
  SOURCE_LOG_POS = 157; -- 上面主库查询出来的数据

从库-开启并查看状态

均为yes则异步同步开启成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 -- 从库
-- 开启从库
START SLAVE;

-- 查看从库配置
show slave status \G;

---

异步复制（GTID）

简化了查询binlog步骤和配置写binlog部分

GTID（Global Transaction Identifier）

• Mysql5.6引入了GTID
• GTID可以给事务分配全局唯一ID
• GTID方便了主从复制的配置，推荐打开
• GTID对主从切换、故障恢复也有很大意义

如何启动GTID

主从均要设置

 -- 主从库
[mysqld]
secure_file_priv=/home # mysqldump备份后的路径，你可以设置到你想要的目录
server-id = 1 # 配置服务器ID，2个数据库server-id不一致即可
log_bin = /var/lib/mysql/binlog # 开启binlog，目录/var/lib/mysql/就会有binlog.000001这样的文件

# 启动GTID
gtid_mode = no
enforce-gtid-consistency = true

# mysql5.6 半同步复制设置
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl-semi-sync-master-enabled = 1 rpl-semi-sync-slave-enabled = 1

# mysql8.0 半同步复制设置
plugin-load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so" rpl-semi-sync-source-enabled = 1 rpl-semi-sync-replica-enabled = 1

从库配置

 -- 从库
-- 停止
stop slave;

-- 配置从库
-- 示例 Mysql5.6
CHANGE MASTER TO
  MASTER_HOST = 'mysql8',
  MASTER_PORT = 3306 ,
  MASTER_USER = 'root',
  MASTER_PASSWORD = '123456',
  MASTER_AUTO_POSITION = 1;

-- 示例 Mysql8.0
-- GET_MASTER_PUBLIC_KEY=1, 这个是为了绕过SSL/HTTPS错误
CHANGE REPLICATION SOURCE TO
  GET_MASTER_PUBLIC_KEY=1,
  SOURCE_HOST = 'mysql8',
  SOURCE_PORT = 3306 ,
  SOURCE_USER = 'root',
  SOURCE_PASSWORD = '123456',
  SOURCE_AUTO_POSITION = 1;

-- 启动
START SLAVE;

-- 查看从库配置
show slave status \G;

---

半同步复制

半同步复制需要先完成上面异步复制的操作。

1.mysql.cnf配置

主库配置

 -- 主库
[mysqld]
secure_file_priv=/home # mysqldump备份后的路径，你可以设置到你想要的目录
server-id = 1 # 配置服务器ID，2个数据库server-id不一致即可
log_bin = /var/lib/mysql/binlog # 开启binlog，目录/var/lib/mysql/就会有binlog.000001这样的文件

# mysql5.6
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl-semi-sync-master-enabled = 1 rpl-semi-sync-slave-enabled = 1

# mysql8.0
plugin-load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so" rpl-semi-sync-source-enabled = 1 rpl-semi-sync-replica-enabled = 1

从库配置

 -- 从库
[mysqld]
secure_file_priv=/home # mysqldump备份后的路径，你可以设置到你想要的目录
server-id = 2 # 配置服务器ID，2个数据库server-id不一致即可
log_bin = /var/lib/mysql/binlog # 开启binlog，目录/var/lib/mysql/就会有binlog.000001这样的文件

# mysql5.6
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl-semi-sync-master-enabled = 1 rpl-semi-sync-slave-enabled = 1

# mysql8.0
plugin-load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so" rpl-semi-sync-source-enabled = 1 rpl-semi-sync-replica-enabled = 1

2.半同步复制配置

主库配置

 -- 主库
-- mysql 5.6
-- 启用插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 全局设置
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-----------分隔线--------------

-- mysql8.0
-- 启用插件
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so'; 

-- 全局设置
SET GLOBAL rpl_semi_sync_source_enabled = 1;

从库配置

 -- 从库
-- mysql 5.6
-- 启用插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 全局设置
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-----------分隔线--------------

-- mysql8.0
-- 启用插件
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

-- 全局设置
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

查看是否成功

 -- 主从均可查看
-- 查看是否开启半同步
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync%';

---

参考

• mysql文档：链接