MySQL双主基于GTID复制方案
title: "MySQL双主基于GTID复制方案"
date: "2021-09-01"
isShowComments: true
tags:
- "数据库"
categories: - "数据库"
时间：2021/09/07
作者：@Yao Yao
操作系统：CentOS Linux release 7.8.2003 (Core)
master-1：192.168.200.50
master-2：192.168.200.51
基本操作
注: 所有节点都需要执行
// 关闭防火墙
systemctl stop firewalld
// 关闭Selinux
vim /etc/sysconfig/selinux
SELINUX=disabled
// 临时关闭Selinux
setenforce 0
二进制安装数据库
注: 所有节点都需要执行
安装数据库
// 安装数据库
mkdir /usr/local/mysql
// 解压缩
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql --strip=1
// 创建用户及用户组
groupadd mysql
useradd mysql -g mysql -s /sbin/nologin
// 创建数据库数据目录
mkdir /usr/local/mysql/data
// 授权
chown -R mysql.mysql /usr/local/mysql
// 复制启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
// 设置开机启动
chkconfig on mysqld
初始化数据库
// 初始化数据库 此处会生成默认root密码
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize
// 会打印一下日志 记住保存好密码
2021-09-01T11:54:32.248336Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-01T11:54:33.099720Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-01T11:54:33.236031Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-01T11:54:33.310273Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5f55799d-0b1b-11ec-9475-005056a53fc6.
2021-09-01T11:54:33.313554Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-01T11:54:34.641583Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-01T11:54:34.641629Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-01T11:54:34.642431Z 0 [Warning] CA certificate ca.pem is self signed.
2021-09-01T11:54:34.944690Z 1 [Note] A temporary password is generated for root@localhost: **urG/hV3t0jo?**
双主配置
节点1配置数据库
// 打开/etc/my.cnf 将以下内容添加进去
vim /etc/my.cnf
[mysqld]
# 数据目录
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql/
socket=/usr/local/mysql/data/mysql.sock
user=mysql
# 端口
port=13306
character-set-server=utf8
default_storage_engine = InnoDB
lower_case_table_names = 1
# 服务ID 唯一 不同节点分配不同ID
server_id = 1
# 打开Mysql日志 格式为二进制
log-bin = mysql-bin
# 开启基于GTID的复制
gtid_mode = on
enforce_gtid_consistency = on
binlog_format = row
log-slave-updates = 1
skip_slave_start = 1
# 与节点数相同
auto-increment-increment = 2
# 自增
auto-increment-offset = 1
symbolic-links=0
[client]
port = 13306
default-character-set=utf8
socket=/usr/local/mysql/data/mysql.sock
[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/database.pid
节点1启动数据库
// 启动数据库
/etc/init.d/mysqld start
节点1初始化密码
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 修改root密码
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql>flush privileges;
mysql>exit;
节点1创建从库同步用户
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 创建同步用户
mysql>GRANT REPLICATION SLAVE ON *.* TO master1@'%' IDENTIFIED BY '123456';
mysql>flush privileges;
mysql>exit;
节点2配置数据库
// 打开/etc/my.cnf 将以下内容添加进去
vim /etc/my.cnf
[mysqld]
# 数据目录
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql/
socket=/usr/local/mysql/data/mysql.sock
user=mysql
# 端口
port=13306
character-set-server=utf8
default_storage_engine = InnoDB
lower_case_table_names = 1
# 服务ID 唯一 不同节点分配不同ID
server_id = 2
# 打开Mysql日志 格式为二进制
log-bin = mysql-bin
# 开启基于GTID的复制
gtid_mode = on
enforce_gtid_consistency = on
binlog_format = row
log-slave-updates = 1
skip_slave_start = 1
# 与节点数相同
auto-increment-increment = 2
# 自增
auto-increment-offset = 2
symbolic-links=0
[client]
port = 13306
default-character-set=utf8
socket=/usr/local/mysql/data/mysql.sock
[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/database.pid
节点2启动数据库
// 启动数据库
/etc/init.d/mysqld start
节点2初始化密码
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 修改root密码
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql>flush privileges;
mysql>exit;
节点2创建从库同步用户
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 创建同步用户
mysql>GRANT REPLICATION SLAVE ON *.* TO master2@'%' IDENTIFIED BY '123456';
mysql>flush privileges;
mysql>exit;
双主同步配置
节点2开启同步节点1
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 创建同步
mysql>change master to master_host='192.168.200.50',master_user='master1',master_password='123456',master_port=13306,master_auto_position=1;
// 开始同步
mysql>start slave;
// 查看同步状态
mysql>show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.50
Master_User: master1
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: work-02-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes // 必须为Yes 表示当前线程会连接Master节点的Bin-Log 并同步到本地中继日志中
Slave_SQL_Running: Yes // 必须为Yes 表示从本地中继日志中读取数据 恢复到对应位置
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 670
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9e07b87e-0b1e-11ec-8609-005056a53fc6
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
节点1开启同步节点2
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
// 创建同步
mysql>change master to master_host='192.168.200.51',master_user='master2',master_password='123456',master_port=13306,master_auto_position=1;
// 开始同步
mysql>start slave;
// 查看同步状态
mysql>show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.51
Master_User: master2
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 759
Relay_Log_File: work-01-relay-bin.000002
Relay_Log_Pos: 862
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 759
Relay_Log_Space: 1071
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: e9ff5da2-0b1e-11ec-b665-005056a5c44c
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: e9ff5da2-0b1e-11ec-b665-005056a5c44c:1-2
Executed_Gtid_Set: 9e07b87e-0b1e-11ec-8609-005056a53fc6:1-5,
e9ff5da2-0b1e-11ec-b665-005056a5c44c:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
对IP进行授权访问
// 在任意一个节点上执行
// 登陆数据库
/usr/local/mysql/bin/mysql -uroot -p --port=13306
mysql>grant all on *.* to 'root'@'192.168.200.50' identified by '123456' with grant option;
mysql>grant all on *.* to 'root'@'192.168.200.51' identified by '123456' with grant option;
mysql>flush privileges;
mysql>exit;
测试数据库
注：任意一台服务器/2个节点其中一个 需要确保已经授权
// 登陆数据库 如果登陆成功 代表数据库集群正常运行
/usr/local/mysql/bin/mysql -uroot -p -h 192.168.200.50 --port=13306
完整数据库测试
// 测试数据库创建
create database test1;
// 测试数据表创建
use test1;
create table user(
id int auto_increment primary key,
user varchar(20) not null,
sex varchar(20) not null,
birthday datetime
)
// 测试数据插入 不带ID 让Mysql自增
insert into user(user,sex,birthday) values('耀耀','男','1977-09-01');
// 查看数据
select * from user;
+----+--------+-----+---------------------+
| id | user | sex | birthday |
+----+--------+-----+---------------------+
| 2 | 张三 | 男 | 1977-09-01 00:00:00 |
| 4 | 张三 | 男 | 1977-09-01 00:00:00 |
| 6 | 张三 | 男 | 1977-09-01 00:00:00 |
+----+--------+-----+---------------------+
发现是2倍数递增
// 测试数据插入 携带ID
insert into user(id,user,sex,birthday) values(7,'耀耀','男','1977-09-01');