MySQL5.7多实例部署

mysql多实例简单来说就是在同一台服务器上用同一套MySQL安装程序，使用不同的my.cnf文件、数据文件同时开启多个不同的服务端口，同时运行多个MySQL服务进程

本文实验环境：

mysql版本：5.7.36二进制包

操作系统版本：CentOS7.6

本文实验在同一台服务器上部署两个实例，部署规划如下

（一）准备MySQL程序包与多实例目录

二进制包下载地址

官方地址：https://downloads.mysql.com/archives/community/

网易源：http://mirrors.163.com/mysql/Downloads/

1.1 下载二进制包（国内建议选择网易源）

网易源：wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
官方源：wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

1.2 解压

# tar -xzvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# mv /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql

1.3 准备好mysql多实例目录

# mkdir -p /mysql/{3306,3307}/{data,sock,logs,pid,tmp,redo}

1.4 目录结构如下

1.5 准备3306实例的配置文件

# cd /mysql/3306
# vim my.cnf

[client]
port=3306
socket=/mysql/3306/sock/mysql.sock    #注意路径
default-character-set=utf8

[mysqld]
server-id=1  #多个实例server-id不能一致
user=mysql
port=3306
log-bin=mysql-bin
binlog_format=ROW
basedir=/usr/local/mysql    #mysql安装路径
datadir=/mysql/3306/data    #注意路径
tmpdir=/mysql/3306/tmp    #注意路径
socket=/mysql/3306/sock/mysql.sock    #注意路径
pid-file=/mysql/3306/pid/mysql.pid    #注意路径
log-error=/mysql/3306/logs/mysql-error.log    #注意路径
slow_query_log_file=/mysql/3306/logs/slow.log    #注意路径
innodb_data_file_path=ibdata1:2G:autoextend
innodb_log_group_home_dir=/mysql/3306/redo    #注意路径
innodb_file_per_table=1

symbolic-links=0
default-storage-engine=INNODB
character_set_server=utf8
collation-server=utf8_general_ci
transaction_isolation=REPEATABLE-READ
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip_ssl               #禁用ssl mode在[mysqld]下配置（独占一行）
default-time-zone='+8:00'
innodb_buffer_pool_instances=1
max_connections=200

1.6 准备3307实例的配置文件

# cd /mysql/3307
# vim my.cnf

[client]
port=3307
socket=/mysql/3307/sock/mysql.sock    #注意路径
default-character-set=utf8

[mysqld]
server-id=2  #多个实例server-id不能一致
user=mysql
port=3307
log-bin=mysql-bin
binlog_format=ROW
basedir=/usr/local/mysql    #mysql安装路径
datadir=/mysql/3307/data    #注意路径
tmpdir=/mysql/3307/tmp    #注意路径
socket=/mysql/3307/sock/mysql.sock    #注意路径
pid-file=/mysql/3307/pid/mysql.pid    #注意路径
log-error=/mysql/3307/logs/mysql-error.log    #注意路径
slow_query_log_file=/mysql/3307/logs/slow.log    #注意路径
innodb_data_file_path=ibdata1:2G:autoextend
innodb_log_group_home_dir=/mysql/3307/redo    #注意路径
innodb_file_per_table=1

symbolic-links=0
default-storage-engine=INNODB
character_set_server=utf8
collation-server=utf8_general_ci
transaction_isolation=REPEATABLE-READ
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip_ssl               #禁用ssl mode在[mysqld]下配置（独占一行）
default-time-zone='+8:00'
innodb_buffer_pool_instances=1
max_connections=200

1.7 此时目录结构如下：

1.8 创建组和用户

# groupadd mysql
# useradd mysql -g mysql -M -s /sbin/nologin

1.9 变更目录权限

# chown -R mysql:mysql /mysql
# chown -R mysql:mysql /usr/local/mysql

（二）初始化实例

2.1 初始化实例前，先修改掉默认的my.cnf文件，否则会一直优先按照默认配置进行参数读取

# mv /etc/my.cnf /etc/my.cnf.bak

2..2 初始化3306实例

# cd /usr/local/mysql/bin
# ./mysqld --defaults-file=/mysql/3306/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3306/data

2.3 查看3306的初始密码

# cat /mysql/3306/logs/mysql-error.log

2.4 初始化3307实例

# cd /usr/local/mysql/bin
# ./mysqld --defaults-file=/mysql/3307/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/3307/data

2.5 查看3307的初始密码

