centos 7 搭建 mysql 数据库双主复制

centos 7 搭建 mysql 数据库双主复制

简介

本次测试主要使用两台机器搭建两台 MySQL 互为主从，提高高可用性

如果搭建主从可参考：一主一从

MySQL 主从复制原理：

1. 在每个事务更新数据完成之前，Master 都会在二进制日志记录这些改变；写入二进制日志完成后，Master 通知存储引擎提交事务
2. Slave 将 Master 的 Binary log 复制到其中继日志：首先，Slave 开始一个工作线程—— I/O thread 线程，I/O 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process ；Binlog dump process 从 Master 的二进制日志中读取时间，如果已经跟上 Master，它会睡眠并等待 Master 产生新的时间；I/O 线程将做这些事件写入到从库的中继日志
3. SQL slave thread（SQL 从线程） 处理该过程的最后一步：SQL 线程从中继日志读取事件，并重做中继日志中的时间从而更新 Slave 的数据，使其与 Master 中的数据一致；只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小
4. 复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作

1. 环境准备

主机名	IP	系统版本信息	MySQL 版本信息
mysql01	192.168.169.100	CentOS Linux release 7.9.2009 (Core)	mysql-5.7.38-el7-x86_64
mysql02	192.168.169.101	CentOS Linux release 7.9.2009 (Core)	mysql-5.7.38-el7-x86_64

2. 安装 MySQL

参考：

源码安装MySQL

yum 安装MySQL

本次安装MySQL使用源码安装

3. 修改配置文件

1️⃣ mysql01 配置文件如下

[mysql]

# 是否自动补全
no-auto-rehash

# 修改命令行链接mysql时的提示符
prompt = '\\u@\\h:\\p \\R:\\m:\\s [\\d]> '


[mysqld]
# DO NOT MODIFY, Universe will generate this part
# 端口
port = 3306

# 主备 id，注意两个主备 id 不能重复
server_id = 1

# 软件目录
basedir = /usr/local/mysql

# 数据目录
datadir = /usr/local/mysql/data

# socket 目录，这里自定义配置 sock 目录的情况下，前面在安装的时候最好是指定 --datadir=/usr/local/mysql/data  mysql的安装目录，不然默认是 /tmp/mysql.sock
socket=/usr/local/mysql/data/mysql.sock

# 开启二进制同步
log-bin=mysql-bin

# 自增长字段增量值
auto-increment-increment=2
# # 自增长字段初始值为1，保证不同节点的自增值不会重复
auto-increment-offset=1

#添加，允许从服务器更新二进制日志
log-slave-updates=true

2️⃣ mysql02 配置文件如下

[mysql]

# 是否自动补全
no-auto-rehash

# 修改命令行链接mysql时的提示符
prompt = '\\u@\\h:\\p \\R:\\m:\\s [\\d]> '


[mysqld]
# DO NOT MODIFY, Universe will generate this part
# 端口
port = 3306

# 主备 id，注意两个主备 id 不能重复
server_id = 2

# 软件目录
basedir = /usr/local/mysql

# 数据目录
datadir = /usr/local/mysql/data

# socket 目录，这里自定义配置 sock 目录的情况下，前面在安装的时候最好是指定 --datadir=/usr/local/mysql/data  mysql的安装目录，不然默认是 /tmp/mysql.sock
socket=/usr/local/mysql/data/mysql.sock

# 开启二进制同步
log-bin=mysql-bin

# 自增长字段增量值
auto-increment-increment=2
# # 自增长字段初始值为1，保证不同节点的自增值不会重复
auto-increment-offset=2

#添加，允许从服务器更新二进制日志
log-slave-updates=true

3️⃣ 重启数据库

service mysql restart

4. 创建用于主从复制的账号

注意：在两个 MySQL 上都需要创建

# 创建用户
CREATE USER 'rep1'@'%';

# 为这个用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.*  TO  'rep1'@'%'  identified by 'Abcd@1234';

5. 查看两个节点信息

root@localhost:mysql.sock 14:37:28 [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      645 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)


字段含义说明：
		file						从哪个文件开始推送日志文件
		position				从文件的哪个位置开始推送日志文件

6. 建立主从关系

主从复制只能复制开启主从复制之后的记录，开启前要保证各节点数据一致

1️⃣ mysql01 配置

-- 先锁表，避免同步时有事务提交
flush tables with read lock;

--  配置同步
CHANGE MASTER TO MASTER_HOST='192.168.169.101',MASTER_USER='rep1',MASTER_PASSWORD='Abcd@1234',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=645;

-- 开始同步
start slave;

-- 查看同步状态，Slave_IO_Running 和 Slave_SQL_Running 都为Yes表示配置成功
-- IO 线程是用来读取二进制日志并写入中继日志的线程
-- SQL 线程是读取中继日志，并将日志信息反馈给 slave，从而进行更新数据
show slave status\G

-- 解除锁定
unlock tables;

• 参数解析

参数	含义
MASTER_HOST=‘192.168.169.101’	配置主机 ip 地址，互为主从即为另外一台机器 ip
MASTER_USER=‘rep1’	用户主从复制的用户名
MASTER_PASSWORD=‘Abcd@1234’	用于主从复制的密码
MASTER_LOG_FILE=‘mysql-bin.000001’	binlog 文件名，在当前服务器配置的主服务器中使用 show master status;
MASTER_LOG_POS=645	主从复制的起始点，在当前服务器配置的主服务器中使用 show master status;

2️⃣ mysql02 配置

基本配置和 mysql01 差不多，主要注意 ip 地址的变化

-- 先锁表，避免同步时有事务提交
flush tables with read lock;

--  配置同步
CHANGE MASTER TO MASTER_HOST='192.168.169.100',MASTER_USER='rep1',MASTER_PASSWORD='Abcd@1234',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=645;

-- 开始同步
start slave;

-- 查看同步状态，Slave_IO_Running 和 Slave_SQL_Running 都为Yes表示配置成功
-- IO 线程是用来读取二进制日志并写入中继日志的线程
-- SQL 线程是读取中继日志，并将日志信息反馈给 slave，从而进行更新数据
show slave status\G

-- 解除锁定
unlock tables;

7. 测试

1️⃣ 在 mysql01 新建数据库 demo

在 mysql01 上创建的 demo 数据库，在 mysql02 上就可以直接查询到

# 创建数据库，但是未指定字符集
create database demo;

# 指定数据库字符集
ALTER database demo DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2️⃣ 在 mysql02 上使用 demo 数据库实例新建表并插入数据

# 创建 t_student 表
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);

# 插入测试数据
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');

insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');

insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ll@126.com');

3️⃣ 在 mysql01 上查看表中数据

# 查看 demo 数据库中的 t_student 表中数据
select * from demo.t_student;