[系统运维]CentOS7安装mysql5.7并搭建(主从复制、读写分离＜多数据源＞)

CentOS7安装mysql5.7并搭建(主从复制、读写分离＜多数据源＞)

1、准备两台虚拟机
这里小编整理了一张mysql5.7的安装图鉴供大家参考

这里是命令

vi /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


yum install mysql-community-server -y

systemctl start mysqld

grep 'temporary password' /var/log/mysqld.log

mysqladmin -u root -p password

Root!Q2w  👈这里是密码(因为mysql需要复杂的密码组合这里我自定义了一个)

grant all privileges on *.* to 'root'@'%' identified by 'Root!Q2w' with grant option;

flush privileges;

大家配合上面的图片和命令安装使用
2、主从复制

大家准备好两台装有MySQL服务的虚拟机后就可以开始准备搭建我们的主从复制

来吧展示！！！

1）分别配置主机与从机：Vim /etc/my.cnf
	server-id=1  #master
	log-bin=mysql-bin
	log-slave-updates
	slave-skip-errors=all
------------------------------
 	server-id=2  #slave
	log-bin=mysql-bin
	log-slave-updates
	slave-skip-errors=all
——————————————————————————————————————————	
2)确定关闭各自主机的防火墙
  systemctl stop firewalld
——————————————————————————————————————————
3）两台机器启动mysql服务
       systemctl start mysqld
——————————————————————————————————————————
4）登录两台机器的mysql验证配置是否生效
   如果没有生效出现server-id=0情况需要systemctl restart mysqld
   SHOW VARIABLES like 'server_id';
——————————————————————————————————————————
5)到master节点，查看主节点状态
       Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      651 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

——————————————————————————————————————————
6)再从节点上指定主节点数据库
Change master to
master_host='192.168.101.170',   
master_user='root',
master_password='Root!Q2w',
master_log_file='mysql-bin.000008',
master_log_pos=154;

配置的内容解释
master_host='ip地址',   
master_user='用户名',
master_password='用户密码',
master_log_file='上面的File值',
master_log_pos=上面的Position值;
——————————————————————————————————————————
7)开启从节点
   start slave;
——————————————————————————————————————————
8)查看从节点状态
  Show slave status\G;
	出现uuids重复执行：rm -rf /var/lib/mysql/auto.cnf
	重启mysql
	Stop slave;
	重新指定master
	Start slave;
	Show slave status\G;

3、读写分离(多数据源配置)—来吧展示！！！
这里我们使用springboot与mybatis-plus展示利用AOP对每个service方法进行动态的切换数据源
直接上代码

• pom.xml依赖

    <dependencies>
        <!--spring boot web 依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>

        <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <!--lombok 依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <!--springboot test测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- springboot-aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    </dependencies>

• application.yml（我们在写url时因为是手动注入factory对象所以url写成 jdbc-url）

我这里只搭建了一主一从下面的配置配置了两个从机指向一个Mysq从机服务，所以小伙伴是两台从机那么一定要修改自己配置的jdbc-url哦！！！

spring:
  datasource:
    master:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.101.170:3306/demo?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: Root!Q2w
    slave1:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.101.171:3306/demo?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: Root!Q2w
    slave2:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.101.171:3306/demo?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: Root!Q2w

• 配置类这里我们使用了动态数据源所以就要手动重新配置SqlSessionFactory并配置我们application.yml里的连接配置

@Configuration
public class UserDefineDataSourceConfig {

