mysql主从搭建脚本
#!/bin/bash
Master_ipddr="192.168.0.191"
Allow_segment=`echo ${Master_ipddr}. |awk -F'.' '{gsub($(NF-1)"."$NF,"");print}'`
Slave_ipaddr="192.168.0.118"
Mysql_user="root"
Mysql_passwd="redhat@2022"
Replication_user="repluser"
Replication_passwd="123456"
Replication_port="3306"
color_green="echo -e \033[1;32m"
color_red="echo -e \033[1;31m"
color_end="\033[0m"
Mysql_log_file="/date/mysql-bin.log"
Backup_time=`date "+%Y-%m-%d_%H:%M"`
Slave_user="root"
Slave_path="/etc/my.cnf"
read -s -p "请输入Slave passwd:" Slave_passwd
read -s -p "请输入Master passwd:" Master_passwd
function Master_Change_config () {
if [ -f /etc/my.cnf.d/server.cnf -a -e /etc/my.cnf.d/server.cnf ] && \
[[ $(grep -o '\[mysqld\]' /etc/my.cnf.d/server.cnf) ]];then
grep -o 'server-id' /etc/my.cnf.d/server.cnf || sed -ri.bak '/\[mysqld\]/a\server-id=191' /etc/my.cnf.d/server.cnf
grep -o 'log-bin=' /etc/my.cnf.d/server.cnf || sed -ri '/\[mysqld\]/a\log-bin=/data/mysql/logbin/mysql-bin' /etc/my.cnf.d/server.cnf && \
mkdir -p /data/mysql/logbin/;chown -R mysql.mysql /data/mysql/logbin/
grep -o 'sync_binlog' //etc/my.cnf.d/server.cnf || sed -ri '/\[mysqld\]/a\sync_binlog=1' /etc/my.cnf.d/server.cnf
grep -o 'innodb_flush_log_at_trx_commit' /etc/my.cnf.d/server.cnf || sed -ri '/\[mysqld\]/a\innodb_flush_log_at_trx_commit=1' /etc/my.cnf.d/server.cnf
grep -o 'sync_master_info' /etc/my.cnf.d/server.cnf || sed -ri '/\[mysqld\]/a\sync_master_info=10' /etc/my.cnf.d/server.cnf
grep -ro '^SELINUX=disabled' /etc/selinux/config || { setenforce 0;sed -ri 's/^(SELINUX=).*/\1disabled' /etc/selinux/config; }
[[ `grep -o 'server-id' /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf` ]] && \
[[ `grep -o 'log-bin=' /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf` ]] && \
[[ `grep -o 'sync_binlog' //etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf` ]] && \
[[ `grep -o 'innodb_flush_log_at_trx_commit' /etc/my.cnf.d/server.cnf` ]] && \
${color_green}"master配置完成"${color_end} || { ${color_red}"master配置失败"${color_end};exit 1; }
elif [ -f /etc/my.cnf.d/mysql-server.cnf -a -e /etc/my.cnf.d/mysql-server.cnf ] && \
[[ $(grep '[mysqld]' /etc/my.cnf.d/mysql-server.cnf) ]];then
grep -o 'server-id' /etc/my.cnf.d/mysql-server.cnf || sed -ri '/\[mysqld\]/a\server-id/=建议为IP的最后的值' /etc/my.cnf.d/server.cnf
grep -o 'log-bin=' /etc/my.cnf.d/mysql-server.cnf || sed -ri '/\[mysqld\]/a\log-bin=/data/mysql/logbin/mysql-bin/' /etc/my.cnf.d/server.cnf
grep -o 'sync_binlog' //etc/my.cnf.d/mysql-server.cnf || sed -ri '/\[mysqld\]/a\sync_binlog=1' /etc/my.cnf.d/server.cnf
grep -o 'innodb_flush_log_at_trx_commit' /etc/my.cnf.d/mysql-server.cnf || sed -ri '/\[mysqld\]/a\innodb_flush_log_at_trx_commit=1' /etc/my.cnf.d/server.cnf
grep -o 'sync_master_info' /etc/my.cnf.d/mysql-server.cnf || sed -ri '/\[mysqld\]/a\sync_master_info=10' /etc/my.cnf.d/server.cnf
grep -ro '^SELINUX=disabled' /etc/selinux/config || { setenforce 0;sed -ri 's/^(SELINUX=).*/\1disabled/' /etc/selinux/config; }
[[ `grep -o 'server-id' /etc/my.cnf.d/server.cnf /etc/my.cnf.d/mysql-server.cnf` ]] && \
[[ `grep -o 'log-bin=' /etc/my.cnf.d/server.cnf /etc/my.cnf.d/mysql-server.cnf` ]] && \
[[ `grep -o 'sync_binlog' //etc/my.cnf.d/server.cnf /etc/my.cnf.d/mysql-server.cnf` ]] && \
[[ `grep -o 'innodb_flush_log_at_trx_commit' /etc/my.cnf.d/mysql-server.cnf` ]] && \
${color_green}"master配置完成"${color_end} || { ${color_red}"master配置失败"${color_end};exit 1; }
else
${color_red}"无配置文件,请安装后启动mysql"${color_end}
fi
systemctl restart mysqld.service &> /dev/null || systemctl restart mariadb.service &> /dev/null
}
function create_Replication_user() {
[ -d /date ] || mkdir -p /date
mysql -u${Mysql_user} -p${Mysql_passwd} -e 'select @@log_bin;'|tr -s "\n" " " >> ${Mysql_log_file}
if [[ $(cat ${Mysql_log_file} | sed -rn 's/^@@log_bin .*([0-9])/\1/p') -eq 0 ]];then
mysql -u${Mysql_user} -p${Mysql_passwd} -e "set sql_log_bin=1;"
sleep 10
fi
sleep 10
mysql -u${Mysql_user} -p${Mysql_passwd} -e "create user ${Replication_user}@'${Allow_segment}%' identified by '${Replication_passwd}';"
sleep 5
mysql -u${Mysql_user} -p${Mysql_passwd} -e 'select user from mysql.user;' |grep "${Replication_user}" && \
mysql -u${Mysql_user} -p${Mysql_passwd} -e "grant replication slave on *.* to ${Replication_user}@${Allow_segment}%;"
}
function Backup() {
mysqldump -u${Mysql_user} -p${Mysql_passwd} -A -F --master-data=1 --single-transaction > /date/Replication${Backup_time}.sql
[ -f /date/Replication${Backup_time}.sql ] && \
sed -ri 's/^-- (CHANGE.*)/\1/' /date/Replication${Backup_time}.sql && \
sed -ri 's/^(CHANGE MASTER TO )(.*)/\1\n\2/' /date/Replication${Backup_time}.sql && \
sed -ri "/^CHANGE /a\MASTER_HOST='${Master_ipddr}'," /date/Replication${Backup_time}.sql && \
sed -ri "/^MASTER_HOST/a\MASTER_USER='${Replication_user}'," /date/Replication${Backup_time}.sql && \
sed -ri "/^MASTER_USER/a\MASTER_PASSWORD='${Replication_passwd}'," /date/Replication${Backup_time}.sql && \
sed -ri "/^MASTER_PASSWORD/a\MASTER_PORT=${Replication_port}," /date/Replication${Backup_time}.sql && \
grep -En '^CHANGE|^MASTER' /date/Replication${Backup_time}.sql |sed -r "/MASTER TO/i\ Replication configuration :'${Backup_time}'" >> ${Mysql_log_file}
}
function Slave_Change_config() {
tee /date/Slave_Change_config.sh<<EOF
ss -ntlp |grep 'mysqld' || systemctl start mysqld.service || systemctl start mariadb.service
grep 'log-bin=' ${Slave_path} 2>/dev/null || sed -ri.bak '/\\[mysqld\\]/a\log-bin' ${Slave_path}
sed -ri.bak '/\\[mysqld\\]/a\\server-id=192' ${Slave_path} && \\
sed -ri '/\\[mysqld\\]/a\\read-only=ON' ${Slave_path} && \\
mysql -u${Slave_user} -e 'set sql_log_bin=0;'
[ -f /root/Replication${Backup_time}.sql ] && \\
mysql -u${Slave_user} -e 'source /root/Replication${Backup_time}.sql' && \\
systemctl restart mysqld.service || systemctl restart mariadb.service
mysql -u${Slave_user} -p'${Mysql_passwd}' -e 'show slave status\G' |grep 'MASTER' && \\
mysql -u${Slave_user} -p'${Mysql_passwd}' -e 'start slave;'
mysql -u${Slave_user} -p'${Mysql_passwd}' -e 'show processlist;' |grep 'waiting for the slave' && \\
echo "Configuration is successful"
EOF
[ ! -e ${Slave_passwd} ] && \
expect <<EOF
set timeout 20
spawn scp /date/Replication${Backup_time}.sql /date/Slave_Change_config.sh ${Slave_ipaddr}:/root
expect {
"yes/no" { send "yes\n";exp_continue }
"password" { send "${Slave_passwd}\n" }
}
expect eof
EOF
sleep 10
expect <<EOF
set timeout 150
spawn ssh ${Slave_ipaddr}
expect {
"yes/no" { send "yes\n";exp_continue }
"password" { send "${Slave_passwd}\n" }
}
expect "]" { send "touch /root/Slave_Change_config.log\n"}
expect "]" { send "sh /root/Slave_Change_config.sh &>/root/Slave_Change_config.log \n"}
expect "]" { send "sleep 100\n" }
expect "]" { send "scp /root/Slave_Change_config.log ${Master_ipddr}:/root\n" }
expect {
"yes/no" { send "yes\n";exp_continue }
"password" { send "${Master_passwd}\n" }
}
expect "]" { send "exit\n"}
expect eof
EOF
grep "Configuration is successful" /root/Slave_Change_config.log && $color_green"配置完成"$color_end
}
function master_check(){
mysql -u${Mysql_user} -p${Mysql_passwd} -e 'processlist;' |grep 'dump' &&\
$color_green主从配置完成$color_end
}
PS3="请选择:(1-6):"
select MENU in 1 2 3 4 5 退出 ;do
case $REPLY in
1)
Master_Change_config
;;
2)
create_Replication_user
;;
3)
Backup
;;
4)
Slave_Change_config
;;
5)
master_check
;;
6)
exit
esac
done