【实用脚本】MySQL5.7多实例安装脚本

在运行这个脚本之前需要理解MySQL多实例的安装方法，可以参考本站文章《【MySQL配置教程】MySQL多实例部署》。下面附带MySQL多实例的安装初始化脚本与服务启停脚本，在运行脚本之前请确保二进制安装包路径为/usr/local/mysql。如果初始化完成后使用临时密码提示Your password has expired密码过期，是因为系统自带的mysql命令行工具与当前安装的MySQL版本不一致，使用绝对路径即可。

一、MySQL多实例安装与初始化脚本

#!/bin/bash
. /etc/init.d/functions
ipaddr=`ifconfig |grep "inet "|grep "netmask"|awk '{print $2}' |grep -v 127.0.0.1 |head -n1`
serverid=`echo $ipaddr |awk -F\. '{print $2$3$4}'`

while true
  do
    read -p "Please enter MySQL instance port to create，such as [3306],enter [Q] exit: " mysql_port
    if [ ${mysql_port} == "Q" ];then
	exit 0
    fi

    if [[ ${mysql_port} =~ ^[3][3][0-9][0-9]$ ]]; then
	#create datadir
		if [ -d /data/mysql${mysql_port} ];then
			echo "Datadir already exists,Please Retry or Entre Q to exit: "
			continue
		else
			mkdir -p /data/mysql${mysql_port} && echo "/data/mysql${mysql_port} Datadir create successful"
		fi
		
		mkdir -p /data/dblog/mysql${mysql_port}/{binlog,relaylog}  && echo "/data/mysql${mysql_port} Logdir create successful"
		mkdir -p /data/tmp/mysql${mysql_port} && echo "/data/mysql${mysql_port}/tmp Tmpdir create successful"
		chown -R mysql.mysql /data
		
		read -p "Please enter MySQL ${mysql_port} instance innodb_buffer_pool_size , The Unit is M: " buf_pool
		
	#my.cnf	
		cp /etc/my.cnf /etc/my${mysql_port}.cnf && echo "Create mysql${mysql_port}.cnf at /etc/"
		sed -i -r "/\<port/ s/port.*/port = ${mysql_port}/g" /etc/my${mysql_port}.cnf 
		sed -i -r "/\<datadir/ s/datadir.*/datadir = \/data\/mysql${mysql_port}/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<tmpdir/ s/tmpdir.*/tmpdir = \/data\/tmp\/mysql${mysql_port}/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<slow_query_log_file/ s/slow_query_log_file.*/slow_query_log_file = \/data\/dblog\/mysql${mysql_port}\/mysql.slow/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<log_error/ s/log_error.*/log_error = \/data\/dblog\/mysql${mysql_port}\/error.log/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<log_bin/ s/log_bin.*/log_bin = \/data\/dblog\/mysql${mysql_port}\/binlog\/mysql-bin/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<relay-log/ s/relay-log.*/relay-log = \/data\/dblog\/mysql${mysql_port}\/relaylog\/relay-bin/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<innodb_data_home_dir/ s/innodb_data_home_dir.*/innodb_data_home_dir = \/data\/mysql${mysql_port}/g" /etc/my${mysql_port}.cnf
		sed -i -r "/\<socket/ s/socket.*/socket = \/tmp\/mysql${mysql_port}.sock/g" /etc/my${mysql_port}.cnf
	
	#server-id
		sed -i -r "/\<server-id/ s/server-id.*/server-id = ${serverid}/g" /etc/my${mysql_port}.cnf
	
	#buf_pool
		#memtotal=`cat /proc/meminfo |grep MemTotal|awk '{print $2}'`
		#memtotal=`echo  $((${memtotal}/1024))`
		#buf_pool=`echo "${memtotal} * 0.75" |bc |cut -d'.' -f1`
		sed -i -r "/\<innodb_buffer_pool_size/ s/innodb_buffer_pool_size.*/innodb_buffer_pool_size = ${buf_pool}M/g" /etc/my${mysql_port}.cnf
    
	#Initialize     
	echo "Initialize MySQL mysql${mysql_port} Instance..."
		/usr/local/mysql/bin/mysqld  --defaults-file=/etc/my${mysql_port}.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql${mysql_port}
		grep password /data/dblog/mysql${mysql_port}/error.log | awk '{print $NF}' > /data/mysql${mysql_port}/passwd.txt && echo "Initialize sucessful , temporary password stored in the /data/mysql${mysql_port}/passwd.txt "
   		 
		 if [ $? -eq 0 ]; then
   			 action "Mysql ${mysql_port} Instance Initialize Successful" /bin/true
   		 else
    			 action "Mysql ${mysql_port} Instance Initialize Failed" /bin/false
   		 fi
    #Startup
		/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my${mysql_port}.cnf &
                 if [ $? -eq 0 ]; then
                	 action "Mysql ${mysql_port} Instance Startup Successful" /bin/true
			 exit 0
                 else
                 	action "Mysql ${mysql_port} Instance Startup Failed" /bin/false
			exit 1
                 fi
    else
      echo "The parameter must be a number,such as 33[06]"
      continue
    fi
  done

脚本说明：

1、第一次输入内容为四位数字端口，必须为33开头，如3310、3311，其他输入会提示重新输入

2、第二次输入内容为innodb_buffer_pool_size的大小，单位为M
3、脚本根据两次输入自动创建配置文件并完成初始化和启动实例
4、root临时密码会记录在/data/mysql${mysql_port}/passwd.txt文件中，必须修改密码才能使用数据库。使用临时密码登录mysql后修改root本地连接密码，创建管理账号，登录时需要指定socket文件路径

/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql${mysql_port}.sock
mysql > alter user root@'localhost' identified by 'NEWPASSWORD';
mysql > grant all privileges on *.* to 'dba'@'%' identified by 'PASSWORD';
mysql > flush privileges;

二、MySQL多实例服务启动脚本，放在各实例目录下运行即可：

#!/bin/bash
. /etc/init.d/functions
mysql_port=3310  #自定修改端口
mysql_user=dba
mysql_pwd=YOUR_PASSWORD
mysql_path=/usr/local/mysql/bin
mysql_sock=/tmp/mysql${mysql_port}.sock

start()
{
    if [ ! -e ${mysql_sock} ];then
      printf "Starting MySQL...\n"
      ${mysql_path}/mysqld_safe --defaults-file=/etc/my${mysql_port}.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit 2
    fi
}

stop()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit 2
    else
       printf "Stoping MySQL...\n"
       ${mysql_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}

restart()
{
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}

case $1 in
start)
    start
 if [ $? -eq 0 ]; then
 action "Mysql ${mysql_port} Instance Startup Successful" /bin/true
    else
        action "Mysql ${mysql_port} Instance Startup Failed" /bin/false
    fi
;;
stop)
    stop
 if [ $? -eq 0 ]; then
 action "Mysql ${mysql_port} Instance Stop Successful" /bin/true
    else
        action "Mysql ${mysql_port} Instance Stop Failed" /bin/false
    fi
;;
restart)
    restart
;;
*)
    printf "Usage: The first parameter must be {start|stop|restart}\n"
esac