Mysql读写分离

发表于 | 分类于 ，

一、 读写分离介绍

在这里插入图片描述
MySQL读写分离基本原理是proxy自动进行一个请求的分发。让master数据库处理写操作，slave数据库处理读操作。
Mysql读写分离能提高系统性能的原因在于：

  1. 物理服务器增加，机器处理能力提升，拿硬件换性能
  2. 主从只负责各自的读和写，极大缓解了排他锁和共享锁争用

二、读写分离相关配置

mysql proxy方式

硬件配置

master 192.168.12.111 node01
slave 192.168.12.112 node02
proxy 192，168.12.113 node03

在master和slave上配置主从复制

进行proxy的相关配置

  1. 解压安装包并修改目录
    1
    2
    tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
    mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
  2. 进入mysql-proxy的目录，创建目录
    1
    2
    3
    4
    cd mysql-proxy
    #6、创建目录
    mkdir conf
    mkdir logs
  3. 添加环境变量
    1
    2
    3
    4
    5
    6
    #打开/etc/profile文件
    vi /etc/profile
    #在文件的最后面添加一下命令
    export PATH=$PATH:/root/mysql-proxy/bin
    #退出后执行命令让环境变量生效
    source /etc/profile
  4. 进入conf目录，创建文件并添加一下内容
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    cd conf/
    vi mysql-proxy.conf
    添加内容
    [mysql-proxy]
    user=root
    proxy-address=192.168.12.113:4040
    proxy-backend-addresses=192.168.12.111:3306
    proxy-read-only-backend-addresses=192.168.12.112:3306
    proxy-lua-script=/root/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
    log-file=/root/mysql-proxy/logs/mysql-proxy.log
    log-level=debug
    daemon=true

上面配置项只指定了node01既承担读也承担写，112只承担读。所以：
msb数据库node01和node02中，node03中没有。node01中psn表里面有数据1，node02中有数据1和2。
如果在navicat中，连接node03，会显示有msb。在msb中查询psn表，会查询到1。也就是说读数据的时候读进来了node01。
在node01中service mysqld stop停掉服务，再次在navicat中查询会卡死失败。这时候再次开启node01中mysqld服务，然后查询，会查询到1和2，也就是查的是从库node02。
如果插入数据再查询，会发现写的时候是往mode01写的，而读是读的node02。如果现在要读node01，需要把node02关闭服务，再重启重读。
麻烦，很少用。

使用amoeba实现mysql读写分离

amoeba（变形虫）项目，专注分布式数据库proxy开发。
主要解决：

  1. 降低数据切分带来的复杂多数据库结构
  2. 提供切分规则并降低切分数据库带来的影响
  3. 读写分离

为什么要用amoeba

目前实现mysql的主从读写分离，主要有以下几个方案：

  1. 通过mysql-proxy来实现，由于mysql-proxy的主从读写分离是通过lua脚本来实现，目前lua的脚步开发跟不上节奏，因此导致用于生产环境风险较大。
  2. 通过程序实现，复杂
  3. 自己开发接口实现，开发成本高
  4. 利用阿里巴巴开源项目Amoeba实现，负载均衡、读写分离、配置简单

安装

在node04中执行下面步骤：
一、安装jdk
1、https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html找到`jdk-8u181-linux-x64.rpm`，复杂到node04中

2、rpm -ivh jdk-8u181-linux-x64.rpm进行安装

如果想删除rpm -Uvh jdk-8u181-linux-x64.rpm

3、配置环境变量

1
vi /etc/profile

按G跳到最后一行，再按o添加内容：
export JAVA_HOME=/usr/java/jdk1.8.0_181-amd64
export PATH=$PATH:$JAVA_HOME/bin

退出source /etc/profile

4、查看
java -version

二、使用amoeba
1、https://sourceforge.net/projects/amoeba/下载复制amoeba-mysql-3.0.5-RC-distribution.zip
2、下载解压包yum install zip unzip，使用unzip解压上面的安装包
3、跳进目录amoeba-mysql-3.0.5-RC里面的conf中
4、修改里面的amoeba.xml和dbServers.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
vi dbServers.xml
修改：
输入dG，把里面东西全部删除，然后按a，把下面东西复制过来
<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

		<!-- 
			Each dbServer needs to be configured into a Pool,
			If you need to configure multiple dbServer with load balancing that can be simplified by the following configu
