Mysql主从复制和读写分离
目录
MySQL主从复制 #
介绍 #
MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现 从库 的数据和 主库 的数据保持一致。MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。
二进制日志： 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的。
原理步骤 #
MySQL复制过程分成三步：
- MySQL master 将数据变更写入二进制日志( binary log)
- slave将master的binary log拷贝到它的中继日志（relay log）
- slave重做中继日志中的事件，将数据变更反映它自己的数据
主从同步延时问题 #
MySQL 实际上在有两个同步机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。
- 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
- 并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
主MySQL服务器设置 #
下载MySQL镜像 #
docker pull mysql:5.7
注意：如果使用docker pull mysql，则默认拉取的是最新版本的mysql。则下述的配置方法无效。
在本机上先创建好MySQL主服务器所需目录 #
以便与MySQL主服务器和从服务器所需目录进行映射，便于管理。
mkdir /usr/local/mysql-docker/master/cnf -p
mkdir /usr/local/mysql-docker/master/data -p
定义主服务器配置文件 #
/usr/local/mysql-docker/master/cnf/mysql.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
#开启 log-bin
log-bin = mysql-bin
server-id = 1
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
#Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
创建并启动MySQL主服务 #
docker run -itd -p 3306:3306 --name mysql-master -v /usr/local/mysql-docker/master/cnf:/etc/mysql/conf.d -v /usr/local/mysql-docker/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
进入主服务器控制台 #
添加复制master数据的用户reader，供从服务器使用
[root@localhost ~]# docker exec -it mysql-master /bin/bash //注意修改容器ID
root@6405439bdd40:/# mysql -u root -proot
#这里创建了一个用户：用户名：reader，密码：readerpwd
mysql> GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'readerpwd';
Query OK, 0 rows affected, 1 warning (1.60 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)
在主服务器上查看 #
master_log_file，master_log_pos两个参数，之后在从服务器中配置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 765 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
从MySQL服务器设置 #
创建目录 #
mkdir /usr/local/mysql-docker/slaver/cnf -p
mkdir /usr/local/mysql-docker/slaver/data -p
定义主服务器配置文件 #
/usr/local/mysql-docker/slaver/cnf/mysql.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
#开启log-bin
log-bin = mysql-bin
server-id = 1
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
#Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
创建并启动MySQL从服务 #
docker run -itd -p 3307:3306 --name mysql-slaver -v /usr/local/mysql-docker/slaver/cnf:/etc/mysql/conf.d -v /usr/local/mysql-docker/slaver/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
从服务器中执行 #
注意：
- master_host 一定要可以 ping 通。这里已经在同一网络中了。
master_log_file，master_log_pos两个参数在主服务器中查看。
[root@localhost ~]# docker exec -it mysql-slaver /bin/bash
root@fbc7e934f424:/# mysql -u root -proot
mysql> change master to master_host='192.168.0.2',master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000003',master_log_pos=765;
从服务器启动I/O 线程和SQL线程 #
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: reader
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 765
Relay_Log_File: b3a8ba2fdc0c-relay-bin.000002
Relay_Log_Pos: 494
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试效果 #
思路：可以使用可视化数据库工具连接两个数据库，主数据库的修改会同步到从数据库中。
ShardingJDBC读写分离 #
背景 #
面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。
通过读写分离,就可以降低单台数据库的访问压力, 提高访问效率，也可以避免单机故障。
ShardingJDBC介绍 #
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，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库
读写分离配置 #
前提是已经实现了Mysql的主从复制。这样才能保证读写分离后数据的一致性。
pom.xml #
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
application.yml #
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.200.200:3306/reggie?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.200.201:3306/reggie?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，用于检查配置是否成功
#该配置项的目的,就是如果当前项目中存在同名的bean,后定义的bean会覆盖先定义的
main:
allow-bean-definition-overriding: true
props属性的其它配置
acceptor.size: # accept连接的线程数量,默认为cpu核数2倍
executor.size: #工作线程数量最大，默认值: 无限制
max.connections.size.per.query: # 每个查询可以打开的最大连接数量,默认为1
check.table.metadata.enabled: #是否在启动时检查分表元数据一致性，默认值: false
proxy.frontend.flush.threshold: # proxy的服务时候,对于单个大查询,每多少个网络包返回一次
proxy.transaction.type: # 默认LOCAL,proxy的事务模型 允许LOCAL,XA,BASE三个值,LOCAL无分布式事务,XA则是采用atomikos实现的分布式事务 BASE目前尚未实现
proxy.opentracing.enabled: # 是否启用opentracing
proxy.backend.use.nio: # 是否采用netty的NIO机制连接后端数据库,默认False ,使用epoll机制
proxy.backend.max.connections: # 使用NIO而非epoll的话,proxy后台连接每个netty客户端允许的最大连接数量(注意不是数据库连接限制) 默认为8
proxy.backend.connection.timeout.seconds: #使用nio而非epoll的话,proxy后台连接的超时时间,默认60s