docker安装服务与mysql分开_docker搭建mysql读写分离

写在前面

当mysql数据量不大且达到读写瓶颈的时候，读的性能消耗远大于写，我们可以将读和写分离开。本实例采用docker容器搭建，通过mycat中间件做分发

准备mysql主从服务

1.准备mysql容器

docker pull mysql:5.7

创建容器网络，用于固定mysql容器ip

docker network create --subnet=172.18.0.0/24 docker-network

2.创建主从数据映射目录

//创建目录，数据和配置不至于丢失

mkdir -p /usr/local/docker_app/mysql/master/conf.d/mkdir-p /usr/local/docker_app/mysql/slave/conf.d/

3.创建master配置文件/usr/local/docker_app/mysql/master/conf.d/my.cnf

vi /usr/local/docker_app/mysql/master/conf.d/my.cnf

修改文件配置

[mysqld]

log-bin=master-bin

server-id=1

4.创建slave配置文件/usr/local/docker_app/mysql/slave/conf.d/my.cnf

vi /usr/local/docker_app/mysql/slave/conf.d/my.cnf

修改文件配置

[mysqld]

log-bin=slave-bin

server-id=2

5.启动master服务

docker run -d --name mysql-master \

-p 3308:3306 \

--net docker-network --ip 172.18.0.10 \

-v /usr/local/docker_app/mysql/master/conf.d/data:/var/lib/mysql \

-v /usr/local/docker_app/mysql/master/conf.d:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=root \

-d mysql:5.7

6.启动slave服务

docker run -d --name mysql-slave \

-p 3307:3306 \

--net docker-network --ip 172.18.0.11 \

-v /usr/local/docker_app/mysql/slave/conf.d/data:/var/lib/mysql \

-v /usr/local/docker_app/mysql/slave/conf.d:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=root \

-d mysql:5.7

7.进入容器修改master 的读写权限

# 进入容器shell

$ docker exec-it mysql-master /bin/sh

mysql -uroot -p//mysql shell//用户名：root//密码：root

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

进入容器修改slave的读写权限

# 进入容器shell

$ docker exec-it mysql-slave /bin/sh

//进入mysql

mysql -uroot -p//mysql shell//用户名：root//密码：root

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

8.进入mysql-master查看master状态

//mysql shell

show master status;+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| master-bin.000003 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

9.进入mysql-slave关联主从模式(自行更改那些参数对应的值)(如果不小心配置错,输入mysql> stop slave;然后重新录入一遍)

//mysql shell

change master to master_host='172.18.0.10', master_user='root',master_password='root', master_log_file='master-bin.000003',master_log_pos=154;

//mysql shell//启用slave

start slave;

10.mysql-slave查看是否成功

show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

//说明主从成功

11.测试主从

在master创建test数据库，再到slave中查看是否存在test库，若存在则基本完成，若未成功检查binlog、master host、position是否正确；目前已经可以给前台使用，只不过要区分读写数据源，挺麻烦！

show databases; --查看表

create database test; --创建表