配置(2):mysql8 主从搭建

前言

学习搭建mysql主从模式，在此记录。

 

 

安装mysql

参考：https://blog.csdn.net/qq_38188762/article/details/100077051

下载镜像
 
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
 
安装镜像
 
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
 
安装MySQL服务
 
yum install mysql-server

设置权限

chown mysql:mysql -R /var/lib/mysql

初始化mysql

mysqld --initializa

查看状态
systemctl status mysqld.service
查看进程
ps -ef|grep mysql

启动/设置开机自启

systemctl start mysqld

　systemctl enable mysqld

　systemctl daemon-reload

　　　　　　ps.这里报错Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.　　

　　　　vim /var/log/mysqld.log查看日志

　绕过权限登录

　mysqld --user=root --skip-grant-tables &

　修改密码（报错刷新下权限）

　alter user 'root'@'%' identified with mysql_native_password by '123456';

　远程访问

　UPDATE user SET `Host` = '%' WHERE `User` = 'root' LIMIT 1;

　刷新权限

　flush privileges;

 

 主从：

修改/etc/my.cnf(配置文件)

#主机配置，同步db1中的数据，不同步mysql自带的数据库
binlog-do-db=db1
binlog-ignore-db=mysql
#启用日志
log-bin=mysql-bin
#服务器唯一ID，所有MySQL的id不能一致
server-id=1


从机

change master to master_host='192.168.105.111',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000002';

 

springboot配置多数据源

application.yml

spring:
  datasource:
    master:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc- url:  *******
      username: root
      password: 123456
    salve1:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url:  ******
      username: root
      password: 123456
    salve2:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url:  ********
      username: root
      password: 123456

 

之后利用@ConfigurationProperties读取配置文件，手动build数据源bean，并且以一个map<数据源名，实例bean>存入dynamicDataSource中

 

package com.demo.config;

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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @Classname DataSourceConfig
 * @Description TODO
 * @Date 2020/5/24 8:56
 * @Created by cyh
 */
@Configuration
public class DataSourceConfig {
    /**
     *
     * @ConfigurationProperties 
     * @return data source
     */
    @Primary
    @Bean("master")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource master() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 
     *
     * @return the data source
     */
    @Bean("slave1")
    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slave1() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 
     *
     * @return the data source
     */
    @Bean("slave2")
    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slave2() {
        return DataSourceBuilder.create().build();
    }


    /**
     * Dynamic data source.
     *
     * @return the data source
     */

    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(4);
        dataSourceMap.put(DataSourceKey.master.getName(), master());
        dataSourceMap.put(DataSourceKey.slave1.getName(), slave1());
        dataSourceMap.put(DataSourceKey.slave2.getName(), slave2());


        // 将 master 数据源作为默认指定的数据源
        dynamicRoutingDataSource.setDefaultTargetDataSource(master());
        // 将 master 和 slave 数据源作为指定的数据源
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
        return dynamicRoutingDataSource;
    }






}

编写路由类来存放上述map的键，以便动态调用

package com.demo.config;

/**
 * @Classname DynamicDataSourceContextHolder
 * @Description TODO
 * @Date 2020/5/24 9:19
 * @Created by cyh
 */
public class DynamicDataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDataSource(DataSourceKey key) {
        contextHolder.set(key.getName());
    }

    public static String getDataSource() {
        return contextHolder.get();
    }

    public static void clearDataSource() {
        contextHolder.remove();
    }


}

继承抽象类AbstractRoutingDataSource，重写determineCurrentLookupKey，利用上面的路由类来传递数据源的键，以获得对应的数据源，由此，在外部可通过动态调用setDataSource来设定相应的键来匹配合适的数据源。

package com.demo.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @Classname DynamicRoutingDataSource
 * @Description TODO
 * @Date 2020/5/24 9:16
 * @Created by cyh
 */
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSource();
    }
}