docker-compose + mysql8.x 主從資料庫設定

0.準備（略過docker的安裝與映象拉取）

docker / docker-compose 安裝

拉取 mysql 8.x

1. master和slave的mysql設定

master:

[mysqld]
server-id=11118
log-bin=mysql-bin

slave:

[mysqld]
log-bin=mysql-bin
server-id=11105

注：兩個 server-id 一定要不一樣

2. docker-compose 檔案設定

version: "3"

services:
  db:
    image: mysql
    ports:
      - "3307:3306"
    volumes:
      - ./dockerMysql/master/db:/var/lib/mysql
      - ./dockerMysql/master/cnf:/etc/mysql/conf.d
    environment:
      - MYSQL_DATABASE=siemens
      - MYSQL_ROOT_PASSWORD=123456
    networks:
      mynet-tier::
        ipv4_address: 172.16.238.2
  slave:
    image: mysql
    ports:
      - "3308:3306"
    volumes:
      - ./dockerMysql/slave/db1:/var/lib/mysql
      - ./dockerMysql/slave/cnf:/etc/mysql/conf.d
    environment:
      - MYSQL_DATABASE=siemens
      - MYSQL_ROOT_PASSWORD=123456
    links:
      - db
    networks:
      - mynet-tier:
 
networks:
  mynet-tier:
    ipam:
      # driver: overlay
      config:
        - subnet: "172.16.238.0/24"

注：這裡專門設定了子網和固定IP（static IP），方便後面重啟資料庫後，以指令碼的形式自動重新設定slave的引數，沒有此需求也可不必。

3. mysql 主從設定

3.1 設定 master

進入 docker
docker exec -it bin_db_1 bash

進入mysql
mysql -u root -pxxx

建立使用者並授權（mysql 8.x 與 5.x 有區別，這裡是8.x方式），這裡使用root使用者來做著叢，推薦建立專門使用者來做
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'xxx';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> show master status\G;

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 236480
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

注：這裡的 File 和 Position 資訊很重要，後面slave設定時會用到。

3.2 設定 slave

進入docker
docker exec -it bin_slave_1 bash

進入mysql
mysql -u root -pxxx

停止、重置 slave
mysql> stop slave;
mysql> reset slave;

設定主庫，啟用 slave
mysql> change master to master_host='172.18.0.3',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=236480;
mysql> start slave;

檢視slave，確保 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes
mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.3
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 236480
               Relay_Log_File: cc9a6ddfeacd-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 236480
              Relay_Log_Space: 537
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11108
                  Master_UUID: f949b20a-7a18-11ed-80df-0242ac120003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

注： Slave_IO_Running 和 Slave_SQL_Running 的值都為Yes 表示slave啟動成功。

補充1. 資料庫重啟後需更新slave的設定

由於一些原因導致docker容器停止，重新啟動docker後，slave 會無法同步主庫。需要重新設定

進入master docker
docker exec -it bin_db_1 bash

進入mysql
mysql -u root -pxxx

停止、重置 slave
mysql> stop slave;
mysql> reset slave;

mysql> reset master;

mysql> show master status\G;

*************************** 1. row ***************************
File: mysql-bin.000001
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)



進入slave docker
docker exec -it bin_slave_1 bash

進入mysql
mysql -u root -pxxx

停止、重置 slave
mysql> stop slave;
mysql> reset slave;

設定主庫，啟用 slave
mysql> change master to master_host='172.18.0.x',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.00000x',master_log_pos=xxx;
mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.2
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: 5d566ef9fd9f-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

...

注：先進入master的mysql，停止slave，reset master 和 slave；

再進入slave的mysql，停止、重置、重新設定slave（高亮紅色的部分）、啟用 slave

補充2. 常見問題

1）主從庫uuid和server_id重複導致錯誤;
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

解決方案：

在mysql中輸入以下命令，檢視主從庫是否重複，確保兩者不同；

show variables like '%server_uuid%';
show variables like '%server_id%';

server_id：

1.可以在my.cnf中修改，但重啟後可能依然不生效；

2.用「SET GLOBAL server_id=;」解決，但此命令會在mysql服務重啟後丟失。

在mysql中的auto.cnf中修改；

待uu_id和server_id修改完後重啟容器。

2）docker-compose down / up MySQL容器IP變化

重啟 docker-compose 後，需要根據MySQL master 的 IP 來設定

mysql> change master to master_host='newIP',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.00000x',master_log_pos=xxx;