MySQL 多实例 单脚本启动


:Mr.zhou  阅读: 1,235 次

  该脚本可实现:在MySQL多实例服务器上,通过脚本加参数的方式,分别(全部)启动、停止、重启 MySQL 实例以及查看各实例及全部实例的启动状态。

  启动、停止、重启参数: {start|stop|restart} {instance|all}

  查看启动状态参数: {show} {instance|all}

  参数中的 instance 为脚本中定义的 MySQL 各实例监听的端口号。如 3306 3307 3308

  在使用脚本前,需要根据服务器的 MySQL 环境,编辑脚本的定义变量部分。

  环境:

[root@mysql-m1 ~]# ifconfig eth0|awk -F "[ :]+" 'NR==2{print $4}'
172.16.1.130
[root@mysql-m1 ~]# uname -r
2.6.32-504.el6.x86_64
[root@mysql-m1 ~]# 

  本机共有三个 MySQL 实例,监听端口分别为 3306 3307 3308,数据存放目录为 /data/mysql/各实例名/data。各实例已初始化完成,且各实例启动配置文件已经保存在各实例目录下。

  部署 MySQL 多实例详见:http://www.z-dig.com/mysql-multiple-instances.html

[root@mysql-m1 ~]# tree -L 2 /data/mysql
/data/mysql
├── 3306
│   ├── data
│   ├── my.cnf
│   ├── mysql
│   ├── mysql-bin.000001
│   ├── mysql-bin.000002
│   ├── mysql-bin.000003
│   ├── mysql-bin.000004
│   ├── mysql-bin.000005
│   ├── mysql-bin.000006
│   ├── mysql-bin.000007
│   ├── mysql-bin.000008
│   ├── mysql-bin.000009
│   ├── mysql-bin.000010
│   ├── mysql-bin.000011
│   ├── mysql-bin.000012
│   ├── mysql-bin.000013
│   ├── mysql-bin.000014
│   ├── mysql-bin.000015
│   ├── mysql-bin.000016
│   ├── mysql-bin.000017
│   ├── mysql-bin.000018
│   ├── mysql-bin.000019
│   ├── mysql-bin.000020
│   ├── mysql-bin.000021
│   ├── mysql-bin.000022
│   ├── mysql-bin.000023
│   ├── mysql-bin.000024
│   ├── mysql-bin.000025
│   ├── mysql-bin.index
│   └── mysql.err
├── 3307
│   ├── data
│   ├── my.cnf
│   ├── mysql
│   ├── mysql-bin.000001
│   ├── mysql-bin.000002
│   ├── mysql-bin.000003
│   ├── mysql-bin.000004
│   ├── mysql-bin.000005
│   ├── mysql-bin.000006
│   ├── mysql-bin.000007
│   ├── mysql-bin.000008
│   ├── mysql-bin.000009
│   ├── mysql-bin.000010
│   ├── mysql-bin.000011
│   ├── mysql-bin.000012
│   ├── mysql-bin.000013
│   ├── mysql-bin.000014
│   ├── mysql-bin.000015
│   ├── mysql-bin.000016
│   ├── mysql-bin.000017
│   ├── mysql-bin.000018
│   ├── mysql-bin.000019
│   ├── mysql-bin.index
│   ├── mysql.err
│   ├── relay-bin.000093
│   ├── relay-bin.000094
│   ├── relay-bin.index
│   └── relay-log.info
└── 3308
    ├── data
    ├── my.cnf
    ├── mysql
    ├── mysql-bin.000001
    ├── mysql-bin.000002
    ├── mysql-bin.000003
    ├── mysql-bin.000004
    ├── mysql-bin.000005
    ├── mysql-bin.000006
    ├── mysql-bin.000007
    ├── mysql-bin.000008
    ├── mysql-bin.000009
    ├── mysql-bin.000010
    ├── mysql-bin.index
    ├── mysql.err
    ├── relay-bin.000046
    ├── relay-bin.000047
    ├── relay-bin.index
    └── relay-log.info

6 directories, 77 files
[root@mysql-m1 ~]# 

  脚本内容:

[root@mysql-m1 ~]# cat /server/scripts/mysqld-mi.sh   
#!/bin/bash
######################################
# Author  : Mr.Zhou                  #
# Email   : zhou@z-dig.com           #
# Website : http://www.z-dig.com     #
######################################
# Description:
# Start,Stop,Restart,ShowStatus MySQL Multiple Instances
# Before use this script ,should Define your MySQL Instance information and MySQL Command
######################################

####### Start Define Instance ########
#
# INSTANCE_ALL=(InstacnePort InstaacnePort InstancePort ...)
# Each Instance Format :INSTANCE_Port=(user password my.cnf sock)

INSTANCE_ALL=(3306 3307 3308)
INSTANCE_3306=(root 123456 /data/mysql/3306/my.cnf /data/mysql/3306/mysql.sock)
INSTANCE_3307=(root 123456 /data/mysql/3307/my.cnf /data/mysql/3307/mysql.sock)
INSTANCE_3308=(root 123456 /data/mysql/3308/my.cnf /data/mysql/3308/mysql.sock)

