数据库(Mysql)读写分离之服务器配置和服务端实现

来源：互联网发布：带数字的域名编辑：程序博客网时间：2024/05/20 14:27

一下详述部分均在Unix服务器上完成(CentOS)

1.安装3个mysql，版本一致

安装仅供参考点击查看

2.修改主服务器master:

[html] view plain copy

[root@yg-rhel-test2 ~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=26 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3.修改从服务器slave 2台，不同的是id:

[html] view plain copy

[root@yg-rhel-test1 ~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=24 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4.重启两台服务器的mysql
   /etc/init.d/mysql restart或者
   service mysql restart

5.在主服务器上授权给slave，并查询master的状态信息:

[html] view plain copy

[root@yg-rhel-test2 ~]# mysql -u root -proot
mysql> GRANT REPLICATION slave ON *.* to 'root'@'172.22.14.%' identified by 'root'; //授权账号root@某个机器上，授权密码是root。
mysql> show master status; //查询master的状态信息，为配置slave使用
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 478 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6.配置从服务器Slave：

[html] view plain copy

[root@yg-rhel-test1 ~]# mysql -u root -proot
mysql> CHANGE MASTER to MASTER_HOST='172.22.14.26',MASTER_USER='root',MASTER_PASSWORD='root',
-> MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=320;
mysql> start slave;

8.检查从服务器复制功能状态：

[html] view plain copy

mysql> show slave status;
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Connecting to master | 172.22.14.26 | root | 3306 | 60 | mysql-bin.000001 | 320 | yg-rhel-test1-relay-bin.000001 | 4 | mysql-bin.000001 | Connecting | Yes | | | | | | | 0 | | 0 | 320 | 120 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | | 0 | | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
1 row in set (0.00 sec)

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
以上操作过程，主从服务器配置完成。

遇到的问题，以及解决的命令：
1.查看MySQL端口号 inux 下通过netstat -a 来查看，或者mysql> show variables like 'port';

2.查看Mysql服务运行状态：ps -ef|grep mysqld

3.MySQL错误：Can't connect to MySQL server (10060)
从以下几个方面入手，找出错误的原因：
1.网络不通，经查询ok。
检查能不能ping通。

2.防火墙设置。防火墙是否放过mysql的进程，是否屏蔽了mysql的3306端口。
防火墙关闭了，没有问题，

3.mysql的账户设置，

[html] view plain copy

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION; //任何远程主机都可以访问数据库
mysql> FLUSH PRIVILEGES; //需要输入次命令使修改生效

结果还是连不到，现在只有怀疑是openstack的问题了，后来发现，openstack有自己的访问规则，相等于一层防火墙，把所有端口放开后，就可以本地连接mysql了。

附加:服务端的实现(Java)

1.使用spring aop 拦截机制现数据源的动态选取。

[html] view plain copy

import java.lang.annotation.ElementType;
import java.lang.annotation.Target;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
/**
* RUNTIME
* 编译器将把注释记录在类文件中，在运行时 VM 将保留注释，因此可以反射性地读取。
* @author yangGuang
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {
String value();
}

3.利用Spring的AbstractRoutingDataSource解决多数据源的问题参考： http://blog.csdn.net/alaahong/article/details/8707915

[html] view plain copy

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class ChooseDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return HandleDataSource.getDataSource();
}
}

4.利用ThreadLocal解决线程安全问题

[html] view plain copy

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();
}
}

5.定义一个数据源切面类，通过aop访问，在可以通过spring配置文件中配置了，也可以使用aop注解。

[html] view plain copy

import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
//@Aspect
//@Component
public class DataSourceAspect {
//@Pointcut("execution(* com.apc.cms.service.*.*(..))")
public void pointCut(){};
// @Before(value = "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();
}
}
}

6.配置applicationContext.xml(数据源的相关配置,可以使用PropertyPlaceholderConfigurer加载properties的方法,本地为了方便,直接写死)

    <!-- 主库数据源 -->     <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://172.22.14.6:3306/cpp?autoReconnect=true"/>        <property name="username" value="root"/>        <property name="password" value="root"/>        <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://172.22.14.7:3306/cpp?autoReconnect=true"/>        <property name="username" value="root"/>        <property name="password" value="root"/>        <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.apc.cms.persistence.rdbms" />    <context:component-scan base-package="com.apc.cms.service">     <context:include-filter type="annotation"              expression="org.springframework.stereotype.Component" />      </context:component-scan>         <context:component-scan base-package="com.apc.cms.auth" />    <!-- enable transaction demarcation with annotations -->    <tx:annotation-driven />    <!-- define the SqlSessionFactory -->    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="dataSource" />        <property name="typeAliasesPackage" value="com.apc.cms.model.domain" />    </bean>    <!-- scan for mappers and let them be autowired -->    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">        <property name="basePackage" value="com.apc.cms.persistence" />        <property name="sqlSessionFactory" ref="sqlSessionFactory" />    </bean>        <bean id="dataSource" class="com.apc.cms.utils.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>          <property name="defaultTargetDataSource" ref="writeDataSource"/>      </bean>          <!-- 激活自动代理功能 -->    <aop:aspectj-autoproxy proxy-target-class="true"/>        <!-- 配置数据库注解aop -->    <bean id="dataSourceAspect" class="com.apc.cms.utils.DataSourceAspect" />    <aop:config>        <aop:aspect id="c" ref="dataSourceAspect">            <aop:pointcut id="tx" expression="execution(* com.apc.cms.service..*.*(..))"/>            <aop:before pointcut-ref="tx" method="before"/>        </aop:aspect>    </aop:config>    <!-- 配置数据库注解aop -->

阅读全文

1 0