1.2 docker 配置mysql8.0主从复制

作者: 回眸淡然笑 | 来源:发表于2023-06-14 14:15 被阅读0次

docker 安装
九、docker安装mysql8.0
RabbitMQ安装
redis主从 + Sentinel 哨兵模式
Wireguard
docker 和k8s的基本学习
Docker 安装 MySQL8.0
DBA（MySQL）学习-主从复制基础
MySQL-lesson9--主从复制高级进阶
001.Docker入门基础篇

1、yml配置文件修改

  mysql:
    #这里镜像文件用mysql:,不用dnmp的mysql/mysql_server
    image: mysql:${MYSQL_VERSION}
    container_name: mysql
    ports:
      - "${MYSQL_HOST_PORT}:3306"
    volumes:
      - ${MYSQL_CONF_FILE}:/etc/mysql/conf.d/mysql.cnf:ro
      - ${DATA_DIR}/mysql:/var/lib/mysql/:rw
      - ${MYSQL_LOG_DIR}:/var/log/mysql/:rw

    restart: always
    networks:
      - default
    environment:
      MYSQL_ROOT_PASSWORD: "${MYSQL_ROOT_PASSWORD}"
      MYSQL_ROOT_HOST: "${MYSQL_ROOT_HOST}"
      TZ: "$TZ"
  #从库配置
  mysql_slave:
    image: mysql:${MYSQL_SLAVE_VERSION}
    container_name: mysql_slave
    ports:
      - "${MYSQL_SLAVE_HOST_PORT}:3306"
    volumes:
      - ${MYSQL_SLAVE_CONF_FILE}:/etc/mysql/conf.d/mysql.cnf:ro
      - ${DATA_DIR}/mysql_slave:/var/lib/mysql/:rw
      - ${MYSQL_SLAVE_LOG_DIR}:/var/log/mysql/:rw

    restart: always
    networks:
      - default
    environment:
      MYSQL_ROOT_PASSWORD: "${MYSQL_SLAVE_ROOT_PASSWORD}"
      MYSQL_ROOT_HOST: "${MYSQL_SLAVE_ROOT_HOST}"
      TZ: "$TZ"

2、配置.env

#
# MySQL8
#
MYSQL_VERSION=8.0.28
MYSQL_HOST_PORT=3306
MYSQL_ROOT_PASSWORD=123456
MYSQL_ROOT_HOST=%
MYSQL_CONF_FILE=./services/mysql/mysql.cnf
MYSQL_CNF=./services/mysql/my.cnf
MYSQL_LOG_DIR=./logs/mysql

#
# MySQL8-slave
#
MYSQL_SLAVE_VERSION=8.0.28
MYSQL_SLAVE_HOST_PORT=3307
MYSQL_SLAVE_ROOT_PASSWORD=123456
MYSQL_SLAVE_ROOT_HOST=%
MYSQL_SLAVE_CONF_FILE=./services/mysql_slave/mysql_slave.cnf
MYSQL_SLAVE_CNF=./services/mysql_slave/my.cnf
MYSQL_SLAVE_LOG_DIR=./logs/mysql_slave

3、登录主库

3.1 修改配置文件
修改主库配置文件
dnmp\services\mysql\mysql.cnf

[client]
port                    = 3306
default-character-set   = utf8mb4

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 指定需要同步的数据库名称
binlog-do-db=hadmin
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用,mysl8.0默认开启
log-bin                 = mysql-master-bin 
##二进制日志的格式，有三种：statement/row/mixed
binlog_format           = mixed 
expire_logs_days        = 7 
slave_skip_errors       = 1062

user                    = mysql
port                    = 3306
sql_mode                = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

default-storage-engine  = InnoDB
default-authentication-plugin   = mysql_native_password
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci
init_connect            = 'SET NAMES utf8mb4'


skip-character-set-client-handshake
explicit_defaults_for_timestamp

slow_query_log
long_query_time         = 3
slow-query-log-file     = /var/log/mysql/mysql.slow.log
log-error               = /var/log/mysql/mysql.error.log

default-time-zone       = '+8:00'

[mysql]
default-character-set   = utf8mb4

修改从库配置
dnmp\services\mysql_slave\mysql.cnf

[client]
port                    = 3306
default-character-set   = utf8mb4


[mysqld]
server_id               = 102
binlog-ignore-db        = mysql
log-bin                 = mysql-slave-bin 
replicate-do-db=hadmin
binlog_format           = mixed 
expire_logs_days        = 7 
slave_skip_errors       = 1062
read_only               = 1
relay_log               = slave-relay-bin-log
log_slave_updates       = 1  

user                    = mysql
port                    = 3306
sql_mode                = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

default-storage-engine  = InnoDB
default-authentication-plugin   = mysql_native_password
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci
init_connect            = 'SET NAMES utf8mb4'


skip-character-set-client-handshake
explicit_defaults_for_timestamp

slow_query_log
long_query_time         = 3
slow-query-log-file     = /var/log/mysql/mysql.slow.log
log-error               = /var/log/mysql/mysql.error.log

default-time-zone       = '+8:00'

[mysql]
default-character-set   = utf8mb4

3.2设置从库访问账户
进入主库容器

docker exec -it mysql /bin/sh

# mysql -uroot -p123456
mysql: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

