MYSQL主从配置以及客户端代码实现读写分离功能

MYSQL主从配置以及客户端代码实现读写分离功能

1.主从配置

环境描述：

主数据库服务器IP:172.16.48.52      

版本：MYSQL5.1.73

系统：CentOS6.5

安装方式：源码安装

 

[root@Jack-PC ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 82Server version: 5.1.73-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

从数据库服务器   IP:172.16.138.55    

版本：MYSQL5.6.26

系统：CentOS6.5

安装方式：rpm安装包

 

[root@localhost home]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 57Server version: 5.6.26-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

 

具体配置：

主数据配置如下：

 

[root@Jack-PC ~]# cat /etc/my.cnf[mysqld]server-id=52 #[必须]服务器唯一ID，默认是1，一般取IP最后一段log_bin=mysql-bin #[必须]启用二进制日志binlog-do-db=community_care #需要记录二进制日志的的数据库名称，该项可重复添加binlog-ignore-db=mysql #需要忽略二进制日志的数据库名称，该项可重复添加datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

 

从数据库配置如下：[root@localhost home]# cat /usr/my.cnf

[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# These are commonly set, remove the # and set as required.# basedir = .....# datadir = .....# port = .....server_id = 55log-bin=mysql-bin  #[不是必须]启用二进制日志replicate-do-db=community_care #需要复制的数据库名称replicate-ignore-db=mysql #需要忽略复制的数据库名称relay_log=relay-bin #[必须]设置中继日志文件名称# socket = .....sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

说明：以上配置中标红的配置是配置主从而新增的配置项，其他的配置项保持原来不变，  而且对于Linux系统不同的安装方式配置文件略有不同，不能直接copy使用。

        

同步授权：

1.在主数据库服务器上建立账户并对从数据库进行授权

 本人没有新建账户，直接使用root账户

 #mysql -u root -p   //登录主数据库服务器命令窗口

 #mysql>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'root'@'172.16.138.55' IDENTIFIED BY '123456'  //只允许172.16.138.55这台主机进行同步数据。

2.查询主数据库服务器的master状态

#mysql>show master status ;

mysql> show master status ;+------------------+----------+----------------+------------------+| File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB |+------------------+----------+----------------+------------------+| mysql-bin.000002 |     2273 | community_care | mysql            |+------------------+----------+----------------+------------------+1 row in set (0.00 sec)

说明：记录该查询数据，下面在设置从数据库是会用到查数据

 

设置主数据库：

1.在从数据库上设置主数据库

#mysql -u root -p   //登录从数据库服务器命令窗口

#mysql>change master to master_host='172.16.48.52',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=2273;   

2.开启复制功能

#mysql>start slave ;   //停止  stop slave

3.验证主从配置否是成功

 

+--------------+-------------+-------------+---------------+---------------+-----------------------+------------------+-------------------+...| Master_Host  | Master_User | Master_Port | Connect_Retry | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running |...+--------------+-------------+-------------+---------------+---------------+-----------------------+------------------+-------------------+...| 172.16.48.52 | root        |        3306 |            60 |           265 | mysql-bin.000002      | Yes              | Yes               |...+--------------+-------------+-------------+---------------+---------------+-----------------------+------------------+-------------------+...

 

如果：标红的两项数据是YES表示同步配置成功。

 

2.读写分离实现

代码实现使用Java客户端

1.继承AbstractRoutingDataSource

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class MultipleDataSource extends AbstractRoutingDataSource {    private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>();    public static void setDataSourceKey(String dataSource) {        dataSourceKey.set(dataSource);    }    @Override    protected Object determineCurrentLookupKey() {        return dataSourceKey.get();    }}

2.配置读写数据源

<bean name="writeDataSource" class="com.alibaba.druid.pool.DruidDataSource"        init-method="init" destroy-method="close">        <property name="url" value="${jdbc.url}" />        <property name="username" value="${jdbc.username}" />        <property name="password" value="${jdbc.password}" />        <!-- 初始化连接大小 -->        <property name="initialSize" value="10" />        <!-- 连接池最大使用连接数量 -->        <property name="maxActive" value="20" />        <!-- 连接池最大空闲 -->        <property name="maxIdle" value="20" />        <!-- 连接池最小空闲 -->        <property name="minIdle" value="0" />        <!-- 获取连接最大等待时间 -->        <property name="maxWait" value="60000" />        <property name="validationQuery" value="${validationQuery}" />        <property name="testOnBorrow" value="false" />        <property name="testOnReturn" value="false" />        <property name="testWhileIdle" value="true" />        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->        <property name="timeBetweenEvictionRunsMillis" value="60000" />        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->        <property name="minEvictableIdleTimeMillis" value="25200000" />        <!-- 打开removeAbandoned功能 -->        <property name="removeAbandoned" value="true" />        <!-- 1800秒，也就是30分钟 -->        <property name="removeAbandonedTimeout" value="1800" />        <!-- 关闭abanded连接时输出错误日志 -->        <property name="logAbandoned" value="true" />        <!-- 监控数据库 -->        <!-- <property name="filters" value="stat" /> -->        <property name="filters" value="mergeStat" />    </bean>        <bean name="readDataSource" class="com.alibaba.druid.pool.DruidDataSource"        init-method="init" destroy-method="close">        <property name="url" value="${slave.jdbc.url}" />        <property name="username" value="${slave.jdbc.username}" />        <property name="password" value="${slave.jdbc.password}" />        <!-- 省略其他配置，同主数据源配置 -->    </bean>

3.配置多数据源和MyBatis的SqlSessionFactory

<bean id="multipleDataSource" class="com.kedacom.tz.community_care.core.multids.MultipleDataSource">        <property name="defaultTargetDataSource" ref="writeDataSource"/>        <property name="targetDataSources">            <map>                <entry key="writeDataSource" value-ref="writeDataSource"/>                <entry key="readDataSource" value-ref="readDataSource"/>            </map>        </property>    </bean>    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="multipleDataSource" />        <property name="typeAliasesPackage" value="com.kedacom.tz.community_care.commondao.dataobject" />        <property name="configLocation" value="classpath:mybatis-config.xml" />    </bean>

 4.单元测试代码

@Test    public void testMasterSlave() {        writeOnMaster() ;        Thread t = new Thread(new Runnable() {            @Override            public void run() {                readOnSlave() ;            }        }) ;        t.start();     }

测试结果如下：

主数据库查询结果

mysql> select * from community limit 6,5 ;+----+---------------------------------------+------------------+| id | name                                  | descrip          |+----+---------------------------------------+------------------+|  7 | 代码级别测试主从多数据源  | 主从代码     ||  8 | 代码级别测试主从多数据源3 | 主从代码3    ||  9 | last one                              | master and slave |+----+---------------------------------------+------------------+3 rows in set (0.00 sec)mysql> select version() ;+------------+| version()  |+------------+| 5.1.73-log |+------------+1 row in set (0.00 sec)

从数据库结果

mysql> select * from community limit 6,5;+----+---------------------------------------+------------------+| id | name                                  | descrip          |+----+---------------------------------------+------------------+|  7 | 代码级别测试主从多数据源              | 主从代码         ||  8 | 代码级别测试主从多数据源3             | 主从代码3        ||  9 | last one                              | master and slave |+----+---------------------------------------+------------------+3 rows in set (0.00 sec)mysql> select version() ;+------------+| version()  |+------------+| 5.6.26-log |+------------+1 row in set (0.21 sec)