Docker安装mysql,以及mysql主从配置,SpringBoot的集成

最新推荐文章于 2023-06-24 11:36:29 发布

阿墨~(amo)

最新推荐文章于 2023-06-24 11:36:29 发布

阅读量325

点赞数

分类专栏： docker 文章标签： mysql docker spring boot

本文链接：https://blog.csdn.net/xiaotianshi521/article/details/127727187

版权

docker 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

docker 安装 mysql

1. 查询mysql 的版本

docker search mysql

在这里插入图片描述

2. 选择要拉去的mysql版本,创建docker镜像

docker pull mysql:5.7

3. 创建容器,并启动容器

docker run -dit --name mysql5.7 \
   -p 3308:3306 \
    -v /opt/docker/mysql/conf:/etc/mysql/conf.d \
    -v /opt/docker/mysql/logs:/logs \
    -v /opt/docker/mysql/data:/var/lib/mysql \
   -e MYSQL_ROOT_PASSWORD=root@123456 \
   -e TZ=Asia/Shanghai \
  mysql:8.0

4. 进入容器

docker exec -it mysql bash

5. 登入mysql账户,设置mysql权限

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

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

6.mysql的配置文件内容

[mysqld]
port=3306
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
#query_cache_type = 1
#query_cache_size = 8M
#query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin
server-id = 2
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
#sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

docker安装主从配置

1.创建两个mysql:5.7版本的容器

docker run -dit --name mysql5.7-master \
   -p 3306:3306 \
    -v /opt/docker/mysql/conf:/etc/mysql/conf.d \
    -v /opt/docker/mysql/logs:/logs \
    -v /opt/docker/mysql/data:/var/lib/mysql \
   -e MYSQL_ROOT_PASSWORD=root@123456 \
   -e TZ=Asia/Shanghai \
  mysql:5.7

修改主数据库的配置文件my.cnf中[mysqld]

[mysqld]
log-bin = mysql-bin
server-id = 1

docker run -dit --name mysql5.7-slave \
   -p 3307:3306 \
    -v /opt/docker/mysql/conf:/etc/mysql/conf.d \
    -v /opt/docker/mysql/logs:/logs \
    -v /opt/docker/mysql/data:/var/lib/mysql \
   -e MYSQL_ROOT_PASSWORD=root@123456 \
   -e TZ=Asia/Shanghai \
  mysql:5.7

修改从数据库的配置文件my.cnf中[mysqld]

[mysqld]
log-bin = mysql-bin
server-id = 2

2.修改主从数据库的权限

use mysql;
select host,user from user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root@123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root@123456' WITH GRANT OPTION;
CREATE USER 'root'@'%'IDENTIFIED BY 'root@123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;

3.查看是否授权成功

SHOW GRANTS for 'root'@'%';

4.Navcat连上从数据库,并执行下边的配置

CHANGE MASTER TO

MASTER_HOST='192.168.0.7',-- 宿主机IP地址

MASTER_PORT=3306, -- 主容器的端口

MASTER_USER='root',-- 主容器MysQL的用户名

MASTER_PASSWORD='root@123456',-- 主容器MysQL的密码

MASTER_LOG_FILE='mysql-bin.000002',-- 在主库中执行SHOW MASTER STATUS，将File列值填到此处

MASTER_LOG_POS=1220;-- 在主库中执行SHOW MASTER STATUS，将Position列值填到此处

5.MASTER_LOG_FILE和MASTER_LOG_POS的值通过show master status语句查询获取

MASTER_LOG_FILE='mysql-bin.000002',-- 在主库中执行SHOW MASTER STATUS，将File列值填到此处

MASTER_LOG_POS=1220;-- 在主库中执行SHOW MASTER STATUS，将Position列值填到此处

6.启动从数据库

START SLAVE;

7.在配置主从期间有任何问题,需要重新配置,必须按顺序执行下边语句

stop slave
reset master

8.如何知道主从搭建成功呢?

执行

SHOW SLAVE STATUS;

查看 Slave_IO_Running与Slave_SQL_Running
如果都是Yes，那么说明已经配置成功。

9.进行主从测试

在主库中新建一个数据库，再新建一个表，表里再随便插入一些数据，观察两个从库是否能同步操作及数据。

SpringBoot简单实现主从数据库读写分离

1.yml配置


# 数据源配置
spring:
  datasource:
  type: com.alibaba.druid.pool.DruidDataSource
  druid:
    # 主库数据源
    master:
    driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
    url: jdbc:log4jdbc:mysql://localhost:3306/xtpt_bengbu?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=Asia/Shanghai
    username: root
    password: 123456

    # 从库数据源
    slave:
    # 从数据源开关/默认关闭
    enabled: true
    # driverClassName: com.mysql.cj.jdbc.Driver
    driverClassName: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@10.163.4.18:1521:LMEP
    username: bengbu001
    password: 123456

2.设置从数据库为只读

# 查看 read_only 参数
mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+

# 动静批改 read_only 参数 （若想重启失效 则需将 read_only = 1 退出配置文件中）
mysql> set global read_only = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

