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 狀態(tài)（注意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(雙主備份網(wǎng)絡必須聯(lián)通或者同一機房)
1、master my.cnf 測試不起效
[mysqld]
# [必須]服務器唯一ID
server-id=1
#開啟 GTID
gtid-mode=on
enforce-gtid-consistency=true
# 復制過濾：也就是指定哪個數(shù)據(jù)庫不用同步（mysql庫一般不同步）
binlog-ignore-db=mysql
# 主從復制的格式（mixed,statement,row，默認格式是statement。建議是設置為row，主從復制時數(shù)據(jù)更加能夠統(tǒng)一）
binlog-format=row
# 跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
# 如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致
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
<
