mysql读写分离

最新推荐文章于 2025-08-16 23:22:14 发布
最新推荐文章于 2025-08-16 23:22:14 发布
阅读量155 收藏
点赞数
分类专栏： 高性能的WEB开发 文章标签： MySQL SQL Server log4j 配置管理 应用服务器

近来公司做了一个计费系统，每天产生的CDR数据较多，数据读写要求比较高，在此基础上，采用了数据读写分离策略，来缓解数据库的压力。

应用环境

1、mysql-proxy，采用了开发框架amoeba，mysql-proxy负责管理client实际访问database的路由策略，实现数据的读写分离。

2、数据库集群环境，database集群采用了mysql的master-slave的replication方案。

3、客户端的应用程序，基于c3p0连接mysql-proxy。


结构示意图



 

实战

一、搭建mysql的master-slave环境

1、分别在192.168.1.132（master）和192.168.1.128（slave）安装mysql数据库，本实例在CentOS5.3的环境下，mysql数据库为mysql-5.1.51.tar.gz版本。

2、配置master

    a、修改master的/etc/my.cnf文件，添加

          log-bin=mysql-bin                                              #slave会基于此log-bin来做replication
          server-id=1                                                        #master的标示
          binlog-do-db = billing                                         #用于master-slave的具体数据库

 

    b、开启mysql的日志文件，方便排错（可选）

          log-error=/usr/local/mysql/log/error.log                       #错误日志 
          log=/usr/local/mysql/log/mysql.log                               #查询日志
          long_query_time=2                                                      #慢查询日志，指执行超过多久的sql会被log下来，这里是2秒
          log-slow-queries=/usr/local/mysql/log/slowquery.log  #将查询返回较慢的语句进行记录

    c、为slave添加一个replication用户，授权

         mysql>grant replication slave on *.* to repl@192.168.1.128 identified by '123456'

    d、重启mysql服务，查看master的状态

mysql> show master status;
+-----------------------+------------+-------------------+-----------------------+
| File                        | Position   | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+------------+-------------------+-----------------------+
| mysql-bin.000005 |      106     | billing              |                              |
+-----------------------+------------+-------------------+-----------------------+
1 row in set (0.00 sec)

  

3、配置salve

   a、修改slave的/etc/my.cnf文件，添加
          server-id=2                                                        #slave的标示

 

   b、开启mysql的日志文件，方便排错（可选）

          log-error=/usr/local/mysql/log/error.log                       #错误日志 
          log=/usr/local/mysql/log/mysql.log                               #查询日志
          long_query_time=2                                                      #慢查询日志，指执行超过多久的sql会被log下来，这里是2秒
          log-slow-queries=/usr/local/mysql/log/slowquery.log  #将查询返回较慢的语句进行记录

   c、重启mysql服务，配置与master的连接

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.132',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000005',
    -> MASTER_LOG_POS=106;

注：MASTER_HOST是master机的ip，MASTER_USER和MASTER_PASSWORD就是我们刚才在master上添加的用户，MASTER_LOG_FILE和MASTER_LOG_POS对应与master status里的信息

   d、启动slave

mysql>start slave;

 

4、验证master-salve环境是否搭配成功

通过查看slave日志（/usr/local/mysql/log/error.log）

有如下信息则表示成功

100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl@192.168.1.132:3306',  replication started in log 'mysql-bin.000005' at position 106

没有成功则通过日志文件查看并解决问题

 

二、搭建mysql-proxy环境

本实例采用了amoeba作为mysql的代理服务器，版本为amoeba-mysql-1.2.0-GA，下装与安装详细参见官方本档，proxy服务器的IP为192.168.1.120

配置amoeba，/usr/local/amoeba-mysql-1.2.0-GA/conf/amoeba.xm

a、提供客户端应用程序的数据库配置

	<server>
		<!-- proxy server绑定的端口 -->
		<property name="port">8066</property>
		
		<!-- proxy server绑定的IP -->
		<!-- 
		<property name="ipAddress">127.0.0.1</property>
		 -->
		<!-- proxy server net IO Read thread size -->
		<property name="readThreadPoolSize">20</property>
		
		<!-- proxy server client process thread size -->
		<property name="clientSideThreadPoolSize">30</property>
		
		<!-- mysql server data packet process thread size -->
		<property name="serverSideThreadPoolSize">30</property>
		
		<!-- socket Send and receive BufferSize(unit:K)  -->
		<property name="netBufferSize">128</property>
		
		<!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->
		<property name="tcpNoDelay">true</property>
		
		<!-- 对外验证的用户名 -->
		<property name="user">root</property>
		
		<!-- 对外验证的密码 -->
		
		<property name="password">123abc</property>
		
	</server>

 

 b、配置proxy与数据库服务器之间的连接