进入mysql容器,如果提示[Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.代表配置文件有写权限，mysql予以忽略了，通过chmod修改mysql.cnf的权限为555，chmod 555 /etc/mysql/conf.d/mysql.cnf。
PS：因为我本地是windows系统，以上操作所以通常会报chmod: changing permissions of '/etc/mysql/conf.d/mysql.cnf': Read-only file system;这时候可找到本地mysql.cnf右键->属性，设置为只读即可，从库也是一样的操作

image.png
重启mysql镜像 docker-compose restart mysql
再次进入mysql容器

# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;

3.3查看主库信息
show master status\G;

image.png

4、登录从库

docker exec -it mysql_slave /bin/sh
mysql -uroot -p123456
mysql> show slave status\G;#查看同步状态
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: mysql
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-master-bin.000004
          Read_Master_Log_Pos: 157
               Relay_Log_File: 540e16593f66-relay-bin.000005
                Relay_Log_Pos: 387
        Relay_Master_Log_File: mysql-master-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: 157
              Relay_Log_Space: 780
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 73e933a6-fa0f-11ed-b86e-0242ac120003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

设置从库信息这里主机地址直接填写主库镜像的名称mysql

change master to master_host='mysql', 
master_user='slave', 
master_password='123456', master_port=3306, 
master_log_file='mysql-master-bin.000003', 
master_log_pos=3438, master_connect_retry=30;

启动salve;

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-master-bin.000004
          Read_Master_Log_Pos: 157
               Relay_Log_File: 540e16593f66-relay-bin.000006
                Relay_Log_Pos: 333
        Relay_Master_Log_File: mysql-master-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: 157
              Relay_Log_Space: 780
              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: 101
                  Master_UUID: 73e933a6-fa0f-11ed-b86e-0242ac120003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

docker下的mysql主从复制算完成

测试

为了方便，我这里使用navicat进行数据库登录

image.png

在主库中新增一条数据

INSERT INTO `hadmin`.`im_article`(`id`, `user_id`, `class_id`, `tags_id`, `title`, `abstract`, `image`, `is_asterisk`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES (10, 0, 0, '', '1', '1', '', 0, 1, '2023-06-14 15:22:00', '2023-06-14 15:22:03', '2023-06-14 15:22:05');

登录从库进行查看数据已同步。

image.png

常见错误
如果启动后报
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '83109884-04b1-11e7-91d9-1866daf67338:349407912' at master log mes-bin-29.000694, end_log_pos 91449. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
可以通过 mysql> select * from performance_schema.replication_applier_status_by_worker\G查看具体错误信息，也可以在dnmpde log中查看问题原因

如何连接MySQL和Redis服务器

这要分两种情况，

第一种情况，在PHP代码中。

// 连接MySQL
$dbh = new PDO('mysql:host=mysql;dbname=mysql', 'root', '123456');

// 连接Redis
$redis = new Redis();
$redis->connect('redis', 6379);

因为容器与容器是expose端口联通的，而且在同一个networks下，所以连接的host参数直接用容器名称，port参数就是容器内部的端口。更多请参考《docker-compose ports和expose的区别》。

第二种情况，在主机中通过命令行或者Navicat等工具连接。主机要连接mysql和redis的话，要求容器必须经过ports把端口映射到主机了。以 mysql 为例，docker-compose.yml文件中有这样的ports配置：3306:3306，就是主机的3306和容器的3306端口形成了映射，所以我们可以这样连接：

$ mysql -h127.0.0.1 -uroot -p123456 -P3306
$ redis-cli -h127.0.0.1

这里host参数不能用localhost是因为它默认是通过sock文件与mysql通信，而容器与主机文件系统已经隔离，所以需要通过TCP方式连接，所以需要指定IP。

8.5 容器内的php如何连接宿主机MySQL

1.宿主机执行ifconfig docker0得到inet就是要连接的ip地址

$ ifconfig docker0
docker0: flags=4099<UP,BROADCAST,MULTICAST>  mtu 1500
        inet 172.17.0.1  netmask 255.255.0.0  broadcast 172.17.255.255
        ...

2.运行宿主机Mysql命令行

 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
 mysql>flush privileges;
// 其中各字符的含义：
// *.* 对任意数据库任意表有效
// "root" "123456" 是数据库用户名和密码
// '%' 允许访问数据库的IP地址，%意思是任意IP，也可以指定IP
// flush privileges 刷新权限信息

3.接着直接php容器使用172.0.17.1:3306连接即可

`常用命令`

change master to master_host='mysql', 
master_user='slave', 
master_password='123456', master_port=3306, 
master_log_file='mysql-master-bin.000003', 
master_log_pos=3438, master_connect_retry=30;

show master status\G;
show slave status\G;
start slave;
stop slave;
select host,user,plugin,authentication_string from mysql.user; 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

docker 安装
1 docker安装 1.1 系统环境检查 1.2 安装docker 1.1 配置docker 镜像加速器
九、docker安装mysql8.0
docker安装mysql8.0
RabbitMQ安装
1、安装和配置Docker 1.1、安装yum和docker 1.2、安装docker加速器在linux中执行下...
redis主从 + Sentinel 哨兵模式
环境介绍 docker配置redis主从复制(一主两从),启动Sentinel(哨兵模式,3个sentinel) ...
Wireguard
1 Linux docker wireguard 服务端 1.1 Docker 容器配置 1.2 获取服务端公钥 ...
docker 和k8s的基本学习
1 docker相关 1.1 CentOS7系统配置国内yum源 1.2 centos docker 安装 1.3...
Docker 安装 MySQL8.0
环境： centos 7.5 docker 19.03.12 MySQL8.0 docker仓库搜索MySQL...
DBA（MySQL）学习-主从复制基础
1. 主从复制 1.1 介绍 1.2 主从复制的前提（如何搭建主从复制） 2. 搭建主从复制 2.1 搭建多实例 ...
MySQL-lesson9--主从复制高级进阶
MySQL-lesson9--主从复制高级进阶 1. 延时从库 1.1介绍 1.2 为什么要有延时从 1.3 配置...
001.Docker入门基础篇
1. Docker安装 1.1 安装并启动Docker 1.2 配置镜像加速开通成功之后，会为你的账户分配一个镜...