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

电玩女神 2022-06-08 03:25 109阅读 0赞

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

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

安装仅供参考[点击查看][Link 1]

2.修改主服务器master:

**\[html\]** [ view plain][view plain] [ copy][view plain]

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

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

**\[html\]** [ view plain][view plain] [ copy][view plain]

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

4.重启两台服务器的mysql  
   /etc/init.d/mysql restart或者  
   service mysql restart  
  
5.在主服务器上授权给slave，并查询master的状态信息:

**\[html\]** [ view plain][view plain] [ copy][view plain]

1.  \[root@yg-rhel-test2 ~\]\# mysql -u root -proot  
2.  mysql> GRANT REPLICATION slave ON \*.\* to 'root'@'172.22.14.%' identified by 'root';  //授权账号root@某个机器上，授权密码是root。  
3.  mysql> show master status;              //查询master的状态信息，为配置slave使用  
4.  \+------------------+----------+--------------+------------------+-------------------+  
5.  | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
6.  \+------------------+----------+--------------+------------------+-------------------+  
7.  | mysql-bin.000001 |      478 |              |                  |                   |  
8.  \+------------------+----------+--------------+------------------+-------------------+  
9.  1 row in set (0.00 sec)

6.配置从服务器Slave：

**\[html\]** [ view plain][view plain] [ copy][view plain]

1.  \[root@yg-rhel-test1 ~\]\# mysql -u root -proot  
2.  mysql> CHANGE MASTER to MASTER\_HOST='172.22.14.26',MASTER\_USER='root',MASTER\_PASSWORD='root',  
3.      ->          MASTER\_LOG\_FILE='mysql-bin.000001',MASTER\_LOG\_POS=320;  
4.  mysql> start slave;  
5.

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

**\[html\]** [ view plain][view plain] [ copy][view plain]

1.  mysql> show slave status;  
2.  \+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+  
3.  | 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 |  
4.  \+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+  
5.  | 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 |  
6.  \+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+  
7.  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][view plain] [ copy][view plain]

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

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

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

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

**\[html\]** [ view plain][view plain 1] [ copy][view plain 1]

1.  import java.lang.annotation.ElementType;  
2.  import java.lang.annotation.Target;  
3.  import java.lang.annotation.Retention;  
4.  import java.lang.annotation.RetentionPolicy;  
5.  /\*\*  
6.   \* RUNTIME  
7.   \* 编译器将把注释记录在类文件中，在运行时 VM 将保留注释，因此可以反射性地读取。  
8.   \* @author yangGuang  
9.   \*  
10.  \*/  
11. @Retention(RetentionPolicy.RUNTIME)  
12. @Target(ElementType.METHOD)  
13. public @interface DataSource \{  
14.     String value();  
15. \}

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

**\[html\]** [ view plain][view plain 1] [ copy][view plain 1]

1.  import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  
2.    
3.   public class ChooseDataSource extends AbstractRoutingDataSource \{  
4.    
5.       @Override  
6.       protected Object determineCurrentLookupKey() \{  
7.           return HandleDataSource.getDataSource();  
8.       \}  
9.         
10.  \}

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

**\[html\]** [ view plain][view plain 1] [ copy][view plain 1]

1.  public class HandleDataSource \{  
2.      public static final ThreadLocal<String> holder = new ThreadLocal<String>();  
3.      public static void putDataSource(String datasource) \{  
4.          holder.set(datasource);  
5.      \}  
6.        
7.      public static String getDataSource() \{  
8.          return holder.get();  
9.      \}      
10. \}

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

**\[html\]** [ view plain][view plain 1] [ copy][view plain 1]

1.  import java.lang.reflect.Method;  
2.  import org.aspectj.lang.JoinPoint;  
3.  import org.aspectj.lang.annotation.Aspect;  
4.  import org.aspectj.lang.annotation.Before;  
5.  import org.aspectj.lang.annotation.Pointcut;  
6.  import org.aspectj.lang.reflect.MethodSignature;  
7.  import org.springframework.stereotype.Component;  
8.  //@Aspect  
9.  //@Component  
10. public class DataSourceAspect \{  
11.     //@Pointcut("execution(\* com.apc.cms.service.\*.\*(..))")    
12.     public void pointCut()\{\};    
13.       
14.   //  @Before(value = "pointCut()")  
15.      public void before(JoinPoint point)  
16.         \{  
17.             Object target = point.getTarget();  
18.             System.out.println(target.toString());  
19.             String method = point.getSignature().getName();  
20.             System.out.println(method);  
21.             Class<?>\[\] classz = target.getClass().getInterfaces();  
22.             Class<?>\[\] parameterTypes = ((MethodSignature) point.getSignature())  
23.                     .getMethod().getParameterTypes();  
24.             try \{  
25.                 Method m = classz\[0\].getMethod(method, parameterTypes);  
26.                 System.out.println(m.getName());  
27.                 if (m != null && m.isAnnotationPresent(DataSource.class)) \{  
28.                     DataSource data = m.getAnnotation(DataSource.class);  
29.                     HandleDataSource.putDataSource(data.value());  
30.                 \}  
31.                   
32.             \} catch (Exception e) \{  
33.                 e.printStackTrace();  
34.             \}  
35.         \}  
36. \}

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>
        
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource" />
        </bean>
    
    
        
        <context:annotation-config />
    
        
        <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" />
    
        
        <tx:annotation-driven />
    
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="typeAliasesPackage" value="com.apc.cms.model.domain" />
        </bean>
    
        
        <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">  
                      
                     <entry key="write" value-ref="writeDataSource"/>  
                     
                     <entry key="read" value-ref="readDataSource"/>  
                  </map>  
                  
            </property>  
            <property name="defaultTargetDataSource" ref="writeDataSource"/>  
        </bean>
          
        
        <aop:aspectj-autoproxy proxy-target-class="true"/>
        
        
        <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>

[Link 1]: http://blog.csdn.net/u011271894/article/details/77964844
[view plain]: http://blog.csdn.net/huoyunshen88/article/details/26597483#
[view plain 1]: http://blog.csdn.net/huoyunshen88/article/details/36674861#
[http_blog.csdn.net_alaahong_article_details_8707915]: http://blog.csdn.net/alaahong/article/details/8707915