mysql主从分离_Mysql主从分离介绍及实现

参考：

一.什么是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 packagecom.swpu.o2o.dao.split;2

3 importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;4 /**

5 * 继承AbstractRoutingDataSource实现抽象方法6 *@authorASUS7 *8 */

9 public class DynamicDataSource extendsAbstractRoutingDataSource{10

11 @Override12 protectedObject determineCurrentLookupKey() {13 returnDynamicDataSourceHolder.getDbType();14 }15

16

17 }

DynamicDataSource：

1 packagecom.swpu.o2o.dao.split;2

3 importorg.slf4j.Logger;4 importorg.slf4j.LoggerFactory;5

6

7

8 public classDynamicDataSourceHolder {9 //这是日志模块

10 private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceHolder.class);11 //线程安全的ThreadLocal模式

12 private static ThreadLocal contextHolder=new ThreadLocal();13 //两个key，主服务器，从服务器

14 private static final String DB_MASTER="master";15 public static final String DB_SLAVE="slave";16 /**

17 * 获取线程的DbType18 *@returndb19 */

20 public staticString getDbType(){21

22 String db=contextHolder.get();23 //如果为空，默认为master，即支持写，也支持读

24 if(db==null){25 db=DB_MASTER;26 }27 returndb;28 }29 /**

30 * 设置线程的dbType31 *@paramstr32 */

33 public static voidsetDbType(String str){34 logger.debug("所使用的数据源是："+str);35 contextHolder.set(str);36

37 }38 /**

39 * 清理连接类型40 */

41 public static voidDbType(){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 ASUS26 *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 @Override40 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 @Override75 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 @Override86 public void setProperties(Properties arg0) {87 // TODO Auto-generated method stub88

89 }90

91 }

View Code

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

1 <?xml version="1.0" encoding="UTF-8"?>

2 "http://mybatis.org/dtd/mybatis-3-config.dtd">

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

配置datasource：

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

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

1

2 destroy-method="close"id="abstractDataSource">

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

View Code

3.测试成功

六.Django实现主从分离