amoeba搭建mysql8.0读写分离

krb___

于 2024-09-10 09:45:00 发布

阅读量208

点赞数 7

文章标签： adb

本文链接：https://blog.csdn.net/krb___/article/details/140930284

版权

一、搭建mysql8.0主从复制

这个可以通过脚本https://download.csdn.net/download/krb___/89615542或者博客搭建docker MySQL主从复制_docker mysql主从复制 empty set-CSDN博客这个是5.6的主从博客和8.0的数据库略有差异例如权限设定。

二、配置jdk1.8

这个可以通脚本https://download.csdn.net/download/krb___/89524089或者博客搭建Centos7 linux安装JDK1.8-CSDN博客

三、配置数据库账号和相应的权限

--1、给主从数据库，添加amoeba账号，数据库访问权限，以及外部访问权限
--添加amoeba账号 密码自定义这里是123456
CREATE USER 'amoeba'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
--设定账号访问数据库权限 这里用的是所有数据库都可操作%
GRANT REPLICATION SLAVE ON *.* TO 'amoeba'@'%';
--开启外部访问权限
alter user amoeba@'%' identified with mysql_native_password by '123456';
--刷新数据库
flush privileges;
--2、添加主从链接账号，数据库访问权限以及外部访问权限（我这里用的的是root账号）
--添加账号，因为是root账号所以下面语句可以不执行 当然也可以添加个其他账号
CREATE USER 'master_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
--设定账号访问数据库权限 这里用的是所有数据库都可操作%
GRANT REPLICATION SLAVE ON *.* TO 'master_slave'@'%';
--开启外部访问权限
alter user root@'%' identified with mysql_native_password by '123456';
--刷新数据库
flush privileges;

四、下载配置amoeba

#安装wget
yum install -y wget
#创建文件夹
mkdir -p /usr/local/amoeba/
#下载amoeba3.0.5
wget https://sourceforge.net/projects/amoeba/files/latest/download/amoeba-mysql-3.0.5-RC-distribution.zip
#解压缩
unzip amoeba-mysql-3.0.5-RC-distribution.zip

1、配置dbServers.xml配置文件

vim /usr/local/amoeba/amoeba-mysql-3.0.5-RC/conf/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 容器 设定的3307 3308 都不用这里统一用3306因为调用的是容器内部IP-->  
                        <property name="port">3306</property>
                           
                        <!-- mysql schema 调用的数据库名称 需要有text数据库不然报错-->
                        <property name="schema">text</property>
                        
                        <!-- mysql user 登录主从的用户名和密码 这个可以是root也可以也是其他 自己设定-->
                        <property name="user">root</property>
                        <!-- 密码 -->
                        <property name="password">123456</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>
        <!-- name自定义 master 下面是主数据库的配置 IP地址以及端口号 如果端口号不一致可以在下面单独配置 可以有读个主数据库配置 -->
        <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">172.18.0.6</property>
                        <property name="port">3306</property>
                </factoryConfig>
        </dbServer>
        <!-- name自定义 slave01 slave02 下面是从数据库的配置 IP地址以及端口号 如果端口号不一致可以在下面单独配置 可以有读个从数据库配置 -->
        <dbServer name="slave01"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">172.18.0.7</property>
                        <property name="port">3306</property>
                </factoryConfig>
        </dbServer>
        <dbServer name="slave02"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip 端口使用容器 内部端口 -->
                        <property name='ipAddress'>172.18.0.8</property>
                        <property name="port">3306</property>
                </factoryConfig>
        </dbServer>
        <!-- 主库调用的组 下面可以调用读个主库的陪，中间用逗号隔开 master1,master2等等 -->
        <dbServer name="masters" 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">master</property>
                </poolConfig>
        </dbServer>
        <!-- 从库调用的组 下面可以调用读个主库的陪，中间用逗号隔开 slave01,slave02等等 -->
        <dbServer name="slaves" 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">slave01,slave02</property>
                </poolConfig>
        </dbServer>
</amoeba:dbServers>

2、配置amoeba.xml配置文件

vim /usr/local/amoeba/amoeba-mysql-3.0.5-RC/conf/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 外部用amoeba链接的端口号 -->
                        <property name="port">8066</property>
                        
                        <!-- bind amoeba的ipAddress -->
                        <!-- -->
                        <property name="ipAddress">192.168.60.61</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">
                                        <!-- amoeba 链接数据库的用户名 主从库都需要添加amoeba账号并且设定数据库访问权限以及外部访问权限 -->
                                        <property name="user">amoeba</property>
                                        <!-- amoeba 数据库的链接密码 123456 -->
                                        <property name="password">123456</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>
                <!-- 主要读写的数据库 默认调用的数据库 名字需要和 dbServers.xml 配置文件赋予的名称一致 （write 和 read一样）-->
                <property name="defaultPool">master</property>
                <!-- write 写  read 读 -->
                <property name="writePool">master</property>
                <property name="readPool">slaves</property>
                <property name="needParse">true</property>
        </queryRouter>
</amoeba:configuration>

3、配置启动文件launcher

vim /usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/launcher
##############launcher 启动文件顶部添加 否则启动报错#######################
#jdk1.8的目录
JAVA_HOME=/usr/local/java/jdk1.8.0_202
#启动配置大小
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
#####################################################

五、启动链接测试amoeba

#切换到启动目录
cd /usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin
#启动
./launcher


#链接
mysql -h 192.168.60.61 -P 8066 -u amoeba -p
#密码上面设置是 123456

#测试 在slave01和slave02添加不同的数据 用查询语句查询即可测试是否成功
#slave01
insert into text values(1,'slave01');
#slave02
insert into text values(2,'slave02');
#amoeba     查询看一下是否一个跳转一次
select * from text

#可以通过查询amoeba的report.log日志来确定是否执行成功
tail -n 50 report.log