mysql主从搭建不使用gtid,搭建使用GTID的mysql主从复制

环境

在虚拟机中使用docker-compose搭建一主两从mysql实例

mysql配置文件

mysql source服务器配置

开启gtid模式, binlog-format必须使用row

[mysqld] server-id = 1

log-bin = source-binlog

gtid-mode=on

enforce-gtid-consistency=on

binlog-format=row

default_authentication_plugin=mysql_native_password

[client]

# 客户端可以显示中文

default-character-set = utf8

mysql replica服务器配置

配置两份server-id为2和3

[mysqld] server-id = 2

gtid-mode=on

enforce-gtid-consistency=on

log-bin=replica-binlog

log-slave-updates=1

binlog-format=row

skip-slave-start=1

default_authentication_plugin=mysql_native_password

[client]

# 客户端可以显示中文

default-character-set = utf8

docker-compose配置

创建目录结构如下: conf文件夹放置配置文件, data文件夹作为容器的数据挂载

mysql-replication/

├── docker-compose.yml

├── replica1

│ ├── conf

│ └── data

├── replica2

│ ├── conf

│ └── data

└── source

├── conf

└── data

docker-compose配置文件

version: '3'

# 网络

networks:

mysql-net:

external: true

services:

source-db:

image: mysql

container_name: source-db

# 挂载配置文件和数据

volumes:

- ./source/conf:/etc/mysql/conf.d

- ./source/data:/var/lib/mysql

networks:

- mysql-net

ports:

- 3307:3306

environment:

- MYSQL_ALLOW_EMPTY_PASSWORD=YES

replica-db1:

image: mysql

container_name: replica-db1

volumes:

- ./replica1/conf:/etc/mysql/conf.d

- ./replica1/data:/var/lib/mysql

networks:

- mysql-net

ports:

- 3308:3306

environment:

- MYSQL_ALLOW_EMPTY_PASSWORD=yes

replica-db2:

image: mysql

container_name: replica-db2

volumes:

- ./replica2/conf:/etc/mysql/conf.d

- ./replica2/data:/var/lib/mysql

networks:

- mysql-net

ports:

- 3309:3306

environment:

- MYSQL_ALLOW_EMPTY_PASSWORD=yes

启动3个mysql实例

docker-compose up -d

配置mysql主从

进入source-db服务器中, 创建用户, 用于replica服务器连接

# 创建了用户:repl, 设置密码

mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

进入两个replica-db服务器中, 连接到source-db, 不需要指定binlog文件和position了

# host就是容器名, docker网络会自动解析, 使用的端口是3306, 不是容器对外部开放的端口

mysql> CHANGE MASTER TO

-> MASTER_HOST='source',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='password',

-> MASTER_AUTO_POSITION = 1;

然后开启主从复制

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

# 查看信息, Slave_IO_Running和Slave_SQL_Running两个都是yes即连接成功

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: source

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 972

Relay_Log_File: 749ff0e111d1-relay-bin.000002

Relay_Log_Pos: 1137

Relay_Master_Log_File: binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes