使用Spring Boot + Sharding-JDBC 快速简单地实现数据库读写分离

一、 Sharding-JDBC介绍

引用官方的一段介绍

① Sharding-JDBC是一个开源的分布式数据库中间件解决方案。它在Java的JDBC层以对业务应用零侵入的方式额外提供数据分片，读写分离，柔性事务和分布式治理能力。并在其基础上提供封装了MySQL协议的服务端版本，用于完成对异构语言的支持。
② Sharding-JDBC是基于JDBC的客户端版本定位为轻量级Java框架，使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
③ Sharding-JDBC封装了MySQL协议的服务端版本定位为透明化的MySQL代理端，可以使用任何兼容MySQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench等)操作数据，对DBA更加友好。

不同于Mycat等中间件，Sharding-JDBC是在代码层面上进行读写分离，传统的读写分离是由开发者自行在业务逻辑中去实现读库与写库两种操作分离，而Sharding-JDBC则是进一步将这种实现剥离出来。对于开发者来说，无需自行去维护读库与写库操作，原有的业务逻辑无需做修改即可实现读写分离，整个过程是透明的。对于运维来说，也可以少维护一个中间件服务器。此方案适用于读多写少或读少写多场景，通过读写分离，可以较为显著地提升系统整体的吞吐量。下面，我将通过一个例子简单演示一下读写分离，源码放在最后一节，欢迎下载与指正。

二、搭建MySql主从复制库

推荐使用docker进行搭建，可参考别的博主写的这篇文章进行搭建
docker mysql 主从复制
下面内容是上述文章提到的关键点及关键命令，记录下来方便查阅，第一次搭建的童鞋建议按照上述文章或自行上网查找相关搭建教程进行搭建。

读写分离Mysql配置文件

主从库的配置文件都和下面的一样，唯一的区别在于配置中的server-id = 1值不能一样，可为任意数字

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 1 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

关闭selinux

此步骤必须进行，否则在主库中执行show matser status命令返回的结果为空。

setenforce 0 

运行主从数据库容器

docker run -d -e MYSQL_ROOT_PASSWORD=123456--name mysql-master -v /home/mysql/m.cnf:/etc/mysql/my.cnf -p 3306:3306 mysql:5.7 &&\
docker run -d -e MYSQL_ROOT_PASSWORD=123456--name mysql-slave -v /home/mysql/s.cnf:/etc/mysql/my.cnf -p 3307:3306 mysql:5