MySQL

Database MySQL

发布日期: 2023-06-06

文章字数: 1.5k

阅读时长: 7 分

本文记录了在本地使用Docker搭建MySQL一主多从的配置文件、脚本文件以及启动过程。

环境设置

Docker: v20.10.24
MacOS: v13.3
MySQL: v5.7

配置文件

Master

master.cnf

[mysqld]
#开启binlog日志
log-bin=mysql-bin
#设置服务id,主从不能一致
server-id=1
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

Slave

slave1.cnf

[mysqld]
#开启binlog日志
log-bin=mysql-bin
#设置服务id,主从不能一致
server-id=2

# 设置忽略的库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

slave2.cnf

[mysqld]
#开启binlog日志
log-bin=mysql-bin
#设置服务id,主从不能一致
server-id=3

# 设置忽略的库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

脚本文件

Master

master.sh

#!/bin/bash

DOCKER_INSTANCE_NAME=master
MYSQL_ROOT_PASSWORD=master
CONTAINER_PORT=3306
HOST_PORT=13306
CONTAINER_CONFIG_PATH=/etc/my.cnf
HOST_CONFIG_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/master.cnf
CONTANIER_DATA_PATH=/var/lib/mysql
HOST_DATA_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/data/master

docker stop "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1
docker rm "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1

docker run \
    --name ${DOCKER_INSTANCE_NAME} \
    -v ${HOST_CONFIG_PATH}:${CONTAINER_CONFIG_PATH} \
    -v ${HOST_DATA_PATH}:${CONTANIER_DATA_PATH} \
    -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
    -p ${HOST_PORT}:${CONTAINER_PORT} \
    -d \
    mysql:5.7 >/dev/null

if [ $? -eq 0 ]; then
    echo "Docker container '${DOCKER_INSTANCE_NAME}' started successfully."
else
    echo "Failed to start Docker container '${DOCKER_INSTANCE_NAME}'."
fi

Slave

slave1.sh

#!/bin/bash

DOCKER_INSTANCE_NAME=slave
MYSQL_ROOT_PASSWORD=slave
CONTAINER_PORT=3306
HOST_PORT=23306
CONTAINER_CONFIG_PATH=/etc/my.cnf
HOST_CONFIG_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/slave.cnf
CONTANIER_DATA_PATH=/var/lib/mysql
HOST_DATA_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/data/slave

docker stop "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1
docker rm "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1

docker run \
    --name ${DOCKER_INSTANCE_NAME} \
    -v ${HOST_CONFIG_PATH}:${CONTAINER_CONFIG_PATH} \
    -v ${HOST_DATA_PATH}:${CONTANIER_DATA_PATH} \
    -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
    -p ${HOST_PORT}:${CONTAINER_PORT} \
    -d \
    mysql:5.7 >/dev/null

if [ $? -eq 0 ]; then
    echo "Docker container '${DOCKER_INSTANCE_NAME}' started successfully."
else
    echo "Failed to start Docker container '${DOCKER_INSTANCE_NAME}'."
fi

slave2.sh

#!/bin/bash

DOCKER_INSTANCE_NAME=slave2
MYSQL_ROOT_PASSWORD=slave2
CONTAINER_PORT=3306
HOST_PORT=33306
CONTAINER_CONFIG_PATH=/etc/my.cnf
HOST_CONFIG_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/slave2.cnf
CONTANIER_DATA_PATH=/var/lib/mysql
HOST_DATA_PATH=/Users/foursevenlove/Desktop/scripts/mysql-master-slave/data/slave2


docker stop "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1
docker rm "${DOCKER_INSTANCE_NAME}" >/dev/null 2>&1

docker run \
    --name ${DOCKER_INSTANCE_NAME} \
    -v ${HOST_CONFIG_PATH}:${CONTAINER_CONFIG_PATH} \
    -v ${HOST_DATA_PATH}:${CONTANIER_DATA_PATH} \
    -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
    -p ${HOST_PORT}:${CONTAINER_PORT} \
    -d \
    mysql:5.7 >/dev/null

if [ $? -eq 0 ]; then
    echo "Docker container '${DOCKER_INSTANCE_NAME}' started successfully."
