MySQL 主從配置和基于Spring 的讀寫分離

MySQL 主從配置。

環境說明: 在windows10 系統上，用VMware建立了兩個虛拟機（一主一從），采用的Linux作業系統是CentOS 6.5 32位,MySQL 伺服器的版本是5.6。

在VMware上建立兩個CentOS 6.5的伺服器。在VMware上建立虛拟機需要注意網絡模式為橋接模式，并且建立的兩台伺服器在同一網段，否則兩台CentOS伺服器無法互相通路。
采用CentOS 自帶的yum源安裝MySQL。由于要安裝定制版本，是以需要在yum上做一些修改。

a:進入CentOS 指令行，
vim /etc/yum.repos.d/mysql-community.repo
,

b:編輯

[mysql56-community]
name=MySQL  Community Server
baseurl=http://repo.mysql.com/yum/mysql--community/el//$basearch/
enabled=
gpgcheck=

c: 檢視可用yum源

yum repolist enabled | grep mysql

。

yum install mysql-community-server

。然後就會下載下傳并執行安裝。

3. 在另一台伺服器上執行相同的操作。

4. 修改兩台伺服器的root密碼，并且允許遠端登入。

5. 在主伺服器上配置MySQl 。

vim /etc/my.cnf

[mysqld] 
 log-bin=mysql-bin

server-id為一個int類型的數字，隻要和從伺服器的server-id不同即可

6. 在從伺服器上配置MySQL

[mysqld] 
 server-id=2

7. 在主伺服器上建立一個使用者，用于從伺服器讀取主伺服器的log檔案。

mysql> CREATE USER 'repl'@'host_ip' IDENTIFIED BY 'slavepass'; 
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'host_ip';

8. 在主伺服器上執行

mysql> FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

+——————+———-+————–+——————+

記住File 列和Position列的值，将會在從伺服器上配置時使用。

9. 在從伺服器上

mysql> CHANGE MASTER TO 
 MASTER_HOST='master_ip', 
 MASTER_USER='userName', 
 MASTER_PASSWORD='password', 
 MASTER_LOG_FILE='log_file', 
 MASTER_LOG_POS= position;

這裡的LOG_FILE就是上面的File列的值，LOG_POS就是上面Position列的值。

在從伺服器上執行 mysql> start slave
在從伺服器上執行 show slave status \G; ，檢視到兩個yes就表示主從配置已經成功

MySQL 主從配置和基于Spring 的讀寫分離

至此就已經在兩台CentOS上安裝了相同版本的MySQL伺服器并且完成了主從配置。

Spring讀寫分離

github源代碼

本文描述的是使用基于Spring AOP 實作應用層的讀寫分離。資料庫查詢，尤其是多表關聯的複雜查詢對伺服器的性能損耗遠遠大于資料庫的插入操作，且日常生産環境中查詢的頻率遠大于增删改。是以為了緩解資料庫壓力，将對資料庫的讀壓力分流到從伺服器，讓主資料庫專注于增删改更加适合于高并發。普通的WEB應用分Controller,Service,Dao三層。事務控制一般在Service層。在Service層的代碼中，基于方法級别的讀寫分離是最理想的一種狀态。在Service層的代碼中，如果某個方法隻涉及到查詢，那麼則分流到從資料庫中，如果涉及到增，删，改則分流到主資料庫中。

1. 建立Maven Web工程。pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.tianxingshuke</groupId>
    <artifactId>MySQLReadAndWrite</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>MySQLReadAndWrite</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>4.1.6.RELEASE</spring.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>${spring.version}</version>
    </dependency>
        <!-- https://mvnrepository.com/artifact/com.jolbox/bonecp -->
        <dependency>
            <groupId>com.jolbox</groupId>
            <artifactId>bonecp</artifactId>
            <version>0.8.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
        </dependency>
    </dependencies>
</project>

定義一個注解類，用于辨別Service層的方法查詢操作從資料庫，涉及到增删改的操作主資料庫。

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {
    String value();
}

3.定義一個切面類，用于在編譯時織入到Service層中。當Service存在超類并且超類方法中有對應的DataSource的注解時，将注解中的值取出，并傳回給spring AbstractRoutingDataSource 子類determineCurrentLookupKey方法作為傳回值。

public class HandleDataSource {
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();

    public static void putDataSource(String datasource){
        holder.set(datasource);
    }

    public static String getDataSource(){
        return holder.get();
    }
}

public class DataSourceAspect {
    public void pointCut() {
    };

