|
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 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=0
c: 查看可用yum源 yum repolist enabled | grep mysql。
d: yum install mysql-community-server。然后就会下载并执行安装。
3. 在另一台服务器上执行相同的操作。
4. 修改两台服务器的root密码，并且允许远程登录。
5. 在主服务器上配置MySQl 。
a: vim /etc/my.cnf
b:
[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 | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 73 | test | manual,mysql |
+——————+———-+————–+——————+
记住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就表示主从配置已经成功
至此就已经在两台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[0].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的主从配置和应用层的读写分离。
(责任编辑：IT) |