mysql多实例mysqld_multi方式

mysql多实例应用，亲测直接执行脚本可使用，可快速部署多实例环境

#!/bin/bash

set -e

#定义mysql_multi多实例数据的配置,如需增加,在后面函数对应地方需要增加

mydir=/data/mysql_multi

#mysql sock

my1=mysql3307

my2=mysql3308

#mysql_multi name

mycnf1=mysqld3307

mycnf2=mysqld3308

#mysql port

my1_port=

my2_port=

#需要安装机器的内网IP

nei_eth=192.168.0.131

#mysql size

pool_size=2048M

#######################分割线，以上内容需要按需修改######################

pack="mysql-5.6.35.tar.gz"

pack_dir="mysql-5.6.35"

conf_file="/usr/local/mysql"

if [ ! -f ${pack} ]

then

    echo "没有找到mysql安装包"

    exit

fi

if [ -e /usr/local/mysql* ] || ps -ef | /bin/grep -Ev "$(basename $0)|grep" | /bin/grep -q mysql

then

    echo "本机可能已安装有mysql,请检查确认！"

    exit

fi

if /bin/grep  mysql /etc/passwd || /bin/grep  mysql /etc/group

then

    echo "已存在mysql账户或用户组"

else

    groupadd mysql

    useradd -r -s /sbin/nologin -g mysql mysql

fi

yum remove mysql*

yum -y install  bison-devel ncurses-devel autoconf

tar xf ${pack} \

&& cd ${pack_dir} \

&& echo "configuring..." \

&& cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE= -DWITH_INNOBASE_STORAGE_ENGINE= -DWITH_MEMORY_STORAGE_ENGINE= -DWITH_READLINE= -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_TCP_PORT= -DENABLED_LOCAL_INFILE= -DWITH_PARTITION_STORAGE_ENGINE= -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  >> /opt/make_mysql.txt \

&& echo "making..." \

&& make -j `grep processor /proc/cpuinfo | wc -l` >> /opt/make_mysql.txt \

&& echo "installing..." \

&& make install >> /opt/make_mysql.txt

if [ "$?" ==  ];then

    echo "程序安装成功，即将配置并启动"

else

    echo "mysql安装失败"

    exit

fi

rm -f /opt/make_mysql.txt

chown -R mysql:mysql ${conf_file}

cd ${conf_file}

scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

[ -f /etc/my.cnf ] && mv /etc/my.cnf /etc/my.cnf.bak

cp support-files/mysql.server /etc/init.d/mysql

echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

source /etc/profile

systemctl enable mysql

systemctl start mysql && echo "OK mysql启动成功"  || echo "lost mysql启动失败"

#配置mysql_multi函数

install_mysql_multi() {

    mkdir -p $mydir/{$my1,$my2}/{mydata,log}

    chown -R mysql:mysql $mydir/{$my1,$my2}

    cd ${conf_file}

    scripts/mysql_install_db --basedir=${conf_file} --datadir=$mydir/$my1/mydata --user=mysql

    scripts/mysql_install_db --basedir=${conf_file} --datadir=$mydir/$my2/mydata --user=mysql

    touch $mydir/{$my1,$my2}/log/log-err.err

    chown -R mysql:mysql $mydir/{$my1,$my2}/log/log-err.err

cat >${conf_file}/my.cnf<<EOF

[client]

[mysqld]

[mysqld_multi]

user=mysql

password=mysql

mysqld = ${conf_file}/bin/mysqld_safe

mysqladmin = ${conf_file}/bin/mysqladmin

[$mycnf1]

socket = /tmp/$my1.sock

port = ${my1_port}

user=mysql

pid-file = $mydir/$my1/mysqld.pid

datadir = $mydir/$my1/mydata

log-bin= $mydir/$my1/log/binlog

server-id = ${my1_port}

innodb_data_home_dir = $mydir/$my1/mydata

#innodb_data_file_path = ibdata1:12M;ibdata2:100M:autoextend:max:500M

innodb_buffer_pool_size = ${pool_size}

log_error =  $mydir/$my1/log/log-err

expire_logs_days =

bind-address = ${nei_eth}

skip-name-resolve

[$mycnf2]

socket = /tmp/$my2.sock

port = ${my2_port}

user=mysql

pid-file = $mydir/$my2/mysqld.pid

datadir = $mydir/$my2/mydata

log-bin= $mydir/$my2/log/binlog

server-id = ${my2_port}

innodb_data_home_dir = $mydir/$my2/mydata

#innodb_data_file_path = ibdata1:12M;ibdata2:100M:autoextend:max:500M

innodb_buffer_pool_size = ${pool_size}

log_error = $mydir/$my2/log/log-err

expire_logs_days =

bind-address    = ${nei_eth}

skip-name-resolve

EOF

#启动各个实例

${conf_file}/bin/mysqld_multi --defaults-file=${conf_file}/my.cnf start ${my1_port},${my2_port}

#授权数据库用户

source /etc/profile

mysql -S /tmp/${my1}.sock <<EOF

SET PASSWORD = PASSWORD('');

GRANT ALL  ON *.* TO 'htdb'@"%" IDENTIFIED BY '';

GRANT ALL  ON *.* TO 'htdb'@"localhost" IDENTIFIED BY '';

flush privileges;

EOF

mysql -S /tmp/${my2}.sock <<EOF

SET PASSWORD = PASSWORD('');

GRANT ALL  ON *.* TO 'htdb'@"%" IDENTIFIED BY '';

GRANT ALL  ON *.* TO 'htdb'@"localhost" IDENTIFIED BY '';

flush privileges;

EOF

}

#调用函数

install_mysql_multi

PS：

如果mysql启动正常，多实例启动不了，看下err日志报错应该是innodb_data_file_path设置的大小有关，看下mydata下ibdata1的值，然后设置上去应该就好了；

如果是测试环境，可以直接去掉innodb_data_home_dir和innodb_data_file_path；

编了好久终于编译好了这个多实例的脚本，大部分情况下可以直接使用的；

需求不同需要修改一下里面的参数！