1、docker-compose编排
version: '3.8'
services:
mysql-master:
image: mysql:8.0.22
container_name: mysql-master
restart: always
command:
--default-authentication-plugin=mysql_native_password
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: 123456
volumes:
- ./mysql/master/data:/var/lib/mysql
- ./mysql/master/cnf/my.cnf:/etc/my.cnf
#- /etc/localtime:/etc/localtime:ro
ports:
- "3306:3306"
mysql-slave:
image: mysql:8.0.22
container_name: mysql-slave
restart: always
command:
--default-authentication-plugin=mysql_native_password
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: 123456
volumes:
- ./mysql/slave/data:/var/lib/mysql
- ./mysql/slave/cnf/my.cnf:/etc/my.cnf
#- /etc/localtime:/etc/localtime:ro
ports:
- "3307:3306"
links:
- mysql-master
2、mysql-master查看(基于log日志，不推荐，每次docker重启日志会改变名称)
1、进入mysql container
docker exec -it mysql-master bash
2、登录mysql,并查看master 状态（注意file 与 position 值)
mysql -uroot -p
mysql> show master status
-> ;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
3、mysql-slave 设置(基于log日志，不推荐，每次docker重启日志会改变名称)
docker exec -it mysql-slave bash
mysql -uroot -p
mysql> change master to master_host='mysql-master',master_user='root',master_password='123456',master_log_file='binlog.000002',master_log_pos=156;
Query OK, 0 rows affected, 2 warnings (0.44 sec)
mysql> start slave; (遇到错误 stop掉 再reset slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status\G
4、基于GTID(双主备份网络必须联通或者同一机房)
1、master my.cnf 测试不起效
[mysqld]
# [必须]服务器唯一ID
server-id=1
#开启 GTID
gtid-mode=on
enforce-gtid-consistency=true
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog-format=row
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave-skip-errors=1062
手动开启GTID:（无效)
mysql> set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.09 sec)
mysql> show global variables like 'gtid_mode';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| gtid_mode | OFF_PERMISSIVE |
+---------------+----------------+
1 row in set (0.00 sec)
2、docker-compose.yml
version: '3.8'
services:
mysql-master:
image: mysql:8.0.22
container_name: mysql-master
restart: always
command:
--default-authentication-plugin=mysql_native_password
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--server_id=1
--gtid_mode=ON
--enforce-gtid-consistency=ON
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: 123456
volumes:
- ./mysql/master/data:/var/lib/mysql
- ./mysql/master/cnf/conf.d:/etc/mysql/conf.d
#- /etc/localtime:/etc/localtime:ro
ports:
- "3306:3306"
mysql-slave:
image: mysql:8.0.22
container_name: mysql-slave
restart: always
command:
--default-authentication-plugin=mysql_native_password
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--server_id=2
--gtid_mode=ON
--enforce-gtid-consistency=ON
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: 123456
volumes:
- ./mysql/slave/data:/var/lib/mysql
- ./mysql/slave/cnf/conf.d:/etc/mysql/conf.d
#- /etc/localtime:/etc/localtime:ro
ports:
- "3307:3306"
depends_on:
- mysql-master
从库设置：
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.33 sec)
mysql> change master to MASTER_HOST='192.168.31.88',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show slave status\G;
change master to MASTER_HOST='192.168.31.88',MASTER_PORT=3307,MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1,GET_MASTER_PUBLIC_KEY=1
<
