MySQL主从复制

发表于 | 评论数： | 阅读次数：
本文字数： 14k | 阅读时长 ≈ 12 分钟

使用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 文件夹

1
2
3
4
5
6
7
8
mysql_cluster
  master/
    my.cnf
    mysql/
 slave/
    my.cnf
    mysql/
  docker-compose.yml

将从容器内复制出来my.cnf分别放入 master、slave 下

文件配置

设置 master my.cnf 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 下面配置为主节点设置 
#开启二进制日志
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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_ci

docker-compose.yml 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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

出现下面状态，表明两个节点都运行成功了

1
2
3
4
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
1
2
3
4
5
+------------------+----------+--------------+------------------+-------------------+
| 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

查看同步结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<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 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
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 设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@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

1
2
3
4
public enum  ClusteEnum {
    SLAVE,
    MASTER;
}

重写路由数据库实现根据key 切换数据库

1
2
3
4
5
6
public class DataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

DataSourceContextHolder 提供线程安全方式返回不同连接切换key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 生效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@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);
    }
}

自定义注解声明需要切换数据源

1
2
3
4
5
6
7
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DatabaseSelector {

    ClusteEnum value() default SLAVE;
    boolean clear() default true;
}

最后一步，设置AOP 环绕通知，动态修改数据库路由Key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@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 验证成果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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);
    }
}

编写测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@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 插入 结果如下hKFo4A.md.png 成功切换到master 库
再运行 findOne 方法 hKAkRI.md.png 使用默认连接slave ，能成功查询到结果
最后混合使用也是没问题的
hKAyy6.md.png