mysql amoeba 事务_CentOS 7.4下MySQL+Amoeba做主从同步读写分离操作

谁践踏了优雅 2022-10-25 00:56 186阅读 0赞

三台mysql数据库：主数据库服务器：192.168.80.100

从数据库服务器1：192.168.80.120

从数据库服务器2：192.168.80.180

一台amoeba调度器：192.168.80.190

一台mariadb客户端： 192.168.80.170

1、配置MySQL主从环境准备

配置NTP服务器-----时间同步

\[root@NGINX ~\]\# yum install -y ntp //安装时间同步程序

\[root@NGINX ~\]\# service ntpd start

\[root@NGINX ~\]\# ntpdate -u cn.pool.ntp.org /.同步网络时间

![97380ee52d2ed4118afc0985682e2bf4.png][]

\[root@NGINX ~\]\# vi /etc/ntp.conf //配置时间同步

在合适位置新增以下三行：

restrict 192.168.80.0 mask 255.255.255.0 nomodify

server 127.127.1.0

fudge 127.127.1.0 stratum 8

![7c9b4f6cbf3f2ff3fc4db6eed43c00d2.png][]

在从服务器上：

\[root@localhost ~\]\# yum install -y ntp

\[root@localhost ~\]\# ntpdate 192.168.80.100

![90249451692da6fc2de6e0a2d61e3df9.png][]

\[root@localhost ~\]\# echo '/30\* /usr/sbin/ntpdate 192.168.80.100' >> /var/spool/cron/root //设置计划任务，每隔30分钟同步一次

\[root@localhost ~\]\# crontab -l

![579d14c6de244a9ff99eebc4b093d14a.png][]

2、配置mysql服务器主从复制

在主mysql服务器上：192.168.80.100

\[root@NGINX ~\]\# vi /etc/my.cnf

添加如下图内容：

![1a90955f50f7144af061f31480ccea1e.png][]

\[root@NGINX ~\]\# service mysqld restart

\[root@NGINX ~\]\# mysql -u root -p //进入数据库

![d73edbb78a7988d538513dc197f271b9.png][]

mysql> GRANT REPLICATION SLAVE ON . TO 'myslave'@'192.168.80.%' IDENTIFIED BY '123456';

//为所有从服务器授权所有数据库

![1afa07addd81fae278025408650a93aa.png][]

mysql> FLUSH PRIVILEGES; //刷新权限

![6070386f8af2ca09fdd12d2be57642d8.png][]

mysql> show master status \\G

![c68b515ab55737cd6a37bcbf9397f012.png][]

注意position 的值和file

在从服务器1上设置：192.168.80.120

\[root@localhost ~\]\# vi /etc/my.cnf

修改并新增以下内容：

server-id = 22

relay\_log=relay-log-bin

relay\_log\_index=slave-relay-bin.index

![59e2fee5c9169157173bb0c51e05f177.png][]

\[root@localhost ~\]\# systemctl restart mysqld

\[root@localhost ~\]\# mysql -u root -p

mysql> change master to master\_host='192.168.80.100',master\_user='myslave',master\_password='123456',master\_log\_file='master\_bin.000001',master\_log\_pos=802;

![ce2960258bc0377460c408f434938682.png][]

mysql> start slave;

![4c47cbbf9008ea1755a9d69d53096b9c.png][]

mysql> show slave status \\G

![60d0e83f9ecc4c22b93a4610c78f6208.png][]

在从服务器2上设置：

\[root@test ~\]\# vi /etc/my.cnf

修改并新增以下内容：

server-id = 33

relay\_log=relay-log-bin

relay\_log\_index=slave-relay-bin.index

![76f314c2959fc18abafb1377313c4e17.png][]

\[root@test ~\]\# service mysqld restart

\[root@test ~\]\# mysql -u root -p

mysql> change master to master\_host='192.168.80.100',master\_user='myslave',master\_password='123456',master\_log\_file='master\_bin.000001',master\_log\_pos=802;

![ce59ab1667edb6eb7d4e15ac7a946394.png][]

mysql> start slave;

![658e71de6d5b119be0eb3e36ab783484.png][]

mysql> show slave status \\G

![067e2fec87f6076dca101b2d24af2660.png][]

测试，在主服务器上创建一个空库，看一下从服务器上有木有

在主服务上

