mysql主从备份

今天恰好关注的公号推送了这个内容，参照着和网上相关资料来一遍。

公号地址：JAVA架构师宝典

mysql的备份是mysql日志（binlog）的复制。

开始操作：

首先创建两个mysql实例。

在数据库1中修改my.cnf配置文件:

添加以下代码

[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=100     #[必须]服务器唯一ID

在数据库2中修改相同位置配置文件:

[mysqld]
server-id=201     #[必须]服务器唯一ID

两个数据库配置好了。接下来是数据库中的配置操作了。

首先配置数据库1：

创建同步用户，释放远程登录权限，修改密码加密方式（使用的是mysql8.0，加密方式变更了）。

#创建用户
CREATE USER 'masterDb' @'%' IDENTIFIED BY 'Master@123456';
#启用复制用户
GRANT REPLICATION SLAVE ON *.* TO 'masterDb' @'%';
#查看密码加密方式
SELECT plugin FROM `user` where user = 'masterDb';
#由于是caching_sha2_password加密方式，修改为了->>>mysql_native_password
ALTER USER 'masterDb'@'%' IDENTIFIED WITH mysql_native_password BY 'Master@123456';
#刷新变更
flush privileges;
#查看当前状态
show master status;
#File==mysql-bin.000001	Position=1954

状态得到文件名和位置就配置完成了。

接下来配置数据库2：

#停用从库配置
stop slave;
#设置主库地址和同步位置
change master to master_host='数据库1的IP',master_user='masterDb',master_password='Master@123456',master_log_file='mysql-bin.000001',master_port=3306,master_log_pos=1954;
#开启从库配置
start slave;

数据库2配置完成。停用的操作是因为。配置一直有问题一直修改所以保留下这个语句。

验证操作为

#Slave_IO_running=yes
#Slave_SQL_running=yes
show slave status;

当以上两个参数都为yes即为配置完成。也可参照Slave_SQL_Running_State。

有问题这个参数会给出错误提示，非常的人性化。

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

接下来在项目中使用的测试：

pom依赖

        
        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>sharding-jdbc-spring-boot-starterartifactId>
            <version>4.0.0-RC1version>
        dependency>
        

spring配置

#数据源
spring:
  shardingsphere:
    datasource:
      names: master,slave
      #主库
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.2.3:3307/springboot?characterEncoding=UTF-8
        username: root
        password: admin
      #从库
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.2.3:3308/springboot?characterEncoding=UTF-8
        username: root
        password: admin
    masterslave:
      #读写分离配置，设置负载均衡的模式为轮询
      load-balance-algorithm-type: round_robin
      #最终的数据源名称
      name: dataSource
      #主库数据源名称
      master-data-source-name: master
      #从库数据源名称列表，多个逗号分隔
      slave-data-source-names: slave
    props:
      sql:
        #开启SQL显示，默认false
        show: true
    # 覆盖注册bean，后面创建数据源会覆盖前面创建的数据源
  main:
    allow-bean-definition-overriding: true

修改完配置后测试。

贴上我的运行日志:

数据插入日志

22:16:50.045 logback [http-nio-8080-exec-5] INFO 
p.mpc.springboot.filter.CommonFilter - requestURI = /springboot/test/addUser
 
22:16:50.046 logback [http-nio-8080-exec-5] DEBUG
p.m.s.one.mapper.UserMapper.addUser - ==>  Preparing: INSERT INTO test_user(userid, username, password, createtime, updatetime) VALUES (?, ?, ?, ?, ?) 
 
22:16:50.046 logback [http-nio-8080-exec-5] INFO 
ShardingSphere-SQL - Rule Type: master-slave
 
22:16:50.046 logback [http-nio-8080-exec-5] INFO 
ShardingSphere-SQL - SQL: INSERT INTO test_user(userid, username, password, createtime, updatetime)
        VALUES (?, ?, ?, ?, ?) ::: DataSources: master
 
22:16:50.047 logback [http-nio-8080-exec-5] WARN 
c.a.d.pool.DruidAbstractDataSource - discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.2.3:3307/springboot?characterEncoding=UTF-8, version : 1.2.8, lastPacketReceivedIdleMillis : 91196
 
22:16:50.060 logback [http-nio-8080-exec-5] DEBUG
p.m.s.one.mapper.UserMapper.addUser - ==> Parameters: 2c4c818d-f465-4285-ae4b-462a64f4f13d(String), mpc2(String), mpc123(String), 2022-09-12 22:16:50.046(Timestamp), 2022-09-12 22:16:50.046(Timestamp)
 
22:16:50.061 logback [http-nio-8080-exec-5] DEBUG
p.m.s.one.mapper.UserMapper.addUser - <==    Updates: 1
 

数据查询日志

22:18:07.433 logback [http-nio-8080-exec-8] INFO 
p.mpc.springboot.filter.CommonFilter - requestURI = /springboot/test/getAllUser
 
22:18:07.434 logback [http-nio-8080-exec-8] DEBUG
p.m.s.o.mapper.UserMapper.getAllUser - ==>  Preparing: SELECT * FROM test_user 
 
22:18:07.434 logback [http-nio-8080-exec-8] INFO 
ShardingSphere-SQL - Rule Type: master-slave
 
22:18:07.434 logback [http-nio-8080-exec-8] INFO 
ShardingSphere-SQL - SQL: SELECT *
        FROM test_user ::: DataSources: slave
 
22:18:07.435 logback [http-nio-8080-exec-8] WARN 
c.a.d.pool.DruidAbstractDataSource - discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.2.3:3308/springboot?characterEncoding=UTF-8, version : 1.2.8, lastPacketReceivedIdleMillis : 153220
 
22:18:07.448 logback [http-nio-8080-exec-8] DEBUG
p.m.s.o.mapper.UserMapper.getAllUser - ==> Parameters: 
 
22:18:07.449 logback [http-nio-8080-exec-8] DEBUG
p.m.s.o.mapper.UserMapper.getAllUser - <==      Total: 3
 

根据日志的打印结果可以得到使用的是主库备库。