详解MySQL主从复制

古城微笑少年丶 2023-10-10 18:46 27阅读 0赞

**目录**

1.概述

2.配置使用

2.1.master配置

2.2.slave配置

2.3.认主

2.4.确认认主结果

3.请求分发

3.1.概述

3.2.手动分发

3.2.1.原生JDBC

3.2.2.数据源

3.2.3.中间件

--------------------

## 1.概述 ##

在实际的数据密集型应用中，数据库层面往往呈现两个特点：

*  单点数据库难以扛得住。
 *  读操作远远多于写操作。

综上所述我们可以使用多个数据库节点来组成集群的。采用主从结构来进行数据同步，多个slave节点向一个master节点拿数据。对数据库的写操作（insert）全在master上节点上进行，从而保证master的数据是全局最新最完整的，slave去master上拿数据进行数据同步，然后读操作就可以负载均衡的分发到不同的从节点上进行处理，这样就有效的减轻了数据库中单节点的压力。

以上说的方式就是MySQL的"主从复制"，它是MySql最常见的集群方式之一。

以下是MySQL主从复制的实现原理：

master将操作记录写到自己的二进制日志文件，salve开一个线程去读master的日志文件，将日志内容写到中继日志中，再照着中继日志，做一遍其中的事件。

![d758f0c34c9e44aaba0005ddd8a7800c.png][]

## 2.配置使用 ##

### 2.1.master配置 ###

配置文件添加以下内容：

server-id=1 集群中每个server的server-id保持唯一性。

log-bin=mysql-bin 开启二进制日志文件，并将日志文件命名为mysql-bin

### 2.2.slave配置 ###

配置文件添加以下内容：

server-id=2 集群中每个server的server-id保持唯一性。

log-bin=XXX 如果是扮演单独的从节点，而不扮演链式结构中其他节点的主节点，可以不配。

### 2.3.认主 ###

**注意：无论master还是slave，重启后都必须重新认主！**

在master上使用

> show master status;

可以查看到主节点的日志文件在集群中的名称以及分区号：

![cbd4e9936e2d4ff99831556a6359472f.png][]

在从节点上进行认主操作：

change master to master\_host='192.168.1.100',

master\_user='root',master\_password='admin',

master\_log\_file=' mysql-bin.000001',

master\_log\_pos= 154;

注意:从节点上配置认主的时候，master\_user 和master\_password所配置的账号必须要有复制权限，如果没有权限的话，需要额外进行权限分配。

分配方式：

在master上：

> GRANT REPLICATION SLAVE ON \*.\* TO '用户名'@'%';
> 
> FLUSH PRIVILEGES;

### 2.4.确认认主结果 ###

认主操作完成后，可以在从节点上确认是否成功与master同步：

> show slave status\\G0;

会显示以下内容：

![3cbfac1ab514469f827ec0bba08166c8.png][]

其中有两个进程：

1.  slave\_io\_running，负责与master通信，
    
    slave\_sql\_running，负责自己的sql。

两个进程都必须同时为yes才可以正常完成主从复制。

但是默认情况下一旦出现sql错误，slave\_sql\_running线程会直接阻塞，变为状态变为no。

通过在slave上配置遇见错误跳过，即可：

![3051c0a5d55a41df8853de72b8c8543a.png][]

## 3.请求分发 ##

### 3.1.概述 ###

配置完主从复制后，还存在一个问题，就是业务层面怎么将请求区分出来分别进行转发，怎么将写请求分发到master节点、将读请求分发到slave节点。

方法有两种：

*  手动分发
 *  使用中间件

手动分发，即手动将写操作分发到master节点上，读操作分发到slave节点上。

### 3.2.手动分发 ###

#### 3.2.1.原生JDBC ####

实际工程中原生JDBC很少会使用了，这里节约篇幅只说一下实现思想，不做展开。如果是使用原生JDBC的话，每个数据库节点准备一套对应的JDBC连接参数（url、username、password），读的时候进行一次负载均衡，先确定出写到哪个slave上去，然后用该slave对应的那套JDBC连参数去取connection就行了。

#### 3.2.2.数据源 ####

如果是使用了数据源的话，可以使用多数据源的方式来实现请求的分发。为master和slave分别准备数据源，将写操作、读操作分别放在不同的mapper，每个mapper用不同的数据源，将请求分发到不同的节点上去。

以下是spring boot+mybatis的示例：

> 配置多数据源：
> 
> \# 主节点数据源配置  
> spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver  
> spring.datasource.master.jdbc-url=jdbc:mysql://master-host:3306/database  
> spring.datasource.master.username=username  
> spring.datasource.master.password=password
> 
> \# 从节点数据源配置  
> spring.datasource.slave.driver-class-name=com.mysql.jdbc.Driver  
> spring.datasource.slave.jdbc-url=jdbc:mysql://slave-host:3306/database  
> spring.datasource.slave.username=username  
> spring.datasource.slave.password=password

配置数据源和SqlSessionFactory：

@Configuration
    @MapperScan(basePackages = "com.example.mappers", sqlSessionTemplateRef = "masterSqlSessionTemplate")
    public class MasterDataSourceConfig {
        @Bean(name = "masterDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.master")
        public DataSource masterDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "masterSqlSessionFactory")
        public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource);
            // 其他配置项
            return sessionFactoryBean.getObject();
        }
    
        @Bean(name = "masterSqlSessionTemplate")
        public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }
    
    @Configuration
    @MapperScan(basePackages = "com.example.mappers", sqlSessionTemplateRef = "slaveSqlSessionTemplate")
    public class SlaveDataSourceConfig {
        @Bean(name = "slaveDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.slave")
        public DataSource slaveDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "slaveSqlSessionFactory")
        public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource);
            // 其他配置项
            return sessionFactoryBean.getObject();
        }
    
        @Bean(name = "slaveSqlSessionTemplate")
        public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

