docker建置mysql-master/slave 不同宿主機

環境 host centeos7 Docker Image mysql:5.7
192.168.1.61 mysql-master
192.168.1.62 mysql-slave

mysql-master.yml

version: '3'
services:
  mysql-master:
    image: mysql:5.7
    container_name: mysql-master
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - "3306:3306"
    volumes:
      - "/var/dev/data/mysql/master/data:/var/lib/mysql"
      - "/etc/localtime:/etc/localtime"
      - "/etc/mysql:/etc/mysql"
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

mysql-slave.yml

version: '3'
services:
  mysql-slave:
    image: mysql:5.7
    container_name: mysql-slave
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - "3306:3306"
    volumes:
      - "/var/dev/data/mysql/slvae/data:/var/lib/mysql"
      - "/etc/localtime:/etc/localtime"
      - "/etc/mysql:/etc/mysql"
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

*主庫
先 #volumes
運行服務後複製出權限資料夾,可以降低權限問題
docker-compose -f mysql-master.yml up -d
docker cp mysql-master:/etc/mysql /etc/
docker cp mysql-master:/etc/alternatives/my.cnf /etc/alternatives/
docker cp mysql-master:/var/lib/mysql 存放的位置

去除 #volumes
重新開啟服務
docker-compose -f mysql-master.yml up -d

*備庫
docker-compose -f mysql-slave.yml up -d
複製方式如上

*主庫
登入主庫

# mysql -u root -p

處理 slave 連入帳密與訪問權限

# CREATE USER 'slave'@'%' IDENTIFIED BY 'slave2021';
# GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
# show master status;

出現 Empty set (0.00 sec),輸出值為空 代表沒有開啟 log_bin

# show variables like '%log_bin%';
sql_log_bin | OFF

修改mysql設定檔

# vim /etc/mysql/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin

查看master狀態

# show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

*備庫

下指令跟隨主庫的資訊

# mysql -u root -p
# show slave status ;
# change master to master_host='192.168.1.61', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=154, master_connect_retry=30;
# show slave status ;
# FLUSH PRIVILEGES;

查看 slave 狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.61
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: 1fdb2f6a4631-relay-bin.000005
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Hits: 470