mysql主从复制及读写分离脚本-亲测可用

集群架构：

 集群一主两从一代理（amoeba）。所谓的主从复制是指mysql从服务器从主服务器复制数据，保持同步。所谓读写分离即主负责写，从负责读，此架构大大提升了数据库的性能。

主从节点部署脚本：

#!/bin/bash
#function:mysql主从复制与读写分离
#author:tommypeng  20220726
#####root判断#####
if
  [  "$USER"  != "root"   ]
then
   echo "错误：非root用户，权限不足！"
  exit  0
fi
###############防火墙及SElinux############
systemctl stop firewalld && systemctl disable firewalld  && echo "防火墙已经关闭"
sed -i 's/SELINUX=.*/SELINUX=disabled/g'  /etc/selinux/config  && echo "关闭selinux"
###########清理旧版本###########
rpm -qa | grep mariadb >  /root/888.txt
rpm -qa | grep mysql  >>  /root/888.txt
PLIST=$(cat  /root/888.txt)
for  PKGNAME   in    $PLIST
do
      rpm -e --nodeps   $PKGNAME   &&  echo  "老旧包已经清理完毕" 
done
rm -rf  /root/888.txt  &&  echo "临时文件已删除"
sleep 8
##############网络测试##############
ping   -c  3  www.baidu.com
if
  [ $? = 0 ]
  then
    echo "外网通讯良好！"
  else
    echo  "丫的你在逗我吗？网都没有安装个毛线！"
   exit  1
fi
###########安装##############
yum -y install mariadb mariadb-server #安装mariadb
#echo "服务器即将重启，重启后请重新运行脚本"
#sleep 10
#reboot
############配置文件#############
function  master()
{
if [[ "$1" -eq "1" ]];then
cat >> /root/mysql.txt << EOF
server-id=1
log-bin=mysql-bin
binlog_format=MIXED
log-slave-updates=true
EOF
sed  -i '/\[mysqld\]/r   /root/mysql.txt'    /etc/my.cnf
#############删除临时文件##############
rm -rf  mysql.txt    &&  echo "临时文件已经删除"
##########启动数据库################
systemctl start mariadb
mysql  -e "grant all on *.* to tom@'%' identified by '123';"
mysql  -e "grant replication slave on *.* to tom@'%' identified by '123';"  ####在主服务器建立账号tom并授权
mysql  -e   "grant all on *.* to test@'%' identified by '123';"
mysql  -e "flush privileges;" 
##mysql -u root  -e "create database wg character set utf8 collate utf8_bin;"  ##创建测试库 
echo  "请记住master_log_file及Position的值，部署slave时需要用到"  &&  mysql -u root  -e "show master status;" 
exit 0
fi
}
function  slave()
{
if [[ "$1" -eq "2" ]];then
read -p  "输入从服务器的服务ID（不能为1，同时不能与其他从服务器相同） "  ppo
cat >> /root/mysql.txt << EOF
server-id=$ppo
relay-log=mysql-relay
relay-log-index=slave-relay-bin.index
EOF
sed  -i '/\[mysqld\]/r   /root/mysql.txt'    /etc/my.cnf
#############删除临时文件##############
rm -rf  mysql.txt    &&  echo "临时文件已经删除"
##########启动数据库################
systemctl start mariadb 
systemctl restart mariadb 
mysql  -e   "grant all on *.* to test@'%' identified by '123';"  
mysql  -e "flush privileges;" 
mysql -e "stop slave;" 
read -p  "请输入主服务器IP地址:  "   ppi
read -p  "请输入主服务器Position编号:  "   pos
read -p  "请输入主服务器master_log_file编号:  "   mlf
mysql  -e  "CHANGE master to master_host='"$ppi"',master_user='tom',master_password='123',master_log_file='"$mlf"',master_log_pos=$pos;"
##echo "change master to master_host='"$ppi"',master_user='tom',master_password='123',master_log_file='"$mlf"',master_log_pos=$pos;"  |mysql
mysql   -e "start slave;" 
exit 1
fi
}
		  
PS3="选择改mysql服务器的角色，1为master或者2为slave:  "
select i in  master   slave   exit
do

case $i in
    master)
    master 1
    ;;
    slave)
    slave 2
    ;;
    exit)
    echo "The system exit"
    exit