######## Define MySQL COMMOND ########

MYSQL="/application/mysql/bin/mysql"
MYSQL_SAFE="/application/mysql/bin/mysqld_safe"
MYSQL_ADMIN="/application/mysql/bin/mysqladmin"

############ End Define ##############
############# Functions ##############

# Check MySQL Instance Status  1 = not running ; 0 = is running
function CheckStatus(){
  LISTEN_NUM=$(/usr/sbin/lsof -i :$1|/usr/bin/wc -l)
  if [  $LISTEN_NUM -eq 0 ] ; then
    return 1
  else
    return 0
  fi
}

# Check User's Define
function CheckDefine(){
if [ -z "$(eval echo \${INSTANCE_$1[*]})" ] ; then
  return 1
fi
}

# Check UserInput $2 return 0 = Instance;1 = Show
function Check_1(){
  if [ "$1" == "start" -o "$1" == "stop" -o "$1" == "restart" ] ; then
    return 0
  else
    return 1
  fi
}

# Start MySQL Instance
function Start(){
  CheckStatus $1
  if [ $? -eq 0 ]; then
    /bin/echo "MySQL Instance $1 is running"
    S="R"
  else
    S="N"
    /bin/echo "Starting MySQL Instance $1 ..."
    $MYSQL_SAFE --defaults-file=$(echo "$(eval echo \${INSTANCE_$1[2]})") &>/dev/null &
  fi
  if [ "$S" != "R" ];then
    sleep 8
    S="N"
  CheckStatus $1
  if [ $? -eq 0 ];then
    /bin/echo "MySQL Instance $1 has been started"
  else
    /bin/echo "MySQL Instance $1 started failure"
  fi
  fi
}

# Start All
function StartAll(){
for i in ${INSTANCE_ALL[*]}
do
  Start $i
done
}

# Stop MySQL Instance 
function Stop(){
  CheckStatus $1
  if [ $? -eq 0 ]; then
    /bin/echo "Stopping MySQL Instacne $1 ..."
    $MYSQL_ADMIN -u$(echo "$(eval echo \${INSTANCE_$1[0]})") -p$(echo "$(eval echo \${INSTANCE_$1[1]})") -S $(echo "$(eval echo \${INSTANCE_$1[3]})") shutdown &>/dev/null
    CheckStatus $1
      if [ $? -eq 0 ];then
        /bin/echo "MySQL Instance $1 stoopped failure"
      else
        /bin/echo "MySQL Instance $1 has been stopped"
      fi
  else
    /bin/echo "MySQL Instance $1 is not running"
  fi
}

# Stop All
function StopAll(){
for i in ${INSTANCE_ALL[*]}
do
  Stop $i
done
}

# Show Instance
function Show(){
case $1 in
  all)
    for i in ${INSTANCE_ALL[*]}
    do
      CheckStatus $i
      if [ $? -eq 0 ] ; then
        echo "Instance $i is running"
      else
        echo "Instance $i is not running"
      fi
    done
    ;;
  *) CheckStatus $1
  if [ $? -eq 0 ] ; then
    echo "Instance $1 is running"
  else
    echo "Instance $1 is not running"
  fi
  ;;
esac
}

# Option Case
function Case (){
  case $1 in
    start) Start $2
      ;;
    stop) Stop $2
      ;;
    restart) Stop $2 && Start $2
      ;;
  esac
}

# Option All Case
function CaseAll (){
  case $1 in
    start) StartAll $2
      ;;
    stop) StopAll $2
      ;;
    restart) StopAll $2 && StartAll $2
      ;;
  esac
}

