Docker安装mysql，并配置两台服务器主主复制

安装mysql

在两台服务器上同时进行，这里以mysql 5.7为例：

docker run -d \
--name mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=sadffsafdsfaf \
mysql:5.7

然后将配置文件复制出来

docker cp mysql:/etc/mysql /root/data/mysql/config

docker compose启动mysql

version: '3.9'
services:
    mysql:
        image: 'mysql:5.7'
        environment:
            - MYSQL_ROOT_PASSWORD=sadffsdadfsdfa
        volumes:
            - './data:/var/lib/mysql'
            - './config:/etc/mysql'
        ports:
            - '3306:3306'
        container_name: mysql

在server1上，进入config文件夹，创建my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
relay-log=relay-bin
auto_increment_increment=2
auto_increment_offset=1
bind-address=0.0.0.0

在server2上，进入config文件夹，创建my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
binlog_format=ROW
relay-log=relay-bin
auto_increment_increment=2
auto_increment_offset=2
bind-address=0.0.0.0

启动mysql

docker compose up -d

配置mysql主主复制

创建复制用户

在两台服务器上运行

docker exec -it mysql mysql -uroot -p -e "
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_password';
FLUSH PRIVILEGES;"

获取主库的二进制日志信息

在server 1上

docker exec -it mysql mysql -uroot -p -e "SHOW MASTER STATUS;"

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

记录 File 和 Position，稍后用于 Server 2 配置

配置 Server 2 复制 Server 1

docker exec -it mysql mysql -uroot -p -e "
CHANGE MASTER TO MASTER_HOST='192.168.1.10', 
MASTER_USER='repl', 
MASTER_PASSWORD='repl_password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=154;
START SLAVE;"

获取 Server 2 的二进制日志信息

docker exec -it mysql mysql -uroot -p -e "SHOW MASTER STATUS;"

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

记录 File 和 Position，稍后用于 Server 1 配置

配置 Server 1 复制 Server 2

在 Server 1（mysql1） 上执行：

docker exec -it mysql mysql -uroot -p -e "
CHANGE MASTER TO MASTER_HOST='192.168.1.10', 
MASTER_USER='repl', 
MASTER_PASSWORD='repl_password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=154;
START SLAVE;"

检查主主复制状态

docker exec -it mysql mysql -uroot -p -e "SHOW SLAVE STATUS\G"

  • 确保 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes

  • Seconds_Behind_Master应该为0或者接近0

测试主主复制状态

在server 1上插入数据：

docker exec -it mysql mysql -uroot -p -e "CREATE DATABASE test_db; USE test_db; CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO test_table (name) VALUES ('Server1');"

在server 2上查询数据

docker exec -it mysql mysql -uroot -p -e "SELECT * FROM test_db.test_table;"

如果数据同步成功，说明 主主复制已正确配置！🎉