    @Bean
    //配置文件的数据源
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    //配置文件的数据源
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave01DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    //配置文件的数据源
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave02DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource proxyDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                      @Qualifier("slave01DataSource") DataSource slave01DataSource,
                                      @Qualifier("slave02DataSource") DataSource slave02DataSource){
        DataSourcetPorxy dataSourcetPorxy = new DataSourcetPorxy();
        dataSourcetPorxy.setDefaultTargetDataSource(masterDataSource);
        HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
        objectObjectHashMap.put("master",masterDataSource);
        objectObjectHashMap.put("slave-01",slave01DataSource);
        objectObjectHashMap.put("slave-02",slave02DataSource);
        dataSourcetPorxy.setTargetDataSources(objectObjectHashMap);

        return  dataSourcetPorxy;
    }

    /**
     * 当?定义数据源，?户必须覆盖SqlSessionFactory创建
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("proxyDataSource") DataSource dataSource) throws Exception {
        //这里我们使用了Mybatis-plus所以使用MybatisSqlSessionFactoryBean 否则使用BaseMapper接口中的方法会报错
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setTypeAliasesPackage("com.bugz.entities");
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com.bugz.mapper/*.xml"));

        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 当?定义数据源，?户必须覆盖SqlSessionTemplate,开启BATCH处理模式
     * @param sqlSessionFactory
     * @return
     */
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory")
                                                         SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory, ExecutorType.BATCH);
    }
    /***
     * 当?定义数据源，?户必须注?，否则事务控制不?效
     * @param dataSource
     * @return
     */
    @Bean
    public PlatformTransactionManager
    platformTransactionManager(@Qualifier("proxyDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

• 配置如何指定哪个数据源

public class DataSourcetPorxy extends AbstractRoutingDataSource {
    //定义数据源的key
    private   String masterDBKey="master";
    private   List<String> slaveDBKeys = Arrays.asList("slave-01","slave-02");
    //设定轮询算法的参数
    private static final  AtomicInteger round=new AtomicInteger(0);
    /**
     * 需要在该?法中，判断当前?户的操作是读操作还是写操作
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        String dbKey=null;
        OperType operType = OperTypeContextHolder.getOperType();
        if (operType.equals(OperType.WRITE)){
            dbKey=masterDBKey;
        }else {
            //轮询返回
            //获取自增数值
            int value = round.getAndIncrement();
            //大于100时重新赋值0
            if (value>100){
                round.set(0);
            }
            logger.info("rund值:——————————————————"+round.get());
            Integer index= round.get()%slaveDBKeys.size();
            dbKey=slaveDBKeys.get(index);

        }
        logger.debug("当前的DBkey:"+dbKey);
        return dbKey;
    }
}

• 定义一个枚举类区分读和**写

public enum OperType {
    WRITE,
    READ;
}

• 定义一个ThreadLocal变量存储枚举变量

public class OperTypeContextHolder {
    private static final ThreadLocal<OperType> OPER_TYPE_THREAD_LOCAL = new ThreadLocal<>();


    public static void setOperType(OperType operType) {
        OPER_TYPE_THREAD_LOCAL.set(operType);
    }

    public static OperType getOperType() {
        return OPER_TYPE_THREAD_LOCAL.get();
    }

    public static void clearOperType() {
        OPER_TYPE_THREAD_LOCAL.remove();
    }
}

• 定义AOP要判断的注解类

/**
 - 标记的业务?法是否是读操作
 */
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = {ElementType.METHOD})
public @interface SlaveDB {}

• 定义AOP切面

@Aspect
@Order(0)//保证切换数据源在事务之前触发 加入配置（0）
@Component
public class ServiceMethodAOP {

    private static final Logger logger= LoggerFactory.getLogger(ServiceMethodAOP.class);
    @Around("execution(* com.bugz.service..*.*(..))")
    public Object methodInterceptor(ProceedingJoinPoint pjp){
        Object result = null;
        try {
            //获取当前的?法信息
            MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
            Method method = methodSignature.getMethod();
            //判断?法上是否存在注解@SlaveDB
            boolean present = method.isAnnotationPresent(SlaveDB.class);
            OperType operType=null;
            if(!present){
                operType=OperType.WRITE;
            }else{
                operType=OperType.READ;
            }
            OperTypeContextHolder.setOperType(operType);
            logger.debug("当前操作："+operType);
            result = pjp.proceed();
            //清除线程变量
            OperTypeContextHolder.clearOperType();
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        }
        return result;
    }
}

我们还可以使用mycat中间件为我们的程序搭建读写分离，但是小编的电脑有点问题这里就不给大家演示了，如果有不同意见欢迎广大读者私信留言。