为什么要主从复制、读写分离
为了提高数据库的并发性能。你想,假设是单机,读写都在一台MySQL上面完成,性能肯定不高。如果有三台MySQL,一台mater只负责写操作,两台salve只负责读操作,性能不就能大大提高了吗?
所以主从复制、读写分离就是为了数据库能支持更大的并发。
随着业务量的扩展、如果是单机部署的MySQL,会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。
介绍
MySQL主从复制是一个异步的复制过程,底层是基于Mysql数据库自带的二进制日志功能。就是一台 或多台MySQL数据库(slave, 即从库)从另一台MySQL数据库(master, 即主库)进行日志的复制然后再解析日志并应用到自身,最终实现从库的数据和主库的数据保持一致。 MySQL主从复制是MySQL数据库自带功能,无需借助第三方工具。
原理
①当Master节点进行insert、update、delete操作时,会按顺序写入到binlog中。
②salve从库连接master主库,Master有多少个slave就会创建多少个binlog dump线程。
③当Master节点的binlog发生变化时,binlog dump 线程会通知所有的salve节点,并将相应的binlog内容推送给slave节点。
④I/O线程接收到 binlog 内容后,将内容写入到本地的 relay-log。
⑤SQL线程读取I/O线程写入的relay-log,并且根据 relay-log 的内容对从数据库做对应的操作。
Mysql复制分三步:
- master将改变记录到二进制日志(binary log )
- slave将master的binary log拷贝到它的中继日志(relay log)
- slave重做中继日志中的事件,将改变应用到自己的数据库中
(一)Mysql主从复制(1主1从)搭建
1.配置主库-Master
第一步:
两台服务器,安装好Mysql
修改Mysql数据配置文件:/etc/my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=100
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能
log-bin=mysql-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
第二步
重启Mysql
systemctl restart mysqld
第三步:登录Mysql数据库,执行下面SQL
GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';
注:上面SQL的作用是创建一个用户 xiaoming,密码为Root@123456,并且给xiaoming用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限,也就是slave必须被master授权具有该权限的用户,才能通过该用户复制。
第四步:登录Mysql数据库,执行下面SQL,记录下结果中File和Position的值
show master status;
注:上面SQL的作用是查看Master的状态,执行完此SQL后不要再执行任何操作
配置从库
第一步:修改Mysq1数据库的配置文件/etc/my.cnf
[mysq1d]
server-id=101 #[必须]服务器唯一-ID
第二步:重启Mysql
systemctl restart mysqld
第三步:登录Mysq1数据库,执行下面SQL
change master to
master_host='192.168.138.100',master_user='xiaoming',master_password='Root@123456',master_log_file='mall_mysql-bin.000003',master_log_pos=439;
start slave;
第四步:登录Mysql数据库,执行下面SQL,查看从数据库的状态
show slave status;
(二)Docker实现Mysql(1主2从)主从复制
1.新建主服务器3308
docker run -p 3308:3306 --name mysql-master \
-v /app/mysql/mysql-master/log:/var/log/mysql \
-v /app/mysql/mysql-master/data:/var/lib/mysql \
-v /app/mysql/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
2.进入 /app/mysql/mysql-master/conf目录新建my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能
log-bin=mall-mysql-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
修改完后重启mysql-master
docker restart mysql-master
3.进入mysql-master容器创建数据同步用户
docker exec -it mysql-master /bin/bash
mysql -uroot -p123456
CREATE USER 'slave'@'%' IDENTIFIED BY 'java@123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
1.新建3309从服务器容器实例
docker run -p 3309:3306 --name mysql-slave1 \
-v /app/mysql/mysql-slave1/log:/var/log/mysql \
-v /app/mysql/mysql-slave1/data:/var/lib/mysql \
-v /app/mysql/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=654321 \
-d mysql:5.7
2.进入 /app/mysql/mysql-slave1/conf目录新建my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读(具有super权限的用户除外)
read_only=1
从启mysql-slave
docker restart mysql-slave1
在主数据库mysql客户端查看主从同步状态(暂时不要关闭)
show master status;
3.进入从数据库3309配置主从复制
docker exec -it mysql-slave1 /bin/bash
mysql -uroot -p654321;
change master to master_host='192.168.8.100', master_user='slave', master_password='java@123', master_port=3308, master_log_file='mall-mysql-bin.000003', master_log_pos=154, master_connect_retry=30;
###
master_host:主数据库的IP地址;
master_port:主数据库的运行端口;
master_user:在主数据库创建的用于同步数据的用户账号;
master_password:在主数据库创建的用于同步数据的用户密码;
master_log_file:指定从数据库要复制数据的日志文件,通过查看主数据的状态,获取File参数;
master_log_pos:指定从数据库从哪个位置开始复制数据,通过查看主数据的状态,获取Position参数;
master_connect_retry:连接失败重试的时间间隔,单位为秒。
在从数据库查看主从同步状态
show slave status \G;
在从数据库开始主从复制,再次查看主从同步状态
start slave;
show slave status \G;
1.新建3310从服务器容器实例
docker run -p 3310:3306 --name mysql-slave2 \
-v /app/mysql/mysql-slave2/log:/var/log/mysql \
-v /app/mysql/mysql-slave2/data:/var/lib/mysql \
-v /app/mysql/mysql-slave2/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
2.进入 /app/mysql/mysql-slave2/conf目录新建my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=103
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave2-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读(具有super权限的用户除外)
read_only=1
3.进入从数据库3310配置主从复制
docker exec -it mysql-slave2 /bin/bash
mysql -uroot -proot;
change master to master_host='192.168.8.100', master_user='slave', master_password='java@123', master_port=3308, master_log_file='mall-mysql-bin.000003', master_log_pos=154, master_connect_retry=30;
###
master_host:主数据库的IP地址;
master_port:主数据库的运行端口;
master_user:在主数据库创建的用于同步数据的用户账号;
master_password:在主数据库创建的用于同步数据的用户密码;
master_log_file:指定从数据库要复制数据的日志文件,通过查看主数据的状态,获取File参数;
master_log_pos:指定从数据库从哪个位置开始复制数据,通过查看主数据的状态,获取Position参数;
master_connect_retry:连接失败重试的时间间隔,单位为秒。
开始主从复制
start slave;
show slave status \G;
读写分离
背景
面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改操作,从库负责处理查询操作,能够有效的避免由数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。
Sharding-JDBC介绍
Sharding-JDBC定位为轻量级Java框架,在Java的JDBC层提供的额外服务。它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种ORM框架。使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离。
- 适用于任何基于JDBC的ORM框架,如: JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
- 支持任何第三方的数据库连接池,如: DBCP, C3P0, BoneCP, Druid, HikariCP等。
- 支持任意实现JDBC规范的数据库。目前支持MySQL, Oracle, SQL .Server, PostgreSQL以及任何遵循SQL92标准的数据库。
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
配置文件
server:
port: 8080
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.138.100:3306/rw?characterEncoding=utf-8
username: root
password: root
# 从数据源
slave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.138.101:3306/rw?characterEncoding=utf-8
username: root
password: root
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 #shareding需要创建数据源 druid也需要创建数据源,开启此配置,可覆盖数据源
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