How to use amoeba to separate reading and writing of mysql database

There are many read-write separation architectures about mysql. Baidu almost all uses mysql_proxy to implement it. Since the proxy is implemented based on the Lua script language, many netizens on the Internet said that the proxy is not efficient and unstable, and is not recommended for use in a production environment;
amoeba is a project developed by Alibaba that separates database reading and writing (read Write separation is only a small function of it). Since it is written based on java, the running environment needs to install jdk;

Related tutorials: mysql video tutorial

Preliminary preparation Work:
1. Two databases, one master and one slave, master-slave synchronization;
master: 172.22.10.237:3306; The main database is responsible for writing operations;
slave: 10.4.66.58:3306; Slave The library is responsible for reading operations;
amoeba: 172.22.10.237:8066; I installed amoeba on the server where the main library is located. Of course, you can also install it on the third server;
All server operating systems are centos7;
2. Configure and install jdk on the server where amoeba is located;
I installed jdk1.8;
The path is: JAVA_HOME=/usr/local/java/jdk1.8.0_131

Be sure to build and configure the above by yourself. The master and slave work normally. Add the jdk environment variable: /etc/profile;

There are many ways to install amoeba, so I won’t waste any time on the installation here. Download the installation package of amoeba-mysql-3.0.5-RC-distribution and unzip it directly to use;
Unzip directory: /usr/local/amoeba/

Obviously the configuration file is in conf, and the startup program is in bin;
As mentioned just now, amoeba has more functions than reading and writing separation, but if you only use the reading and writing separation function, you only need to configure these files: conf/dbServers.xml conf/amoeba.xml and bin/launcher;
conf/dbServers.xml:

`<property name="port">3306</property>　　
           #设置Amoeba要连接的mysql数据库的端口，默认是3306
    <property name="schema">testdb</property>　　
          #设置缺省的数据库，当连接amoeba时，操作表必须显式的指定数据库名，即采用dbname.tablename的方式，不支持 use dbname指定缺省库，因为操作会调度到各个后端dbserver
    <property name="user">test1</property>　　
             #设置amoeba连接后端数据库服务器的账号和密码，因此需要在所有后端数据库上创建该用户，并授权amoeba服务器可连接
    <property name="password">111111</property>

    <property name="maxActive">500</property>　　#最大连接数，默认500
    <property name="maxIdle">500</property>　　　　#最大空闲连接数
    <property name="minIdle">1</property>　　　　#最新空闲连接数

    <dbServer name="writedb"  parent="abstractServer">　　#设置一个后端可写的数据库，这里定义为writedb，这个名字可以任意命名，后面还会用到
        <property name="ipAddress">172.22.10.237</property> #设置后端可写dbserver的ip

    <dbServer name="slave01"  parent="abstractServer">　　#设置后端可读数据库
        <property name="ipAddress">10.4.66.58</property>

     <dbServer name="myslave" virtual="true">　　#设置定义一个虚拟的dbserver，实际上相当于一个dbserver组，这里将可读的数据库ip统一放到一个组中，将这个组的名字命名为myslave
            <property name="loadbalance">1</property>　　#选择调度算法，1表示复制均衡，2表示权重，3表示HA， 这里选择1
            <property name="poolNames">slave01</property>　　#myslave组成员`

conf/amoeba.xml:

       #启动脚本，需要配置jdk环境变量；
        #在注释后的第一行添加：
        JAVA_HOME=/usr/local/java/jdk1.8.0_131

launcher is the startup script, if If JAVA_HOME is not configured, even if you configure environment variables in /etc/profile, you may get an error: jdk environment variables are not configured;
There is also a configuration file: jvm.properties

  #占用内存配置文件
        # -Xss参数有最小值要求，必须大于228才能启动JVM
        #修改：
            JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

Experienced Everyone in operation and maintenance knows that anything related to jdk is basically related to memory tuning, and amoeba is no exception;

It can be started now:

After startup, you can see the 8066 port of the machine:

At this time, you only need to pass the 8066 port of the machine ip and Just use the account and password set in your configuration file to connect to the database. The data written will go to the master, and the data read will be read from the slave;
Test:
Close the master database and it can still be read. : Execute the select view command;
or
Close the slave database, you can still write: Execute the update and inster commands;

The above is the detailed content of How to use amoeba to separate reading and writing of mysql database. For more information, please follow other related articles on the PHP Chinese website!