# Check user input
function CheckInput(){
if [ $# -ne 2 ];then
  /bin/echo "USAGE: $0 {start|stop|restart} {instance} || {show} {all|instance}"
else
  Check_1 $1
  if [  $? -eq 0 ] ; then
    CheckDefine $2
    if [ $? -eq 1 -a "$2" != "all" ];then
      /bin/echo "Instance $2 is not in this server"
    else
      if [ "$2" == "all" ] ; then
        CaseAll $* 
      else
        Case $*
      fi
    fi
  else
    CheckDefine $2
    if [ $? -eq 1 -a "$2" != "all" ];then
      /bin/echo "Instance $2 is not in this server"
    else
    Show $2
    fi
  fi
fi
}

function main (){
  Input=$(/bin/echo $*|/usr/bin/tr "A-Z" "a-z")
  CheckInput $Input
}
############ End Functions ###########

main $*
[root@mysql-m1 ~]# 

  若要在您的环境中测试脚本。需要更改脚本中 MySQL 各实例的详细信息(root用户 root密码 各实例启动配置文件my.cnf 各实例sock文件),及 MySQL 命令全路径。

  将脚本复制到 /usr/local/sbin 目录下重命名为 mysqld-mi ,并设置可执行权限,方便脚本的执行。这样在任何目录下输入 mysqld-mi 参数 即可执行。

[root@mysql-m1 ~]# cp /server/scripts/mysqld-mi.sh /usr/local/sbin/mysqld-mi
[root@mysql-m1 ~]# chmod 700 /usr/local/sbin/mysqld-mi    
[root@mysql-m1 ~]# ll /usr/local/sbin/mysqld-mi        
-rwx------ 1 root root 4282 Aug 28 00:12 /usr/local/sbin/mysqld-mi
[root@mysql-m1 ~]# 

  脚本测试:

[root@mysql-m1 ~]# mysqld-mi
USAGE: /usr/local/sbin/mysqld-mi {start|stop|restart} {instance|all} || {show} {instance|all}
[root@mysql-m1 ~]# 
[root@mysql-m1 ~]# mysqld-mi show all
Instance 3306 is not running
Instance 3307 is not running
Instance 3308 is not running
[root@mysql-m1 ~]# 

[root@mysql-m1 ~]# lsof -i :3306
[root@mysql-m1 ~]# lsof -i :3307
[root@mysql-m1 ~]# lsof -i :3308
[root@mysql-m1 ~]# ps -ef|grep 'mysqld'|grep -v 'grep'
[root@mysql-m1 ~]# 
[root@mysql-m1 ~]# mysqld-mi start
USAGE: /usr/local/sbin/mysqld-mi {start|stop|restart} {instance|all} || {show} {instance|all}
[root@mysql-m1 ~]# mysqld-mi start all
Starting MySQL Instance 3306 ...
MySQL Instance 3306 has been started
Starting MySQL Instance 3307 ...
MySQL Instance 3307 has been started
Starting MySQL Instance 3308 ...
MySQL Instance 3308 has been started
[root@mysql-m1 ~]# 

[root@mysql-m1 ~]# mysqld-mi show all                 
Instance 3306 is running
Instance 3307 is running
Instance 3308 is running
[root@mysql-m1 ~]# 

[root@mysql-m1 ~]# lsof -i :3306                      
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  20731 mysql   12u  IPv4 23190456      0t0  TCP *:mysql (LISTEN)
mysqld  20731 mysql   34u  IPv4 23206255      0t0  TCP localhost:mysql->localhost:34816 (ESTABLISHED)
mysqld  20731 mysql   36u  IPv4 23224542      0t0  TCP localhost:mysql->localhost:34817 (ESTABLISHED)
mysqld  25986 mysql   39u  IPv4 23206254      0t0  TCP localhost:34816->localhost:mysql (ESTABLISHED)
mysqld  31952 mysql   39u  IPv4 23224541      0t0  TCP localhost:34817->localhost:mysql (ESTABLISHED)
[root@mysql-m1 ~]# lsof -i :3307                      
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  25986 mysql   12u  IPv4 23206217      0t0  TCP *:opsession-prxy (LISTEN)
[root@mysql-m1 ~]# lsof -i :3308                      
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  31952 mysql   12u  IPv4 23224529      0t0  TCP *:tns-server (LISTEN)
[root@mysql-m1 ~]# 

[root@mysql-m1 ~]# mysql -S /data/mysql/3308/mysql.sock -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| db2                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql-m1 ~]# mysqld-mi show 3307
Instance 3307 is running
[root@mysql-m1 ~]# mysqld-mi stop 3307
Stopping MySQL Instacne 3307 ...
MySQL Instance 3307 has been stopped
[root@mysql-m1 ~]# mysqld-mi show 3307
Instance 3307 is not running
[root@mysql-m1 ~]# lsof -i :3307
[root@mysql-m1 ~]# mysqld-mi show all
Instance 3306 is running
Instance 3307 is not running
Instance 3308 is running
[root@mysql-m1 ~]# 
[root@mysql-m1 ~]# mysqld-mi show 3308
Instance 3308 is running
[root@mysql-m1 ~]# mysqld-mi start 3308
MySQL Instance 3308 is running
[root@mysql-m1 ~]# 
[root@mysql-m1 ~]# mysqld-mi show all
Instance 3306 is running
Instance 3307 is not running
Instance 3308 is running
[root@mysql-m1 ~]# mysqld-mi stop all
Stopping MySQL Instacne 3306 ...
MySQL Instance 3306 has been stopped
MySQL Instance 3307 is not running
Stopping MySQL Instacne 3308 ...
MySQL Instance 3308 has been stopped
[root@mysql-m1 ~]# mysqld-mi show all
Instance 3306 is not running
Instance 3307 is not running
Instance 3308 is not running
[root@mysql-m1 ~]# 
[root@mysql-m1 ~]# mysqld-mi show 3310
Instance 3310 is not in this server
[root@mysql-m1 ~]# mysqld-mi start 3310
Instance 3310 is not in this server
[root@mysql-m1 ~]# mysqld-mi start abc
Instance abc is not in this server
[root@mysql-m1 ~]# 

  下载该脚本:下载


转载请注明原文链接:http://www.z-dig.com/single-mysql-instance-script.html



正文部分到此结束