# cat /mysql/3307/logs/mysql-error.log

（三）设置实例启停脚本

3.1 设置3306启停脚本

# vim /mysql/3306/mysql_3306

#!/bin/bash
port=3306    #注意端口号
mysql_user="mysql"
Cmdpath="/usr/local/mysql/bin"
mysql_sock="/mysql/${port}/sock/mysql.sock"
mysqld_pid_file_path=/mysql/${port}/pid/mysqld_${port}.pid


start(){
if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/mysql/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
        sleep 3
else
        printf "MySQL is running...\n"
        exit 1
fi
}


stop(){
if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
else
        printf "Stoping MySQL...\n"
        mysqld_pid=`cat "$mysqld_pid_file_path"`
        if (kill -0 $mysqld_pid 2>/dev/null);then
                kill $mysqld_pid
                sleep 2
        fi
fi
}


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


case "$1" in
  start)
        start
;;
  stop)
        stop
;;
  restart)
        restart
;;
  *)
        printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac

3.2 设置3307启停脚本

# vim /mysql/3307/mysql_3307

#!/bin/bash
port=3307    #注意端口号
mysql_user="mysql"
Cmdpath="/usr/local/mysql/bin"
mysql_sock="/mysql/${port}/sock/mysql.sock"
mysqld_pid_file_path=/mysql/${port}/pid/mysqld_${port}.pid


start(){
if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/mysql/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
        sleep 3
else
        printf "MySQL is running...\n"
        exit 1
fi
}


stop(){
if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
else
        printf "Stoping MySQL...\n"
        mysqld_pid=`cat "$mysqld_pid_file_path"`
        if (kill -0 $mysqld_pid 2>/dev/null);then
                kill $mysqld_pid
                sleep 2
        fi
fi
}


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


case "$1" in
  start)
        start
;;
  stop)
        stop
;;
  restart)
        restart
;;
  *)
        printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac

3.3 此时/mysql目录的结构如下

（四）启动实例

4.1 启动3306实例

# chmod +x /mysql/3306/mysql_3306
# /mysql/3306/mysql_3306 start

4.2 启动3307实例

# chmod +x /mysql/3307/mysql_3307
# /mysql/3307/mysql_3307 start

4.3 查看端口监听3306 3307实例皆已启动

# netstat -nltp|grep mysqld

（五）登录实例

5.1 通过sock文件登录3306实例（-p后面更换成第步骤2.3中查到的密码）

# cp /usr/local/mysql/bin/mysql /usr/bin/
# mysql -S /mysql/3306/sock/mysql.sock -uroot -p'e0Kmhv.o/Vt;'

5.2 3306实例修改root用户默认密码，并设置root用户远程登录权限

# mysql -S /mysql/3306/sock/mysql.sock -uroot -p'e0Kmhv.o/Vt;'

mysql> set password=password('123456');    #修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;    #刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'%' identified by '123456';    #授权root用户远程登录权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)


mysql> exit;
Bye

5.3 通过sock文件登录3307实例（-p后面更换成第步骤2.5中查到的密码）

# mysql -S /mysql/3307/sock/mysql.sock -uroot -p'tORIeutZa8*Y'

5.4 3307实例修改root用户默认密码，并设置root用户远程登录权限

# mysql -S /mysql/3307/sock/mysql.sock -uroot -p'tORIeutZa8*Y'

mysql> set password=password('123456');    #修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;    #刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'%' identified by '123456';    #授权root用户远程登录权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

mysql> exit;
Bye

（六）其他设置

6.1 设置开机自启

# echo "/mysql/3306/mysql_3306 start" >> /etc/rc.d/rc.local
# echo "/mysql/3307/mysql_3307 start" >> /etc/rc.d/rc.local

6.2 脚本启停mysql

启动：

# /mysql/3306/mysql_3306 start
# /mysql/3307/mysql_3307 start

停止：

# /mysql/3306/mysql_3306 stop
# /mysql/3307/mysql_3307 stop

重启：

# /mysql/3306/mysql_3306 restart
# /mysql/3307/mysql_3307 restart

6.3 使用server命令控制实例启停

将实例的启停脚本复制到/etc/init.d目录

# cp /mysql/3306/mysql_3306 /etc/init.d
# cp /mysql/3307/mysql_3307 /etc/init.d

此时就可以通过service命令调用脚本启停实例了

# service mysql_3306 {start|stop|restart}
# service mysql_3307 {start|stop|restart}