環境 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