Mysql主从复制

Mysql复制过程分为三步：

master(主库)将改变记录到二进制日志

slave(从库)将master的binary lo拷贝到它的中继日志

slave 重做中继日志中的事件，将改变应用到自己的数据库

​

配置主库

1.修改Mysql数据库的配置文件/etc/my.cnf

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin  #启用二进制日志

server-id=100   #服务器唯一id
1
2
3
4
5

2.重启mysql

systemctl restart mysqld

3.登录到Mysql数据库，执行sql

GRANT REPLICATION SLAVE ON *.* to 'xiaoyang'@'%' identified by 'Root@0630";'

创建一个用户，为该用户授予REPLICATION SLAVE权限

执行

show master status;

从库的配置

修改配置文件

server-id=101 #服务器唯一id
1

执行sql(File和Position从前面的步骤获得)

change master to master_host='192.168.205.128',master_user='xiaoyang',master_password='Root@0630',master_log_file='mysql-bin.000001',master_log_pos=441;

start slave

show slave status

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cYXdPKmo-1660265173943)(https://s2.loli.net/2022/08/10/zOr25GYy3ILEicx.png)]

实现读写分离

Sharding-JDBC

    org.apache.shardingsphere
    sharding-jdbc-spring-boot-starter
    4.0.0-RC1

1
2
3
4
5
6

配置文件application.yml

server:
  port: 8080
mybatis-plus:
  configuration:
    #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    map-underscore-to-camel-case: true

    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: ASSIGN_ID
      
      
      #增加下面这一部分
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.205.128:3306/rw?characterEncoding=utf-8&useSSL=false
        username: root
        password: root
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/rw?characterEncoding=utf-8&useSSL=false
        username: root
        password: '20020630'


    masterslave:
      # 读写分离配置
      load-balance-algorithm-type: round_robin #轮询
      # 最终的数据源名称
      name: dataSource
      # 主库数据源名称
      master-data-source-name: master
      # 从库数据源名称列表，多个逗号分隔
      slave-data-source-names: slave
    props:
      sql:
        show: true #开启SQL显示，默认false
        
  main:
    allow-bean-definition-overriding: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

当查询数据库时查的是从库

==> Preparing: SELECT id,name,age,addr FROM user WHERE id=?
2022-08-10 16:21:54.840 INFO 11308 — [nio-8080-exec-3] ShardingSphere-SQL : Rule Type: master-slave
2022-08-10 16:21:54.841 INFO 11308 — [nio-8080-exec-3] ShardingSphere-SQL : SQL: SELECT id,name,age,addr FROM user WHERE id=? ::: DataSources: slave

当新增数据时操作的是主库

==> Preparing: UPDATE user SET age=? WHERE id=?
2022-08-10 16:22:29.947 INFO 11308 — [nio-8080-exec-8] ShardingSphere-SQL : Rule Type: master-slave
2022-08-10 16:22:29.948 INFO 11308 — [nio-8080-exec-8] ShardingSphere-SQL : SQL: UPDATE user SET age=? WHERE id=? ::: DataSources: master