<dbServerList>
		<!-- 
			一台mysqlServer 需要配置一个pool，
			如果多台 平等的mysql需要进行loadBalance， 
			平台已经提供一个具有负载均衡能力的objectPool：com.meidusa.amoeba.mysql.server.MultipleServerPool
			简单的配置是属性加上 virtual="true",该Pool 不允许配置factoryConfig
			或者自己写一个ObjectPool。
		-->
		<dbServer name="server1">
			
			<!-- PoolableObjectFactory实现类 -->
			<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
				<property name="manager">defaultManager</property>
				
				<!-- 真实mysql数据库端口 -->
				<property name="port">3306</property>
				
				<!-- 真实mysql数据库IP -->
				<property name="ipAddress">192.168.1.132</property>
				<property name="schema">billing</property>
				
				<!-- 用于登陆mysql的用户名 -->
				<property name="user">root</property>
				
				<!-- 用于登陆mysql的密码 -->
				
				
				<property name="password">123456</property>
				
				
			</factoryConfig>
			
			<!-- ObjectPool实现类 -->
			<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
				<property name="maxActive">200</property>
				<property name="maxIdle">200</property>
				<property name="minIdle">10</property>
				<property name="minEvictableIdleTimeMillis">600000</property>
				<property name="timeBetweenEvictionRunsMillis">600000</property>
				<property name="testOnBorrow">true</property>
				<property name="testWhileIdle">true</property>
			</poolConfig>
		</dbServer>
		
		<dbServer name="multiPool1" virtual="true">
			<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
				<!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
				<property name="loadbalance">1</property>
				
				<!-- 参与该pool负载均衡的poolName列表以逗号分割 -->
				<property name="poolNames">server1</property>
			</poolConfig>
		</dbServer>
		
		<dbServer name="server2">
			
			<!-- PoolableObjectFactory实现类 -->
			<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
				<property name="manager">defaultManager</property>
				
				<!-- 真实mysql数据库端口 -->
				<property name="port">3306</property>
				
				<!-- 真实mysql数据库IP -->
				<property name="ipAddress">192.168.1.128</property>
				<property name="schema">billing</property>
				
				<!-- 用于登陆mysql的用户名 -->
				<property name="user">root</property>
				
				<!-- 用于登陆mysql的密码 -->
				
				
				<property name="password">123456</property>
				
				
			</factoryConfig>
			
			<!-- ObjectPool实现类 -->
			<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
				<property name="maxActive">200</property>
				<property name="maxIdle">200</property>
				<property name="minIdle">10</property>
				<property name="minEvictableIdleTimeMillis">600000</property>
				<property name="timeBetweenEvictionRunsMillis">600000</property>
				<property name="testOnBorrow">true</property>
				<property name="testWhileIdle">true</property>
			</poolConfig>
		</dbServer>
		
		
		<dbServer name="multiPool2" virtual="true">
			<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
				<!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
				<property name="loadbalance">1</property>
				
				<!-- 参与该pool负载均衡的poolName列表以逗号分割 -->
				<property name="poolNames">server2</property>
			</poolConfig>
		</dbServer>
		
	</dbServerList>

 

c、配置proxy读写分离

写道
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>
<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property>
<property name="writePool">server1</property>
<property name="readPool">server2</property>
<property name="needParse">true</property>
</queryRouter>

  

d、运行mysql-proxy

[root@localhost etc]#./usr/local/amoeba-mysql-1.2.0-GA/bin/amoeba

有如下提示表示启动成功

[app@SIMBANK /]$ ./home/app/java/amoeba-mysql-1.2.0-GA/bin/amoeba
log4j:WARN log4j config load completed from file:/home/app/java/amoeba-mysql-1.2.0-GA/conf/log4j.xml
log4j:WARN ip access config load completed from file:/home/app/java/amoeba-mysql-1.2.0-GA/conf/access_list.conf
2011-05-13 11:37:54,888 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

  

 

三、测试

a、编辑测试程序，本实例采用基于SSH框架的应用程序进行测试，具体代码略，数据库连接配置如下:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.1.120:8066/billing" />
<property name="user" value="root" />
<property name="password" value="123abc" />
<property name="minPoolSize" value="1" />
<property name="maxPoolSize" value="1" />
<property name="maxIdleTime" value="1800" />
<property name="acquireIncrement" value="1" />
<property name="maxStatements" value="0" />
<property name="initialPoolSize" value="1" />
<property name="idleConnectionTestPeriod" value="1800" />
<property name="acquireRetryAttempts" value="6" />
<property name="acquireRetryDelay" value="1000" />
<property name="breakAfterAcquireFailure" value="false" />
<property name="testConnectionOnCheckout" value="true" />
<property name="testConnectionOnCheckin" value="false" />
</bean>

 

