使用Docker Compose搭建MySQL主从复制架构
环境准备
docker 安装MySQL数据库
docker pull mysql
运行MySQL容器
docker run --name mysql mysql -e MYSQL_ROOT_PASSWORD=123456
使用命令将MySQL配置文件my.cnf 复制出主机上
docker cp mysql:/var/lib/mysql/ D:/docker/mysql_cluster/my.cnf
拿到my.cnf原配置文件，加以改造就可以实现数据库主从同步了
配置文件
创建文件夹
在主机创建mysql_cluster 文件夹
mysql_cluster
master/
my.cnf
mysql/
slave/
my.cnf
mysql/
docker-compose.yml 将从容器内复制出来my.cnf分别放入 master、slave 下
文件配置
设置 master my.cnf
# 下面配置为主节点设置
#开启二进制日志
log_bin=mysql-bin
#为当前节点设置一个全局唯一的ID号
server_id=95
# 不需要同步数据库
binlog-ignore-db = mysql
binlog_cache_size = 1M
# 二级制自动删除的天数，默认为0，表达没有自动删除，启动时和二级制日志循环可能删除时间
expire_logs_days = 7
log_bin_trust_function_creators = 1
binlog_format=mixed
# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci配置 slave my.cnf
server_id = 102
log-bin = mysql-bin
relay_log = relicas-mysql-relay-bin
log-slave-updates = 1
binlog-ignore-db = mysql
log_bin_trust_function_creators = 1
binlog_format=mixed
read_only = 1
# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_cidocker-compose.yml 配置
version: '3.8'
services:
mysql-master:
container_name: mysql-master
hostname: mysql-master
image: mysql
restart: always
ports:
- "3306:3306"
volumes:
- D:/docker/mysql_cluster/master/my.cnf:/etc/mysql/my.cnf
- D:/docker/mysql_cluster/master/mysql:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: 123456
TZ: Asia/Shanghai
command: [
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_general_ci',
'--max_connections=3000'
]
networks: ## 引入外部预先定义的网段
myweb:
ipv4_address: 192.168.102.120 #设置ip地址
mysql-slave:
container_name: mysql-slave
hostname: mysql-slave
image: mysql
restart: always
ports:
- "3307:3306"
volumes:
- D:/docker/mysql_cluster/slave/my.cnf:/etc/mysql/my.cnf
- D:/docker/mysql_cluster/slave/mysql:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: 123456
TZ: Asia/Shanghai
command: [
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_general_ci',
'--max_connections=3000'
]
networks:
myweb:
ipv4_address: 192.168.102.121 #设置ip地址
networks:
myweb:
driver: bridge
ipam:
config:
# 自定义 网段
- subnet: "192.168.102.0/24"还用一点值得注意的，如果宿主机上的mysql 文件夹不是空的，配置中MySQL root 密码不生效的。有次我将一个正在运行中/var/lib/mysql copy到宿主机上，使用逻辑卷映射到容器中去，导致MySQL一直登录不上去。
设置主从同步
运行容器
docker-compose up -d
查看运行情况
docke ps -a
出现下面状态，表明两个节点都运行成功了
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bf3f254d75b2 mysql "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-slave
691e10949d3f mysql "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql-master登录master节点
设置slave 连接master节点
mysql> grant replication client,replication slave on *.* to 'root'@'192.168.102.120'
保存设置
mysql> flush privileges
获取binlog 文件名和Position
mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 156 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+登录slave 节点
mysql> reset master;
mysql> CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=156
mysql> start slave
查看同步结果
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql-master
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: relicas-mysql-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 156
Relay_Log_Space: 541
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Seconds_Behind_Master: 0
Replicate_Ignore_Server_Ids:
Master_Server_Id: 95
Master_UUID: 903599fc-0336-11ec-9228-0242adc80678
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400看见 Slave_IO_Running: Yes Slave_SQL_Running: Yes这两个都是Yes 说明同步已经成功了。
验证同步
连接master 节点，创建一个数据库，在新数据库下再创建一个新表。再连接slave 节点可以看见再master 创建数据库下的新表，这样就表明数据已经实现同步了。
使用springboot 搭建读写分离
maven pox.xml
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.1</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
主要原来就是利用org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource可以将不同连接设置到Map 根据key 获取获取dataSource ,重写determineCurrentLookupKey() ,实现对读取数据库时，切换到读库，写操作切换到写库。 下面会贴出全部代码
application.yml 配置
server:
port: 8001
spring:
datasource:
druid:
master:
url: jdbc:mysql://127.0.0.1:3306/example?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username: root
password: 123456
slave:
url: jdbc:mysql://127.0.0.1:3307/example?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username: root
password: 123456多数据库druid 设置
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource master() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.druid.slave")
public DataSource slave(){
return DruidDataSourceBuilder.create().build();
}
@Bean("dynamicDB")
public DataSourceRouter dynamicDB(@Qualifier("master") DataSource master,
@Qualifier("slave") DataSource slave){
DataSourceRouter router = new DataSourceRouter();
Map<Object,Object> map = new HashMap<>(2);
map.put(SLAVE,slave);
map.put(MASTER,master);
router.setTargetDataSources(map);
router.setDefaultTargetDataSource(slave);
return router;
}
}创建枚举类用作数据库路由Key
public enum ClusteEnum {
SLAVE,
MASTER;
}重写路由数据库实现根据key 切换数据库
public class DataSourceRouter extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.get();
}
}DataSourceContextHolder 提供线程安全方式返回不同连接切换key
public class DataSourceContextHolder {
private static ThreadLocal<ClusteEnum> contextHolder = new ThreadLocal<>();
public static void setEnum(ClusteEnum clusteEnum){
contextHolder.set(clusteEnum);
}
public static void remove(){
contextHolder.remove();
}
public static ClusteEnum get(){
return contextHolder.get();
}
}设置了多数据源，需要手上生成SqlSessionFactory,SqlSessionTemplate bean，让Mybatis 生效。
@Configuration
public class MybatisConfig {
@Resource(name = "dynamicDB")
private DataSource dataSource;
@Bean("sqlSessionFactory")
public SqlSessionFactory sessionFactory() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*Mapper.xml"));
return factoryBean.getObject();
}
@Bean
public PlatformTransactionManager transactionManager(){
return new DataSourceTransactionManager(dataSource);
}
@Bean("sqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate( @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}自定义注解声明需要切换数据源
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DatabaseSelector {
ClusteEnum value() default SLAVE;
boolean clear() default true;
}最后一步，设置AOP 环绕通知，动态修改数据库路由Key
@Slf4j
@Aspect
@Component
@Order(1) //这个注解很关键，如果没有不能成功切换数据源
public class DatabaseAopPointCut {
@Around("@annotation(tk.shenyifeng.study.bean.DatabaseSelector)")
public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
boolean clear = true;
try {
MethodSignature signature = (MethodSignature) pjp.getSignature();
Method method = signature.getMethod();
DatabaseSelector databaseSelector = method.getAnnotation(DatabaseSelector.class);
clear = databaseSelector.clear();
log.info("set cluster db => {}",databaseSelector.value().name());
DataSourceContextHolder.setEnum(databaseSelector.value());
return pjp.proceed();
}finally {
if (clear)
DataSourceContextHolder.remove();
}
}
}b编写一个简单service 验证成果
@Component
public class UserService {
@Autowired
private UserMaper userMaper;
@Transactional(rollbackFor = Exception.class)
@DatabaseSelector(MASTER)
public void insert(UserModel userModel){
userMaper.insert(userModel);
}
public UserModel findOne(int id){
return userMaper.findOne(id);
}
@DatabaseSelector(MASTER)
public void updateOne(UserModel userModel){
userMaper.updateOne(userModel);
}
}编写测试类
@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = RunApp.class)
@Slf4j
public class ClusterServiceTest {
@Autowired
private UserService userService;
@Test
public void insertTest(){
UserModel userModel = new UserModel();
userModel.setName("神易风");
userModel.setAge(8848);
userService.insert(userModel);
}
@Test
public void findOne(){
UserModel one = userService.findOne(1);
log.info("user data {}",one);
}
@Test
public void integrationTest(){
UserModel userModel = userService.findOne(1);
userModel.setName("神易风02");
userModel.setAge(18);
userService.updateOne(userModel);
}
}运行insert 插入 结果如下
再运行 findOne 方法 
最后混合使用也是没问题的
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。