在mapper上进行分发：

@Mapper
    @Qualifier("masterSqlSessionTemplate")
    public interface MasterMapper {
        // 主节点操作
        @Insert("XXXXX")
        int insertToMaster();
    }
    
    @Mapper
    @Qualifier("slaveSqlSessionTemplate")
    public interface SlaveMapper {
        // 从节点操作
        @Select("XXXX")
        List<MyEntity> selectFromSlave();
    }

这里还是会存在一个问题就是没有充分利用到slave节点来进行负载均衡，我们可以改进一下：

首先实现自己的负载均衡策略：

public class LoadBalanceDataSource {
        private List<SqlSessionTemplate> sqlSessionTemplates;
    
        public LoadBalanceDataSource(List<SqlSessionTemplate> sqlSessionTemplates) {
            this.sqlSessionTemplates = sqlSessionTemplates;
        }
    
        public SqlSessionTemplate getReadSqlSessionTemplate() {
            // 根据负载均衡算法选择一个从节点
            int index = calculateIndex();
            return sqlSessionTemplates.get(index);
        }
    
        private int calculateIndex() {
            // 自定义负载均衡算法，例如轮询、随机等
            // 这里仅作示例，实际应根据具体情况选择适合的算法
            // ...
        }
    }

然后将这个策略实体托管给IOC：

@Configuration
    @MapperScan(basePackages = "com.example.mappers")
    public class MyDataSourceConfig {
        @Autowired
        private List<SqlSessionTemplate> sqlSessionTemplates;
    
        @Bean
        public LoadBalanceDataSource loadBalanceDataSource() {
            return new LoadBalanceDataSource(sqlSessionTemplates);
        }
    }

然后，在需要的地方，注入：

@Service
    public class MyService {
        @Autowired
        private LoadBalanceDataSource loadBalanceDataSource;
    
        public void readOperation() {
            SqlSessionTemplate readSqlSessionTemplate = loadBalanceDataSource.getReadSqlSessionTemplate();
            // 使用readSqlSessionTemplate执行读操作
            // ...
        }
    }

#### 3.2.3.中间件 ####

前面手动分发的时候我们会发现一个问题，就是我们需要大量的代码去手写整个分发过程，而且需要我们去手动去写负载均衡算法，往往我们自己实现的负载均衡算法，效果不会很好。所以有没有开源组件可以帮我们实现请求分发的这个效果喃？其实有专门的这一类组件——数据库中间件。它们在client和数据库server间作为中间层，请求先走到它们上面，它们再对请求做二次处理，这种二次处理就提供了诸如：负载均衡、安全访问控制、故障转移、请求过滤等能力。

以下给出一个使用数据库中间件中最热门、应用最为广泛的proxy sql解决读写请求分发的示例：

1.安装ProxySQL：根据您的操作系统，从ProxySQL的官方网站下载并安装适当版本的ProxySQL。

2.配置ProxySQL：创建ProxySQL的配置文件，通常为`proxysql.cnf`，并根据您的环境进行配置。以下是一个示例配置文件的基本结构：

datadir="/var/lib/proxysql"
    
    admin_variables=
    {
        admin_credentials="admin:admin"
        mysql_ifaces="0.0.0.0:6032"
    }
    
    mysql_variables=
    {
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033;/path/to/mysql.sock"
        default_schema="information_schema"
        stacksize=1048576
    }
    
    mysql_servers =
    (
        { address = 'master_host', port = master_port, hostgroup = 1 },
        { address = 'slave1_host', port = slave1_port, hostgroup = 2 },
        { address = 'slave2_host', port = slave2_port, hostgroup = 2 },
        ...
    )
    
    mysql_users =
    (
        { username = 'your_username', password = 'your_password', default_hostgroup = 1, active = 1 },
    )
    
    mysql_query_rules =
    (
        { rule_id = 1, match_pattern = "^SELECT.*FOR UPDATE", destination_hostgroup = 1 },
        { rule_id = 2, match_pattern = ".*", destination_hostgroup = 2 },
    )

在上述配置中，·需要替换以下内容：

> `admin_credentials`中的用户名和密码，用于访问ProxySQL的管理接口。
> 
> `mysql_servers`中的主节点和从节点的主机名（或IP地址）和端口号。
> 
> `mysql_users`中的用户名和密码，用于连接到MySQL数据库。
> 
> `mysql_query_rules`中的规则，用于指定不同类型的查询应该转发到哪个主机组。

3.启动ProxySQL：运行以下命令启动ProxySQL：

> proxysql --initial -f -c /path/to/proxysql.cnf

4.配置应用程序连接：将应用程序的数据库连接配置更改为连接到ProxySQL的地址和端口号。例如，使用JDBC连接字符串：

> jdbc:mysql://proxysql\_host:proxysql\_port/database\_name

将`proxysql_host`和`proxysql_port`替换为ProxySQL的主机名（或IP地址）和端口号，`database_name`替换为实际的数据库名称。

[d758f0c34c9e44aaba0005ddd8a7800c.png]: https://img-blog.csdnimg.cn/d758f0c34c9e44aaba0005ddd8a7800c.png
[cbd4e9936e2d4ff99831556a6359472f.png]: https://img-blog.csdnimg.cn/cbd4e9936e2d4ff99831556a6359472f.png
[3cbfac1ab514469f827ec0bba08166c8.png]: https://img-blog.csdnimg.cn/3cbfac1ab514469f827ec0bba08166c8.png
[3051c0a5d55a41df8853de72b8c8543a.png]: https://img-blog.csdnimg.cn/3051c0a5d55a41df8853de72b8c8543a.png