mysql gtid 双主_mysql5.6版本基于GTID双主-docker方式-新环境没有数据

#(0)环境规划

mysql版本:5.6

master01 : 192.168.19.131

master02 : 192.168.19.132

#(1)安装docker和docker-compose

curl -o /etc/yum.repos.d/docker-ce.repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

yum install docker-ce-17.06.0.ce-1.el7.centos.x86_64 -y

systemctl enable docker

systemctl start docker

cat > /etc/docker/daemon.json <

{

"registry-mirrors": ["https://ui5lsypg.mirror.aliyuncs.com"]

}

EOF

sudo systemctl daemon-reload

sudo systemctl restart docker

sudo curl -L https://mirrors.aliyun.com/docker-toolbox/linux/compose/1.21.2/docker-compose-Linux-x86_64 > /usr/local/bin/docker-compose

sudo chmod +x /usr/local/bin/docker-compose

docker-compose --version

#(2)准备目录

mkdir /mysql3307/{data,conf} -pv

mkdir /temp/docker/compose/ -pv

#(3)master01 和 master02 的配置文件

注意修改server-id和 auto_increment_offset

#cat /mysql3307/conf/my.cnf

[mysqld]

#basic

character-set-server=utf8

lower_case_table_names=1

skip-name-resolve

max_connections = 10000

user=mysql

port=3306

datadir=/mysql3307/data

socket=/var/lib/mysql/mysql.sock

pid-file=/var/run/mysql/mysql.pid

log_error=/var/log/mysql/error.log

default_time_zone = '+8:00'

explicit_defaults_for_timestamp=true

#slow log

slow_query_log = 1

long_query_time = 1

#lock

innodb_lock_wait_timeout=50

lock_wait_timeout=50

innodb_autoinc_lock_mode=2

#innodb

innodb_file_per_table=ON

innodb_buffer_pool_size=128M

#redo log

innodb_flush_log_at_trx_commit=1

innodb_support_xa=ON

innodb_log_file_size=16M

innodb_log_files_in_group=2

innodb_log_group_home_dir=./

#binlog

sync_binlog=1

binlog_format=row

max_binlog_size=1073741082

log-bin=master01

expire_logs_days = 7

#Replication availability

master-info-repository=TABLE

relay-log-info-repository=TABLE

relay_log_recovery=1

sync_relay_log=1

sync_relay_log_info=1

#flush

innodb_io_capacity=200

innodb_flush_neighbors=0

#gtid

server-id=1

gtid-mode=on

enforce-gtid-consistency=true

auto_increment_offset=1

auto_increment_increment=2

log-slave-updates=true

#(4)compose文件

#cat /temp/docker/mysql3307/mysql3307.yaml

version: '2.2'

services:

mysql3307:

hostname: mysql3307

container_name: mysql3307

restart: always

image: registry.cn-hangzhou.aliyuncs.com/wangfang-dev/mysql:5.6

ports:

- 3307:3306

volumes:

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

- /mysql3307/data:/var/lib/mysql

environment:

MYSQL_ROOT_PASSWORD: 123456

cpus: 1

mem_limit: 2048m

#(5)启动容器

docker-compose -f /temp/docker/mysql3307/mysql3307.yaml up -d

两台容器正常启动成功

#(6)在master01上配置复制权限

docker exec -it mysql3307 bash

mysql -uroot -p123456

grant replication slave,replication client on *.* to 'rep'@'%' identified by 'rep';

#(7)在master02发起连接以及验证

docker exec -it mysql3307 bash

mysql -uroot -p123456

change master to master_host='192.168.19.131',master_user='rep',master_password='rep',master_port=3307,master_auto_position=1;

start slave;

show slave status\G

#(8)在master01上发起连接以及验证

docker exec -it mysql3307 bash

mysql -uroot -p123456

change master to master_host='192.168.19.132',master_user='rep',master_password='rep',master_port=3307,master_auto_position=1;

start slave;

#(9)分别在master01和master02 进行dml, dcl语句验证

master01上

master02上

master01上

master02上

master01上