mysql 主从搭建，读写分离

原创已于 2022-08-12 11:40:39 修改 · 441 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2022-08-12 11:37:38 首次发布

本文介绍MySQL基于日志点及GTID的主从复制配置方法，包括服务账号创建与授权、主从节点配置、数据同步及链路启动等关键步骤。

基于日志点复制

最早支持。bug较少

对查询sql没有任何限制

缺点：主节点挂了以后新的日志节点难以获取

在主DB上建立服务账号并授权 ,该账号只能在从服务器登录

#创建用户
CREATE USER 'repl'@'192.168.2.115' IDENTIFIED BY '123123'
#授权
grant REPLICATION SLAVE on *.* to 'repl'@'192.168.2.115';

主节点配置

#开启binlog
log-bin=mysql-bin
#日志id,整个集群必须唯一
server-id=1921682114
#日志格式
binlog_format=mixed
#启动日志事务，防止极端情况下日志与数据不一致情况
innodb_support_xa=NO

从节点配置

#开启binlog
log-bin=mysql-bin
#日志id,整个集群必须唯一
server-id=1921682115
#中继日志名称，数据库Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入到中继日志中，然后在Slave数据库执行修改操作。这就是中继日志Relay Log。
relay_log=mysql-relay-bin
#是否把中继日志执行的sql记录到二进制日志中
log_slave_updates=on
#是否只读该数据库
read_only=on

初始化数据库，将主数据库数据复制到从数据库中，并且获取日志点

mysqldump (会对备份表加锁)
```
mysqldump --single-transaction --master-data --triggers --routines --databases  test  >>all.sql -uroot -proot
```
- –single-transaction 事务隔离保证备份的数据是一直的
  
  ① 在开始的时候把该 session 的事务隔离级别设置成 repeatable read ；
  
  ② 然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）
- –master-data
  
  该参数用于将主库的bin-log信息写入到dump文件中，即当前文件名(filename)和位置（position），用于主从复制的搭建
- –triggers
  
  包含表的触发器
- –routines
  
  存储过程
xtrabackup --slave-info

mysql -uroot -proot < all.sql

从节点启动服务链路

在mysqldump 导出文件中找到 CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000004’, MASTER_LOG_POS=1091;

#配置链路信息
change master to master_host='master_ip',
master_user='repl',
master_password='root',
master_log_file='mysql-bin.000004',
master_log_pos=1091;
#启动链路
start slave;

通过 show slave status\G; 查看链路信息

基于GTID复制

5.6 版本开始，GTID即全局事务id,create select … 语句无法使用。5.6早期的版本bug较多。建议在5.6后期的版本以及5.7以上的版本中使用

在主DB上建立服务账号并授权 ,该账号只能在从服务器登录

#创建用户
CREATE USER 'repl'@'192.168.2.115' IDENTIFIED BY '123123'
#授权
grant REPLICATION SLAVE on *.* to 'repl'@'192.168.2.115';

主节点配置

#开启binlog
log-bin=mysql-bin
#日志id,整个集群必须唯一
server-id=1921682114
#日志格式
binlog_format=mixed
#其实日志事务，防止极端情况下日志与数据不一致情况
innodb_support_xa=NO
#启用gtid
gtid_mode=on

enforce-gtid-consistency
#5.7以下必须使用这个配置
log-slave-updates=on

从库配置

#日志id,整个集群必须唯一
server-id=1921682115
#中继日志名称，数据库Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入到中继日志中，然后在Slave数据库执行修改操作。这就是中继日志Relay Log。
relay_log=mysql-relay-bin
#启用gtid
gtid_mode=on
enforce-gtid-consistency

log-slave-updates=on
#建议,将复制节点信息存储到表中
master_info_repository=TABLE
relay_log_info_repository=TABLE

同步主从库数据

mysqldump --single-transaction --master-data=2 --triggers --routines --databases  test  >>all.sql -uroot -proot

mysql -uroot -proot < all.sql

配置链路

#配置链路信息
change master to master_host='192.168.2.114',
master_user='repl',
master_password='root',
master_auto_position=1;
#启动链路
start slave;