MySQL5.7主从复制搭建部署以及集成springboot使用

自从入行就开始接触mysql，中间也间断几年使用sqlserver，做过sqlserver的集群部署使用，这段时间再此使用mysql，项目涉及的数据量级越来越大、对数据安全的要求也越来越高，开始广泛地使用主从复制，主库进行写操作，从库进行读取统计操作。以此为背景，结合实际使用，以及相关线上资料文件，学习主从复制原理知识，在本地实际搭建部署配置mysql主从库，并结合springboot、mybatis、dynamic多数据源的实际使用，汇总整理此文，为后续深入学习以及使用做记录，也为后来者提供参考借鉴，文中不免疏漏之处，望读者予以指正，不胜感激！

1. mysql主从复制原理

上图中有两个服务器， 演示了从一个主服务器（master） 把数据同步到从服务器（slave）的过程。

这是一个主-从复制的例子。

对于一个mysql服务器， 一般有两个线程来负责复制和被复制。当开启复制之后。

1. 作为主服务器Master， 会把自己的每一次改动都记录到 二进制日志 Binarylog 中。（从服务器会负责来读取这个log， 然后在自己那里再执行一遍。）

2. 作为从服务器Slave， 会用master上的账号登陆到 master上， 读取master的Binarylog, 写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。 到这里主服务器上的更改就同步到从服务器上了。

2. mysql单机部署

2.1 部署环境

1. 服务器以及mysql版本

CentOS Linux release 7.9.2009 (Core)
MySQL 5.7.36

2. 原环境检查

//检查系统中有无安装过mysql和mariadb
rpm -qa|grep mysql
//检查与卸载
rpm -qa|grep mariadb
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

2.2 部署步骤

1. 安装包下载

https://dev.mysql.com/downloads/mysql/
Linux – Generic

2. 解压、创建用户、构建安装目录、设置目录权限

tar -zvxf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql

groupadd mysql
useradd -r -g mysql mysql

在/usr/local/mysql下创建数据目录data，日志目录log
mkdir data
mkdir log
touch log/error.log

修改文件目录权限
chown -R mysql:mysql /usr/local/mysql/

3. 编译安装并初始化

/usr/local/mysql/bin目录下执行
./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

记住初始密码_Ky_slMi2UV%

4. 设置配置文件

vi /etc/my.cnf

[client]
#客户端设置
port    = 3306
socket    = /usr/local/mysql/data/mysql.sock
default-character-set = utf8mb4

[mysqld]
#mysql启动时使用的用户
#user    = mysql
#默认连接端口
port    = 3306
#为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
socket    = /usr/local/mysql/data/mysql.sock
#数据库服务器id，这个id用来在主从服务器中标记唯一mysql服务器
server-id = 1
#端口绑定的ip地址，0.0.0.0表示允许所有远程访问，127.0.0.1表示只能本机访问，默认值为*
bind-address = 0.0.0.0
#默认名为 主机名.pid,在数据库/mysql/data/主机名.pid,记录mysql运行的process id
#如果存在，再次start时会报已经启动
pid-file = /usr/local/mysql/data/mysql.pid

#安装目录
basedir    = /usr/local/mysql
#数据库存放目录
datadir    = /usr/local/mysql/data/

#系统数据库编码设置，排序规则
character_set_server = utf8mb4
collation_server = utf8mb4_bin

###########################日志设置######################################
##错误日志：记录启动，运行，停止mysql时出现的信息
log-error = /usr/local/mysql/log/error.log

##二进制日志设置
#默认不开启二进制日志
log_bin = ON
#设置二进制路径时，如果没有声明log_bin=OFF，会开启日志
log-bin = /usr/local/mysql/data/mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format= ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image= minimal

##慢查询，开发调式阶段才需要开启慢日志功能。上线后关闭
slow_query_log = ON
#慢日志文件路径
slow_query_log_file = /usr/local/mysql/log/slow_query.log
#MySQL能够记录执行时间超过参数 long_query_time 设置值的SQL语句，默认是不记录的。超过这个时间的sql语句会被记录到慢日志文件中
long_query_time = 10

5. 启动服务

/usr/local/mysql/support-files/mysql.server start

//添加软连接
ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
//重启mysql服务
service mysql restart

6. 登录修改密码设置远程连接

mysql -u root -p
set password for root@localhost = password('root');
use mysql;
grant all privileges on *.* to root@'%' identified by 'root';
flush privileges;

7. 设置开机自启

//将服务文件拷贝到init.d下，并重命名为mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
//赋予可执行权限
chmod +x /etc/init.d/mysqld
//添加服务
chkconfig --add mysqld
//显示服务列表
chkconfig --list

3. 主从配置

3.1 配置从库配置文件

#数据库服务器id，这个id用来在主从服务器中标记唯一mysql服务器
server-id = 2
# 启用中继日志，其中mysql-relay表示日志的文件名称，文件存放在datadir参数指向的目录下面
relay-log = mysql-relay-bin

3.2 查看主库状态

show master status;

添加一个用户【用于在从库主机中登录进行同步】:
create user 'replicate'@'%' identified by 'replicate';
再对该用户授予复制权限：
grant replication slave on *.* to 'replicate'@'%';
使执行生效：
flush privileges;

查看：Slave_IO_Running: YES、Slave_SQL_Running: YES，说明配置成功。

3.3 从库执行同步命令

change master to master_host='192.168.109.138',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000003',master_log_pos=154;
此处使用拥有全部权限得root账户进行操作

开启从机角色的
start slave;

查看从库状态
show slave status;

4. 测试以及端口开放

主库创建数据库、创建表；查看从库是否同步创建数据库表；

执行过程中注意3306端口开放情况

#查看想开的端口是否已开（开启:yes,未开启:no）：
firewall-cmd --query-port=3306/tcp
#添加指定需要开放的端口：
firewall-cmd --add-port=3306/tcp --permanent
#重载入添加的端口：
firewall-cmd --reload
#查询3306端口是否开启成功：
firewall-cmd --query-port=3306/tcp
#移除3306端口：
firewall-cmd --permanent --remove-port=3306/tcp

5. springboot结合mybatis dynamic使用主从库

5.1 引用依赖

<!-- Mysql Connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

        <!-- Mybatis 依赖配置 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <!-- Druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!-- Dynamic DataSource -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>

5.2 配置文件

# Spring
spring:
  application:
    # 应用名称
    name: ahunicom-01
  profiles:
    # 环境配置
    active: dev
  datasource:
    dynamic:
      primary: master
      strict: false
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,wall,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=1
      datasource:
        # 主库数据源
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.109.138:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: root
        # 从库数据源
        slave:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.109.139:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
          username: root
          password: root

# mybatis配置
mybatis:
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapperLocations: classpath:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

5.3 主从库注解类

/**
 * 主库数据源
 */
@Target({ ElementType.TYPE, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS("master")
public @interface Master{
}

/**
 * 从库数据源
 */
@Target({ ElementType.TYPE, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS("slave")
public @interface Slave{
}

5.3 mapper注解使用

@Mapper
public interface TestMapper {

   // 未加注解，默认使用primary数据源
    int insertUser(@Param("name") String name,@Param("age") int age,@Param("birthday") Date birthDay);

    @Slave
    String getNameById(String id);
}

6. 参考资料

https://baijiahao.baidu.com/s?id=1686499484922123145&wfr=spider&for=pc
https://blog.csdn.net/li1325169021/article/details/121515102