|
今天恰好关注的公号推送了这个内容，参照着和网上相关资料来一遍。
公号地址：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依赖
<!-- sharding-jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- sharding-jdbc -->
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
根据日志的打印结果可以得到使用的是主库备库。
|