今天给大家分享一个mysql主从配置脚本

回复 收藏
这是一个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
2016-03-03 16:31 举报
已邀请:
0

369666951

赞同来自:

看看
0

balich

赞同来自:

谢谢分享
0

qq495966654

赞同来自:

略懂
0

jinm

赞同来自:

l厉害
0

inzaghidai

赞同来自:

真棒,学习
0

opensky

赞同来自:

很好, 本来准备自个写的
0

maria

赞同来自:

{:6_148:}学习一下

回复帖子,请先登录注册

退出全屏模式 全屏模式 回复
评分
可选评分理由: