一、简介
多实例就是一台服务器上开启多个不同的服务端口(默认3306),运行多个mysql的服务进程,这此服务进程通过不同的socket监听不同的服务端口来提供各在的服务,所有实例之间共同使用一套MySQL的安装程序,但各自使用不同的配置文件、启动程序、数据文件,在逻辑上是相对独立的。
多实例主要作用是:充分利用现有的服务器硬件资源,为不同的服务提供数据服务,但是如果某个实例并发比较高的,同样是会影响到其它实例的性能。
二、安装多实例环境准备
安装前需要先安装mysql,但是只需将安装过程进行到make install即可(编译安装,不用初始化),如果使用二进制安装,只需解压软件包即可。
1、系统环境
[root@linux-node1 ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
[root@linux-node1 ~]# uname -r
3.10.0-693.el7.x86_64
2、下载二进制安装程序和创建mysql用户
[root@linux-node1 ~]# cd /usr/local/src/
[root@linux-node1 src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
[root@linux-node1 src]# groupadd mysql
[root@linux-node1 src]# useradd mysql -s /sbin/nologin -g mysql -M
3、创建多实例的数据目录
[root@linux-node1 src]# mkdir -p /data/{3306,3307}
[root@linux-node1 src]# tree /data/
/data/
+-- 3306
+-- 3307
2 directories, 0 files
三、安装MySQL多实例
1、解压软件
[root@linux-node1 src]# ls
mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
[root@linux-node1 src]# tar -zxf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
2、拷贝配置文件
[root@linux-node1 src]# cd mysql-5.6.39-linux-glibc2.12-x86_64
[root@linux-node1 mysql-5.6.39-linux-glibc2.12-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf
[root@linux-node1 mysql-5.6.39-linux-glibc2.12-x86_64]# cp support-files/mysql.server /data/3306/mysqld
[root@linux-node1 mysql-5.6.39-linux-glibc2.12-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf
[root@linux-node1 mysql-5.6.39-linux-glibc2.12-x86_64]# cp support-files/mysql.server /data/3307/mysqld
[root@linux-node1 ~]# mv /usr/local/src/mysql-5.6.39-linux-glibc2.12-x86_64 /usr/local/mysql
[root@linux-node1 ~]# chown mysql.mysql /data -R
3、修改配置文件与启动文件
##配置文件/data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3307/data
#log_long_format
#log-error = /data/3307/error3307.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
server-id = 3
[mysqld_safe]
log-error=/data/3307/error3307.log
pid-file=/data/3307/mysqld.pid
##启动程序文件/data/3307/mysqld
#!/bin/sh
init port=3307
mysql_user="root"
mysql_pwd="root"
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup
start_mysql() {
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
stop_mysql() {
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
restart_mysql() {
printf "Restarting MySQL...\n"
stop_mysql
sleep 2
start_mysql
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql { start | stop | restart }\n"
esac
备注:其它的配置可参考配置文件进行修改即可
四、多实例初始化操作
[root@linux-node1 ~]# cd /data 3306
[root@linux-node1 3306]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
##初始化成功后,会在数据目录下产生一个数据目录data和一些文件
[root@linux-node1 3306]# ls /data/3306/data/
mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index performance_schema test
##另一个实例的初始化
[root@linux-node1 ~]# cd /data 3307
[root@linux-node1 3307]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
[root@linux-node1 3307]# ls /data/3307/data/
mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index performance_schema test
五、启动多实例并登录
1、启动服务
[root@linux-node1 ~]# /data/3306/mysqld start
Starting MySQL...
[root@linux-node1 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)
[root@linux-node1 ~]# /data/3307/mysql
start Starting MySQL...
[root@linux-node1 ~]# lsof -i :3307
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)
2、检查端口
[root@linux-node1 ~]# netstat -lntup | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqld
3、登陆多实例数据库
[root@linux-node1 ~]# mysql -S /data/3306/mysql.sock
mysql> create database data_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> \q
Bye
[root@linux-node1 ~]# mysql -S /data/3307/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.05 sec)
mysql> \q
Bye
备注:成功登陆,并在3306实例中创建数据库,但是3307实例上查看并没有创建过的数据,说明两个实例是独立的