MySQL 5.7 主从配置（docker-compose）

博主： Kevin
发布时间：2024 年 05 月 18 日
45 次浏览
暂无评论
4512字数
分类： MySQL

master主节点配置

系统环境

Ubuntu 20.04.3 LTS

机器环境规划

MySQL-Master-5.7    10.2.0.47
MySQL-Slave-5.7     10.2.0.48

安装并启动docker：

apt install docker.io -y && systemctl start docker

mkdir -p mysql-5.7.43/mysql/conf && mkdir -p /apps/mysql/

创建my.cnf文件

cat >> mysql/conf/my.cnf <<EOF
[mysqld]
server-id=1
log-bin=mysql-bin 
binlog-ignore-db=mysql
sync_binlog = 1
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
innodb_data_file_path = ibdata1:10M:autoextend
binlog_format = mixed
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 60
interactive_timeout = 10
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/run/mysqld/mysql.sock
no-auto-rehash
default-character-set = utf8

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
EOF

创建docker-compose.yml文件

version: '3'
services:
  mysql:
    container_name: mysql-master
    image: mysql:5.7
    restart: always
    volumes:
      - /apps/mysql/log:/var/log/mysql
      - ./mysql/conf/my.cnf:/etc/mysql/my.cnf
      - /apps/mysql/data:/var/lib/mysql
    ports:
      - '3306:3306'
    command: [
        '--explicit_defaults_for_timestamp=true',
        '--character-set-server=utf8mb4',
        '--collation-server=utf8mb4_general_ci',
        '--max_connections=3000'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: "PASSWD"

目录结构

.
├── docker-compose.yml
└── mysql
    └── conf
        └── my.cnf

构建MySQL

docker-compose up -d

配置主从同步账号和权限

1. 进入容器

docker exec -it mysql-master /bin/bash

2. 登录MySQL

mysql -uroot -pPASSWD

3. 检查主库是否开启 binlog

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

4. 查看配置的服务ID，显示的应该是my.cnf配置中的server_id

mysql>  show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)

5. 查看master信息 File 和 Position 从服务要用

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

6. 创建同步账户并开启权限，只是同步账号

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sync'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

Query OK, 0 rows affected, 1 warning (0.00 sec)

7. 刷新信息生效

mysql> FLUSH PRIVILEGES;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

Query OK, 0 rows affected (0.01 sec)

slave从节点配置

系统环境

Ubuntu 20.04.3 LTS

安装并启动docker：

apt install docker.io -y && systemctl start docker

创建build目录

mkdir -p mysql-5.7.43/mysql/conf

创建my.cnf文件

cat >> mysql/conf/my.cnf <<EOF
[mysqld]
server-id=1
log-bin=mysql-bin 
binlog-ignore-db=mysql
sync_binlog = 1
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
innodb_data_file_path = ibdata1:10M:autoextend
binlog_format = mixed
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 60
interactive_timeout = 10
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/run/mysqld/mysql.sock
no-auto-rehash
default-character-set = utf8

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
EOF

创建docker-compose.yml文件

version: '3'
services:
  mysql:
    container_name: mysql-slave
    image: mysql:5.7
    restart: always
    volumes:
      - /apps/mysql/log:/var/log/mysql  
      - ./mysql/conf/my.cnf:/etc/mysql/my.cnf
      - /apps/mysql/data:/var/lib/mysql
    ports:
      - '3306:3306'
    command: [
        '--explicit_defaults_for_timestamp=true',
        '--character-set-server=utf8mb4',
        '--collation-server=utf8mb4_general_ci',
        '--max_connections=3000'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: "PASSWD"

目录结构

.
├── docker-compose.yml
└── mysql
    └── conf
        └── my.cnf

构建MySQL

docker-compose up -d

链接master节点

1. 进入容器

docker exec -it mysql-slave /bin/bash

2. 登录MySQL

mysql -uroot -pPASSWD

3. 查看server_id是否生效

mysql> show variables like '%server_id%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+

4. 告知从库复制信息

change master to master_host='10.2.0.47',master_user='sync',master_password='123',master_port=3306,master_log_file='mysql-bin.000004', master_log_pos=604,master_connect_retry=30;

5. 连接主mysql参数说明：

master_port：Master的端口号，指的是容器的端口号
master_user：用于数据同步的用户
master_password：用于同步的用户的密码
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒

6. 开启slave

mysql> start slave;

7. 查看同步状态

mysql> show slave status \G 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.0.47
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 604
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 604
              Relay_Log_Space: 527
              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: 5abe9b8a-1347-11ef-b693-0242ac120002
             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)

看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes时意味着主从搭建完成

最后修改：2024 年 05 月 18 日

如果觉得我的文章对你有用，请随意赞赏

仅登录用户可评论，点击登录

MySQL 5.7 主从配置（docker-compose）

