docker搭建mysql读写分离

 

写在前面

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

准备mysql主从服务

  1. 准备mysql容器
docker pull mysql:5.7
  1. 创建主从数据映射目录
// 创建目录,数据和配置不至于丢失
mkdir -p /usr/local/docker_app/mysql/master/conf.d/
mkdir -p /usr/local/docker_app/mysql/slave/conf.d/
  1. 创建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
  1. 创建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 3306:3306     -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
  1. 启动slave服务
docker run -d --name mysql-slave     -p 3307:3306     -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
  1. 进入容器修改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  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  1. 获取master容器的host
     docker inspect --format={{.NetworkSettings.IPAddress}} mysql-master
  1. 进入mysql-slave关联主从模式(自行更改那些参数)
    // mysql shell
    change master to master_host=172.17.0.2, master_user=root,master_password=root, master_log_file=master-bin.000003,master_log_pos=154;
// mysql shell
// 启用slave
start slave;

查看是否成功

show slave status \G;

以下两个都为yes就代表成功了

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
//说明主从成功

docker搭建mysql读写分离

 

 

  1. 测试主从
    在master创建test数据库,再到slave中查看是否存在test库,若存在则基本完成,若未成功检查binlog、master host、position是否正确;目前已经可以给前台使用,只不过要区分读写数据源,挺麻烦!
show databases;

create database test;

 

docker搭建mysql读写分离

上一篇:SQLSERVER错误-Invalid object name ‘dbo.tablename’. (Microsoft SQL Server, Error: 208)


下一篇:linux 安装 mongodb