mysql运维脚本_【实用脚本】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 "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

sed -i -r "/\

#server-id

sed -i -r "/\

#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 "/\

#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