这是一个mysql的主从配置脚本,本人编写,亲测有效。脚本有两个功能:1.主从搭建 2.主从简单修复
如果有错误,欢迎大家指正。
以下是脚本内容:
#! /bin/bash
## 这个脚本用来实现两个功能:1.mysql的主从搭建 2.mysql的主从断开的简单重连
## 版本:1.0
## 作者:xingyys
## 时间:2016-3-2
##防火墙模块##
mod_firewall() {
##关闭iptables##
iptables-save > /etc/sysconfig/iptables_`date +%s`
iptables -F
service iptables save
##关闭selinux##
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
selinux_s=`getenforce`
if [ $selinux_s == "enforcing" ]
then
setenforce 0
fi
}
##检错模块##
mod_check() {
if [ $? != 0 ]
then
echo "Error,exit script."
exit 1
fi
}
##获取linux版本
ar=`arch`
###### 主从查看模块#######
mod_con() {
slave_io=` mysql -S /tmp/mysql_slave.sock -e "show slave status\G;" |grep Running|awk -F" " '{print $NF}' |head -1`
slave_sql=` mysql -S /tmp/mysql_slave.sock -e "show slave status\G;" |grep Running|awk -F" " '{print $NF}' |tail -1`
if [[ $slave_io != "Yes" ]] || [[ $slave_sql != "Yes" ]]
then
echo -e "\033[32m mysql master-slave error!\033[0m"
status_ms=1
else
status_ms=0
fi
}
############################### mysql主从搭建 ############################
mod_master(){
######### 主mysql ############
cd /usr/src
##下载mysql
[ -f mysql-5.1.73-linux-$ar-glibc23.tar.gz ] || wget http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-$ar-glibc23.tar.gz
mod_check
tar -xvf mysql-5.1.73-linux-$ar-glibc23.tar.gz
mod_check
[ -d /usr/local/mysql ] && /bin/mv /usr/local/mysql /usr/local/mysql_`date +%s`
mv mysql-5.1.73-linux-$ar-glibc23 /usr/local/mysql
mod_check
if ! grep '^mysql:' /etc/passwd
then
useradd -M mysql -s /sbin/nologin
mod_check
fi
[ -d /data/mysql ] && /bin/mv /data/mysql /data/mysql_`date +%s`
mkdir -pv /data/mysql && chown -R mysql:mysql /data/mysql
mod_check
##初始化mysql
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
mod_check
[ -f /etc/my.cnf ] && /bin/mv /etc/my.cnf /etc/my.cnf_`date +%s`
##修改配置文件和脚本
/bin/cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
mod_check
#sed -i '/^\[mysqld\]$/a\datadir = /data/mysql' /etc/my.cnf
/bin/cp support-files/mysql.server /etc/init.d/mysqld
mod_check
sed -i 's#^datadir=.*#datadir=/data/mysql#' /etc/init.d/mysqld
sed -i 's#^basedir=.*#basedir=/usr/local/mysql#' /etc/init.d/mysqld
mod_check
chmod 755 /etc/init.d/mysqld
mod_check
chkconfig --add mysqld
mod_check
/etc/init.d/mysqld start
mod_check
chkconfig mysqld on
mod_check
echo "\033[32m mysql_master successful."
}
########### 从mysql ############
mod_slave(){
###修改配置文件
[ -d /usr/local/mysql_slave ] || /bin/cp -r /usr/local/mysql /usr/local/mysql_slave
mod_check
/bin/cp /etc/my.cnf /usr/local/mysql_slave/my.cnf
mod_check
sed -i '22,$s/^port.*= 3306/port = 3307/' /usr/local/mysql_slave/my.cnf
mod_check
sed -i '22,$s#^socket.*= \/tmp\/mysql.sock#socket = \/tmp\/mysql_slave.sock#' /usr/local/mysql_slave/my.cnf
mod_check
sed -i '/^socket.*= \/tmp\/mysql_slave.sock/a\datadir = /data/mysql_slave\' /usr/local/mysql_slave/my.cnf
mod_check
cd /usr/local/mysql_slave && ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave
mod_check
[ -d /data/mysql_slave ]
mod_check
###修改服务启动脚本
/bin/cp /etc/init.d/mysqld /etc/init.d/mysql_slave
sed -i 's#^datadir=.*$#datadir=/data/mysql_slave#' /etc/init.d/mysql_slave
sed -i 's#^basedir=.*$#basedir=/usr/local/mysql_slave#' /etc/init.d/mysql_slave
sed -i 's#^.*conf=\/etc\/my\.cnf$#conf=$basedir/my.cnf#' /etc/init.d/mysql_slave
cd
chkconfig --add mysql_slave
chkconfig mysql_slave on
/etc/init.d/mysql_slave start && chmod 755 /etc/init.d/mysql_slave
mod_check
echo -e "\033[32m mysql_slave successful.\033[0m"
}
########################## mysql主从配置 ##############################
mod_ms() {
## 主mysql语句
SQL1="create database db1"
SQL2="grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456'"
SQL3="flush privileges"
SQL4="flush tables with read lock"
MYSQL_M="mysql"
##修改mysql主的配置文件
sed -i 's/^log-bin=mysql-bin$/log-bin=ping/' /etc/my.cnf
mod_check
sed -i '/^log-bin=ping$/a\binlog-do-db=db1\' /etc/my.cnf
mod_check
service mysqld restart
##在mysql主端执行mysql语句
echo "${SQL1};${SQL2};${SQL3};${SQL4}" | ${MYSQL_M}
mod_check
mysqldump -S /tmp/mysql.sock mysql > /tmp/123.sql
mod_check
##获取主的Position
pos=`mysql -e "show master status;" | grep ping |awk -F" " '{print $2}'`
## 从mysql语句
sql1="slave stop"
sql2="change master to master_host='127.0.0.1', master_port=3306,master_user='repl',master_password='123456',master_log_file='ping.000001',master_log_pos= $pos"
sql3="slave start"
mysql_m="mysql -S /tmp/mysql_slave.sock"
##修改从mysql配置文件
sed -i 's/^server.*= 1$/server-id = 111/' /usr/local/mysql_slave/my.cnf
mod_check
##更新数据库
mysql -S /tmp/mysql_slave.sock -e "create database db1";
mod_check
mysql -S /tmp/mysql_slave.sock db1 < /tmp/123.sql
mod_check
service mysql_slave restart
mod_check
##执行从mysql语句
echo "${sql1};${sql2};${sql3}"|${mysql_m}
mod_check
mod_con
if [ $status_ms -eq 0 ]
then
echo -e "\033[32m mysql主从成功 \033[0m"
fi
}
############################# 主程序 ####################################
FUNC2="重连mysql主从"
FUNC1="搭建mysql主从"
echo -e "输入你想实现的功能:\033[32m1.$FUNC1 2.$FUNC2\033[0m"
select func in $FUNC1 $FUNC2
do
case $func in
$FUNC1)
mod_firewall
mod_master
mod_slave
mod_ms
echo -e "主从配置完成."
break
;;
$FUNC2)
mod_con
if [ $status_ms -eq 1 ]
then
echo -e "主从环境已破坏,正在执行简单修复."
mysql -S /tmp/mysql_slave.sock -e "slave stop;"
mod_check
mysql -S /tmp/mysql_slave.sock -e "slave start;"
mod_check
fi
mod_con
if [ $status_ms -eq 0 ]
then
echo -e "\033[32m 已修复主从环境,谢谢使用.\033[0m"
else
echo -e "\033[32m 严重事故,无能为力,还请另请高明.\033[0m"
fi
break
;;
*)
echo -e "\033[32m 请输入0或1\033[0m"
break
;;
esac
done
如果有错误,欢迎大家指正。
以下是脚本内容:
#! /bin/bash
## 这个脚本用来实现两个功能:1.mysql的主从搭建 2.mysql的主从断开的简单重连
## 版本:1.0
## 作者:xingyys
## 时间:2016-3-2
##防火墙模块##
mod_firewall() {
##关闭iptables##
iptables-save > /etc/sysconfig/iptables_`date +%s`
iptables -F
service iptables save
##关闭selinux##
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
selinux_s=`getenforce`
if [ $selinux_s == "enforcing" ]
then
setenforce 0
fi
}
##检错模块##
mod_check() {
if [ $? != 0 ]
then
echo "Error,exit script."
exit 1
fi
}
##获取linux版本
ar=`arch`
###### 主从查看模块#######
mod_con() {
slave_io=` mysql -S /tmp/mysql_slave.sock -e "show slave status\G;" |grep Running|awk -F" " '{print $NF}' |head -1`
slave_sql=` mysql -S /tmp/mysql_slave.sock -e "show slave status\G;" |grep Running|awk -F" " '{print $NF}' |tail -1`
if [[ $slave_io != "Yes" ]] || [[ $slave_sql != "Yes" ]]
then
echo -e "\033[32m mysql master-slave error!\033[0m"
status_ms=1
else
status_ms=0
fi
}
############################### mysql主从搭建 ############################
mod_master(){
######### 主mysql ############
cd /usr/src
##下载mysql
[ -f mysql-5.1.73-linux-$ar-glibc23.tar.gz ] || wget http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-$ar-glibc23.tar.gz
mod_check
tar -xvf mysql-5.1.73-linux-$ar-glibc23.tar.gz
mod_check
[ -d /usr/local/mysql ] && /bin/mv /usr/local/mysql /usr/local/mysql_`date +%s`
mv mysql-5.1.73-linux-$ar-glibc23 /usr/local/mysql
mod_check
if ! grep '^mysql:' /etc/passwd
then
useradd -M mysql -s /sbin/nologin
mod_check
fi
[ -d /data/mysql ] && /bin/mv /data/mysql /data/mysql_`date +%s`
mkdir -pv /data/mysql && chown -R mysql:mysql /data/mysql
mod_check
##初始化mysql
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
mod_check
[ -f /etc/my.cnf ] && /bin/mv /etc/my.cnf /etc/my.cnf_`date +%s`
##修改配置文件和脚本
/bin/cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
mod_check
#sed -i '/^\[mysqld\]$/a\datadir = /data/mysql' /etc/my.cnf
/bin/cp support-files/mysql.server /etc/init.d/mysqld
mod_check
sed -i 's#^datadir=.*#datadir=/data/mysql#' /etc/init.d/mysqld
sed -i 's#^basedir=.*#basedir=/usr/local/mysql#' /etc/init.d/mysqld
mod_check
chmod 755 /etc/init.d/mysqld
mod_check
chkconfig --add mysqld
mod_check
/etc/init.d/mysqld start
mod_check
chkconfig mysqld on
mod_check
echo "\033[32m mysql_master successful."
}
########### 从mysql ############
mod_slave(){
###修改配置文件
[ -d /usr/local/mysql_slave ] || /bin/cp -r /usr/local/mysql /usr/local/mysql_slave
mod_check
/bin/cp /etc/my.cnf /usr/local/mysql_slave/my.cnf
mod_check
sed -i '22,$s/^port.*= 3306/port = 3307/' /usr/local/mysql_slave/my.cnf
mod_check
sed -i '22,$s#^socket.*= \/tmp\/mysql.sock#socket = \/tmp\/mysql_slave.sock#' /usr/local/mysql_slave/my.cnf
mod_check
sed -i '/^socket.*= \/tmp\/mysql_slave.sock/a\datadir = /data/mysql_slave\' /usr/local/mysql_slave/my.cnf
mod_check
cd /usr/local/mysql_slave && ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave
mod_check
[ -d /data/mysql_slave ]
mod_check
###修改服务启动脚本
/bin/cp /etc/init.d/mysqld /etc/init.d/mysql_slave
sed -i 's#^datadir=.*$#datadir=/data/mysql_slave#' /etc/init.d/mysql_slave
sed -i 's#^basedir=.*$#basedir=/usr/local/mysql_slave#' /etc/init.d/mysql_slave
sed -i 's#^.*conf=\/etc\/my\.cnf$#conf=$basedir/my.cnf#' /etc/init.d/mysql_slave
cd
chkconfig --add mysql_slave
chkconfig mysql_slave on
/etc/init.d/mysql_slave start && chmod 755 /etc/init.d/mysql_slave
mod_check
echo -e "\033[32m mysql_slave successful.\033[0m"
}
########################## mysql主从配置 ##############################
mod_ms() {
## 主mysql语句
SQL1="create database db1"
SQL2="grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456'"
SQL3="flush privileges"
SQL4="flush tables with read lock"
MYSQL_M="mysql"
##修改mysql主的配置文件
sed -i 's/^log-bin=mysql-bin$/log-bin=ping/' /etc/my.cnf
mod_check
sed -i '/^log-bin=ping$/a\binlog-do-db=db1\' /etc/my.cnf
mod_check
service mysqld restart
##在mysql主端执行mysql语句
echo "${SQL1};${SQL2};${SQL3};${SQL4}" | ${MYSQL_M}
mod_check
mysqldump -S /tmp/mysql.sock mysql > /tmp/123.sql
mod_check
##获取主的Position
pos=`mysql -e "show master status;" | grep ping |awk -F" " '{print $2}'`
## 从mysql语句
sql1="slave stop"
sql2="change master to master_host='127.0.0.1', master_port=3306,master_user='repl',master_password='123456',master_log_file='ping.000001',master_log_pos= $pos"
sql3="slave start"
mysql_m="mysql -S /tmp/mysql_slave.sock"
##修改从mysql配置文件
sed -i 's/^server.*= 1$/server-id = 111/' /usr/local/mysql_slave/my.cnf
mod_check
##更新数据库
mysql -S /tmp/mysql_slave.sock -e "create database db1";
mod_check
mysql -S /tmp/mysql_slave.sock db1 < /tmp/123.sql
mod_check
service mysql_slave restart
mod_check
##执行从mysql语句
echo "${sql1};${sql2};${sql3}"|${mysql_m}
mod_check
mod_con
if [ $status_ms -eq 0 ]
then
echo -e "\033[32m mysql主从成功 \033[0m"
fi
}
############################# 主程序 ####################################
FUNC2="重连mysql主从"
FUNC1="搭建mysql主从"
echo -e "输入你想实现的功能:\033[32m1.$FUNC1 2.$FUNC2\033[0m"
select func in $FUNC1 $FUNC2
do
case $func in
$FUNC1)
mod_firewall
mod_master
mod_slave
mod_ms
echo -e "主从配置完成."
break
;;
$FUNC2)
mod_con
if [ $status_ms -eq 1 ]
then
echo -e "主从环境已破坏,正在执行简单修复."
mysql -S /tmp/mysql_slave.sock -e "slave stop;"
mod_check
mysql -S /tmp/mysql_slave.sock -e "slave start;"
mod_check
fi
mod_con
if [ $status_ms -eq 0 ]
then
echo -e "\033[32m 已修复主从环境,谢谢使用.\033[0m"
else
echo -e "\033[32m 严重事故,无能为力,还请另请高明.\033[0m"
fi
break
;;
*)
echo -e "\033[32m 请输入0或1\033[0m"
break
;;
esac
done
编辑回复