应用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 = mysqlbinlog_cache_size = 1M# 二级制主动删除的天数，默认为0，表白没有主动删除，启动时和二级制日志循环可能删除工夫expire_logs_days = 7log_bin_trust_function_creators = 1binlog_format=mixed# MySQL 8.x，须要如下配置default_authentication_plugin=mysql_native_passwordcharacter-set-server=utf8mb4collation-server=utf8mb4_unicode_ci配置 slave my.cnf
server_id = 102log-bin = mysql-binrelay_log = relicas-mysql-relay-bin log-slave-updates = 1binlog-ignore-db = mysqllog_bin_trust_function_creators = 1binlog_format=mixedread_only = 1# MySQL 8.x，须要如下配置default_authentication_plugin=mysql_native_passwordcharacter-set-server=utf8mb4collation-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 NAMESbf3f254d75b2 mysql "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-slave691e10949d3f 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=156mysql> 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: 8001spring: 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 设置
@Configurationpublic 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 失效。
@Configurationpublic 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 验证成绩
@Componentpublic 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)@Slf4jpublic 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 插入 后果如下 胜利切换到master 库
再运行 findOne 办法 应用默认连贯slave ，能胜利查问到后果
最初混合应用也是没问题的