docker-compose構建 mysql 主從複製(讀寫分離)集群，此集群由3個MySQL實例組成，一個Master節點，2個Slave節點。Master可讀寫，Secondary可讀。集群之間會自動同步數據。
Master位於192.168.0.142服務器上， 兩個Slave位於192.168.0.11服務器上。
1. 下載MySQL鏡像
MySQL5.6是以朗尊軟件的的MySQL5.6鏡像作為標準。
docker pull mysql:5.7
docker pull mysql:8.0.17
2. docker-compose.yml
MySQL5.6的master
?
version:"3"
services:
### mysql container #########################################
mysql142:
container_name: mysql142
image: harbor.legendshop.cn/legendshop-public/mysql:5.6
restart: always
volumes:
- ./db/mysql/data:/var/lib/mysql
- ./db/mysql/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
ports:
- 3306:3306
MySQL5.7的Master配置
?
version:"3"
services:
### mysql container #########################################
mysql5.7-142:
container_name: mysql5.7-142
image: mysql:5.7
restart: always
volumes:
- ./db/mysql/data:/var/lib/mysql
- ./db/mysql/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- 3376:3306
MySQL8.0的Master配置
?
version:"3"
services:
### mysql container #########################################
mysql8-142:
container_name: mysql8-142
image: mysql:8.0.17
restart: always
volumes:
- ./db/mysql/data:/var/lib/mysql
- ./db/mysql/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ:"Asia/Shanghai"
command:
--max_allowed_packet=128M
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- 3386:3306
注意編碼是採用utf8mb4，mysql的配置項在/db/mysql/conf.d目錄下，該目錄下增加mysql.cnf文件
MySQL5.6的配置
?
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
lower_case_table_names=1
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake=FALSE
log-bin=mysql-bin
server-id=1
max_connections=2000
MySQL5.7的配置
?
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
transaction-isolation=READ-COMMITTED
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
lower_case_table_names=1
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
log-bin=mysql-bin
server-id=1
relay-log =relay-bin
relay-log-index = slave-relay-bin.index
max_connections=2000
innodb_log_file_size = 512M
MySQL8.0的配置
?
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
default-time-zone='+08:00'
character-set-server = utf8mb4
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
log-bin=mysql-bin
server-id=1
relay-log =relay-bin
relay-log-index = slave-relay-bin.index
記得要填寫上server-id，用於做為集群的服務ID
以下對Slave節點的docker-compose配置羅列出來。
MySQL5.6的SLAVE配置
?
version:"3"
services:
### mysql container #########################################
mysql101:
container_name: mysql101
image: harbor.legendshop.cn/legendshop-public/mysql:5.6
restart: always
volumes:
- ./db/mysql101/data:/var/lib/mysql
- ./db/mysql101/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
ports:
- 3306:3306
### redis container #########################################
mysql102:
container_name: mysql102
image: harbor.legendshop.cn/legendshop-public/mysql:5.6
restart: always
volumes:
- ./db/mysql102/data:/var/lib/mysql
- ./db/mysql102/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
ports:
- 3307:3306
MySQL5.7的配置
?
version:"3"
services:
### mysql container #########################################
mysql5.7-101:
container_name: mysql5.7-101
image: mysql:5.7
restart: always
volumes:
- ./db/mysql101/data:/var/lib/mysql
- ./db/mysql101/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
ports:
- 3376:3306
### redis container #########################################
mysql5.7-102:
container_name: mysql5.7-102
image: mysql:5.7
restart: always
volumes:
- ./db/mysql102/data:/var/lib/mysql
- ./db/mysql102/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: xxxxxx
TZ: Asia/Shanghai
command:
--max_allowed_packet=128M
ports:
- 3377:3306
MySQL8.0的配置
?
version:"3"
services:
### mysql container #########################################
mysql8-101:
container_name: mysql8-101
image: mysql:8.0.17
restart: always
volumes:
- ./db/mysql101/data:/var/lib/mysql
- ./db/mysql101/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: Ls@12345678@
TZ:"Asia/Shanghai"
command:
--max_allowed_packet=128M
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- 3386:3306
### backup mysql container #########################################
mysql8-102:
container_name: mysql8-102
image: mysql:8.0.17
restart: always
volumes:
- ./db/mysql102/data:/var/lib/mysql
- ./db/mysql102/conf.d:/etc/mysql/conf.d
restart: always
environment:
MYSQL_ROOT_PASSWORD: Ls@12345678@
TZ:"Asia/Shanghai"
command:
--max_allowed_packet=128M
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- 3387:3306
對應SLAVE的mysql.conf的配置
MySQL5.6SLAVE第一個節點配置
?
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
lower_case_table_names=1
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
log-bin=mysql-bin
server-id=101
read_only=1
MySQL5.6SLAVE第二個節點的配置
?
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
lower_case_table_names=1
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
log-bin=mysql-bin
server-id=102
read_only=1
Slave節點配置為read only模式， 由於篇幅問題這裡不再羅列mysql5.7,5.8的配置。
3. 配置 MySQL 主從複製
在master和2個slave都啟動完畢的狀態下， 進入master的mysql環境。
1. 首先連接 master 服務器，查看數據庫狀態
SHOW MASTER STATUS
記錄master上的日誌文件名、position兩個值需要記住後面要用。
2. 在master中創建用戶
MySQL5.6 5.7的寫法：
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY 'xxxxxx';
或者IP地址
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.11' IDENTIFIED BY 'xxxxxx';
在MySQL8下創建賬號、分配權限的做法
CREATE USER 'backup'@'%' IDENTIFIED BY 'xxxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' WITH GRANT OPTION;
3. 配置slave
讓slave連接master，並開始重做master二進制日誌中的事件。master_log_file的值為上面的日誌文件名；master_log_pos為position的值
CHANGE MASTER TO
MASTER_HOST='192.168.0.142',
MASTER_USER='backup',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=518,
MASTER_PORT=3317,
MASTER_PASSWORD='xxxxxx';
查看slave的狀態
SHOW SLAVE STATUS
Slave_IO_State狀態位Waiting for master to send event為正常狀態。
Slave_IO_Running 正常狀態位Yes
Slave_SQL_Running正常狀態位Yes
4. 問題處理
4.1 Slave_SQL_Running和Slave_IO_Running狀態位No時無法通知Slave，執行以下腳本重新讓slave鏈接master
?
STOP SLAVE ;
SETGLOBALSQL_SLAVE_SKIP_COUNTER=1; //如果是Slave_SQL_Running：no：
CHANGE MASTERTOMASTER_LOG_FILE='xxxx', MASTER_LOG_POS=xxxx; //如果是slave_io_running：no,根據master的值重新執行一次
START SLAVE;
SHOW SLAVE STATUS //直到slave狀態位正常才行
4.2. Relay log 導致複製啟動失敗
新版本使用表來代替原來的文件，主要為了crash-safe replication，從而大大提高從庫的可靠性。為了保證意外情況下從庫的可靠性，
mysql.slave_master_info和mysql.slave_relay_log_info表必須為事務性的表，從5.6.6起，這些表默認使用InnoDB存儲引擎。在5.6.5及之前的版本默認使用MyISAM引擎，可用下面語句進行轉換：
ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
reset slave乾的那些事：
1、刪除slave_master_info ，slave_relay_log_info兩個表中數據；
2、刪除所有relay log文件，並重新創建新的relay log文件；
3、不會改變gtid_executed 或者 gtid_purged的值
下面解決問題：
dba:(none)> reset slave;
Query OK, 0 rows affected (0.00 sec)
1
dba:(none)> change master to ......
1
2
dba:(none)> start slave;
Query OK, 0 rows affected (0.00 sec)
到這裡問題解決了。
【經驗】：以後用冷備份恢復實例後，在啟動slave前，先進行reset slave清空下以前的舊信息。
4.3. 設置slave1和slave2為只讀
在配置文件my.cnf中的mysqld中配置read_only=1
注意：read_only=1只讀模式，可以限定普通用戶進行數據修改的操作，但不會限定具有super權限的用戶（如超級管理員root用戶）的數據修改操作。
如果想保證super用戶也不能寫操作，就可以就需要執行給所有的表加讀鎖的命令 “flush tables with read lock;”。這樣使用具有super權限的用戶登錄數據庫，想要發生數據變化的操作時，也會提示表被鎖定不能修改的報錯。
我們的一般做法是，給從庫分配一個普通用戶。
將slave數據庫read-only=1設置只讀後，在master執行GRANT USAGE ON *.* TO 'user01'@'localhost' IDENTIFIED BY'123456' WITH GRANT OPTION;（這裡要區別上面給從庫分配複製權限的寫法，這裡分配的是usage權限， 後面多了with grant option）
創建一個普通用戶，然後用普通用戶登錄從庫，執行操作會報錯。切換到root用戶後還是可以進行增刪改查的。
5. 登錄master數據庫測試
新增用戶和授權
1. 建立數據庫
CREATE DATABASE legendshop_test;
2. 創建用戶
grant all privileges on legendshop_sr1.* to legendshop_sr1@'%' identified by 'legendshop_sr1123';
在主庫做的動作同樣在從庫中會有相同的數據，否則就是無法同步數據了。