mysql> create database ab;

![d0edff9f90cb897a2ec6bcd53275eb92.png][]

在从服务器1上：

mysql> show databases;

![c275d9670e2aa3a112e005bf7d80389f.png][]

在服务器2上：

mysql> show databases;

![fa40912ecfded1bd640710b42c81736c.png][]

如上两幅图所示，表示主从同步成功了。

3、搭建amoeba前端服务器：192.168.80.190

\[root@amoeba ~\]\# yum install -y ntpdate

\[root@amoeba ~\]\# ntpdate 192.168.80.100

![8a7a1f9ede7b9971e71dddb6a4bc2a23.png][]

\[root@amoeba ~\]\# echo '/30\* /usr/sbin/ntpdate 192.168.80.100' >> /var/spool/cron/root

\[root@amoeba ~\]\# crontab -l

![ce41893d836ca2d304467181e552f8b5.png][]

\[root@amoeba ~\]\# tar xf jdk-8u144-linux-x64.tar.gz -C /opt/

\[root@amoeba ~\]\# cd /opt/

\[root@amoeba opt\]\# cp -rv jdk1.8.0\_144/ /usr/local/java

\[root@amoeba opt\]\# vi /etc/profile //搭建Java环境

export JAVA\_HOME=/usr/local/java

export JRE\_HOME=/usr/local/java/jre

export PATH=$PATH:/usr/local/java/bin

export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib

![54d1e27d297d09014df8b7c21c3024b3.png][]

\[root@amoeba opt\]\# source /etc/profile

\[root@amoeba opt\]\# java -version

![539b4e33de346e14df706b6fe172d58b.png][]

如上图所示，搭建好了Java环境

\[root@amoeba ~\]\# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /opt/

\[root@amoeba ~\]\# cd /opt/

\[root@amoeba opt\]\# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba

\[root@amoeba opt\]\# chmod -R 755 /usr/local/amoeba/

\[root@amoeba opt\]\# vi /usr/local/amoeba/jvm.properties

新增：

JVM\_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

![edca4498c2c30fd21c764365eac66192.png][]

\[root@amoeba opt\]\# vi /etc/init.d/amoeba //编辑系统服务脚本

\#!/bin/bash

\#chkconfig: 35 62 62

export JAVA\_HOME=/usr/local/java

export PATH=$JAVA\_HOME/bin:$JAVA\_HOME/jre/bin:$PATH

NAME=Amoeba

AMOEBA\_BIN=/usr/local/amoeba/bin/launcher&

SHUTDOWN\_BIN=/usr/local/amoeba/bin/shutdown

PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid

SCRIPTNAME=/etc/init.d/amoeba

case "$1" in

start)

echo -n "Starting $NAME... "

$AMOEBA\_BIN

echo " done"

;;

stop)

echo -n "Stoping $NAME... "

$SHUTDOWN\_BIN

echo " done"

;;

restart)

$SHUTDOWN\_BIN

sleep 1

$AMOEBA\_BIN

;;

\*)

echo "Usage: $SCRIPTNAME \{start|stop|restart\}"

exit 1

;;

Esac

![dbc02a3d579fc18686ef23a3469db0c1.png][]

![ad97bf184ad86dd3b55767eec45d4b64.png][]

\[root@amoeba opt\]\# chmod +x /etc/init.d/amoeba //给予执行权限

\[root@amoeba opt\]\# chkconfig --add amoeba //添加到系统管理中

\[root@amoeba opt\]\# service amoeba start //开启amoeba服务

![4828535b2545c827273ae88bc4ff53e3.png][]

4、配置读写分离

\----在三台mysql数据库中为amoeba授权-------

mysql> GRANT ALL ON . TO test@'192.168.80.%' IDENTIFIED BY '123123';

![24a351603978c28b1fad2c8ef12f78b7.png][]

mysql> FLUSH PRIVILEGES;

![cecec2ea887ef0fe88a4531104587f72.png][]

\--------在amoeba前端服务器上----

\[root@amoeba amoeba\]\# cd /usr/local/amoeba

\[root@amoeba amoeba\]\# vi conf/amoeba.xml

\-----设置客户端连接amoeba前端服务器时使用的用户名和密码----28行

amoeba

123456

![0263424198c5f57f760fac27e529ca38.png][]

