Mac 下 利用 docker 构建 MySQL 集群

部署准备

下载镜像

MySql镜像版本 ------ 8.0

docker pull mysql:8.0

新建挂载目录以及挂载的配置文件

用于主机跟容器之间数据的互通

mkdir /tmp/mysql_data/master
mkdir /tmp/mysql_data/slave_cnf
mkdir /tmp/mysql_data/master
mkdir /tmp/mysql_data/slave_cnf

编辑主节点的配置文件

~ touch /tmp/mysql_data/master_cnf/master.cnf
~ vim /tmp/mysql_data/master_cnf/master.cnf
[mysqld]
server_id=1
skip-host-cache
skip-name-resolve

编辑从节点的配置文件

~ touch /tmp/mysql_data/slave_cnf/slave.cnf
~ vim /tmp/mysql_data/slave_cnf/slave.cnf
[mysqld]
server_id=2
skip-host-cache
skip-name-resolve

两个文件主要内容的差异是 server_id 值的不同,必须设置,否则在配置从库时,会报错:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

报错内容提示:slave 节点I/O线程停止运行,原因是 masterslave 节点的 server_id 冲突。

构建两个 MySql 容器

master

master 节点运行在主机的 3307 端口

~  docker run -d -e MYSQL_ROOT_PASSWORD=123456 --name master --restart always -v /tmp/mysql_data/master:/var/lib/mysql -v /tmp/mysql_data/master_cnf:/etc/mysql/conf.d -p 3307:3306 mysql:8.0
ba987e79856a14cadb2fd195ca9d8912e81a4f51ae30e5c762aba664aa5e4c97
  1. -v /tmp/mysql_data/master:/var/lib/mysql 将主机的 /tmp/mysql_data/master 目录挂载到 master 容器中 /var/lib/mysql目录上。
  2. -v /tmp/mysql_data/master_cnf:/etc/mysql/conf.d 将主机中的 /tmp/mysql_data/master_cnf 路径下的 mysql 配置文件挂载到 /etc/mysql/conf.d 目录中。

slave

master 节点运行在主机的 3308 端口

~  docker run -d -e MYSQL_ROOT_PASSWORD=123456 --name slave --restart always -v /tmp/mysql_data/slave:/var/lib/mysql -v /tmp/mysql_data/slave_cnf:/etc/mysql/conf.d -p 3308:3306 mysql:8.0
3d7227e0db499f8d7e615ee6bea77e623e2f3b2bc45347d832282bc4da2aca40
  1. -v /tmp/mysql_data/slave:/var/lib/mysql 将主机的 /tmp/mysql_data/slave 目录挂载到 slave 容器中 /var/lib/mysql目录上。
  2. -v /tmp/mysql_data/slave_cnf:/etc/mysql/conf.d 将主机中的 /tmp/mysql_data/slave_cnf 路径下的 mysql 配置文件挂载到 /etc/mysql/conf.d 目录中。

查看是否容器状态

~  docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
3d7227e0db49        mysql:8.0           "docker-entrypoint.s…"   15 minutes ago      Up 15 minutes       33060/tcp, 0.0.0.0:3308->3306/tcp   slave
ba987e79856a        mysql:8.0           "docker-entrypoint.s…"   16 minutes ago      Up 16 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   master

运转正常

  • master 节点的 CONTAINER ID 为 ba987e79856a
  • slave 节点的 CONTAINER ID 为 3d7227e0db49

开始部署

Master 节点

检验是否配置成功

进入容器内部:

docker exec -it ba98 /bin/bash

查看 MySql 配置:

/# ls /etc/mysql/conf.d/
master.cnf
/# cat /etc/mysql/conf.d/master.cnf
[mysqld]
server_id=1
skip-host-cache
skip-name-resolve

master 配置文件挂载成功。

登录并配置 master 节点

 ~  mysql -uroot -h127.0.0.1 -P 3307 -p

创建用户

  1. 创建用户,identified with 'mysql_native_password'表示使用该用户的密码加密方式。
  2. 赋予用户相应的权限;
  3. 刷新 MySql 系统权限。
mysql> create user 'reader'@'%' identified with 'mysql_native_password' by 'reader';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'reader'@'%'
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看 master 节点的状态

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      818 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave 节点

配置 Slave 节点

mysql> CHANGE MASTER TO MASTER_HOST='192.168.25.228',Master_Port=3307,MASTER_USER='reader',MASTER_PASSWORD='reader',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=0;
Query OK, 0 rows affected, 1 warning (0.04 sec)
  • MASTER_HOST:主库的 IP 地址,通过 ifconfig 查看
  • MASTER_USER:指定的用户名:此处为 reader
  • MASTER_PASSWORD:指定用户名的密码
  • MASTER_LOG_FILE:从库需要从主库上备份的文件名称
  • MASTER_LOG_POS:从库开始备份的位置,此时设置为0,也就是从初始位置开始备份

开启主从同步

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

查看从库状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.228
                  Master_User: reader
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1239
               Relay_Log_File: 8634c5ebfdf9-relay-bin.000003
                Relay_Log_Pos: 319
        Relay_Master_Log_File: binlog.000002
             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: 1239
              Relay_Log_Space: 1826
              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: 64845198-f3eb-11e9-9969-0242ac110002
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

可见 :

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave 节点运转正常。

测试

Master 节点上运行

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> create table test (Id int primary key auto_increment, Name varchar(18))
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(18) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Master 节点上测试数据写入成功。

Slave 节点上检测是否同步成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(18) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Slave 节点数据同步成功。

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 1

收藏了,等有时间学习一下。

4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!