Mysql主从分离介绍及实现

参考:

  http://www.cnblogs.com/panxuejun/p/5887118.html

  https://www.cnblogs.com/alvin_xp/p/4162249.html

  https://blog.51cto.com/13555423/2068071

一.什么是Mysql主从分离

  将读操作和写操作分离到不同的数据库上,避免主服务器出现性能瓶颈;主服务器进行写操作时,不影响查询应用服务器的查询性能,降低阻塞,提高并发; 数据拥有多个容灾副本,提高数据安全性,同时当主服务器故障时,可立即切换到其他服务器,提高系统可用性;

二.为什么要实现Mysql主从分离

  大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库必然会崩溃,数据丢失的话,后果更是 不堪设想。这时候,我们会考虑如何减少数据库的联接,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据缓存技术如:memcached,如果资金丰厚的话,必然会想到假设服务器群,来分担主数据库的压力。

三.主从分离原理

 

  1.第一步:Master(主服务器)将操作记录到binary log(二进制日志文件当中)【即每个事务更新数据完成之前先把操作记录在日志文件中,Mysql将事务串行的写入二进制日志文件中】,写入日志文件完成之后,Master通知存储引擎提交事务(注:对数据的操作成为一次二进制的日志事件【binary log event】);

  2.第二步:slave(从服务器)把binary log拷贝到relay log(中介日志)【相当于缓存作用,存储在从服务器的缓存中】,首先slave会开始一个工作线程(I/O线程),I/O线程会在Master上打开一个普通的连接,然后读取binary log事件,如果已经跟上master,就会睡眠,并等待Master产生新的事件,I/O线程将读取的这些事件写入到relay log;

  3.第三步:slave从做中介日志事件(relay log),sql线程读取relay log事件并执行更新从服务器上的数据,使其与Master上的数据一致。

  总结:主服务器把操作记录到binary log——>从服务器将binary log中的数据同步到relay log(中介日志中)——>从服务器读取中介日志执行同步数据

四.数据库主从配置

  注:可以是一主一从,一主多从,主从从等。

  我这里配置一主一从,自己有一台服务器,又借了室友一台服务器准备试试。

  1.两个服务器:

    1.1配置主服务器(Master):

      打开binary log,配置mysql配置文件:

        (1)vim /etc/my.cnf(编辑服务器上的mysql配置文件,一般都存储在这儿,如果没有可以根据自己的配置文件):

        (2)配置打开binary log:

