mysql主从读写分离简介及搭建，仆人级教程，弄不成打我。

目录

前言：

一，mysql读写分离

1，基本原理

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。2，优点

3，引入amoeba

二，mysql读写分离搭建

1，环境：

2，安装jdk

3，测试jdk

4，安装amoeba

5，配置amoeba

6，启动

7，测试

---

前言：

继本专栏上一篇博文mysql主从备份之后的读写分离，实验过程中请先保证主从复制已成功完成。

一，mysql读写分离

1，基本原理

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操
作的变更同步到各个slave节点。
2，优点

进一步提高读写性能，增加冗余，提高可用性，主从只负责各自的读写，节约开销，slave可以单独设置一些参数来提升其读的性能

3，引入amoeba

mysql是通过amoeba（变形虫）实现读写分离的，amoeba对客户端透明，具有负载均衡，高可用性，读写分离，可并发操作。

二，mysql读写分离搭建

1，环境：

主从复制是两台主机，而读写分离需要新增加一台服务器作为主从服务器的代理服务器，也就是再加一台服务器，一共三台服务器,安装mysql

第三台代理服务器同样也需要安装mysql服务，请参照Mysql主从备份数据库服务器搭建

2，安装jdk

上传

解码

rpm -ivh jdk-8u181-linux-x64.rpm

配置环境变量

vi /etc/profile

在末尾添加如下

export JAVA_HOME=/usr/java/jdk1.8.0_181-amd64

export PATH=$PATH:$JAVA_HOME/bin

3，测试jdk

加载环境变量

source /etc/profile
java -version

如图所示成功

4，安装amoeba

上传

解压

下载解压工具

yum -y install unzip

unzip unzip amoeba-mysql-3.0.5-RC-distribution.zip

5，配置amoeba

修改配置文件

cd /root/amoeba-mysql-3.0.5-RC/conf/
vi dbServers.xml

<?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 configuration:

                         add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name 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">jpz</property>



                        <!-- mysql user -->

                        <property name="user">root</property>



                        <property name="password">000000</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="master"  parent="abstractServer">

                <factoryConfig>

                        <!-- mysql ip -->

                        <property name="ipAddress">192.168.100.140</property>

                </factoryConfig>

        </dbServer>



        <dbServer name="slave"  parent="abstractServer">

                <factoryConfig>

                        <!-- mysql ip -->

                        <property name="ipAddress">192.168.100.150</property>

                </factoryConfig>

        </dbServer>



        <dbServer name="amoeba" 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>

保存退出

vi 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">000000</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">master</property>





                <property name="writePool">master</property>

                <property name="readPool">slave</property>



                <property name="needParse">true</property>

        </queryRouter>

</amoeba:configuration>

保存退出 

cd ..

vi jvm.properties

JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss512k -XX:PermSize=16m -XX:MaxPermSize=96m"

6，启动

/root/amoeba-mysql-3.0.5-RC/bin/launcher

在mysql从服务器上开启3306端口

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

7，测试

-测试的sql --在安装amoeba的服务器上登录mysql

mysql -h192.168.150.30 -uroot -p123456 -P8066

--分别在master、slave、amoeba上登录mysql

use zq
select * from user;

--在amoeba上插入数据 insert into user values(2,2);

--在master和slave上分别查看表中的数据

use jpz

select * from user;

 

--将master上的mysql服务停止，继续插入数据会发现插入不成功，但是能够查询

master：

systemctl stop mysqld

amoeba：

insert into user values(3,3);

--将master上的msyql服务开启，停止slave上的mysql，发现插入成功，但是不能够查询

master:

systemctl start mysqld

slave:

systemctl stop mysqld

amoeba:

insert into user values(3,3);

select * from user;