上一篇文章介绍了Mycat的安装和配置(Docker自动化部署安装(八)之安装Mycat),下面介绍下通过MyCat来实现MySQL的读写分离操作
要用Mycat实现读写分离,我们先实现数据库的主从同步,然后在通过mycat配置实现读写分离
一、Mysql主从配置
先看下效果:
1.准备两个数据库;主库(master)和从库(slave)可参考前面的文章 Docker自动化部署安装(四)之安装Mysql; 这里我就改动的docker-mysql.yml可直接实现安装两个Mysql
version: '3'
services:
mysql-master:
restart: always
image: mysql:5.7.29
container_name: mysql-master
hostname: mysql-master
ports:
- 3306:3306
volumes:
- /etc/localtime:/etc/localtime
- /work/docker/conf/mysql-master/mysql.conf.cnf:/etc/mysql/conf.d/mysql.conf.cnf
- /work/docker/data/mysql-master:/var/lib/mysql
- /work/docker/logs/mysql-master:/var/log/mysql
environment:
- MYSQL_ROOT_PASSWORD=Root@2021
networks:
default:
ipv4_address: 172.18.0.3
mysql-slave:
restart: always
image: mysql:5.7.29
container_name: mysql-slave
hostname: mysql-slave
ports:
- 3307:3306
volumes:
- /etc/localtime:/etc/localtime
- /work/docker/conf/mysql-slave/mysql.conf.cnf:/etc/mysql/conf.d/mysql.conf.cnf
- /work/docker/data/mysql-slave:/var/lib/mysql
- /work/docker/logs/mysql-slave:/var/log/mysql
environment:
- MYSQL_ROOT_PASSWORD=Root@2021
networks:
default:
ipv4_address: 172.18.0.2
使用命令运行安装:
docker-compose -f /work/docker/docker-mysql.yml up -d
2.同步两个数据库现有的数据文件,如果是两个新安装的数据库忽略这步操作
3.主库配置文件修改如下:
[root@iZuf6g20pbfuqtc2santjkZ ~]# vim /work/docker/conf/mysql-master/mysql.conf.cnf
[mysqld]
server-id = 100
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = 1
binlog-ignore-db = mysql
expire-logs-days = 7
max_connections = 2000
slow_query_log = ON
interactive_timeout = 1800
wait_timeout = 1800
#log-bin = mysql-bin
#[必须]启用二进制日志
#server-id = 4
#[必须]服务器唯一ID,默认是1,最好取ip的后3位
#expire-logs-days = 7
#只保留7天的二进制日志,以防磁盘被日志占满
#binlog-ignore-db = mysql
#不备份的数据库
#binlog-ignore-db = information_schema
#binlog-ignore-db = performation_schema
#binlog-ignore-db = sys
#binlog-do-db=qianqian
#需要做复制的数据库名
注意:
(1)本环境中 server_id 是 100
MySQL 服务唯一标识
配置要求:
server_id 任意配置,只要是数字即可
server_id Master 唯一标识数字必须小于 Slave 唯一标识数字.(2)log_bin配置
本环境中 log_bin 值 : master_log
开启日志功能以及日志文件命名,log_bin=master_log
变量的值就是日志文件名称.是日志文件名称的主体.
MySQL 数据库自动增加文件名后缀和文件类型.
4.修改完后重启mysql-master
docker restart mycat
5.进入mysql-master服务程序,在主库中配置从库slave访问主库的用户
grant all privileges on *.* to ‘username’@’ip’ identified by ‘password’ with grant option;
flush privileges;