\----同时把默认的server1改成master,把默认的servers改成 slaves-----83行

master

slaves

![f510b534cd9f4f746020bff55e2ec46d.png][]

\[root@amoeba conf\]\# vi dbServers.xml

test

123123 //指定数据库的登录用户名和密码

![83129fe431eb2021b9627f13ad11e527.png][]

修改以下内容

192.168.80.100

192.168.80.120

192.168.80.180/property>

![df9d09feb621bb6ef0ac77edaa5f0fe0.png][]

![2f5621d344bbef4c89e60085e1327c0d.png][]

slave1,slave2

![653aa307cfa0b01320c9d26c8d8d7fdd.png][]

\[root@amoeba amoeba\]\# service amoeba restart

![982d662f4d0d9e98b9da1f7e90d86d86.png][]

\[root@amoeba amoeba\]\# netstat -anpt | grep java

![12d4a692658e9e3911136cf4b88b8d7e.png][]

5、搭建mariadb客户端：192.168.80.170

\[root@MYSQL ~\]\# yum install -y mysql

\[root@MYSQL ~\]\# mysql -u amoeba -p123456 -h 192.168.80.190 -P8066

![43262ff2bec3ff0c6aa62590baee9cdb.png][]

如上图，已经成功连接到数据库了

在客户端上使用命令在test创建一个表：

MySQL \[(none)\]> use test;

MySQL \[test\]> create table zhang (id int(10),name varchar(10),address varchar(20));

MySQL \[test\]> show tables;

![d33b9fbf884d2980bfacc54896fc6401.png][]

在从服务器上

![d1cefaf4a45cdf9a84865d6f3ac087db.png][]

在其中一台从服务器上创建一个空库

![e6658b174d25aee64ace66ae4c8c94fc.png][]

在主服务器上查看所有库

![b4bd4160836ee9dd119cff7dced23292.png][]

没有新建的那个库

在客户端上查看：

![d5ac2a0d493cb8d3af7d0ac9b393f5fc.png][]

由此所见，从服务器上建库，客户端可以读取，说明读写分离成功

![0b1331709591d260c1c78e86d0c51c18.png][]