ration:			 add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with n
ame factoryConfig			 such as 'multiPool' dbServer   
		-->
		
	<dbServer name="abstractServer" abstractive="true">
		<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
			<property name="connectionManager">${defaultManager}</property>
			<property name="sendBufferSize">64</property>
			<property name="receiveBufferSize">128</property>
				
			<!-- mysql port -->
			<property name="port">3306</property>
			
			<!-- mysql schema -->
			<property name="schema">msb</property>
			
			<!-- mysql user -->
			<property name="user">root</property>
			
			<property name="password">140140</property>
		</factoryConfig>

		<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
			<property name="maxActive">500</property>
			<property name="maxIdle">500</property>
			<property name="minIdle">1</property>
			<property name="minEvictableIdleTimeMillis">600000</property>
			<property name="timeBetweenEvictionRunsMillis">600000</property>
			<property name="testOnBorrow">true</property>
			<property name="testOnReturn">true</property>
			<property name="testWhileIdle">true</property>
		</poolConfig>
	</dbServer>

	<dbServer name="writedb"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.12.111</property>
		</factoryConfig>
	</dbServer>
	
	<dbServer name="slave"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.12.112</property>
		</factoryConfig>
	</dbServer>
	<dbServer name="myslave" virtual="true">
		<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
			<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
			<property name="loadbalance">1</property>
			
			<!-- Separated by commas,such as: server1,server2,server1 -->
			<property name="poolNames">slave</property>
		</poolConfig>
	</dbServer>
</amoeba:dbServers>

amoeba.xml同样删除后粘贴下面内容：
<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

	<proxy>
	
		<!-- service class must implements com.meidusa.amoeba.service.Service -->
		<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
			<!-- port -->
			<property name="port">8066</property>
			
			<!-- bind ipAddress -->
			<!-- 
			<property name="ipAddress">127.0.0.1</property>
			 -->
			
			<property name="connectionFactory">
				<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
					<property name="sendBufferSize">128</property>
					<property name="receiveBufferSize">64</property>
				</bean>
			</property>
			
			<property name="authenticateProvider">
				<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
					
					<property name="user">root</property>
					
					<property name="password">140140</property>
					
					<property name="filter">
						<bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
							<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
						</bean>
					</property>
				</bean>
			</property>
			
		</service>
		
		<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
			
			<!-- proxy server client process thread size -->
			<property name="executeThreadSize">128</property>
			
			<!-- per connection cache prepared statement size  -->
			<property name="statementCacheSize">500</property>
			
			<!-- default charset -->
			<property name="serverCharset">utf8</property>
			
			<!-- query timeout( default: 60 second , TimeUnit:second) -->
			<property name="queryTimeout">60</property>
		</runtime>
		
	</proxy>
	
	<!-- 
		Each ConnectionManager will start as thread
		manager responsible for the Connection IO read , Death Detection
	-->
	<connectionManagerList>
		<connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">
			<property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>
		</connectionManager>
	</connectionManagerList>
	
		<!-- default using file loader -->
	<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
		<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
	</dbServerLoader>
	
	<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
		<property name="ruleLoader">
			<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
				<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
				<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
			</bean>
		</property>
		<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
		<property name="LRUMapSize">1500</property>
		<property name="defaultPool">writedb</property>
		
		<property name="writePool">writedb</property>
		<property name="readPool">myslave</property>
		<property name="needParse">true</property>
	</queryRouter>
</amoeba:configuration>

5、cd ../bin

1
./launcher

可能会报错

1
2
3
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

因为栈空间太小。解决：
跳回amoeba-mysql-3.0.5-RC中，修改jvm.properties

1
2
3
cd ..
vi jvm.properties
里面-Xss改为-Xss256k

再到bin目录下执行./launcher，成功。

6、配置好后，可以进行连接。转到node03中：
mysql -uroot -p140140 -h192.168.12.114 -P8066

读数据

1
2
use msb
select * from psn;

打印结果为1，2，4。即查看的是node02
插数据

1
2
insert into psn values(5,'5');
select * from psn;

打印结果为1，2，4，5。即查看的是node02

现在停掉node01服务（service mysqld stop），再到node03中插入6.报错：
ERROR 1044 (42000): Amoeba could not connect to MySQL server[192.168.12.111:3306],Connection refused
重启node01服务，再插入就会成功。

停掉node02，node03查询，失败。再重启才可以查询到1，2，4，5，6

说明：node01是写库，node02是读库。

Mycat

在这里插入图片描述
对于DBA来说，Mycat就是Mysql Server，而Mycat后面连接的Mysql Server，就好像是Mysql的存储引擎。因此Mycat本身不存储数据，数据是在后端的Mysql上存储的。简单来说，Mycat在一定程度上让mysql拥有了跟Oracle pk的能力。

对于软件工程师来说，Mycat近似等于Mysql的数据库服务器，可用连接mysql的方式去连接mycat（默认端口号是8066而不是mysql的3306）