Mysql实现主从复制、读写分离

一、MySql主从复制

MySQL数据库默认是支持主从复制的，不需要借助于其他的技术，我们只需要在数据库中简单的配置即可。

1.1 主从复制简介

MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。

二进制日志：

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。默认MySQL是未开启该日志的。

MySQL复制过程分成三步：

1). MySQL master 将数据变更写入二进制日志( binary log)

2). slave将master的binary log拷贝到它的中继日志（relay log）

3). slave重做中继日志中的事件，将数据变更反映它自己的数据

1.2 环境搭建

1.2.1 准备工作

这里需要提前准备好两台服务器，并且在服务器中安装MySQL!

192.168.112.136 作为 mysql 的主机 master
192.168.112.137 作为 mysql 的从机 slave

我这里是通过虚拟机进行搭建的（利用其克隆功能直接复制出一个主机/从机）。系统搭建过程可参考文章(虚拟机的安装、mysql的安装等)：Linux系统相关操作

并且在两台服务器上做如下准备工作：

防护墙开发 3306 端口号：

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --zone=public --list-ports

firewall-cmd --reload

将两台服务器的Mysql启动起来

systemctl start mysqld 或者

/etc/init.d/mysql start

这里的启动方式根据自己的mysql安装方式来确定实际的启动方式,只要将数据库访问启动起来即可！

1.2.2 主库配置 master

进入主机服务器中（192.168.112.136）进行相关配置操作：

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

在该配置文件下方增加配置信息：

#[必须] 启用二进制日志
log_bin=master-bin
#log_bin_index=master-bin.index

#[必须]服务器唯一ID(唯一即可)
server-id=200

#二进制日志的有效期

expire-logs-days=7

#不需要同步的数据库

binlog_ignore_db=mysql

binlog_ignore_db=information_schema

binlog_ignore_db=performation_schema

binlog_ignore_db=sys

#需要同步的数据库名字

binlog_do_db=mybatis

添加完成后保存退出，然后重启主机中的 mysql 服务！

systemctl restart mysqld

创建数据同步的用户并授权

这里需要在主机中登录mysql，然后进行授权操作：

Mysql 中的用户授权方式：

用户授权基本语法：

GRANT 权限列表(如：select、insert、update、drop…) ON 数据库名.表名 TO ‘登录用户名’@’ 远程登录的主机地址’ identified by ‘密码’ WITH GRANT OPTION;

刷新权限

flush privileges;

这里需要创建一个用户 ,并且给该用户授予 REPLICATION SLAVE 权限。常用于建立复制时所需要用到的用户权限，也就是从机slave必须被主机master授权具有该权限的用户，才能通过该用户复制。

GRANT REPLICATION SLAVE ON *.* to 'masterUser'@'%' identified by 'lcjMysql123..' with grant option;-- 刷新权限！
flush privileges;-- 这里创建了一个用户 masterUser  密码为 lcjMysql123..
-- 并且赋予了 PEPLICATION SLAVE (主从复制)权限！ 
-- 以上%可以改为主机名，如果是%表示所有人都可以连接
-- 其中的 * 表示所有的 数据库或表-- 这里额外还需要注意的是，我们还需要给root用户授予远程连接支持的权限，方便后续对数据进行远程连接的操
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'root密码' WITH GRANT OPTION;
-- 这里 ALL PRIVIEGES 直接赋予所有权限！

登录Mysql数据库查看 master 同步状态信息

进入mysql后通过 sql：show master status; 查看 File 和 Position 的值！这两个值需要记住，后续在从机中需要进行关联操作！

这里通过 show variables like '%log_bin%'; 可以观察到日志是否成功开启：

1.2.3 从库配置 slave

进入从机服务器中（192.168.112.137）进行相关配置操作：

修改从库 Mysql 的配置文件/etc/my.cnf

[必须] 服务器唯一id
server-id = 2

#relay-log = slave-relay-bin

#relay-log-index = slave-relay-bin.index

在从数据库的配置文件中，只需要添加服务器唯一Id即可：server-id=201 #[必须]服务器唯一ID

添加配置信息完成后，重启从机中的 Mysql服务即可！

登录从库服务器的Mysql数据库，配置主库地址以及同步位置

 change master to master_host='192.168.112.136',master_port=3306,master_user='masterUser',master_password='lcjMysql123..',master_log_file='master-bin.000001',master_log_pos=1224;-- 启动slave;
start slave;-- 停止slave;
stop slave;

参数说明：

master_host : 主库的IP地址
master_user : 访问主库进行主从复制的用户名(上面在主库创建的)
master_password : 访问主库进行主从复制的用户名对应的密码
master_log_file : 从哪个日志文件开始同步(上述查询master状态中展示的有)
master_log_pos : 从指定日志文件的哪个位置开始同步(上述查询master状态中展示的有)

