基于Docker的MySQL集群配置
安装Docker
获取MySQL镜像
下载官方镜像,本文采用MySQL 5.7 版本
[root@localhost /]docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 8951 [OK]
[root@localhost /]docker pull mysql:5.7
[root@localhost /]docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 1e4405fe1ea9 4 weeks ago 437MB
创建容器实例
命令:
docker run -p 容器端口:宿主机端口 -p 容器目录:宿主机目录 镜像名称:版本号
参数说明:
-p 3308:3306:将容器的3306端口映射到主机的3308端口
-v /mydata/mysql/master/conf:/etc/mysql:将配置文件夹挂在到主机
-v /mydata/mysql/master/log:/var/log/mysql:将日志文件夹挂载到主机
-v /mydata/mysql/master/data:/var/lib/mysql/:将配置文件夹挂载到主机
-e MYSQL_ROOT_PASSWORD=root:初始化root用户的密码
- 创建mysql-master实例
[root@localhost /]docker run -p 3308:3306 --name mysql-master \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
- 创建mysql-slaver-01实例
[root@localhost /]docker run -p 3318:3306 --name mysql-slaver-01 \
-v /mydata/mysql/slaver-01/log:/var/log/mysql \
-v /mydata/mysql/slaver-01/data:/var/lib/mysql \
-v /mydata/mysql/slaver-01/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
- 创建mysql-slaver-02实例
[root@localhost /]docker run -p 3328:3306 --name mysql-slaver-02 \
-v /mydata/mysql/slaver-02/log:/var/log/mysql \
-v /mydata/mysql/slaver-02/data:/var/lib/mysql \
-v /mydata/mysql/slaver-02/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
- 实例创建完成
[root@localhost /]docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8a10cca7782b mysql:5.7 "docker-entrypoint.s…" 2 weeks ago Up 4 days 33060/tcp, 0.0.0.0:3328->3306/tcp mysql-slaver-02
6fe4a6336042 mysql:5.7 "docker-entrypoint.s…" 2 weeks ago Up 4 days 33060/tcp, 0.0.0.0:3318->3306/tcp mysql-slaver-01
e2f5771fd273 mysql:5.7 "docker-entrypoint.s…" 2 weeks ago Up 4 days 33060/tcp, 0.0.0.0:3308->3306/tcp mysql-master
配置主从
- 修改主数据库配置文件
[root@localhost /]#vim /mydata/mysql/master/conf/my.cnf
主库配置文件详情
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
##跳过名称解析,避免过慢
skip-name-resolve
server_id=1
log-bin=mysql-bin
read-only=0
##允许同步的库
binlog-do-db=gmall_ums
binlog-do-db=gmall_pms
binlog-do-db=gmall_oms
binlog-do-db=gmall_sms
binlog-do-db=gmall_cms
##不允许同步的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
- 修改从库1配置文件
[root@localhost /]#vim /mydata/mysql/slaver-01/conf/my.cnf
从库1配置文件详情
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
##跳过名称解析,避免过慢
skip-name-resolve
##唯一ID
server_id=2
log-bin=mysql-bin
##从库设为只读
read-only=1
##允许同步的库
binlog-do-db=gmall_ums
binlog-do-db=gmall_pms
binlog-do-db=gmall_oms
binlog-do-db=gmall_sms
binlog-do-db=gmall_cms
##不允许同步的库,MySQL自带的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
- 修改从库2配置文件
[root@localhost /]vim /mydata/mysql/slaver-02/conf/my.cnf
从库2配置文件详情
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
##跳过名称解析,避免过慢
skip-name-resolve
server_id=2
log-bin=mysql-bin
##从库设为只读
read-only=1
##允许同步的库
binlog-do-db=gmall_ums
binlog-do-db=gmall_pms
binlog-do-db=gmall_oms
binlog-do-db=gmall_sms
binlog-do-db=gmall_cms
##不允许同步的库,MySQL自带的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
- 修改配置文件后重启容器
[root@localhost /]# docker restart mysql-master mysql-slaver-01 mysql-slaver-02
- 为master创建用户来同步其数据
进入容器内部
[root@localhost conf]# docker exec -it mysql-master /bin/bash
root@e2f5771fd273:/#
连接mysql
root@e2f5771fd273:/# mysql -uroot -proot
mysql>
授权root可以远程访问( 主从无关,为了方便我们远程连接mysql)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
mysql>flush privileges;
添加用来同步的用户
mysql>GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
查看主库状态
mysql> show master status;
+------------------+----------+---------------------------------------------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------------------------------------------+------------------+-------------------+
| mysql-bin.000020 | 154 | gmall_ums,gmall_pms,gmall_oms,gmall_sms,gmall_cms | | |
+------------------+----------+---------------------------------------------------+------------------+-------------------+
1 row in set (0.00 sec)
- 配置从库2(mysql-salver-02)连接主库
进入容器内部
[root@192 /]docker exec -it mysql-slaver-02 /bin/bash
root@8a10cca7782b:/#
连接从库MySQL,设置连接主库
root@8a10cca7782b:/# mysql -uroot -proot
###设置主库连接
mysql> change master to master_host='192.168.33.129',master_user='backup',master_password='123456',master_log_file='mysql-bin.000020',master_log_pos=0,master_port=3308;
命令详情:
master_host :主库IP
master_user:主库创建的同步用户名
master_password:主库创建的同步用户密码
master_log_file:主库文件(show master status)中的File
master_port:主库端口
###启动从库同步
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.33.129
Master_User: backup
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 154
Relay_Log_File: 8a10cca7782b-relay-bin.000038
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
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: 747
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: e27c820a-14ff-11ea-9a4d-0242ac110002
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)
- 从库01(mysql-slaver-01)配置同从库2
验证
主库发生变化后,自动在从库中同步
变化前
变化后
同步后