阅读 305

Docker容器下配置MySQL主从配置

“这是我参与更文挑战的10天,活动详情查看: 更文挑战

MySQL主从复制

获取MySQL镜像

搜索mysql,前两个是mysql官方制作的镜像

[root@msr ~]# docker search mysql
NAME    DESCRIPTION                                     STARS        OFFICIAL   AUTOMATED
mysql   MySQL is a widely used, open-source relation…   9686         [OK]                
mariadb MariaDB is a community-developed fork of MyS…   3523         [OK]                
mysql/mysql-server   Optimized MySQL Server Docker images. Create…   706         [OK]
复制代码

拉取mysql镜像到本地,因为mysql镜像有很多版本,对应mysql的版本:mysql:tag

[root@msr ~]# docker pull mysql:5.7
复制代码

运行MySQL镜像生成容器

运行mysql镜像,生成容器。用户名root,密码123456

[root@msr ~]# docker run -d --name mysql5.7 -p 3306:3306 -v /opt/workspace/mysql/data:/var/lib/mysql -v /opt/workspace/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 master

[root@slave ~]# docker run -d --name mysql5.7 -p 3306:3306 -v /opt/workspace/mysql/data:/var/lib/mysql -v /opt/workspace/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 slave
复制代码

至于mysql的配置文件my.cnf,可以把自己准备,也可以通过一下命令获取

[root@msr ~]# docker run -i --rm mysql5.7 cat /etc/my.cnf > my.cnf
复制代码

MySQL主从配置

修改mysql的配置文件

master:

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
#开启log-bin
log-bin = /var/lib/mysql/mysql-bin
#设置服务id,主从不能一致  
serverid = 1  
#设置需要同步的数据库  
#binlog‐do‐db=user_db  
#屏蔽系统库同步  
binlogignore‐db=mysql  
binlogignore‐db=information_schema  
binlogignore‐db=performance_schema  
expire-logs-days = 14
max-binlog-size = 500M
default-time-zone='+08:00'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
复制代码

slave:

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
#开启log-bin
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
max-binlog-size = 500M
server-id = 2
#relay_log的位置
relay_log=mysql-relay-bin
log-slave-updates = 1
#设置需要同步的数据库  
#replicate_wild_do_table=user_db.%  
#屏蔽系统库同步  
replicate_wild_ignore_table=mysql.%  
replicate_wild_ignore_table=information_schema.%  
replicate_wild_ignore_table=performance_schema.% 
#只读
read-only = 1
default-time-zone='+08:00'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
复制代码

重启master和slave

[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
复制代码

下一步在Master数据库创建数据同步用户,授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限,用于在主从库之间同步数据。首先要进入到docker容器内,也可以通过向Navicat等工具执行。

[root@msr ~]# docker exec -it master /bin/bash
root@207f37f4c64a:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1633
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

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

重启数据库

[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
复制代码

在salve中执行

change master to master_host='192.168.74.130', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos= 154, master_connect_retry=30;
复制代码

master_host :Master的地址,指的是容器的独立ip,可以通过docker inspect --format='{% raw %} {{.NetworkSettings.IPAddress}} {% endraw %}'容器名称|容器id`查询容器的ip,这个命令查看的是容器的Host,同服务器下可以使用。不同服务器下就要使用服务的真实IP地址。

master_port:Master的端口号,指的是容器的端口号

master_user:用于数据同步的用户

master_password:用于同步的用户的密码

master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值

master_log_pos:从哪个 Position 开始读,即上文中提到的 Position 字段的值

master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒

查看slave的状态

show slave status\\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.74.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 154
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'slave@47.96.175.101:3306' - retry-time: 60  retries: 10
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200628 09:03:36
     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: No和Slave_SQL_Running: No。因为我们还没有开启主从复制过程。使用start slave开启主从复制过程,然后再次查询主从同步状态show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.74.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: edu-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: 154
              Relay_Log_Space: 531
              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: 0994ae5c-b83a-11ea-9769-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)
复制代码

Slave_IO_State: Waiting for master to send event,说明正在等待master的数据。Slave_IO_Running: Yes和Slave_SQL_Running: Yes说明主从复制已经开启。此时可以测试数据同步是否成功。

配置过程中可能出现的错误

Slave_IO_State: Connecting to master或Slave_IO_Running:Connectiog。说明从库一直在尝试连接主库,所以可能是:

  • 主库的Host地址不对
  • 端口是否不对
  • 用于连接主库的用户和密码是否不对
  • master_log_file和master_log_pos是否和主库的不一致
文章分类
后端
文章标签