    public void before(JoinPoint point) {
        Object target = point.getTarget();
        System.out.println(target.toString());
        String method = point.getSignature().getName();
        System.out.println(method);
        Class<?>[] classz = target.getClass().getInterfaces();
        Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
        try {
            Method m = classz[].getMethod(method, parameterTypes);
            System.out.println(m.getName());
            if (m != null && m.isAnnotationPresent(DataSource.class)) {
                DataSource data = m.getAnnotation(DataSource.class);
                HandleDataSource.putDataSource(data.value());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

public class ChooseDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // TODO Auto-generated method stub
        return HandleDataSource.getDataSource();
    }
}

4 . 配置檔案 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd"
    default-lazy-init="true">
    <!-- 主庫資料源 -->
    <bean id="writeDataSource" class="com.jolbox.bonecp.BoneCPDataSource"
        destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl"
            value="jdbc:mysql://192.168.1.127:3306/mydb?autoReconnect=true" />
        <property name="username" value="root" />
        <property name="password" value="yaoyuan" />
        <property name="partitionCount" value="4" />
        <property name="releaseHelperThreads" value="3" />
        <property name="acquireIncrement" value="2" />
        <property name="maxConnectionsPerPartition" value="40" />
        <property name="minConnectionsPerPartition" value="20" />
        <property name="idleMaxAgeInSeconds" value="60" />
        <property name="idleConnectionTestPeriodInSeconds" value="60" />
        <property name="poolAvailabilityThreshold" value="5" />
    </bean>

    <!-- 從庫資料源 -->
    <bean id="readDataSource" class="com.jolbox.bonecp.BoneCPDataSource"
        destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl"
            value="jdbc:mysql://192.168.1.107:3306/mydb?autoReconnect=true" />
        <property name="username" value="root" />
        <property name="password" value="yaoyuan" />
        <property name="partitionCount" value="4" />
        <property name="releaseHelperThreads" value="3" />
        <property name="acquireIncrement" value="2" />
        <property name="maxConnectionsPerPartition" value="40" />
        <property name="minConnectionsPerPartition" value="20" />
        <property name="idleMaxAgeInSeconds" value="60" />
        <property name="idleConnectionTestPeriodInSeconds" value="60" />
        <property name="poolAvailabilityThreshold" value="5" />
    </bean>

    <!-- transaction manager, 事務管理 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>


    <!-- 注解自動載入 -->
    <context:annotation-config />

    <!--enale component scanning (beware that this does not enable mapper scanning!) -->
    <context:component-scan base-package="com.tianxingshuke" />

    <!-- enable transaction demarcation with annotations -->
    <tx:annotation-driven />

 <bean id="userDao" class="com.tianxingshuke.MySQLReadAndWrite.UserDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="dataSource" class="com.tianxingshuke.MySQLReadAndWrite.ChooseDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <!-- write -->
                <entry key="write" value-ref="writeDataSource" />
                <!-- read -->
                <entry key="read" value-ref="readDataSource" />
            </map>

        </property>

    </bean>

    <!-- 激活自動代理功能 -->
    <aop:aspectj-autoproxy proxy-target-class="true" />

    <!-- 配置資料庫注解aop -->
    <bean id="dataSourceAspect" class="com.tianxingshuke.MySQLReadAndWrite.DataSourceAspect" />
    <aop:config>
        <aop:aspect id="c" ref="dataSourceAspect">
            <aop:pointcut id="tx"
                expression="execution(* com.tianxingshuke.MySQLReadAndWrite.*.*(..))" />
            <aop:before pointcut-ref="tx" method="before" />
        </aop:aspect>
    </aop:config>
    <!-- 配置資料庫注解aop -->
</beans>

5 . 測試

public class User {
    private int id;
    private String name;
    public User(){}
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + "]";
    }
}

public interface UserDaoInterface {
    @DataSource(value = "write")
    public int addUser(User user);
    @DataSource(value="read")
    public List<User> getUser();
    @DataSource(value="write")
    public int delete();
}

public class UserDao implements UserDaoInterface{

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(javax.sql.DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public int addUser(User user) {
        return jdbcTemplate.update("insert into user values (null,?)", user.getName());
    }
    public List<User> getUser(){
        return jdbcTemplate.query("select id,name from user", new UserMapper());
    }
    private static final class UserMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            return user;
        }
    }
    public int delete() {
        int result = jdbcTemplate.update("delete from user");
        return result;
    }
}

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:/applicationContext.xml"})
public class AppTest {
    @Resource 
    UserDao userDao;

    @Test
    public void testGetTest(){
        List<User> list = userDao.getUser();
        for(User user : list){
            System.out.println(user.toString());
        }
    }

    @Test
    public void testMyTest(){
        User user = new User();
        user.setName("xxxxxxxx");
        int result = userDao.addUser(user);
        System.out.println(result);
    }

    @Test
    public void testDelete(){
        int delete = userDao.delete();
        System.out.println(delete);
    }
}

通過上面的介紹，可以完成MySQL的主從配置和應用層的讀寫分離。

MySQL 主從配置和基于Spring 的讀寫分離

繼續閱讀

MyBatis-Plus 之AR模式

馴服爛代碼_馴服Tomcat：Tomcat 5的過濾技巧

基于Springboot+VUE+Mysql的大學生校園疫情防控管理系統

Using MessageConverter

馴服爛代碼_C＃中的馴服配置

Spring Cloud整合Sleuth，當請求完成後，Zipkin沒有鍊路資訊

JAVA自學之路——馬士兵

Rabbitmq指令行手動建立隊列

整合 Spring 開發架構及服務端技術

SSM整合02---Spring-SpringMVC層一、添加web架構支援二、web.xml三、spring-mvc.xml

SSM架構（二）------------表現層的SpringMVC

Intellij idea基于Maven 的SSM 架構整合

Spring資料和Redis

SpringMVC的啟動過程源碼分析1 SpringMVC 配置XML的方式啟動2 SpringMVC純注解模式啟動

Java Spring 架構

SpringCloud微服務（四）之ribbon筆記ribbon是什麼？入門案例自定義均衡負載算法