sharding jdbc_sharding-jdbc的读写分离，数据库主从同步实践

前段时间看到sharding-jdbc这个东西，我就用它来实践一下读写分离，下面是我要下面的三个步骤来写这篇文章

1，弄一个基础框架spring boot + mybatis generator

2，整合sharding-jdbc，实现读写分离

3，实现主从数据库同步数据

首先我们来简单的将一下第一，二步，因为比较简单，第三步对于自己来说相当复杂一点。

第一步：

自己简单的配置一个spring boot项目，然后整合mybatis。这里注重讲一下mybatis generator

a:首先我们先加入相应的jar

  org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2org.mybatis.generator mybatis-generator-core 1.3.2mysql mysql-connector-java 

b:然后在配置相应的generatorConfig.xml,想要查看相应的配置详情，大家可以查看网上，或者参考我自己的例子(后面会附上我的仓库地址)

c:写main方法去根据配置文件生产文件

import org.mybatis.generator.api.MyBatisGenerator;import org.mybatis.generator.config.Configuration;import org.mybatis.generator.config.xml.ConfigurationParser;import org.mybatis.generator.exception.InvalidConfigurationException;import org.mybatis.generator.exception.XMLParserException;import org.mybatis.generator.internal.DefaultShellCallback;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class GenMain { public static void main(String[] args) { List warnings = new ArrayList(); boolean overwrite = true; String genCfg = "/generatorConfig.xml"; ClassLoader load = Thread.currentThread().getContextClassLoader(); InputStream is = load.getResourceAsStream(genCfg); File configFile = new File(GenMain.class.getResource(genCfg).getFile());// ClassLoader load = Thread.currentThread().getContextClassLoader();// InputStream is = load.getResourceAsStream(genCfg); ConfigurationParser cp = new ConfigurationParser(warnings); Configuration config = null; try { config = cp.parseConfiguration(configFile); } catch (IOException e) { e.printStackTrace(); } catch (XMLParserException e) { e.printStackTrace(); } DefaultShellCallback callback = new DefaultShellCallback(overwrite); MyBatisGenerator myBatisGenerator = null; try { myBatisGenerator = new MyBatisGenerator(config, callback, warnings); } catch (InvalidConfigurationException e) { e.printStackTrace(); } try { myBatisGenerator.generate(null); } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (InterruptedException e) { e.printStackTrace(); } }}

注意的点：

Mapper.xml文件如果不放在resources文件夹中的话，编译的时候是不会去加载该配置的，需要加如下配置在mavenbuild标签中 src/main/java**/*.properties**/*.xml

这样就能正常批量生成文件了

​

第二步：sharding-jdbc配置，实现读写分离(这里只是简单的实现读写分离，一些sharding-jdbc具体的自己另行去学习)

a:加jar

 io.shardingjdbc sharding-jdbc-spring-boot-starter 2.0.0.M3com.alibaba druid 1.1.10

b:配置相应的sharding-jdbc(具体看我后面放的git项目地址)

这样就能实现读写分离了，很简单

第三步：主从数据库同步

我目前是在同一台物理机上部署2套数据库服务，这里我们命名为A(主)，B(从)。我们先从A，B的配置说起吧

先附上A，B数据库的配置文件

A:

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the# *** default location during install, and will be replaced if you# *** upgrade to a newer version of MySQL.[mysqld]# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin##server_id和log_bin两项即可，其它配置都是可配可不配port=5506# 唯一标志idserver-id=1#开启二进制日志log-bin=mysql-bin#binlog-do-db=wordpress是表示只备份wordpress。#binlog_ignore_db=mysql表示忽略备份mysql。#不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库#binlog-do-db=wordpress#binlog_ignore_db=mysql# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

B:

[mysql]# 设置mysql客户端默认字符集default-character-set=utf8 [mysqld]server-id=2#开启二进制日志log-bin=mysql-binrelay-log=relay-bin#设置只读权限read-only =1#复制时忽略相关表或者数据库#replicate-do-db = name 只对这个数据库进行镜像处理。#replicate-ignore-table = dbname.tablename 不对这个数据表进行镜像处理。#replicate-wild-ignore-table = dbn.tablen 不对这些数据表进行镜像处理。#replicate-ignore-db = dbname 不对这个数据库进行镜像处理。replicate-ignore-db = mysqlreplicate-ignore-db = information_schema#replicate-wild-do-table = tt.admin#所要同步的数据库的单个表replicate-wild-do-table = test.user

这里有一个点很重要，就是A，Bmysql我是直接免安装版的，里面有一个my-default.ini配置文件，但是上面的配置在这个文件里面写是无法生效的，网上的意思是你还得自己新建一个my.ini,将上面的配置写到文件里面，我试了确实是要写在my.ini里面才能生效。

之后就是启动A，B服务器。在A服务器上执行如下命令，创建一个REPLICATION SLAVE权限的账号给从数据库用

 mysql>CREATE USER 'user'@ 'X.X.X.X' IDENTIFIED BY 'password'; mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';

然后在B服务器上执行如下脚本(将信息写入到B中的master.info文件中)

 change master to master_host='127.0.0.1',master_user='mytest',master_password='password',master_port=5506,master_log_file='mysql-bin.000004';

后面的那个日志号，在主服务器上执行如下代码就可以拿到

​

之后就是执行启动从服务器的SLAVE服务

START SLAVE；启动 STOP SLAVE 停止

查看SLAVE的日志脚本：

mysql> SHOW SLAVE STATUS;+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+| Waiting for master to send event | 127.0.0.1 | mytest | 5506 | 60 | mysql-bin.000007 | 120 | relay-bin.000007 | 283 | mysql-bin.000007 | Yes | Yes | | mysql,information_schema | | | test.user | | 0 | | 0 | 120 | 613 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | 96cb631a-831c-11e8-b47b-089e0105f2eb | C:甥敳獲zfh-workDesktopmysql-5.6.40-winx64datamaster.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+1 row in set

到这里为止三个步骤都已经完成了

还有在实践过程中，我这边会碰到复制过程中发生某种错误导致主从复制停止，我们有以下两种方式来处理这种中断：

1，跳过该错误的event

mysql>slave stop;mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n(保险起见设置n=1) #跳过这n个eventmysql>slave start

2，修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf[mysqld]#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误#slave-skip-errors=all #跳过所有错误

附上在数据库同步过程常用的数据库脚本：

查看mysql数据库端口：

show global variables like 'port'; 查看当前使用的用户: select user(); 查看mysql server_id: show variables like 'server_id'; 查看主数据库日志：

show master logs;

怎么判断mysql的主从是否同步？(同步日志)

mysql> show slave statusG

#查看是否都为yes Slave_IO_Running Slave_SQL_Running

数据库主从复制的原理：

Salve的IO线程会读取自己的文件目录mastr.info文件中配置好的主库信息，比如说存放的有：Master数据库的用户名、密码、端口、还有Master的binlog索引位置； 例如本人的master.info：

23mysql-bin.000007 (master使用的binlog日志)120127.0.0.1(master的ip)mytest (复制数据的库)password(数据库密码)5506(端口)60001800.000096cb631a-831c-11e8-b47b-089e0105f2eb864000

拿到信息之后就带着信息去链接Master的主库IO线程 当主库的IO线程先检查SLave传过来的配置信息是否正确，如果正确，就拿着Slave传过来的binlog索引位置和Master库的binlog文件中最后一个索引位置进行对比，如果一致就陷入等待状态，等待Master的binlog索引位置更新； 如果不一致就把Slave传过来的binlog索引位置往后的所有SQL语句包括最后一条SQL语句的索引位置发送个给Slave的IO线程； Slave的IO线程拿到信息之后，先把Master传过来的binlog索引在Slave的master.info文件中进行更新； 然后再把Master传过来的SQL语句写入到relay文件中，然后继续循环执行第二个步骤； Slave的SQL线程会一直持续的观察relay日志文件中是否有改动，如果没有就继续监听； 如果发现relay中有变动，那么就获取变动的内容转换为SQL语句，并且把SQL语句在Salve的数据库中进行执行

下面自己的项目，码云地址

https://gitee.com/zfh/shardingjdbc-demo