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

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

1.主从配置

环境描述：

主数据库服务器IP:172.16.48.52      

版本：MYSQL5.1.73

系统：CentOS6.5

安装方式：源码安装

 

[root@Jack-PC ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.1.73-log Source distribution

Copyright (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 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server 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/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-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 = 55 log-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)