官方文档： https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html
https://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html
1.MySQL多实例介绍
- 配置文件安装路径不能相同
- 数据库目录不能相同
- 启动脚本不能同名
- 端口不能相同
- socket文件的生成路径不能相同
MySQL 5.7 多实例安装部署实例
1. 背景
MySQL数据库的集中化运维，可以通过在一台服务器上，部署运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的，每个实例的datadir, port, socket, pid都是不同的。
2. 多实例特点
* 有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。
* 资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降。
3. 环境 [ 关闭SeLinux ]
[root@MySQL ~]# cat /etc/RedHat-release
CentOS release 6.9 (Final)
[root@MySQL ~]# uname -r
2.6.32-696.3.2.el6.x86_64
[root@MySQL ~]# getenforce
Disabled
4. 部署 [ 4个实例 ]
* 下载 MySQL 5.7 二制包 [ 推荐官方下载 ] 此下载版本大于5.7.5
[root@MySQL ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
* 解压 MySQL 5.7 二进制包到指定目录
[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
* 创建 MySQL 软链接
[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql
* 创建 MySQL 用户
[root@MySQL ~]# useradd -r -s /sbin/nologin mysql
* 在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]
[root@MySQL ~]# mkdir -v /usr/local/mysql/mysql-files
mkdir: created directory `/usr/local/mysql/mysql-files'
* 创建多实例数据目录
[root@MySQL ~]# mkdir -vp /data/mysql_data{1..4}
mkdir: created directory `/data'
mkdir: created directory `/data/mysql_data1'
mkdir: created directory `/data/mysql_data2'
mkdir: created directory `/data/mysql_data3'
mkdir: created directory `/data/mysql_data4'
* 修改 MySQL 二进制包目录的所属用户与所属组
1 [root@MySQL ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64
* 修改 MySQL 多实例数据目录与 数据导入/导出专放目录的所属用户与所属组
[root@MySQL ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}
* 配置 MySQL 配置文件 /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /tmp/mysql_multi.log
[mysqld1]
# 设置数据目录 [多实例中一定要不同]
datadir = /data/mysql_data1
# 设置sock存放文件名 [多实例中一定要不同]
socket = /tmp/mysql.sock1
# 设置监听开放端口 [多实例中一定要不同]
port = 3306
# 设置运行用户
user = mysql
# 关闭监控
performance_schema = off
# 设置innodb 缓存大小
innodb_buffer_pool_size = 32M
# 设置监听IP地址
bind_address = 0.0.0.0
# 关闭DNS 反向解析
skip-name-resolve = 0
[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[mysqld3]
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3
port = 3308
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[mysqld4]
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4
port = 3309
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
* 初始化各个实例 [ 初始化完成后会自带随机密码在输出日志中 ]
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4
* 各实例开启 SSL 连接
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4
* 复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]
[root@MySQL ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
* 添加脚本执行权限
[root@MySQL ~]# chmod +x /etc/init.d/mysqld_multi
* 添加进service服务管理
[root@MySQL ~]# chkconfig --add mysqld_multi
5. 启动测试
* 查个多实例状态
[root@MySQL ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
* 启动多实例
[root@MySQL ~]# /etc/init.d/mysqld_multi start
* 查看多实例状态
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
* 查看实例监听端口
[root@MySQL ~]# netstat -lntp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2673/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2676/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 2679/mysqld
tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 2682/mysqld
6. 连接测试
* 实例1
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p'z+Ilo*>s:3kw'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = '123456';
Query OK, 0 rows affected (0.00 sec)
* 实例2
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2 -p'b*AHUrTgu1rl'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = '123456';
Query OK, 0 rows affected (0.00 sec)
mysqld_multi安装多个mysql实例
一个机器上安装多个mysql实例，除了将每个实例在不同的目录下编译安装，为每个实例指定不同的端口，socket，配置文件，安装目录等。还可以使用mysqld_multi方式。
mysqld_multi是mysql设计的专门用来管理不同端口，不同socket的mysql实例的工具。它可以同时启动，停止多个mysql实例，也可以查看这些实例的状态。
mysqld_multi会去配置文件my.cnf(或者是—defaults-file参数指定的配置文件)中检查每一个以[mysqldN]为开头的一组配置(N这里是数字)。这个N是mysqld_multi用来区分每一个mysql实例用的。用法如下：
mysqld_multi start|stop|reload|report N1, N2, N3,… //reload等价于stop和start。
mysqld_multi需要的信息记录在配置文件my.cnf中的[mysqld_multi]组下。
注意，为了能用mysqld_multi统计管理所有的mysql实例，该管理账号必须存在于所有的mysql实例上，而且密码也一致。
以下是我做的测试以及安装步骤：
1、 编写配置文件my.cnf，如下：
[root@CentOSMysql1 etc]# cat my.cnf
[mysqld_multi]
mysqld=mysqld_safe
#表示用mysqld_safe来启动mysql
mysqladmin=/usr/local/mysql/bin/mysqladmin
#指定mysqladmin工具的路径
log=/usr/local/mysql/mysqld_multi.log
#指定mysqld_multi的日志文件
user=root
pass=123456
#指定使用mysqld_multi工具的用户和密码
[mysqld3307]
#指定实例编号为3307的一些配置参数
datadir=/opt/data3307
port=3307
socket=/tmp/mysql.sock3307
[mysqld3308]
#指定实例编号为3308的一些配置参数
datadir=/opt/data3308
port=3308
socket=/tmp/mysql.sock3308
[mysqld56]
#指定实例编号为56的一些配置参数
basedir=/usr/local/mysql56/
datadir=/opt/data56
socket=/tmp/mysql.sock56
port=3310
2、 准备安装之前工作
(1)查看是否有mysql用户和mysql组
[root@CentOSMysql1 etc]# cat group |grep mysql
[root@CentOSMysql1 etc]# groupadd mysql
[root@CentOSMysql1 mysql]# cat /etc/passwd|grep mysql
[root@CentOSMysql1 etc]# useradd -g mysql mysql
(2)下载mysql的安装文件。这里我下载的都是linux generic类型的
[root@CentOStest1 opt]# wget http://downloads.mysql.com/archives/get/file/mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz
[root@CentOStest1 opt]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
(3)解压这些安装包
[root@CentOSMysql1 opt]# tar -zxf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz
[root@CentOSMysql1 opt]# tar -zxf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz
3、 安装编号为3307的mysql实例，这里我计算用mysql5.7的版本。
[root@CentOSMysql1 opt]#mv mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local
[root@CentOSMysql1 opt]#cd /usr/local/
[root@CentOSMysql1 local]#ln –s mysql-5.7.16-linux-glibc2.5-x86_64 mysql
[root@CentOSMysql1 local]# cd mysql
[root@CentOSMysql1 mysql]# chown -R mysql:mysql .
[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3307
//这条命令执行成功之后，会在结尾打印出临时密码，如下：
2017-12-21T02:08:32.598340Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-21T02:08:33.002195Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-12-21T02:08:33.090315Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-12-21T02:08:33.179548Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d898cae9-e5f3-11e7-9e66-000c299323ae.
2017-12-21T02:08:33.181477Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-12-21T02:08:33.184759Z 1 [Note] A temporary password is generated for root@localhost: L;0/g:XaweYi
[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup --datadir=/opt/data3307
到这里，编号为3307的实例初步安装完毕，还剩下一个步骤就是修改临时密码。改密码之前需要把其所在mysql服务启动起来，这里我们用mysqld_multi启动，先查看下状态，如下：
[root@CentOSMysql1 bin]# mysqld_multi report
-bash: mysqld_multi: command not found
解决方法为将mysqld_multi工具所在的路径添加到环境变量中去：
[root@CentOSMysql1 bin]# vi /etc/profile
//在文件尾部添加以下语句
PATH=/usr/local/mysql/bin:$PATH
"/etc/profile" 79L, 1828C written
[root@CentOSMysql1 bin]# source /etc/profile
环境变量生效之后重试
[root@CentOSMysql1 mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
MySQL server from group: mysqld56 is not running
[root@CentOSMysql1 mysql]# mysqld_multi start 3307
[root@CentOSMysql1 mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is not running
MySQL server from group: mysqld56 is not running
可以看到编号3307的mysql实例已经启动，然后修改root密码。因为my.cnf里面配置了root用户的密码是123456，所以现在我们将root的密码改为“123456”:
[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3307
Enter password:
//这里输出刚才安装时出现的临时密码。
mysql> set password=password("123456");
到这里，编号为3307的实例安装完毕。
4、 安装编号为3308的mysql实例，这里仍用mysql5.7的版本，但是端口号以及datadir要根据my.cnf中的来。
[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3308
2017-12-21T02:36:31.294011Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-21T02:36:31.779696Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-12-21T02:36:31.881428Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-12-21T02:36:31.948774Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c138a63c-e5f7-11e7-8f7a-000c299323ae.
2017-12-21T02:36:31.950062Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-12-21T02:36:31.953758Z 1 [Note] A temporary password is generated for root@localhost: whKyXCtxz9;g
//临时密码同样在尾处生成。
[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup --datadir=/opt/data3308
拉起编号3308的mysql实例服务
[root@CentOSMysql1 mysql]# mysqld_multi start 3308
修改编号3308的mysql实例中root用户的密码为“123456”。
[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3308
Enter password:
//这里输出刚才安装时出现的临时密码。
mysql> set password=password("123456");
到这里，编号为3308的实例安装完毕。
5、 安装编号为56的mysql实例，这里使用mysql5.6的版本，由安装目录，端口号以及datadir根据my.cnf中的来。
[root@CentOSMysql1 opt]#mv mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local
[root@CentOSMysql1 opt]#cd /usr/local/
[root@CentOSMysql1 local]#ln –s mysql-5.6.34-linux-glibc2.5-x86_64 mysql56
[root@CentOSMysql1 local]# cd mysql56
[root@CentOSMysql1 mysql56]# chown -R mysql:mysql .
[root@CentOSMysql1 mysql56]# scripts/mysql_install_db --user=mysql --datadir=/opt/data56
这一步和mysql5.7有些不一样，不会生成临时密码。Mysql5.6的临时密码默认是空。
[root@CentOSMysql1 local]# mysqld_multi start 56
拉起编号56的mysql实例服务
[root@CentOSMysql1 local]# mysql -uroot -p -S /tmp/mysql.sock56
修改编号56的mysql实例中root用户的密码为“123456”。
mysql> set password="123456";
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
mysql> set password=password("123456");
到这里，编号为56的实例安装完毕。
6、 最后，试试用mysqld_multi统一起停所有的mysql实例
[root@CentOSMysql1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld56 is running
[root@CentOSMysql1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld56 is running
[root@CentOSMysql1 ~]# mysqld_multi stop
[root@CentOSMysql1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
MySQL server from group: mysqld56 is not running
[root@CentOSMysql1 ~]# mysqld_multi start
[root@CentOSMysql1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld56 is running
About Me
.............................................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................