else
    echo "Failed to start Docker container '${DOCKER_INSTANCE_NAME}'."
fi

Start

start.sh

#! /bin/bash

SCRIPTS_DIR=/Users/foursevenlove/Desktop/scripts/mysql-master-slave

sh ${SCRIPTS_DIR}/master.sh
sh ${SCRIPTS_DIR}/slave.sh
sh ${SCRIPTS_DIR}/slave2.sh

启动过程

在准备好上述配置文件和脚本文件后，就可以开始启动数据库。

首先执行以下命令启动主从数据库，看到以下日志代表成功启动数据库实例：

> sh ./start.sh
Docker container 'master' started successfully.
Docker container 'slave' started successfully.
Docker container 'slave2' started successfully.

通过docker查看数据库实例：

> docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED         STATUS         PORTS                                                                                                                                  NAMES
0061e924d5cb   mysql:5.7                "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   33060/tcp, 0.0.0.0:33306->3306/tcp                                                                                                     slave2
8995ce05c39d   mysql:5.7                "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   33060/tcp, 0.0.0.0:23306->3306/tcp                                                                                                     slave
51c14e787f46   mysql:5.7                "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   33060/tcp, 0.0.0.0:13306->3306/tcp

Master

登录Master数据库，创建主从复制权限的账户：

通过docker exec命令与容器交互

> docker exec -it master /bin/bash

登录root用户，输入密码：

> mysql -uroot -p

创建主从复制权限的账户：

GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
FLUSH PRIVILEGES;

查看master bin log文件名和position，根据结果文件名为mysql-bin.000006，position为592：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000006 |      592 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

查看Master所在容器IP，下面Slave启动需要用到：

> docker inspect --format '{{ .NetworkSettings.IPAddress }}' master
172.17.0.3

Slave

下面是slave的启动过程，对于slave2操作过程类似。

通过docker exec命令与容器交互

docker exec -it slave /bin/bash

登录root用户，输入密码：

mysql -uroot -p

确保slave没有启动：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

设置主库信息，其中master_host是上述查看的主库容器IP、master_user和master_password是在主库设置的有主从复制的账户、master_log_file和master_log_pos参数是上述提到的binlog文件名和指针位置：

mysql> change master to master_host='172.17.0.3', master_user='db_sync', master_password='db_sync', master_port=3306, master_log_file='mysql-bin.000006', master_log_pos=592;
Query OK, 0 rows affected, 2 warnings (0.28 sec)

启动slave进程：

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

通过以下命令查看slave是否启动成功，Slave_IO_Running和Slave_SQL_Running参数的值为Yes代表启动成功：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.3
                  Master_User: db_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 592
               Relay_Log_File: 8995ce05c39d-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             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: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 592
              Relay_Log_Space: 534
              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: 1
                  Master_UUID: 6bcf284e-004c-11ee-98e0-0242ac110003
             Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)

验证

在主库上新建一个测试DB，创建一张测试表，插入一些测试数据，再登录从库查看数据。

Master

登录主库

> docker exec -it master /bin/bash

> mysql -uroot -p

创建测试数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.09 sec)

mysql> create database testDB;
Query OK, 1 row affected (0.02 sec)

创建测试表：

mysql> CREATE TABLE `t` (
    ->   `ftime` datetime NOT NULL,
    ->   `c` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`ftime`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.10 sec)

插入测试数据：

mysql> insert into t values('2017-4-1',1),('2018-4-1',1),('2019-4-1',1),('2020-4-1',1),('2021-4-1',1);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

Slave

登录从库：

> docker exec -it slave /bin/bash

> mysql -uroot -p

查看测试数据：

mysql> select * from testDB.t;
+---------------------+------+
| ftime               | c    |
+---------------------+------+
| 2017-04-01 00:00:00 |    1 |
| 2018-04-01 00:00:00 |    1 |
| 2019-04-01 00:00:00 |    1 |
| 2020-04-01 00:00:00 |    1 |
| 2021-04-01 00:00:00 |    1 |
+---------------------+------+
5 rows in set (0.00 sec)