MySQL主从+amoeba实现读写分离

chen豆豆鸭

于 2020-02-04 22:17:11 发布

阅读量109

点赞数

分类专栏： shell脚本

本文链接：https://blog.csdn.net/weixin_46164213/article/details/104175659

版权

shell脚本专栏收录该内容

7 篇文章 1 订阅

订阅专栏

准备工作准备3台虚拟机
10.0.0.41 （MySQL主）
10.0.0.42 （MySQL从）
10.0.0.43 amoeba 服务器
用脚本实现MySQL主从就是有一个不好的缺点就是使用的服务器必须是干净的只能使用一次
提前做好免密登录

#!/bin/bash
mysql_start() {
        ss -ntlp|grep -w mysqld
        if [ $? -eq 0 ]; then
                echo "MySQL服务已经开启"
        else
                systemctl restart mariadb
                if [ $? -eq 0 ]; then
                        echo "成功开启MySQL服务器"
                else
                        exit
                fi
        fi
}
install_mariadb() {
        rpm -q mariadb && rpm -q mariadb-server
        if [ $? -ne 0 ]; then
                yum -y install mariadb mariadb-server
                if [ $? -eq 0 ]; then
                        echo "安装好mysql服务"
                else
                        echo "安装MySQL服务出错"
                        exit
                fi
        fi
}
install_mariadb
mysql_start
sed -i '10a server-id=1\nlog-bin=mysql-bin\n' /etc/my.cnf
systemctl restart mariadb
mysql -e "grant all on *.* to 'tom'@'10.0.0.%' identified by '123';"
mysql -e "flush privileges;"
log_file=`mysql -e "show master status;"|awk 'NR==2{print $1}'`
log_pos=`mysql -e "show master status;"|awk 'NR==2{print $2}'`
echo "$log_file"
echo "$log_pos"
read -p "请输入master的IP地址" master_ip
read -p "请输入slave的IP地址"  slave_ip
ssh root@$slave_ip > /dev/null <<eof
yum -y install mariadb mariadb-server
sed -i '10a server-id=2\nrelay-log=mysql-relay\n' /etc/my.cnf
systemctl stop mariadb 
systemctl start mariadb
mysql -e "stop slave;"
mysql -e "change master to master_user='tom',master_password='123',master_host='$master_ip',master_log_file='$log_file',master_log_pos=$log_pos;"
mysql -e "start slave;"
mysql -e "show slave status \G;"
eof

在amoeba(10.0.0.43)的操作提前做好免密登录
首先在MySQL主从的服务器上给ameoba服务器授权一个可以操作读写的用户

#!/bin/bash
read -p "请输入MySQL主的IP地址" master_ip
read -p "请输入MySQL从的IP地址" slave_ip
ssh root@$master_ip > /dev/null <<eof
mysql -e "grant all on *.* to 'amoeba'@'10.0.0.%' identified by '123';"
mysql -e "flush privileges;"
eof
ssh root@$slave_ip > /dev/null <<eof
mysql -e "grant all on *.* to 'amoeba'@'10.0.0.%' identified by '123';"
mysql -e "flush privileges;"
eof

在Amoeba的服务器上安装Amoeba
1.安装jdk 版本要求1.6 提前分发过去jdk的安装包这里是二进制包

#!/bin/bash
echo "给予权限"
cd /usr/local && chmod +x jdk-6u14-linux-x64.bin && ./jdk-6u14-linux-x64.bin
ls /usr/local/ | grep jdk1.6.0_14
if [ $? -eq 0 ]; then
        ls /usr/local/ | grep jdk1.6
        if [ $? -eq 0 ]; then
                cd /usr/local && mv jdk1.6.0_14 jdk1.6
                echo "修改成功"
        fi
fi
echo "修改环境变量"
sed -i '80a export export AMOEBA=/usr/local/amoeba\nexport JAVA_HOME=/usr/local/jdk1.6\nexport PATH=$PATH:$JAVA_HOME/bin:$AMOEBA/bin
' /etc/profile
echo "使增加的变量生效"
source /etc/profile
echo "版本"
java -version
echo "已经分发好ameoba的安装包"
ls /usr/local/ | grep amoeba
if [ $? -eq 0 ]; then
        echo "已存在"
        cd /usr/local/amoeba && tar -zxf amoeba-mysql-binary-2.1.0-RC5.tar.gz
else
        mkdir /usr/local/amoeba
        cd /usr/local/amoeba && tar -zxf amoeba-mysql-binary-2.1.0-RC5.tar.gz
fi

修改配置文件 dbServers.xml

cd /usr/local/amoeba/conf
vim dbServer.xml

……
    <!-- mysql port -->
    <property name="port">3306</property>
 
     <!-- mysql schema -->
    <property name="schema">test</property>
 
     <!-- mysql user -->
     <property name="user">amoeba</property>  //MySQL主从中给予权限的用户
 
     <!--  mysql password-->
     <property name="password">123456</property>  //MySQL主从中给予权限的用户的密码
     
            ……
    <dbServer name="server1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">10.0.0.41</property>  //MySQL主的IP地址
                </factoryConfig>
        </dbServer>
 
        <dbServer name="server2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">10.0.0.42</property>   //MySQL从的IP地址
                </factoryConfig>
        </dbServer>
        <dbServer name="slave" 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">server2</property>
                </poolConfig>
        </dbServer>

编辑amoeba.xml的配置文件

cd /usr/local/amoeba/conf
vim amoeba.xml

……
<property name="authenticator">
                                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
 
                                        <property name="user">java</property>
 
                                        <property name="password">123</property>
 
                                        <property name="filter">
                                                <bean class="com.meidusa.amoeba.server.IPAccessController">
                                                        <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                                                </bean>
                                        </property>
                                </bean>
                        </property>
……
<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>
 
                <property name="writePool">server1</property>
                <property name="readPool">slave</property>
                <property name="needParse">true</property>
        </queryRouter>

启动amoeba

cd /usr/local/amoeba/bin
./amoeba start

出现以下字样表示成功
在这里插入图片描述
把Amoeba的终端再开启一台

关闭防火墙 SELinux 安装数据库

systemctl stop firewalld
setenforce 0
yum -y install mariadb mariadb-server

登录amoeba测试读写分离效果
mysql -ujava -p123 -h10.0.0.43 -P8066
create database ceshi charset utf8;
可以在MySQL主从数据库中查看是否存在这个数据库
在从的MySQL数据库中停掉从的同步
stop slave;
在amoeba的服务器上再创建一个数据库
这时再分别去主从数据库上查看只有主的可以看到刚刚创建的数据库而从上看不到这样就实现了读写分离