[mysqld]
#红色的为新配置的打开binary log的配置 #配置binary log #配置server-id server-id=1 #打开二进制日志文件 log-bin=master-bin #打开二进制日志文件索引 log-bin-index=master-bin.index character_set_server=utf8 init_connect='SET NAMES utf8' datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid

         

      (3)重启mysql加载配置文件:service mysqld restart或者(etc/init.d/mysql stop 然后etc/init.d/mysql start)

        注:如果用的是MariaDB,会发生如下错误(Failed to start mysql.server.service: Unit not found.)则需要用systemctl restart mariadb.service启动(参照:https://www.cnblogs.com/yuanchaoyong/p/9749060.html

        再注:systemctl和service是Linux服务管理的两种方式,service命令其实是去/etc/init.d目录下,去执行相关程序,systemd是Linux系统最新的初始化系统(init),作用是提高系统的启动速度,尽可能启动较少的进程,尽可能更多进程并发启动。systemd对应的进程管理命令是systemctl

           systemctl命令兼容了service(即systemctl也会去/etc/init.d目录下,查看,执行相关程序),并且systemctl命令管理systemd的资源Unit(systemd的Unit放在目录/usr/lib/systemd/system(Centos)或/etc/systemd/system(Ubuntu))

      (4)SHOW MASTERT STATUS;

         会查看到第一个二进制日志文件:

      

    1.2从服务器配置:

      (1)同理进入mysql配置文件:vim /etc/my.cnf:

      (2)配置relay log:

 [mysqld]
  #配置relay log
  #配置server id
  server-id=2
  #打开从服务器中介日志文件
  relay-log=slave-relay-bin
  #打开从服务器中介日志文件索引
  relay-log-index=slave-relay-bin.index
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
 
 # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=0
 
  # Recommended in standard MySQL setup
  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid

        

      (3)同理,保存配置重启mysql加载配置文件生效

 

     1.3相关连接配置:

      (1)在主服务器上(Master)创建一个用于用于从服务器连接并赋予其所有数据库所有表的权限:

          创建用户:create user 用户名;

          赋予连接权限:GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从服务器IP' IDENTIFIED BY '密码'

          刷新:flush privileges;

 

       (2)从服务器建立连接(使用主服务器创建的repl用户及密码): 

          建立连接:change master to master_host='主服务器IP',master_port=主服务器MYSQL端口,master_user='用户名',master_password='密码',master_log_file='master-bin.000001',master_log_pos=0;

          读取master-bin.000001文件,即主服务器的第一个日志文件,master_log_pos的作用是如果从服务器挂掉后,只要记得这个master_log_pos的大小,然后赋值就能恢复同步在某个时刻。

          开启主从跟踪:start slave;

          相应得关闭主从跟踪:stop slave;

           查看从服务器show slave status \G:

            这里注意状态是否正确,有可能连接不正确(如主服务器配置文件种有blind-address只能指定ip访问数据库,权限未赋予正确,防火墙等等),我最后遇到的原因是阿里云服务器端口没有打开3306端口,打开即可。

 

 

  2.测试:

    主服务器创建一个数据库:

      

    从服务器也创建成功:

 

     

      

五.Spring代码读写分离

  1.Spring中的AbstractRoutingDataSource(Eclipce中Ctrl+Shift+T搜索jar包)

    

     EclipceCtrl+O查看类中所有方法及属性:查看AbstractRoutingDataSource中的determineTargetDataSource()方法:

      

  2.相关实现:

    DynamicDataSourceHolder:

 1 package com.swpu.o2o.dao.split;
 2 
 3 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 4 /**
 5  * 继承AbstractRoutingDataSource实现抽象方法
 6  * @author ASUS
 7  *
 8  */
 9 public class DynamicDataSource extends AbstractRoutingDataSource{
10 
11     @Override
12     protected Object determineCurrentLookupKey() {
13         return DynamicDataSourceHolder.getDbType();
14     }
15     
16 
17 }

 

    DynamicDataSource:

 1 package com.swpu.o2o.dao.split;
 2 
 3 import org.slf4j.Logger;
 4 import org.slf4j.LoggerFactory;
 5 
 6 
 7 
 8 public class DynamicDataSourceHolder {
 9     //这是日志模块
10     private static Logger logger =  LoggerFactory.getLogger(DynamicDataSourceHolder.class);
11     //线程安全的ThreadLocal模式
12     private static ThreadLocal<String> contextHolder=new ThreadLocal<String>();
13     //两个key,主服务器,从服务器
14     private static final String DB_MASTER="master";
15     public static final String DB_SLAVE="slave";
16     /**
17      * 获取线程的DbType
18      * @return db
19      */
20     public static String getDbType(){
21         
22         String db=contextHolder.get();
23         //如果为空,默认为master,即支持写,也支持读
24         if(db==null){
25             db=DB_MASTER;
26         }
27         return db;
28     }
29     /**
30      * 设置线程的dbType
31      * @param str
32      */
33     public static void setDbType(String str){
34         logger.debug("所使用的数据源是:"+str);
35         contextHolder.set(str);
36         
37     }
38     /**
39      * 清理连接类型
40      */
41     public static void DbType(){
42         contextHolder.remove();
43     }
44 }

    mybatis拦截器:拦截mybatis传递进来的sql信息,根据sql信息选择数据源,如写的数据源【master】(update,insert等),读的数据源【slave】(select)

 1 package com.swpu.o2o.dao.split;
 2 
 3 import java.util.Locale;
 4 import java.util.Properties;
 5 
 6 import org.apache.ibatis.executor.Executor;
 7 import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
 8 import org.apache.ibatis.mapping.BoundSql;
 9 import org.apache.ibatis.mapping.MappedStatement;
10 import org.apache.ibatis.mapping.SqlCommandType;
11 import org.apache.ibatis.plugin.Interceptor;
12 import org.apache.ibatis.plugin.Intercepts;
13 import org.apache.ibatis.plugin.Invocation;
14 import org.apache.ibatis.plugin.Plugin;
15 import org.apache.ibatis.plugin.Signature;
16 import org.apache.ibatis.session.ResultHandler;
17 import org.apache.ibatis.session.RowBounds;
18 import org.slf4j.Logger;
19 import org.slf4j.LoggerFactory;
20 import org.springframework.transaction.support.TransactionSynchronizationManager;
21 
22 /**
23  * 拦截器,实现mybatis中的Interceptor接口
24  * 
25  * @author ASUS
26  *
27  */
28 //指定拦截类型,mybatis会把增删改封装到update中
29 @Intercepts({@Signature(type=Executor.class,method="update",args={MappedStatement.class,Object.class}),
30     @Signature(type=Executor.class,method="query",args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})
31 public class DynamicDataSourceInterceptor implements Interceptor {
32     //日志
33     private static Logger logger =  LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
34 
35     // 匹配的正则表达(增删改\u0020表示空格)
36     private static final String REGEX = ".*insert\\u0020.*|.*delete \\u0020.*|.*update\\u0020.*";
37 
38     // 主要拦截方法
39     @Override
40     public Object intercept(Invocation invocation) throws Throwable {
41         // 判断当前是不是事务的
42         boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
43         Object[] objects = invocation.getArgs();
44         MappedStatement ms = (MappedStatement) objects[0];
45         String lookupKey = DynamicDataSourceHolder.DB_MASTER;
46         if (synchronizationActive != true) {
47             // 是否为读方法
48             if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
49                 // selectKey为自增id查询主键(SELECT_KEY_SUFFIX())方法,使用主库(更新)
50                 if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
51                     lookupKey = DynamicDataSourceHolder.DB_MASTER;
52                 } else {
53                     // 格式化sql语句
54                     BoundSql boundsql = ms.getSqlSource().getBoundSql(objects[1]);
55                     String sql = boundsql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
56                     // 使用正则匹配是否是增删改
57                     if (sql.matches(REGEX)) {
58                         lookupKey = DynamicDataSourceHolder.DB_MASTER;
59 
60                     } else {
61                         lookupKey = DynamicDataSourceHolder.DB_SLAVE;
62                     }
63                 }
64             }
65 
66         } else {
67             lookupKey = DynamicDataSourceHolder.DB_SLAVE;
68         }
69         logger.debug("设置方法[{}]use [{}] Strategy,SqlCommanType[{}]",ms.getId(),lookupKey,ms.getSqlCommandType());
70         return invocation.proceed();
71     }
72 
73     // 返回封装好的对象或代理对象
74     @Override
75     public Object plugin(Object target) {
76         // 如果是mybatis中的Executor对象(增删改查),就通过intercept()封装返回,否则直接防回
77         if (target instanceof Executor) {
78             return Plugin.wrap(target, this);
79         } else {
80             return target;
81         }
82     }
83 
84     // 设置相关代理,不是必备的
85     @Override
86     public void setProperties(Properties arg0) {
87         // TODO Auto-generated method stub
88 
89     }
90 
91 }
View Code 

    mybatis配置文件中配置拦截器:

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE configuration 
 3  PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
 4  "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <!-- 配置全局属性 -->
 7     <settings>
 8         <!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
 9         <setting value="true" name="useGeneratedKeys" />