b、测试插入数据

Hibernate: 
    insert 
    into
        tf_user
        (name, password) 
    values
        (?, ?)

 

查看master日志文件

写道
5 Query SHOW COLLATION
5 Query set names latin1
5 Query SET NAMES latin1
5 Query SET character_set_results = NULL
5 Query SET autocommit=1
5 Query SHOW FULL TABLES FROM `amoeba_study` LIKE 'PROBABLYNOT'
5 Query SHOW FULL TABLES FROM `amoeba_study` LIKE 'PROBABLYNOT'
5 Query SET autocommit=0
5 Query insert into tf_user (name, password) values ('china', 'china')
5 Query commit
5 Query SET autocommit=1

 

查看slave日志文件

110513 11:55:32     5 Query     SELECT @@session.tx_isolation
110513 11:55:33     2 Query     insert into tf_user (name, password) values ('china', 'china')

 说明实现了master上的writer操作，同时master向slave进行了replication操作

 

c、查询数据

Hibernate: 
    select
        this_.id as id6_0_,
        this_.name as name6_0_,
        this_.password as password6_0_ 
    from
        tf_user this_

 

查看slave日志文件

写道
110513 12:00:11 5 Query SELECT @@session.tx_isolation
5 Query select this_.id as id6_0_, this_.name as name6_0_, this_.password as password6_0_ from tf_user this_

 

综上所述，一个简单的mysql读写分离项目已经完成。

