3. Mysql主从复制

3.1 复制概述

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

3.2 复制原理

MySQL 的主从复制原理如下。

从上层来看，复制分成三步：

1.Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。

2.主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。

3.slave重做中继日志中的事件，将改变反映它自己的数据。

3.3 复制优势

MySQL 复制的要点主要包含以下三个方面：

1.主库出现问题，可以快速切换到从库提供服务。

2.可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。

3.可以在从库中执行备份，以避免备份期间影响主库的服务。

3.4 搭建步骤

3.4.1 master(主库)

1.在master 的配置文件（/usr/my.cnf）中，配置如下内容：

#mysql 服务ID,保证整个集群环境中唯一
server-id=1
#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
#错误日志,默认已经开启
#log-err
#mysql的安装目录
#basedir
#mysql的临时目录
#tmpdir
#mysql的数据存放目录
#datadir
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

2.执行完毕之后，需要重启Mysql：

systemctl restart mysqld

3.创建同步数据的账户，并且进行授权操作：

grant replication slave on *.* to 'slave128'@'192.168.75.128' identified by 'slave128';
flush privileges;

如果提示Your password does not satisfy the current policy requirements，则根据需要自己修改下配置，参考链接

4.查看master状态（因为之前配置过日志，可以先清除一下reset master）：

show master status;

字段含义：

File : 从哪个日志文件开始推送日志文件
Position ： 从哪个位置开始推送日志
Binlog_Ignore_DB : 指定不需要同步的数据库

3.4.2 slave(从库)

首先把虚拟机克隆一台，然后配置下网络就能使用了。

1.在 slave 端配置文件中，配置如下内容：

#mysql服务端ID,唯一
server-id=2
#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin

2.执行完毕之后，需要重启Mysql：

systemctl restart mysqld

3.执行如下指令 (指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。)：

change master to master_host= '192.168.75.127', master_user='slave128',master_password='slave128', master_log_file='mysqlbin.000001', master_log_pos=154;

4.开启同步操作

start slave;
show slave status\G;

如果出现如下错误：

则还需要改一个地方:

可以看到因为是克隆，两台机子uuid还是一样，更改下从机（随便改）：

然后重启服务。

然后重新配置（又报错了，根据提示需要先停止同步）：

可以看到配置成功了：

3.4.3 验证同步操作

1.在主库中创建数据库，创建表，并插入数据：

create database db01;
use db01;
create table user(
id int(11) not null auto_increment,
name varchar(50) not null,
sex varchar(1),
primary key (id)
)engine=innodb default charset=utf8;
insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');

2.在从库中查询数据，进行验证：

在从库中，可以查看到刚才创建的数据库和查询user表中的数据：

4. MySQL读写分离

4.1 概述

在Mysql主从复制的基础上，可以使用读写分离来降低单台Mysql节点的压力，从而来提高访问效率，读写分离的架构如下：

对于读写分离的实现，可以通过Spring AOP 来进行动态的切换数据源。

4.2 创建项目

创建一个spring boot项目，使用mybatis操作数据库，数据库和表使用的是demo_03的tb_user表，那个表使用的是MyISAM引擎，因为后面有需求，现在改为Innodb的。

表结构和数据：

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `tb_user` VALUES (1, '令狐冲');
INSERT INTO `tb_user` VALUES (2, '田伯光');
SET FOREIGN_KEY_CHECKS = 1;

4.2.1 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>mysqlrw</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysqlrw</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--加入aop-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

4.2.2 application.properties

#master
spring.datasource.master.driverClassName=com.mysql.cj.jdbc.Driver
#这里要用jdbcUrl，url是不行的，看默认数据源HikariConfig类
spring.datasource.master.jdbcUrl=jdbc:mysql://192.168.75.127:3306/demo_03?characterEncoding=utf8
spring.datasource.master.username=root
spring.datasource.master.password=123456
#slave
spring.datasource.slave.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.slave.jdbcUrl=jdbc:mysql://192.168.75.128:3306/demo_03?characterEncoding=utf8
spring.datasource.slave.username=root
spring.datasource.slave.password=123456

4.2.3 主要配置

三层架构的代码就不贴了，直接上配置：

当前线程需要使用的数据源：

package com.example.mysqlrw.config;
/**
 * @author baikunlong
 * @date 2021/2/5 11:32
 */