Kevin • 2024 年 05 月 18 日

<h1>master主节点配置</h1><h2>系统环境</h2><pre><code>Ubuntu 20.04.3 LTS</code></pre><h1>机器环境规划</h1><pre><code>MySQL-Master-5.7    10.2.0.47
MySQL-Slave-5.7     10.2.0.48</code></pre><h2>安装并启动docker：</h2><pre><code>apt install docker.io -y &amp;&amp; systemctl start docker</code></pre><h2>创建build目录和数据目录</h2><pre><code>mkdir -p mysql-5.7.43/mysql/conf &amp;&amp; mkdir -p /apps/mysql/</code></pre><h2>创建my.cnf文件</h2><pre><code>cat &gt;&gt; mysql/conf/my.cnf &lt;&lt;EOF
[mysqld]
server-id=1
log-bin=mysql-bin 
binlog-ignore-db=mysql
sync_binlog = 1
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
innodb_data_file_path = ibdata1:10M:autoextend
binlog_format = mixed
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 60
interactive_timeout = 10
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/run/mysqld/mysql.sock
no-auto-rehash
default-character-set = utf8

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
EOF</code></pre><h2>创建docker-compose.yml文件</h2><pre><code>version: '3'
services:
  mysql:
    container_name: mysql-master
    image: mysql:5.7
    restart: always
    volumes:
      - /apps/mysql/log:/var/log/mysql
      - ./mysql/conf/my.cnf:/etc/mysql/my.cnf
      - /apps/mysql/data:/var/lib/mysql
    ports:
      - '3306:3306'
    command: [
        '--explicit_defaults_for_timestamp=true',
        '--character-set-server=utf8mb4',
        '--collation-server=utf8mb4_general_ci',
        '--max_connections=3000'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: &quot;PASSWD&quot;</code></pre><h2>目录结构</h2><pre><code>.
├── docker-compose.yml
└── mysql
    └── conf
        └── my.cnf</code></pre><h2>构建MySQL</h2><pre><code>docker-compose up -d</code></pre><h2>配置主从同步账号和权限</h2><h3>1. 进入容器</h3><pre><code>docker exec -it mysql-master /bin/bash</code></pre><h3>2. 登录MySQL</h3><pre><code>mysql -uroot -pPASSWD</code></pre><h3>3. 检查主库是否开启 binlog</h3><pre><code>mysql&gt; select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)</code></pre><h3>4. 查看配置的服务ID，显示的应该是my.cnf配置中的server_id</h3><pre><code>mysql&gt;  show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)</code></pre><h3>5. 查看master信息 File 和 Position 从服务要用</h3><pre><code>mysql&gt; show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      604 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)</code></pre><h3>6. 创建同步账户并开启权限，只是同步账号</h3><pre><code>mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'sync'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

Query OK, 0 rows affected, 1 warning (0.00 sec)</code></pre><h3>7. 刷新信息生效</h3><pre><code>mysql&gt; FLUSH PRIVILEGES;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

Query OK, 0 rows affected (0.01 sec)</code></pre><h1>slave从节点配置</h1><h2>系统环境</h2><pre><code>Ubuntu 20.04.3 LTS</code></pre><h2>安装并启动docker：</h2><pre><code>apt install docker.io -y &amp;&amp; systemctl start docker</code></pre><h2>创建build目录</h2><pre><code>mkdir -p mysql-5.7.43/mysql/conf</code></pre><h2>创建my.cnf文件</h2><pre><code>cat &gt;&gt; mysql/conf/my.cnf &lt;&lt;EOF
[mysqld]
server-id=1
log-bin=mysql-bin 
binlog-ignore-db=mysql
sync_binlog = 1
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
innodb_data_file_path = ibdata1:10M:autoextend
binlog_format = mixed
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 60
interactive_timeout = 10
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/run/mysqld/mysql.sock
no-auto-rehash
default-character-set = utf8

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+</code></pre><h3>4. 告知从库复制信息</h3><pre><code>change master to master_host='10.2.0.47',master_user='sync',master_password='123',master_port=3306,master_log_file='mysql-bin.000004', master_log_pos=604,master_connect_retry=30;</code></pre><h3>5. 连接主mysql参数说明：</h3><p>master_port：Master的端口号，指的是容器的端口号<br>master_user：用于数据同步的用户<br>master_password：用于同步的用户的密码<br>master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值<br>master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值<br>master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒</p><h3>6. 开启slave</h3><pre><code>mysql&gt; start slave;</code></pre><h3>7. 查看同步状态</h3><pre><code>mysql&gt; show slave status \G 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.0.47
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 604
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 604
              Relay_Log_Space: 527
              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: 5abe9b8a-1347-11ef-b693-0242ac120002
             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)
</code></pre><p>看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes时意味着主从搭建完成</p>