Mysql 读写分离中间件
u013454416的博客
01-18 2424
mysql读写分离算是很早就提出的一个提高数据库性能的方案，最少有10多年之久了吧，但是实际应用中非常多的企业系统中还是在使用MySQL主从，甚至就是一个mysql。甚至很多资深的开发人员知道读写分离，什么mycat 等等，但是真正用于生产的还是不多。今天我们就给大家推荐一款轻量级，配置简单搭建方便的读写分离中间件。经过多款读写分离中间件比对后最为实用，非常适合中小型系统的读写分离中间件 maxscale。
mysql 读写分离_详解MySQL读写分离
weixin_33173924的博客
01-18 3186
主从复制的原理MySQL的主从复制和读写分离两者有着紧密的联系，首先要部署主从复制，只有主从复制完成了才能在此基础上进行数据的读写分离。读写分离的原理简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本原理是让主数据库处理事务性查询，而从服务器处理select查询。数据库复制被用来把事务性查询导致的变更同步到从数据库中。基于中间代理层实现：代理一般位于客户端和服务器之间，代理服务器接到客...
MySQL读写分离
2501_91344113的博客
05-15 1322
在slave1和2上查询不到，在master上才能查看到这条语句内容，说明写操作在master服务器上，由此验证，已经实现了mysql读写分离，目前所有的操作都全部在master主服务器上，用来避免数据的不同步，所有的读操作都分摊给了slave从服务器，用来分担数据库的压力。简单来说，读写分离就是只在主服务器上写，只在从服务器上读。不过，无论是哪种存储方式，在最终用户看，MyCAT里都是一个传统的数据库表，支持标准的 SQL,语句进行数据的操作，这样，对前端业务系统来说可以大幅降低开发难度，提升开发速度。
MySQL 读写分离
程序猿进阶
10-11 6604
当系统的所有写操作效率尚可，读数据请求效率较低时，比如之前订单表存放了几千万条数据，且查询订单信息需要关联十几个字表，每个字表的数据超亿条。进行消息订阅消费，根据订单号进行分区消费，同一个订单分配至同一个分区，同一个分区是顺序消费的，从而保证消息的时序性。【1】横向扩展：通过增加从库的数量，可以轻松地扩展系统的读处理能力，而不需要对主库进行复杂的扩展。从库可以针对特定的读操作进行优化，如创建特定的索引或缓存策略，而不影响主库的写操作性能。格式，丰富的查询语言，支持复杂的查询和聚合操作，水平扩展。
Mysql 读写分离
lihui12356的博客
10-23 2063
读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
MySQL 读写分离实例详解
12-15
MySQL 读写分离是一种常见的数据库优化策略，它将数据库的读操作和写操作分配到不同的服务器上，以提高系统的并发处理能力和响应速度。在大型应用系统中，由于大量的读操作通常远超过写操作，这种分离可以显著提升...
springboot+mysql读写分离
07-29
springboot结合mysql主从来实现读写分离 一、实现的功能 1、基于springboot框架，application.yml配置多个数据源，使用AOP以及AbstractRootingDataSource、ThreadLocal来实现多数据源切换，以实现读写分离。mysql...
Thinkphp实现MySQL读写分离操作示例
12-19
MySQL的读写分离是一种常见的优化策略，尤其在高并发场景下，通过将读取操作分散到多个从库，减轻主库的压力，保证数据的写入速度。Thinkphp框架提供了一种便捷的方式实现MySQL的读写分离，使得开发者无需深入底层...
Maven私服配置模版
ZZHow Blog
08-16 488
Maven私服配置模版，包括 settings.xml 配置文件 和 pom.xml 配置文件 写于2024/08/02
嵌入式C语言从入门到精通视频教程.zip
08-21
目录： 1.1C语言会被淘汰吗?.mp4 1.2 如何成为嵌入式高手.mp4 1.3 搭建开发环境.mp4 1.4初识程序结构.mp4 1.5单片机程序的编译与运行简介.mp4 2.1 单片机中数据表现形式.mp4 2.2 为什么要引入数据类型.mp4 2.3 为什么要使用C99的整数类型.mp4 2.4 sizeof用法.mp4 2.5 GPIO输出速度影响什么.mp4 2.5 负数的二进制表现形式.mp4 2.6 变量的用法与注意事项.mp4 2.7 浮点型数据类型 2.8 一个字符引入的BUG 2.9 浮点数应用注意事项 2.10 为什么要引入ASCII码 3.1 C语言有哪些运算符 3.2 算数运算符及应用案例 3.3 算数复合赋值运算符 3.4 增1和减1运算符及应用案例 3.5 单片机是如何控制外设的 3.6 牢记位运算符的口诀 3.7 逻辑移位与算数移位的区别 3.8 左移右移位运算应用案例 3.9 位运算应用案例1.mp4 3.10 位运算应用案例2 .mp4 4.1 printf的基本用法.mp4 4.2 printf的精细格式控制.mp4 4.3 printf输出转义序列.mp4 5.1数据运算的类型转换.mp4 5.2数据截断和数据扩充的规则.mp4 5.3数据扩充的应用案例和总结.mp4 5.4数据运算发生溢出的危害.mp4 5.5 数据扩充案例.mp4 5.6 24000000U中的U是做什么用的?.mp4 6.1 bool数据类型.mp4 ............ 网盘文件永久链接
该课题为基于Matlab的人脸考勤系统。如果需要硬件结合的话，可以调取摄像头。带有人机交互界面。可以在人机交互界面的基础之上，进行相应的拓展。人脸库可以使用您自己的真实人脸库。(11).zip
08-21
该课题为基于Matlab的人脸考勤系统。如果需要硬件结合的话，可以调取摄像头。带有人机交互界面。可以在人机交互界面的基础之上，进行相应的拓展。人脸库可以使用您自己的真实人脸库。(11).zip
该课题为基于Matlab的运动目标跟踪系统。可以实时框定运动目标。对运动目标的行为做识别。带有人机交互界面，需要在人机交互界面的基础上进行拓展(12).zip
08-21
该课题为基于Matlab的运动目标跟踪系统。可以实时框定运动目标。对运动目标的行为做识别。带有人机交互界面，需要在人机交互界面的基础上进行拓展(12).zip
毫米波MIMO系统中基于凹度近似的功率分配.zip
08-21
1.版本：matlab2014a/2019b/2024b 2.附赠案例数据可直接运行。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。
该课题为基于Matlab的运动目标跟踪系统。可以实时框定运动目标。对运动目标的行为做识别。带有人机交互界面，需要在人机交互界面的基础上进行拓展(21).zip
最新发布
08-21
该课题为基于Matlab的运动目标跟踪系统。可以实时框定运动目标。对运动目标的行为做识别。带有人机交互界面，需要在人机交互界面的基础上进行拓展(21).zip
b652817b2fe4d0f6c8776c53339d8e46.apk
08-21
b652817b2fe4d0f6c8776c53339d8e46.apk
自制RK3566核心板，包含原理图和PCB图，硬件成本仅需55元
08-21
核心板板载外设： - lpddr4*1 - emmc flash*1 - wifi&蓝牙*1 - type-c otg接口*1 - uart debug接口*1 - 必要按键*3
MySQL读写分离配置实战
"MySQL读写分离实现方法" 在MySQL数据库系统中，读写分离是一种常见的优化策略，用于提高数据库系统的性能和可扩展性。当数据库负载较大时，读写分离能够将读请求和写请求分散到不同的服务器上，减轻主数据库的压力...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值