查看从数据库的状态

通过命令 show slave status \G;

然后通过状态信息中的 Slave_IO_running 和 Slave_SQL_running 可以看出主从同步是否就绪，如果这两个参数全为Yes，表示主从同步已经配置完成！

PS: 这里 Slave_IO_Running:NO 就看下一小结的排查方案

Slave_IO_running: 负责从数据库去主数据库读取二进制日志，并写入到从数据库的中继日志！

Slave_SQL_running：负责将中继日志转换成SQL语句后执行！

MySQL命令行技巧： \G : 在MySQL的sql语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

1.2.3 Slave_IO_Running: No 问题排查

这是一个很常见的错误，大概排查原因有以下几种：

主服务器的网络不通，或者主服务器的防火墙拒绝了外部连接3306端口

首先在从服务器上执行ping程序，确定能ping通主服务器在从服务器上执行mysq的远程连接！

<font style="color:rgb(77, 77, 77);">[root@slave204 log]# mysql -urep -p -h 192.168.112.137 -P3306</font>

如果显示ERROR 1045 (28000): Access denied for user ‘test’@‘192.168.1.204’ (using password: YES) 则说明没问题！

在配置从服务器时，输错了ip地址和密码，或者主服务器在创建用户时写错了用户名和密码
在配置从服务器时，输错了主服务器的二进制日志信息
[重点这也是我这里一直为 NO 的原因]

原因：两台主从数据库的uuid相同了（直接克隆了linux服务器，然后配置主从数据库，一通操作，到最后启动从数据库，发现的这个问题）。

解决办法：

使用 find / -iname "auto.cnf"; 命令查找数据库的auto.cnf 配置文件位置
然后通过vi 命令，修改该配置文件中的uuid 即可

这里随便改掉以为就可以了，保证这个uuid与克略对象的mysql 的uuid不同就行！

重新启动mysql服务
登录mysql，然后重启 slave 服务，然后再查看 slave 状态信息。

1.3 测试主从复制

主从复制的环境,已经搭建好了,接下来,我们可以通过Navicat连接上两台MySQL服务器,进行测试。测试时，我们只需要在主库Master执行操作，查看从库Slave中是否将数据同步过去即可。

在master中创建数据库 TestTempDb , 刷新slave查看是否可以同步过去

在master的 TestTempDb 数据下创建user表, 刷新slave查看是否可以同步过去

在master的user表中插入一条数据, 刷新slave查看是否可以同步过去

向主库中插入数据：

在从表中查询该表：

二、Mysql读写分离

2.1 读写分离简介

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过读写分离,就可以降低单台数据库的访问压力, 提高访问效率，也可以避免单机故障。

2.2 ShardingJDBC介绍

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离。

Sharding-JDBC具有以下几个特点：

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

Maven 依赖：

<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</version>
</dependency>

2.3 项目读写分离实现

数据库环境准备：

这里需要在主从复制配置完成的主库master 中创建出一个测试表( 会自动同步到 slaver 库中 )！

创建项目够，对应工程中的 Mapper、Service、Controller层编写好，这里就不演示编写过程了，同时构建好增删改查操作！

项目读写分离实现步骤：

首先在项目pom.xml 中增加 Sharding 的maven坐标依赖。

***注： **这里版本号选用4.0.0-RC1 ,选用了 4.1.1 版本可能会出现相关兼容性问题！(这里就不延展讨论！)

在application.yml 配置文件中增加数据源(master、slave)配置

spring:application:name: grdc_project  #应用项目名称# 该配置项的目的,就是如果当前项目中存在同名的bean,后定义的bean会覆盖先定义的。main:allow-bean-definition-overriding: true# ====== [数据库读写分离配置] ======shardingsphere:datasource:names:master,slave  #该名称可以随意定，下面保存一致即可# 配置主数据库master:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.112.136:3306/TestTempDb?characterEncoding=utf-8username: rootpassword: lcjMysql123..# 从数据源slave:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.112.137:3306/TestTempDb?characterEncoding=utf-8username: rootpassword: lcjMysql123..#读写分离配置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

ps：注意

如果出现如下错误的情况：

***************************
APPLICATION FAILED TO START
***************************Description:The bean 'dataSource', defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. 
A bean with that name has already been defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class] and overriding is disabled.Action:Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=trueProcess finished with exit code 1

需要在 yml 文件中配置

spring:  main:allow-bean-definition-overriding: true

该配置项的目的,就是如果当前项目中存在同名的bean,后定义的bean会覆盖先定义的。

报错信息表明，在声明 org.apache.shardingsphere.shardingjdbc.spring.boot 包下的SpringBootConfiguration中的dataSource这个bean时出错, 原因是有一个同名的 dataSource 的bean在com.alibaba.druid.spring.boot.autoconfigure包下的DruidDataSourceAutoConfigure类加载时已经声明了。