docker创建多个mysql集群_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线程停止运行，原因是 master 和 slave 节点的 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

-v /tmp/mysql_data/master:/var/lib/mysql 将主机的 /tmp/mysql_data/master 目录挂载到 master 容器中 /var/lib/mysql目录上。

-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

-v /tmp/mysql_data/slave:/var/lib/mysql 将主机的 /tmp/mysql_data/slave 目录挂载到 slave 容器中 /var/lib/mysql目录上。

-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

创建用户

创建用户，identified with 'mysql_native_password'表示使用该用户的密码加密方式。

赋予用户相应的权限；

刷新 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 协议》，转载必须注明作者和本文链接