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

安装mysql#

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

1
docker run -d \
2
--name mysql \
3
-p 3306:3306 \
4
-e MYSQL_ROOT_PASSWORD=sadffsafdsfaf \
5
mysql:5.7

然后将配置文件复制出来

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

docker compose启动mysql

1
version: '3.9'
2
services:
3
    mysql:
4
        image: 'mysql:5.7'
5
        environment:
6
            - MYSQL_ROOT_PASSWORD=sadffsdadfsdfa
7
        volumes:
8
            - './data:/var/lib/mysql'
9
            - './config:/etc/mysql'
10
        ports:
11
            - '3306:3306'
12
        container_name: mysql

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

1
[mysqld]
2
server-id=1
3
log-bin=mysql-bin
4
binlog_format=ROW
5
relay-log=relay-bin
6
auto_increment_increment=2
7
auto_increment_offset=1
8
bind-address=0.0.0.0

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

1
[mysqld]
2
server-id=2
3
log-bin=mysql-bin
4
binlog_format=ROW
5
relay-log=relay-bin
6
auto_increment_increment=2
7
auto_increment_offset=2
8
bind-address=0.0.0.0

启动mysql

1
docker compose up -d

配置mysql主主复制#

创建复制用户#

在两台服务器上运行

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

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

在server 1上

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

1
+------------------+----------+--------------+------------------+-------------------+
2
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
3
+------------------+----------+--------------+------------------+-------------------+
4
| mysql-bin.000003 |      599 |              |                  |                   |
5
+------------------+----------+--------------+------------------+-------------------+

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

配置 Server 2 复制 Server 1#

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

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

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

1
+------------------+----------+--------------+------------------+-------------------+
2
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
3
+------------------+----------+--------------+------------------+-------------------+
4
| mysql-bin.000003 |      599 |              |                  |                   |
5
+------------------+----------+--------------+------------------+-------------------+

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

配置 Server 1 复制 Server 2#

在 Server 1（mysql1） 上执行：

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

检查主主复制状态#

1
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上插入数据：

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上查询数据

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

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