Amoeba中间件结合mysql结合springboot做分布式数据库小测试（2021-6-10)
【摘要】
这次必须的得好好记录一下了，这么多坑，不想再跳了，希望能够帮到需要的你，一步一步来，客官且听我娓娓道来。
公众号：知识浅谈 同步更新相关博文
目录
环境说明安装环境修改mysql和amoe...
这次必须的得好好记录一下了，这么多坑，不想再跳了，希望能够帮到需要的你，一步一步来，客官且听我娓娓道来。
公众号：知识浅谈 同步更新相关博文
目录
- 环境说明
- 安装环境
- 修改mysql和amoeba的配置文件涉及水平分片和垂直分片
- 搭建springboot项目
- 测试
- 环境说明
Servers
Amoeba Server (centos8): 192.168.43.128
Mysql 1 Server (centos8): 192.168.43.33
Mysql 2 Server (centos8): 192.168.43.132
Clients
Mysql GUI Tools (Windows): 192.168.43.148
Java Programs (IDEA): 192.168.43.148
mysql 5.7版本
mysql-connect-java 5.1.32版本
springboot 2.3.4.REALEASE版本
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
安装环境
- java环境
Amoeba，mysql 1 ，mysql 2 上均需要配置相关环境 - mysql环境
mysql 1 ，mysql 2 上均需要配置相关环境
关于java和mysql的配置看下方的图片中高亮的部分
- amoeba环境
在Amoeba上安装相应的安装包我用的是amoeba-mysql-3.0.4-BETA-distribution.zip
下载地址- dbServer.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 设置Amoeba要连接的mysql数据库的端口，默认是3306--> <property name="port">3306</property> <!-- mysql schema设置缺省的数据库，当连接amoeba时，操作表必须显式的指定数据库名，即采用dbname.tablename的方式，不支持 use dbname指定缺省库，因为操作会调度到各个后端dbserver。注意检查你的数据库里面一定要有这个数据库 --> <property name="schema">test</property> <!-- mysql user设置amoeba连接后端数据库服务器的账号和密码，因此需要在所有后端数据库上创建该用户，并授权amoeba服务器可连接 --> <property name="user">root</property> <!-- 设置amoeba连接后端数据库服务器的账号的密码，注意这里原来是注释的状态，注意把注释去掉，否则无效 --> <property name="password">chaiyinlei</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> <!-- server1名称可以根据自己需求改变--> <dbServer name="server1" parent="abstractServer"> <factoryConfig> <!-- mysql ip 对应第一台mysql所在的ip --> <property name="ipAddress">192.168.43.33</property> </factoryConfig> </dbServer> <!-- server2名称可以根据自己需求改变--> <dbServer name="server2" parent="abstractServer"> <factoryConfig> <!-- mysql ip 对应第二台mysql所在的ip --> <property name="ipAddress">192.168.43.132</property> </factoryConfig> </dbServer> <!--写你的连接池，myslave池名称可以改，这个池提供客户端读取数据 --> <dbServer name="multiPool" 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 --> <!-- 指定读取数据时从从服务器上读取。也可以指定读取数据时从主服务器和从服务器上读取，则改成server1,server2--> <property name="poolNames">server1</property> </poolConfig> </dbServer> </amoeba:dbServers>- 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
- 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监听的端口，默认是8066 --> <property name="port">8066</property> <!-- bind ipAddress 配置监听的接口 注释掉就是默认的0.0.0.0就是对所有的进行监听--> <!-- <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"> <!-- 提供客户端连接amoeba时需要使用这里设定的账号 (这里的账号密码和amoeba连接后端数据库服务器的密码无关--> <property name="user">root</property> <!-- 客户端连接amoeba时的账户的密码--> <property name="password">chaiyinlei</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">server1</property> <!-- 设置amoeba默认连接的池，这里设置为server1--> <!-- <property name="writePool">server1</property> <!-- 这两个选项默认是注销掉的，需要取消注释，这里用来指定前面定义好的俩个读写池。写入数据到刚才dbServer.xml中配置的server1池 --> <property name="readPool">server1</property> <!-- 写入数据到刚才dbServer.xml中配置的server1池 --> --> <property name="needParse">true</property> </queryRouter> </amoeba:configuration>- 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
- rule.xml
<?xml version="1.0" encoding="gbk"?> <!DOCTYPE amoeba:rule SYSTEM "rule.dtd"> <amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/"> <!-- 水平分片设置 --> <tableRule name="t_user" schema="test" defaultPools="server1,server2"> <rule name="rule1"> <parameters>user_id</parameters> <expression><![CDATA[ user_id % 2 ==0 ]]></expression> <defaultPools>server1</defaultPools> <readPools>server1</readPools> <writePools>server1</writePools> </rule> <rule name="rule2"> <parameters>user_id</parameters> <expression><![CDATA[ user_id % 2 ==1 ]]></expression> <defaultPools>server2</defaultPools> <writePools>server2</writePools> <readPools>server2</readPools> </rule> <!-- <rule name="rule4"> <parameters>ID</parameters> <expression><![CDATA[ ID > 20000000 ]]></expression> <defaultPools>server3</defaultPools> </rule> --> <!-- <rule name="rule3"> <parameters>ID,CREATE_TIME</parameters> <expression><![CDATA[ID>4 or CREATE_TIME between to_date('2008-11-12 00:00:00.0000') and to_date('2008-12-10 00:00:00.0000') ]]></expression> <defaultPools>server3</defaultPools> </rule> --> </tableRule> <!-- vertical split--> <!-- 垂直分片设置 --> <tableRule name="t_goods" schema="test" defaultPools="server2"/> </amoeba:rule>- 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
- 修改amoeba脚本，否则会由于stack size太小，导致JVM启动失败，需要做如下修改：
# 如果是3.X 之前需要在/usr/local/amoeba/bin/amoeba里进行修改 vim /usr/local/amoeba/bin/amoeba # 如果是3.X 需要在 /usr/local/amoeba/bin/ vim /usr/local/amoeba/jvm.properties 修改配置文件中以下对应的数字 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"- 1
- 2
- 3
- 4
- 5
- 6
- dbServer.xml文档的配置
- java环境
-
启动mysql和amoeba
安装sql的两个主机上运行如下
service mysqld start
安装amoba的两个主机上运行如下
cd /usr/local/amoeba/bin/
ls
./launcher -
搭建springboot项目
注意java的mysql驱动版本要合适否则会报错，看上边环境配置中我的项目版本
项目下载地址： https://englishcode.lanzoui.com/iDFK5q07rqb -
测试
文章来源: englishcode.blog.csdn.net，作者：知识浅谈，版权归原作者所有，如需转载，请联系作者。
原文链接：englishcode.blog.csdn.net/article/details/117773102
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）