# read_only 开启的状况下 操作数据
# 应用超级权限用户
mysql> create table tb_a (a int);
Query OK, 0 rows affected (0.05 sec)
# 应用一般权限用户
mysql> create table tb_b (b int); 
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

# 开启 super_read_only，再次应用超级权限用户来操作数据
mysql> set global super_read_only = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'super_read_only';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | ON    |
+-----------------+-------+
mysql> create table tb_c (c int);  
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

# 敞开 read_only 参数
mysql> set global read_only = 0;
Query OK, 0 rows affected (0.00 sec)

3.SpringBoot配置数据源

package demo.datasource.config;
 
import java.util.HashMap;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.springframework.beans.factory.annotation.Qualifier;
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 demo.datasource.config.bean.MyRoutingDataSource;
import demo.datasource.config.enums.DBTypeEnum;
 
/**
 * 多数据源配置
 */
 
@Configuration
public class DataSourceConfig {
 
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
 
}

4.MyBatista配置MyBatisConfig

package demo.datasource.config;
 
import javax.annotation.Resource;
import javax.sql.DataSource;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
 
/**
 * MyBatis配置
 */
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
 
	@Resource(name = "myRoutingDataSource")
	private DataSource myRoutingDataSource;
 
	@Bean
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		// 设置数据源
		sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
		// 添加一行如下代码,解决setTypeAliasesPackage别名设置不起作用问题
		sqlSessionFactoryBean.setVfs(SpringBootVFS.class);
		
		//设置mybatis的主配置文件
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        org.springframework.core.io.Resource mybatisConfigXml = resourceResolver.getResource(Cast.MYBATIS_CONFIG);
		sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
		// 设置别名包
        sqlSessionFactoryBean.setTypeAliasesPackage(Cast.PO_PACKAGE);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(Cast.MAPPER_SCANNER));
		
		return sqlSessionFactoryBean.getObject();
	}
 
	@Bean
	public PlatformTransactionManager platformTransactionManager() {
		return new DataSourceTransactionManager(myRoutingDataSource);
	}
}

5.枚举定义主从


package com.cjs.example.enums;
 
public enum DBTypeEnum {
 
    MASTER, SLAVE1, SLAVE2;
 
}

6.通过ThreadLocal将数据源设置到每个线程的上下文中

package demo.datasource.config.bean;
 
import java.util.concurrent.atomic.AtomicInteger;
 
import demo.datasource.config.enums.DBTypeEnum;
import demo.datasource.utils.Log;
 
/**
 * 通过ThreadLocal将数据源设置到每个线程上下文中
 */
public class DBContextHolder {
 
    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
 
    private static final AtomicInteger counter = new AtomicInteger(-1);
 
    public static void set(DBTypeEnum dbType) {
        contextHolder.set(dbType);
    }
 
    public static DBTypeEnum get() {
        return contextHolder.get();
    }
 
    public static void master() {
        set(DBTypeEnum.MASTER);
        Log.i(DBContextHolder.class, "切换到master");
    }
 
    public static void slave() {
        //  轮询
        int index = counter.getAndIncrement() % 2;
        if (counter.get() > 9999) {
            counter.set(-1);
        }
        Log.i(DBContextHolder.class, "轮询index："+index);
        if (index == 0) {
            set(DBTypeEnum.SLAVE1);
            Log.i(DBContextHolder.class, "切换到slave1");
        } else {
            set(DBTypeEnum.SLAVE2);
            Log.i(DBContextHolder.class, "切换到slave2");
        }
    }
 
}

7.设置路由key

package com.cjs.example.bean;
 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
 
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }
 
}

8.定义切面

package demo.datasource.aop;
 
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
 
import demo.datasource.config.bean.DBContextHolder;
 
/**
 * 设置路由key
 * 默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
 */
@Aspect
@Component
public class DataSourceAop {
 
    @Pointcut("!@annotation(demo.datasource.annotation.Master) " +
            "&& (execution(* demo.datasource.service..*.select*(..)) " +
            "|| execution(* demo.datasource.service..*.get*(..)))")
    public void readPointcut() {
 
    }
 
    @Pointcut("@annotation(demo.datasource.annotation.Master) " +
            "|| execution(* demo.datasource.service..*.insert*(..)) " +
            "|| execution(* demo.datasource.service..*.add*(..)) " +
            "|| execution(* demo.datasource.service..*.update*(..)) " +
            "|| execution(* demo.datasource.service..*.edit*(..)) " +
            "|| execution(* demo.datasource.service..*.delete*(..)) " +
            "|| execution(* demo.datasource.service..*.remove*(..))")
    public void writePointcut() {
 
    }
 
    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }
 
    @Before("writePointcut()")
    public void write() {
        DBContextHolder.master();
    }
 
 
    /**
     * 另一种写法：if...else...  判断哪些需要读从数据库，其余的走主数据库
     */
//    @Before("execution(* demo.datasource.service.impl.*.*(..))")
//    public void before(JoinPoint jp) {
//        String methodName = jp.getSignature().getName();
//
//        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
//            DBContextHolder.slave();
//        }else {
//            DBContextHolder.master();
//        }
//    }
}