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

集群架构：

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

主从节点部署脚本：

#!/bin/bash
#function:mysql主从复制与读写分离
#author:tommypeng  20220726
#####root判断#####
if[  "$USER"  != "root"   ]
thenecho "错误：非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
dorpm -e --nodeps   $PKGNAME   &&  echo  "老旧包已经清理完毕" 
done
rm -rf  /root/888.txt  &&  echo "临时文件已删除"
sleep 8
##############网络测试##############
ping   -c  3  www.baidu.com
if[ $? = 0 ]thenecho "外网通讯良好！"elseecho  "丫的你在逗我吗？网都没有安装个毛线！"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
docase $i inmaster)master 1;;slave)slave 2;;exit)echo "The system exit"exit
esacdone

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

部署脚本：

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

#!/bin/bash
#function:mysql代理（阿米巴变形虫）
#author:tommypeng  20220727
#####root判断#####
if[  "$USER"  != "root"   ]
thenecho "错误：非root用户，权限不足！"exit  0
fi
#####安装文件判断######
MOUNT_File="/root/amoeba-mysql-binary-2.2.0.tar.gz"
if[ ! -e  $MOUNT_File ]thenecho "安装文件不存在，请上传安装文件到/root/,上传完成再重新运行该脚本"
exit 1
fi
MOUNT_File1="/root/jdk-8u20-linux-x64.rpm"
if[ ! -e  $MOUNT_File1 ]thenecho "安装文件不存在，请上传安装文件到/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">/"password">123456/"   /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

${defaultManager}641283306testmysql password12345650050010600000600000truetruetrue$masterIP$slave1IP$slave2IP1slave1, slave2
EOF
/usr/local/amoeba/bin/amoeba start&   ###后台启动
yum -y install mariadb mariadb-server  $$  echo  "mariadb安装成功"
echo "请通过命令mysql -utest -p123 -h $masterIP|$slave1IP|$slave2IP 访问测试"

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

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