一下详述部分均在Unix服务器上完成(CentOS)
1.安装3个mysql,版本一致
安装仅供参考点击查看
2.修改主服务器master:
- [root@yg-rhel-test2 ~]# vi /etc/my.cnf
- [mysqld]
- log-bin=mysql-bin //[必须]启用二进制日志
- server-id=26 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
3.修改从服务器slave 2台,不同的是id:
- [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的状态信息:
- [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:
- [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.检查从服务器复制功能状态:
- 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的账户设置,
- mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION; //任何远程主机都可以访问数据库
- mysql> FLUSH PRIVILEGES; //需要输入次命令使修改生效
结果还是连不到,现在只有怀疑是openstack的问题了,后来发现,openstack有自己的访问规则,相等于一层防火墙,把所有端口放开后,就可以本地连接mysql了。
附加:服务端的实现(Java)
1.使用spring aop 拦截机制现数据源的动态选取。
- 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
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- public class ChooseDataSource extends AbstractRoutingDataSource {
- @Override
- protected Object determineCurrentLookupKey() {
- return HandleDataSource.getDataSource();
- }
- }
4.利用ThreadLocal解决线程安全问题
- 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注解。
- 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 -->