（一）MySQL多实例概述
实例是进程与内存的一个概述，所谓MySQL多实例，就是在服务器上启动多个相同的MySQL进程，运行在不同的端口（如3306，3307，3308），通过不同的端口对外提供服务。
由于MySQL在一个实例下面可以创建多个数据库，所以通常在一台服务器上只要安装一个MySQL实例即可满足使用。但在实际使用中，因为服务器硬件资源充足，或者业务需要（比如在一台服务器上创建开发数据库和测试数据库），往往会在一台服务器上创建多个实例。
（二）MySQL部署多实例的方法
MySQL多实例部署主要有以下两种方式：
- 使用官方自带的mysqld_multi来配置管理，特点是使用同一份MySQL配置文件，这种方式属于集中式管理，管理起来较为方便；
- 使用单独的MySQL配置文件来单独配置实例，这种方式逻辑简单，数据库之间没有关联。
本文将对第一种方式进行环境搭建学习。
（三）实验环境
操作系统 ：CentOS Linux release 7.4.1708 (Core)
数据库版本：5.7.24-log
预计划安装4个MySQL实例，规划信息为：
| 实例1 | 实例2 | 实例3 | 实例4 |
|
basedir=/usr/local/mysql datadir=/mysql/3306/data port=3306 socket=/tmp/mysql_3306.sock |
basedir=/usr/local/mysql datadir=/mysql/3307/data port=3307 socket=/tmp/mysql_3307.sock |
basedir=/usr/local/mysql datadir=/mysql/3308/data port=3308 socket=/tmp/mysql_3308.sock |
basedir=/usr/local/mysql datadir=/mysql/3309/data port=3309 socket=/tmp/mysql_3309.sock |
（四）实验过程
（4.1）在安装MySQL之前，需要卸载服务器自带的MySQL包和MySQL数据库分支mariadb的包
[root@masterdb ~]# rpm -qa|grep mysql
[root@masterdb ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.-.el7.x86_64 [root@masterdb ~]# rpm -e mariadb-libs-5.5.-.el7.x86_64 --nodeps
（4.2）依赖包安装
MySQL对libaio 库有依赖性。如果未在本地安装该库，则数据目录初始化和随后的服务器启动步骤将失败
、
# install library
[root@mysql mysql]# yum install libaio
对于MySQL 5.7.19和更高版本：通用Linux版本中增加了对非统一内存访问（NUMA）的支持，该版本现在对libnuma库具有依赖性 。
# install library
[root@mysql mysql]# yum install libnuma
（4.3）创建用户和用户组
[root@masterdb ~]# groupadd mysql
[root@masterdb ~]# useradd -r -g mysql -s /bin/false mysql
（4.4）解压安装包
[root@masterdb ~]# cd /usr/local/
[root@masterdb local]# tar xzvf /root/mysql-5.7.-linux-glibc2.-x86_64.tar.gz # 修改解压文件名，与前面定义的basedir相同
[root@masterdb local]# mv mysql-5.7.-linux-glibc2.-x86_64/ mysql
最终解压结果如下：
[root@masterdb mysql]# ls -l
total
drwxr-xr-x root root Mar : bin
-rw-r--r-- Oct COPYING
drwxr-xr-x root root Mar : docs
drwxr-xr-x root root Mar : include
drwxr-xr-x root root Mar : lib
drwxr-xr-x root root Mar : man
-rw-r--r-- Oct README
drwxr-xr-x root root Mar : share
drwxr-xr-x root root Mar : support-files
（4.5）创建数据文件存放路径
[root@masterdb mysql]# mkdir -p /mysql/{,,,}/data
[root@masterdb mysql]# chown -R mysql:mysql /mysql
[root@masterdb mysql]# cd /mysql
[root@masterdb mysql]# tree
.
├──
│ └── data
├──
│ └── data
├──
│ └── data
└──
└── data
（4.6）创建MySQL参数配置文件
[root@masterdb mysql]# vim /etc/my.cnf [mysqld]
user=mysql
basedir = /usr/local/mysql [mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/usr/local/mysql/mysqld_multi.log [mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/mysql//data
port=
server_id=
socket=/tmp/mysql_3306.sock
log-error = /mysql//error_3306.log [mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/mysql//data
port=
server_id=
socket=/tmp/mysql_3307.sock
log-error=/mysql//error_3307.log [mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/mysql//data
port=
server_id=
socket=/tmp/mysql_3308.sock
log-error=/mysql//error_3308.log [mysqld3309]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/mysql//data
port=
server_id=
socket=/tmp/mysql_3309.sock
log-error = /mysql//error_3309.log
（4.7）初始化数据库
注意，初始化实例的最后一行记录了root的初始密码
# 初始化3306实例
[root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql//data
--28T06::.484174Z [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
--28T06::.689102Z [Warning] InnoDB: New log files created, LSN=
--28T06::.723881Z [Warning] InnoDB: Creating foreign key constraint system tables.
--28T06::.781205Z [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: d29ad574-70ba-11ea-a38f-000c29fb6200.
--28T06::.782195Z [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
--28T06::.783078Z [Note] A temporary password is generated for root@localhost: YuJ6Bi=PtqCJ # 初始化3307实例
[root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql//data
--28T06::.598676Z [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
--28T06::.793277Z [Warning] InnoDB: New log files created, LSN=
--28T06::.829673Z [Warning] InnoDB: Creating foreign key constraint system tables.
--28T06::.886255Z [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: dcccdb2f-70ba-11ea-a565-000c29fb6200.
--28T06::.887571Z [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
--28T06::.890477Z [Note] A temporary password is generated for root@localhost: &s)nYg.e4qx#
[root@masterdb mysql]# # 初始化3308实例
[root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql//data
--28T06::.237714Z [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
--28T06::.442794Z [Warning] InnoDB: New log files created, LSN=
--28T06::.479012Z [Warning] InnoDB: Creating foreign key constraint system tables.
--28T06::.534839Z [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: e28d1d57-70ba-11ea-a5c4-000c29fb6200.
--28T06::.535622Z [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
--28T06::.536387Z [Note] A temporary password is generated for root@localhost: Mz<kr!vsh1yj
[root@masterdb mysql]# # 初始化3309实例
[root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql//data
--28T06::.644331Z [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
--28T06::.840498Z [Warning] InnoDB: New log files created, LSN=
--28T06::.879941Z [Warning] InnoDB: Creating foreign key constraint system tables.
--28T06::.936262Z [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: e8c03ed2-70ba-11ea-a8fb-000c29fb6200.
--28T06::.937179Z [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
--28T06::.937877Z [Note] A temporary password is generated for root@localhost: K.KLa30i-sv3
（4.8）设置环境变量
添加了环境变量，操作系统才能够自己找到mysql、mysqld_multi等命令的位置
[root@masterdb mysql]# vim /etc/profile
# 在文件末尾添加下面信息
export PATH=/usr/local/mysql/bin:$PATH #使环境变量生效
[root@masterdb mysql]# source /etc/profile
（4.9）使用mysqld_multi管理多实例
# 使用mysqld_multi启动3306端口的实例
[root@masterdb mysql]# mysqld_multi start # 使用mysqld_multi启动全部实例
[root@masterdb mysql]# mysqld_multi start # 使用mysqld_multi查看实例状态
[root@masterdb mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
使用mysqld_multi关闭实例较为麻烦，需要配置密码，因此如何关闭各个实例，见后面章节：（六）关闭多实例数据库 。
（五）访问多实例数据库
（5.1）登录MySQL数据库
在安装完成并启动数据库后，需要去访问各个MySQL实例，这里非常有意思，经常会发现无法连接到数据库上，我们不妨看一下几种连接方式：
连接方式一：使用服务器IP地址，无法连接。这里还是比较好理解的，MySQL创建完成后，数据库账号root@localhost只允许本地连接，参数“-h”后面用服务器IP被认为了远程连接，因此无法登陆
[root@masterdb mysql]# mysql -uoot -p -h192.168.10. -P3306
Enter password:
ERROR (HY000): Host 'masterdb' is not allowed to connect to this MySQL server
连接方式二：使用localhost访问数据库，无法连接。我觉得有些匪夷所思，可以看到，MySQL实例使用的socket文件不对
[root@masterdb mysql]# mysql -uroot -p -hlocalhost -P3306
Enter password:
ERROR (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
连接方式三：使用127.0.0.1访问数据库，可以连接。有些难以理解，理论上127.0.0.1和localhost是对应的，127.0.0.1可以访问数据库，但是localhost却无法访问
[root@masterdb mysql]# mysql -uroot -p -h127.0.0. -P3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7. MySQL Community Server (GPL) Copyright (c) , , 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> exit
Bye
连接方式四：使用socket文件连接，可以正常访问
[root@masterdb mysql]# mysql -S /tmp/mysql_3306.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7. Copyright (c) , , 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>
（5.2）修改数据库root@localhost密码
初次登陆MySQL数据库，需要修改root密码，否则无法正常使用
[root@masterdb mysql]# mysql -S /tmp/mysql_3306.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. -- 无法查询
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. -- 修改root@localhost用户的密码
mysql> alter user root@localhost identified by '';
Query OK, 0 rows affected (0.00 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> exit
Bye
（六）关闭多实例数据库
（6.1）直接使用mysqld_multi来关闭实例
使用mysqld_multi关闭多实例数据库目前来看比较麻烦，需要在my.cnf文件的[mysqld_multi]模块里面配置用户密码，并且各个数据库的用户密码都需要相同，否则无法关闭。
我们可以看一下使用mysqld_multi来关闭数据库实例的日志：
[root@masterdb mysql]# cat /usr/local/mysql/mysqld_multi.log # 当执行：mysqld_multi report时，显示所有数据库均在运行
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
mysqld_multi log file version 2.16; run: Sat Mar ::
# 当执行：mysqld_multi stopt时，mysqld_multi会调用mysqladmin去关闭数据库，使用的是[mysqld_multi]里面配置的账号密码，此时3306的密码是正确的，
# 其它都是错误的，因此3306关闭成功，而其它端口的实例因为密码错误而连接数据库失败，自然没有关闭数据库
Stopping MySQL servers mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqld_multi log file version 2.16; run: Sat Mar :: # 结果：仅仅关闭了密码正确的3306端口数据库
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
mysqld_multi log file version 2.16; run: Sat Mar ::
既然知道了mysqld_multi是调用mysqladmin来关闭数据库的，那最好的办法还是直接使用mysqladmin来关闭各个数据库了，下面演示使用mysqladmin来关闭数据库实例。
（6.2）使用mysqladmin来关闭实例
[root@masterdb mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
[root@masterdb mysql]#
[root@masterdb mysql]#
[root@masterdb mysql]# cd
[root@masterdb ~]# mysqladmin -h127.0.0. -uroot -p -P3306 shutdown
Enter password:
[root@masterdb ~]#
[root@masterdb ~]# mysqladmin -h127.0.0. -uroot -p -P3307 shutdown
Enter password:
[root@masterdb ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
最终关闭了3306和3307数据库。
【结束】
|
相关文档集合： 1.在一台Linux服务器上安装多个MySQL实例（一）--使用mysqld_multi方式 |