Mac上用Docker搭建mysql主从

主节点

docker run -d --name mysql-master \
  --network mysql-net \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -v /Users/wgg/dev/docker/Apps/mysql/master/data:/var/lib/mysql \
  -v /Users/wgg/dev/docker/Apps/mysql/master/config:/etc/mysql/conf.d \
  -p 3316:3306 \
  mysql:8.0 \
  --server-id=1 \
  --log-bin=mysql-bin \
  --binlog-format=ROW \
  --binlog-do-db=testdb \
  --gtid-mode=ON \
  --enforce-gtid-consistency=ON

从节点

docker run -d --name mysql-slave \
--network mysql-net \
-e MYSQL_ROOT_PASSWORD=rootpassword \
-v /Users/wgg/dev/docker/Apps/mysql/slave/data:/var/lib/mysql \
-v /Users/wgg/dev/docker/Apps/mysql/slave/config:/etc/mysql/conf.d \
-p 3317:3306 \
mysql:8.0 \
--server-id=2 \
--log-bin=mysql-bin \
--binlog-format=ROW \
--gtid-mode=ON \
--enforce-gtid-consistency=ON \
--skip-slave-start

主从配置

• 主节点处理

docker exec -it mysql-master mysql -uroot -prootpassword

REATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replpassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看主服务器状态，记录File和Position值
SHOW MASTER STATUS;

• 从节点处理

docker exec -it mysql-slave mysql -uroot -prootpassword

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='replpassword',
MASTER_AUTO_POSITION=1;

START SLAVE;

-- 查看从服务器状态
SHOW SLAVE STATUS\G

主从验证

• 主节点

USE testdb;
CREATE TABLE test_table (id INT, name VARCHAR(100));
INSERT INTO test_table VALUES (1, 'Test Data');

select * from test_table;

SHOW MASTER STATUS;

SHOW PROCESSLIST;
 
SHOW SLAVE HOSTS;

• 从节点

 select * from test_table;

拓展配置

主节点配置同步的数据库，与忽略同步的数据库

• my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
binlog-do-db=testdb1
binlog-do-db=testdb2
binlog-do-db=testdb3
gtid-mode=ON
enforce-gtid-consistency=ON

• Docker

# 开启多个
--binlog-do-db=testdb1 \
--binlog-do-db=testdb2 \
--binlog-do-db=testdb3 \

# 忽略多个
--binlog-ignore-db=mysql \
--binlog-ignore-db=information_schema \
--binlog-ignore-db=performance_schema \
--binlog-ignore-db=sys