public class HandleDataSource {
    public static final ThreadLocal<String> holder=new ThreadLocal<>();
    public static void putDataSource(String dataSource){
        holder.set(dataSource);
    }
    public static String getDataSource(){
        return holder.get();
    }
}

自定义路由数据源

package com.example.mysqlrw.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * @author baikunlong
 * @date 2021/2/5 11:34
 */
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("determineCurrentLookupKey()--->: "+HandleDataSource.getDataSource());
        return HandleDataSource.getDataSource();
    }
}

注册数据源相关bean：

package com.example.mysqlrw.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.HashMap;
/**
 * @author baikunlong
 * @date 2021/2/5 11:36
 */
@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource(){
        return DataSourceBuilder.create().build();
    }
    /**
     * 设置数据源路由，通过该类中的determineCurrentLookupKey方法决定使用哪个数据源
     * @return
     */
    @Bean
    public AbstractRoutingDataSource routingDataSource(){
        MyAbstractRoutingDataSource source = new MyAbstractRoutingDataSource();
        HashMap<Object, Object> map = new HashMap<>();
        map.put("master",masterDataSource());
        map.put("slave",slaveDataSource());
        // 默认使用主数据源
        source.setDefaultTargetDataSource(masterDataSource());
        source.setTargetDataSources(map);
        return source;
    }
    @Bean(name = "SqlSessionFactory")
    @Primary
    public SqlSessionFactory MasterSqlSessionFactory( DataSource routingDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(routingDataSource);//DataSource使用路由数据源
//        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//        try {
//            bean.setMapperLocations(resolver.getResources("classpath*:mappers/*.xml"));
//            bean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
            return bean.getObject();
//        } catch (Exception e) {
//            e.printStackTrace();
//            throw new RuntimeException(e);
//        }
    }
    @Bean(name = "TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(DataSource routingDataSource) {
        return new DataSourceTransactionManager(routingDataSource);
    }
    @Bean(name = "SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate MasterSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

最后使用切面实现在每次请求时动态选择数据源：

package com.example.mysqlrw.config;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
 * @author baikunlong
 * @date 2021/2/5 11:43
 */
@Aspect
@Component
@Order(-9999)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {
    @Pointcut("execution(* com.example.mysqlrw.controller.*.*(..))")
    public void pointcut(){}
    @Before("pointcut()")
    @Order(-9999)
    public void before(JoinPoint joinPoint){
        String name = joinPoint.getSignature().getName();
        System.out.println("执行方法："+name);
        //读用从机，比如get、find、select等开头的方法都可认定为读
        if(name.startsWith("get")){
            HandleDataSource.putDataSource("slave");
        }else {
            //写用主机
            HandleDataSource.putDataSource("master");
        }
    }
}

通过 @Order(-9999) 注解来控制事务管理器, 与该通知类的加载顺序 , 需要让通知类先加载 , 来判定使用哪个数据源。

4.3 验证

在主库和从库中，执行如下SQL语句，来查看是否读的时候，从从库中读取；写入操作的时候，是否写入到主库(因为上边开启了主从复制，所以从库的写操作数也会更新)。

5. MySQL性能优化

5.1 分析性能问题

系统中用户访问日志的数据量，随着时间的推移，这张表的数据量会越来越大，因此我们需要根据业务需求，来对日志查询模块的性能进行优化。

分页查询优化

由于在进行日志查询时，是进行分页查询，那也就意味着，在查看时，至少需要查询两次：

A. 查询符合条件的总记录数。–> count 操作

B. 查询符合条件的列表数据。–> 分页查询 limit 操作

通常来说，count() 都需要扫描大量的行（意味着需要访问大量的数据）才能获得精确的结果，因此是很难对该SQL进行优化操作的。如果需要对count进行优化，可以采用另外一种思路，可以增加汇总表，或者redis缓存来专门记录该表对应的记录数，这样的话，就可以很轻松的实现汇总数据的查询，而且效率很高，但是这种统计并不能保证百分之百的准确。对于数据库的操作，“快速、精确、实现简单”，三者永远只能满足其二，必须舍掉其中一个。

条件查询优化

针对于条件查询,需要对查询条件,及排序字段建立索引。

读写分离

通过主从复制集群，来完成读写分离，使写操作走主节点，而读操作，走从节点。

MySQL服务器优化

应用优化

5.1 性能优化 - 分页

5.1.1 优化count

创建一张表用来记录日志表的总数据量：

create table log_counter(
logcount bigint not null
)engine = innodb default CHARSET = utf8;

在每次插入数据之后，更新该表：

update log_counter set logcount = logcount + 1

在进行分页查询时, 获取总记录数，从该表中查询既可。

select logcount from log_counter limit 1

5.1.2 优化limit

在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。一个非常常见，而又非常头疼的分页场景就是"limit 1000000,10" ，此时MySQL需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记录，前1000000 记录会被抛弃，查询代价非常大。

当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。

优化前SQL：

select * from operation_log limit 3000000 , 10;

优化后SQL：

select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;

5.2 性能优化 - 索引

当根据操作人进行查询时，查询的效率很低，耗时比较长。原因就是因为在创建数据库表结构时，并没有针对于操作人字段建立索引。

CREATE INDEX idx_user_method_return_cost ON
operation_log(operate_user,operate_method,return_class,cost_time);

同上，为了查询效率高，我们也需要对 操作方法、返回值类型、操作耗时 等字段进行创建索引，以提高查询效率。

CREATE INDEX idx_optlog_method_return_cost ON
operation_log(operate_method,return_class,cost_time);
CREATE INDEX idx_optlog_return_cost ON operation_log(return_class,cost_time);
CREATE INDEX idx_optlog_cost ON operation_log(cost_time);

总结就是一句话，根据最左原则，建立需要用到的所有的复合索引速度就能提升。

5.3 性能优化 - 排序

在查询数据时，如果业务需求中需要我们对结果内容进行了排序处理，这个时候，我们还需要对排序的字段建立适当的索引，来提高排序的效率。

5.4 性能优化 - 读写分离

上面例子已经讲述了。

5.5性能优化 - 应用优化

5.5.1缓存

可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率。

5.5.2 全文检索

如果业务系统中的数据量比较大（达到千万级别），这个时候，如果再对数据库进行查询，特别是进行分页查询，速度将变得很慢（因为在分页时首先需要count求合计数），为了提高访问效率，这个时候，可以考虑加入Solr 或

者 ElasticSearch全文检索服务，来提高访问效率。

5.5.3 非关系数据库

也可以考虑将非核心（重要）数据，存在 MongoDB 中，这样可以提高插入以及查询的效率。

MySQL高级第四篇（共四篇）之MySQL常用工具、MySQL日志、MySQL主从复制、MySQL读写分离、MySQL性能优化（二）

3. Mysql主从复制

3.1 复制概述

3.2 复制原理

3.3 复制优势

3.4 搭建步骤

3.4.1 master(主库)

3.4.2 slave(从库)

3.4.3 验证同步操作

4. MySQL读写分离

4.1 概述

4.2 创建项目

4.2.1 pom.xml

4.2.2 application.properties

4.2.3 主要配置

4.3 验证

5. MySQL性能优化

5.1 分析性能问题

5.1 性能优化 - 分页

5.1.1 优化count

5.1.2 优化limit

5.2 性能优化 - 索引

5.3 性能优化 - 排序

5.4 性能优化 - 读写分离

5.5性能优化 - 应用优化

5.5.1缓存

5.5.2 全文检索

5.5.3 非关系数据库

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL高级第四篇（共四篇）之MySQL常用工具、MySQL日志、MySQL主从复制、MySQL读写分离、MySQL性能优化（二）

3. Mysql主从复制

3.1 复制概述

3.2 复制原理

3.3 复制优势

3.4 搭建步骤

3.4.1 master(主库)

3.4.2 slave(从库)

3.4.3 验证同步操作

4. MySQL读写分离

4.1 概述

4.2 创建项目

4.2.1 pom.xml

4.2.2 application.properties

4.2.3 主要配置

4.3 验证

5. MySQL性能优化

5.1 分析性能问题

5.1 性能优化 - 分页

5.1.1 优化count

5.1.2 优化limit

5.2 性能优化 - 索引

5.3 性能优化 - 排序

5.4 性能优化 - 读写分离

5.5性能优化 - 应用优化

5.5.1缓存

5.5.2 全文检索

5.5.3 非关系数据库

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像