docker-compose安装mysql一主二从读写分离
一、docker安装
- 准备3台服务器 ,root@my-study-1 ,root@my-study-2,root@my-study-3
- 查看服务器的版本 ,Centos 7.X以上,内核版本高于3.10才能支持docker
[root@my-study-1 ~]# uname -a
Linux my-study-1 3.10.0-1160.15.2.el7.x86_64 #1 SMP Wed Feb 3 15:06:38 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
[root@my-study-1 ~]#
- 分别在3台服务器上安装docker
[root@my-study-1 ~]# yum -y install docker-io
- 启动docker
[root@my-study-1 ~]# service docker start
Redirecting to /bin/systemctl start docker.service
[root@my-study-1 ~]#
二、docker-compose安装
- 分别在3台服务上安装docker-compose并添加可执行权限
[root@my-study-1 ~]# curl -L https://get.daocloud.io/docker/compose/releases/download/1.22.0/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
[root@my-study-1 ~]# chmod +x /usr/local/bin/docker-compose
三、配置并启动主库mysql-master
- 配置文件准备,目录结构如下
├── home
└── mysql
└── master
└── docker-compose.yml
├── master.cnf
- 配置 master.cnf
[root@my-study-1 /]# vim /home/mysql/master/master.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
log-bin = mysql-bin
server-id = 1
- 配置docker-compose.yml
version: '3.2'
services:
mysql-master:
image: "mysql:5.7"
container_name: mysql-master
restart: always
privileged: true
environment:
MYSQL_ROOT_PASSWORD: 66666
command:
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--max_allowed_packet=128M;
ports:
- 3306:3306
volumes:
- /home/mysql/master/master.cnf:/etc/mysql/my.cnf
- 启动mysql-master实例
[root@my-study-1 master]# cd /home/mysql/master/
[root@my-study-1 master]# docker-compose up -d
- 查看mysql-master是否启动成功
[root@my-study-1 master]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
4e5f758289dd mysql:5.7 "docker-entrypoint..." 8 seconds ago Up 6 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql-master
四、配置并启动从库mysql-slave
步骤与mysql-mater一样,只是个人习惯修改了一下配置文件的命名
- 配置文件准备,目录结构如下
├── home
└── mysql
└── master
└── docker-compose.yml
├── slave.cnf
- 配置 slave.cnf
[root@my-study-1 /]# vim /home/mysql/slave/slave.cnf
注意:这里的server-id每个节点都必须唯一
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
log-bin = mysql-bin
server-id = 2
- 配置docker-compose.yml
version: '3.2'
services:
mysql-slave:
image: "mysql:5.7"
container_name: mysql-slave
restart: always
privileged: true
environment:
MYSQL_ROOT_PASSWORD: 66666
command:
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--max_allowed_packet=128M;
ports:
- 3306:3306
volumes:
- /home/mysql/slave/slave.cnf:/etc/mysql/my.cnf
- 启动mysql-slave实例
[root@my-study-1 master]# cd /home/mysql/slave/
[root@my-study-1 master]# docker-compose up -d
- 查看mysql-slave是否启动成功
[root@my-study-2 slave]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d799324e920a mysql:5.7 "docker-entrypoint..." 6 seconds ago Up 5 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql-slave
五、主从复制配置
- 进入root@my-study-1docker容器连接上mysql-master
[root@my-study-1 master]# docker exec -it mysql-master bash
root@4e5f758289dd:/# mysql -uroot -p66666
- 查看mysql-master的master status
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
此时不要操作mysql-master,记录下以上的信息接下来会用到
- 进入root@my-study-2的docker容器连接上mysql-slave
[root@my-study-2 slave]# docker exec -it mysql-slave bash
root@d799324e920a:/# mysql -uroot -p66666
mysql>
- 设置主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='47.242.208.137', --master的IP
-> master_user='root',--连接master的用户名
-> master_log_file='mysql-bin.000003', --第2步骤查询到的结果
-> master_log_pos=154, --第2步骤查询到的结果
-> master_port=3306, --master的端口
-> master_password='66666'; --连接master的密码
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
- 查询slave的状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
看到以下两个yes即配置成功
备注:root@my-study-3同样的操作
六、验证主从复制是否成功
- 本地打开Navicat Premium连接上以上3个数据库
备注: 阿里云上的3306必须打开
- 在root@my-study-1 新建一个库名称为study,在study下面建一张表
发现2个从库的表都同步了,说明一主二从的mysql搭建成功
七、写个springboot的demo来测试效果
- 用idea开发工具新建一个SSM工程,在pom.xml引入依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
- 配置yml
sharding:
jdbc:
datasource:
names: ds0,ds1,ds2
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class: com.mysql.jdbc.Driver
url: jdbc:mysql://47.242.208.137:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: root
password: 66666
maxPoolSize: 50
minPoolSize: 1
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class: com.mysql.jdbc.Driver
url: jdbc:mysql://47.242.246.9:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: root
password: 66666
maxPoolSize: 50
minPoolSize: 1
ds2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class: com.mysql.jdbc.Driver
url: jdbc:mysql://47.243.87.202:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: root
password: 66666
maxPoolSize: 50
minPoolSize: 1
config:
sharding:
default-data-source-name: ds0
masterslave:
name: ms
master-data-source-name: ds0
slave-data-source-names: ds1,ds2
props:
sql.show: true
备注:为了避免从库被误操作,建议建个只读的用户
mysql> CREATE USER 'slaveuser'@'%' IDENTIFIED BY '66666';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> GRANT select ON *.* to 'slaveuser'@'%';
Query OK, 0 rows affected (0.01 sec)
- 分别写一个查询和新增的方法测试一下
package com.xmit.controller;
import com.xmit.domain.Village;
import com.xmit.service.IVillageService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/vallage")
@Repository
public class VillageController {
@Autowired
IVillageService villageService;
@GetMapping("/findAll")
public Object findAll() {
return villageService.findAll();
}
@PostMapping("/add")
public Object Add(@RequestBody Village village) {
villageService.add(village);
return "ok";
}
}
- 先看下查询,执行了几次之后发现都是查询ds1,ds2的库
从日志可以看出这里的查询默认策略是轮询,可以 load-balance-algorithm-type: random 设置成随机的
- 再看下新增
发现新增的后面查询突然变成了主库?其实sharding-jdbc早就为我们封装好了很多实际使用上的遇到的问题,因为主从复制的异步的,为了保证数据一致性,有更新操作(应该是同一个事务中的更新和查询:待验证)后的查询就直接查询主库了。
八、总结
本文主要是描述了如何部署主从复制的mysql,关于sharding-jdbc的一些细节就没有展开具体的说明,希望有时间能补上,如有错误还请各位大神指出。关于mysql的安装有很多方法,希望大家都能选择更便捷的方式去安装,本文提到的用docker-compose也是希望能够更好的保留这些命令,但是可能会增加写配置文件的工作量,不过对于学习还是挺有帮助的。
如有转载,请注明出处,谢谢。