10         <!-- 使用列别名替换列名 默认:true -->
11         <setting value="true" name="useColumnLabel" />
12         <!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
13         <setting value="true" name="mapUnderscoreToCamelCase" />
14         <!-- 打印查询语句 -->
15     </settings>
16     <!-- 配置mybatis拦截器 -->
17     <plugins>
18         <plugin interceptor="com.swpu.o2o.dao.split.DynamicDataSourceInterceptor"></plugin>
19     </plugins>
20 </configuration>

 

    配置datasource:

       mysql的相关信息(主从数据库):

       datasource及动态数据源相关配置(选择不同数据源):

 1 <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" abstract="true"
 2     destroy-method="close" id="abstractDataSource">
 3     <!-- 配置连接池属性 -->
 4     <property name="driverClass" value="${jdbc.driver}" />
 5     <property name="jdbcUrl" value="${jdbc.url}" />
 6     <property name="user" value="${jdbc.username}" />
 7     <property name="password" value="${jdbc.password}" />
 8     <!-- c3p0连接池的私有属性 -->
 9     <property name="maxPoolSize" value="30" />
10     <property name="minPoolSize" value="10" />
11     <!-- 关闭连接后不自动commit -->
12     <property name="autoCommitOnClose" value="false" />
13     <!-- 获取连接超时时间 -->
14     <property name="checkoutTimeout" value="10000" />
15     <!-- 当获取连接失败重试次数 -->
16     <property name="acquireRetryAttempts" value="2" />
17 </bean>
18 <!-- 主服务器数据源,继承abstractDataSource,这里主库从库密码一致 -->
19 <bean id="master" parent="abstractDataSource">
20     <property name="driverClass" value="${jdbc.driver}" />
21     <property name="jdbcUrl" value="${jdbc.masterurl}" />
22     <property name="user" value="${jdbc.username}" />
23     <property name="password" value="${jdbc.password}" />
24 </bean>
25 <!-- 从库先关信息 -->
26 <bean id="slave" parent="abstractDataSource">
27     <property name="driverClass" value="${jdbc.driver}" />
28     <property name="jdbcUrl" value="${jdbc.slaveurl}" />
29     <property name="user" value="${jdbc.username}" />
30     <property name="password" value="${jdbc.password}" />
31 </bean>
32 <!-- 配置动态数据源,targetDataSouece就是lu'you数据源锁对应的名称 -->
33 <bean id="dynamicDataSource" class="com.swpu.o2o.dao.split.DynamicDataSource">
34     <property name="targetDataSource">
35         <map>
36         <!-- value-ref和datasource名字保持一致,key和DynamicDataSourceHolder中的kookupKey保持一致 -->
37             <entry value-ref="master" key="master"></entry>
38             <entry value-ref="slave" key="slave"></entry>
39 
40         </map>
41     </property>
42 </bean>
43 <!-- 放入连接池,做懒加载 -->
44 <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
45 <property name="targetDataSource">
46 <ref bean="dynamicDataSource"/></property>
47 </bean>
View Code

 

 

  3.测试成功

六.Django实现主从分离

  配置很简单:https://blog.csdn.net/linzi1994/article/details/82934612

 

posted @ 2019-05-13 21:23  biu嘟  阅读(6094)  评论(0编辑  收藏  举报