[97380ee52d2ed4118afc0985682e2bf4.png]: /images/20221024/a3cb5d561e714b399e675614ed631bab.png
[7c9b4f6cbf3f2ff3fc4db6eed43c00d2.png]: /images/20221024/3a6f6a020c4c42da9fa982b487c5d6a6.png
[90249451692da6fc2de6e0a2d61e3df9.png]: /images/20221024/17bd1365e5114f0994af2be1942a277f.png
[579d14c6de244a9ff99eebc4b093d14a.png]: /images/20221024/14a5e44ba1164b5ba260656ed166303a.png
[1a90955f50f7144af061f31480ccea1e.png]: /images/20221024/848f0876e99c4645bbce010e174ff7bb.png
[d73edbb78a7988d538513dc197f271b9.png]: /images/20221024/f015db1fb2d5453594399a242c20bb41.png
[1afa07addd81fae278025408650a93aa.png]: /images/20221024/2c7534c6269f4e01861bae0c9cb68dd6.png
[6070386f8af2ca09fdd12d2be57642d8.png]: /images/20221024/da34a36f2e1a4f08a6345d32b10aae49.png
[c68b515ab55737cd6a37bcbf9397f012.png]: /images/20221024/562934129b694f3cb80cc0fb3f34997b.png
[59e2fee5c9169157173bb0c51e05f177.png]: /images/20221024/2f09460887464c75bf9da22099280322.png
[ce2960258bc0377460c408f434938682.png]: /images/20221024/fd9b6e8774c04c178eed71ae191ddd05.png
[4c47cbbf9008ea1755a9d69d53096b9c.png]: /images/20221024/67b12cd31611472491433043c81ea5b1.png
[60d0e83f9ecc4c22b93a4610c78f6208.png]: /images/20221024/31424caee9944a6488579ce41b8303c0.png
[76f314c2959fc18abafb1377313c4e17.png]: /images/20221024/b2cf28352ad5491fae22d7ca2c0a567b.png
[ce59ab1667edb6eb7d4e15ac7a946394.png]: /images/20221024/d572e5cc849d4546a8cd916bfd5789ee.png
[658e71de6d5b119be0eb3e36ab783484.png]: /images/20221024/2190d60cf97e49ed9d6228d3b877a654.png
[067e2fec87f6076dca101b2d24af2660.png]: /images/20221024/b5a975c77fd14c6e8f45ddb870746383.png
[d0edff9f90cb897a2ec6bcd53275eb92.png]: /images/20221024/f95b405cdfe141c5a019544bd168e71f.png
[c275d9670e2aa3a112e005bf7d80389f.png]: /images/20221024/f9bcafc5bfe34f44984b429e575fcd6d.png
[fa40912ecfded1bd640710b42c81736c.png]: /images/20221024/2ff207dc13f842c3b3bfa511eabc9bdd.png
[8a7a1f9ede7b9971e71dddb6a4bc2a23.png]: /images/20221024/2c514e1b5c20480d84ca3958dfc629bd.png
[ce41893d836ca2d304467181e552f8b5.png]: /images/20221024/9b8ff6e8a9854b30be62274f593db6da.png
[54d1e27d297d09014df8b7c21c3024b3.png]: /images/20221024/336ab9b0132542b2bc5501510a4bff00.png
[539b4e33de346e14df706b6fe172d58b.png]: /images/20221024/9c2f178c3702438a908edeff3ebf95a4.png
[edca4498c2c30fd21c764365eac66192.png]: /images/20221024/b905006df70f4f9b993f1f93fe443cbc.png
[dbc02a3d579fc18686ef23a3469db0c1.png]: /images/20221024/f7917146b09248129c555aab98fccf48.png
[ad97bf184ad86dd3b55767eec45d4b64.png]: /images/20221024/6b3b83bd33484868a151550fec4364cf.png
[4828535b2545c827273ae88bc4ff53e3.png]: /images/20221024/3e06aff07d0f47a59cb77a52802de77a.png
[24a351603978c28b1fad2c8ef12f78b7.png]: /images/20221024/a0a26e969d73406099f534ffb04f4146.png
[cecec2ea887ef0fe88a4531104587f72.png]: /images/20221024/6e4fe84e9fc64d27a63601aa0933c4a2.png
[0263424198c5f57f760fac27e529ca38.png]: /images/20221024/1e35cf86afd849b1b5b2ed77abcdea71.png
[f510b534cd9f4f746020bff55e2ec46d.png]: /images/20221024/c570054d3da642609d1d39d922f691ad.png
[83129fe431eb2021b9627f13ad11e527.png]: /images/20221024/2862857f5a5c432e9e58a3e6bd37e322.png
[df9d09feb621bb6ef0ac77edaa5f0fe0.png]: /images/20221024/5f5d830e6cf34c2b810f8fc0c868cf8d.png
[2f5621d344bbef4c89e60085e1327c0d.png]: /images/20221024/90bc6b8db9d14f8ba4054c57a48d658d.png
[653aa307cfa0b01320c9d26c8d8d7fdd.png]: /images/20221024/e2a9a75afcf243d393c8379d7d5dd7cc.png
[982d662f4d0d9e98b9da1f7e90d86d86.png]: /images/20221024/a8b139886c034aeeaec9acf4a7c6c3f6.png
[12d4a692658e9e3911136cf4b88b8d7e.png]: /images/20221024/14216fd937c44d16865442361e3e03b5.png
[43262ff2bec3ff0c6aa62590baee9cdb.png]: /images/20221024/7d593ec8716747fc8f0e5f2abacdbaba.png
[d33b9fbf884d2980bfacc54896fc6401.png]: /images/20221024/729751a75d2d4983995bfd6bfa9fc7fe.png
[d1cefaf4a45cdf9a84865d6f3ac087db.png]: /images/20221024/7b2c3b46eadd40aa82d948fb3d8ba85b.png
[e6658b174d25aee64ace66ae4c8c94fc.png]: /images/20221024/1e343eec4a1b43098fc76f40ec71cebe.png
[b4bd4160836ee9dd119cff7dced23292.png]: /images/20221024/21cd8f9a44cd4ff9ad83dcd899c66c6a.png
[d5ac2a0d493cb8d3af7d0ac9b393f5fc.png]: /images/20221024/27b931178b71448dbe2ec1f5ffdb85e3.png
[0b1331709591d260c1c78e86d0c51c18.png]: /images/20221024/24aff6178263407188c5c827fd53e52b.png