esac

done

 
 

脚本运行中选择1部署为主，选择2部署为从，选择3退出。脚本运行过程中请根据脚本提示输入相关值。

amoeba部署脚本：

运行脚本前请上传安装包到/root/下

#!/bin/bash
#function:mysql代理（阿米巴变形虫）
#author:tommypeng  20220727
#####root判断#####
if
  [  "$USER"  != "root"   ]
then
   echo "错误：非root用户，权限不足！"
  exit  0
fi
#####安装文件判断######
MOUNT_File="/root/amoeba-mysql-binary-2.2.0.tar.gz"
if
   [ ! -e  $MOUNT_File ]
   then
   echo "安装文件不存在，请上传安装文件到/root/,上传完成再重新运行该脚本"
exit 1
fi
MOUNT_File1="/root/jdk-8u20-linux-x64.rpm"
if
   [ ! -e  $MOUNT_File1 ]
   then
   echo "安装文件不存在，请上传安装文件到/root/,上传完成再重新运行该脚本"
exit 1
fi
###############防火墙及SElinux############
systemctl stop firewalld && systemctl disable firewalld  && echo "防火墙已经关闭"
sed -i 's/SELINUX=.*/SELINUX=disabled/g'  /etc/selinux/config  && echo "关闭selinux"
sleep 10
##############jdk#############
cd /root   &&  rpm -ivh jdk-8u20-linux-x64.rpm
cat >> /etc/profile << EOF
export  JAVA_HOME=/usr/java/jdk1.8.0_20/
export  CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export  PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export  AMOEBA_HOME=/usr/local/amoeba
export  PATH=$PATH:$AMOEBA_HOME/bin
EOF
source /etc/profile #重新加载
source /etc/profile #重新加载
#############安装amoeba#############
mkdir /usr/local/amoeba &&  chmod -R 755 /usr/local/amoeba #创建目录amoeba及更改权限
tar xzf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ #解包到/usr/local/amoeba目录中
### /usr/local/amoeba/bin/
sed -i "58s/128/256/" /usr/local/amoeba/bin/amoeba
cd /usr/local/amoeba/bin/  &&  ./amoeba  #########进入执行目录安装
###################
cp  /usr/local/amoeba/conf/amoeba.xml   /usr/local/amoeba/conf/amoeba.xml.bak
sed -i "30s/root/amoeba/"   /usr/local/amoeba/conf/amoeba.xml
sed -i "32s/"password"></property>/"password">123456</property>/"   /usr/local/amoeba/conf/amoeba.xml
sed -i "115s/server1/master/"   /usr/local/amoeba/conf/amoeba.xml
sed -i "119s/readPool/slave/"   /usr/local/amoeba/conf/amoeba.xml 

mv /usr/local/amoeba/conf/dbServers.xml   /usr/local/amoeba/conf/dbServers.xml.bak
read -p "请输入mysql主服务器的IP： "  masterIP  
read -p "请输入mysql从服务器1的IP： "  slave1IP
read -p "请输入mysql从服务器2的IP： "  slave2IP
cat >> /usr/local/amoeba/conf/dbServers.xml  << EOF
<?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="manager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>

                        <!-- mysql port -->
                        <property name="port">3306</property>

                        <!-- mysql schema -->
     <!-- <property name="schema">test</property> -->

                        <!-- mysql user -->
                        <property name="user">test</property>

                       mysql password
                        <property name="password">123456</property>
                    
                </factoryConfig>

                <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
                        <property name="maxActive">500</property>
                        <property name="maxIdle">500</property>
                        <property name="minIdle">10</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>

        <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">$masterIP</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">$slave1IP</property>
                </factoryConfig>
        </dbServer>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">$slave2IP</property>
                </factoryConfig>
        </dbServer>
        
        <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">slave1, slave2</property>
                </poolConfig>
        </dbServer>
EOF
/usr/local/amoeba/bin/amoeba start&   ###后台启动
yum -y install mariadb mariadb-server  $$  echo  "mariadb安装成功"
echo "请通过命令mysql -utest -p123 -h $masterIP|$slave1IP|$slave2IP 访问测试"

脚本运行过程中需要输入相关服务器IP。

代理脚本所需要的安装包请到作者个人网站下载：

https://www.huoshichong.cn/index.php/download.html