一键搭建MySql主从
已完成
- 一键安装mysql
- 一键安装ansible
- 一键配置主从
- 支持一主一从
- 支持一主多从
未完成
集成控制系统
- 显示主从状态
- 显示当前服务器负载情况
- 管理mysql数据库(赠删改查
- 在线编辑mysql配置文件
详细代码
#!/bin/bash
# Author: Ropon
# Blog: https://www.ropon.top
#借助ansible、expect
downurl="http://panel.ropon.top/panel/lnmp/"
mysqlname="mysql-5.6.43-1.el7.x86_64.rpm"
#mysqlname="mysql-5.7.25-1.el7.x86_64.rpm"
ip="172.16.7.124"
#多个从服务器
declare -A CserverLst
#CserverLst=([s1]="172.16.7.125")
CserverLst=([s1]="172.16.7.125" [s2]="172.16.7.126")
cport="22"
cpasswd="ropon.top"
ansible_host="/etc/ansible/hosts"
mysqlpasswd="West.cn2020"
user="westdemo"
passwd="west.cn"
host="172.16.7.%"
tmpsshfile="/tmp/ssh.exp"
mysqlmasterfile="/tmp/master.info"
cmysqllibfile="/tmp/cmysqllib.sh"
cmysqlslavefile="/tmp/slave.sh"
MySql() {
yum install -y ncurses ncurses-devel libaio numactl numactl-libs perl-Module-Install
if [ $? -eq 0 ]; then
wget ${downurl}${mysqlname}
rpm -ivh $mysqlname
sleep 1
. /etc/profile
mysql -uroot -p$mysqlpasswd -e "grant replication slave on *.* to '${user}'@'${host}' identified by '${passwd}';"
mysql -uroot -p$mysqlpasswd -e "flush privileges;"
mysql -uroot -p$mysqlpasswd -e "show master status;" > $mysqlmasterfile
CFirwall
fi
}
Ansible() {
if [[ -f /etc/yum.repos.d/epel*.repo ]]; then
mv /etc/yum.repos.d/epel*.repo{,_bak}
fi
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install -y ansible
}
Ssh() {
yum install -y expect
echo '#!/usr/bin/expect
spawn ssh-keygen
expect {
"*.ssh/id_rsa*" {exp_send "\r";exp_continue}
"*passphrase*" {exp_send "\r";exp_continue}
"*again*" {exp_send "\r"}
}' > $tmpsshfile
expect $tmpsshfile
sleep 1
echo "[db]" >> $ansible_host
for key in ${!CserverLst[*]}; do
cat > $tmpsshfile << EOF
#!/usr/bin/expect
spawn ssh-copy-id ${CserverLst[$key]} -p ${cport}
expect {
"*yes/no*" {exp_send "yes\r";exp_continue}
"*password*" {exp_send "${cpasswd}\r";exp_continue}
}
EOF
expect $tmpsshfile
echo "${CserverLst[$key]} ansible_ssh_port=${cport}" >> $ansible_host
done
ansible db -m ping
}
CMySql() {
cat > $cmysqllibfile << EOF
#!/bin/bash
yum install -y ncurses ncurses-devel libaio numactl numactl-libs perl-Module-Install
if [ $? -eq 0 ]; then
wget $downurl${mysqlname}
rpm -ivh $mysqlname
sleep 1
sed -i "s@server-id.*@server-id = 2@g" /etc/my.cnf
sed -i "s@log_bin.*@#log_bin = mysql-bin@g" /etc/my.cnf
service mysqld restart
fi
EOF
ansible db -m script -a "${cmysqllibfile}"
sleep 1
mysql_bin_name=$(cat $mysqlmasterfile |awk '{if (NR>1){print $1}}')
mysql_bin_offset=$(cat $mysqlmasterfile |awk '{if (NR>1){print $2}}')
cat > $cmysqlslavefile << EOF
#!/bin/bash
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "change master to master_host='${ip}',master_user='${user}',master_port=3306,master_password='${passwd}',master_log_file='${mysql_bin_name}',master_log_pos=${mysql_bin_offset};"
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "start slave;"
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "show slave status\G;"
service mysqld restart
EOF
ansible db -m script -a "${cmysqlslavefile}"
}
CFirwall() {
iptables -L -n |grep -w dpt:$cport >/dev/null
if [ $? -eq 0 ] ;then
iptables -L -n |grep -w dpt:3306 >/dev/null
if [ $? -ne 0 ] ;then
sed -i "/dport ${cport} -j ACCEPT/a\-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT" /etc/sysconfig/iptables
service iptables restart
fi
fi
}
Clean() {
rm -rf $tmpsshfile
rm -rf $mysqlmasterfile
rm -rf $cmysqllibfile
rm -rf $cmysqlslavefile
}
Main() {
MySql
Ansible
Ssh
CMySql
Clean
}
Main