前言

下午三点，天刚蒙蒙亮！

当团队中出现多个项目，你并不想把所有的项目库都放到一个实例下，这时需要建立多个 mysql 实例，分类管理更多的数据库。
mysql官网提供了两种管理多实例的方法，我们不采用 mysqld_multi 的方式。

一、准备配置

预计划安装2个 MySQL 实例，规划信息为：
实例1：

[mysqld]
port = 3306
basedir=/usr/local/mysql/
datadir=/mysql/3306/data
lower_case_table_names=1
innodb_buffer_pool_size=128M
socket=/tmp/mysql_3306.sock
user=mysql
symbolic-links=0

[client]
port = 3306
socket=/tmp/mysql_3306.sock

[mysql]
socket=/tmp/mysql_3306.sock

[mysqladmin]
socket=/tmp/mysql_3306.sock

[mysqldump]
socket=/tmp/mysql_3306.sock

[mysql_safe]
log-error=/mysql/3306/log/mysqld.log
pid-file=/mysql/3306/run/mysqld/mysqld.pid

实例2：

[mysqld]
port = 3307
basedir=/usr/local/mysql/
datadir=/mysql/3307/data
lower_case_table_names=1
innodb_buffer_pool_size=128M
socket=/tmp/mysql_3306.sock
user=mysql
symbolic-links=0

[client]
port = 3307
socket=/tmp/mysql_3307.sock

[mysql]
socket=/tmp/mysql_3307sock

[mysqladmin]
socket=/tmp/mysql_3307.sock

[mysqldump]
socket=/tmp/mysql_3307.sock

[mysql_safe]
log-error=/mysql/3307/log/mysqld.log
pid-file=/mysql/3307/run/mysqld/mysqld.pid

二、检查环境依赖

在安装 MySQL 之前，需要卸载服务器自带的 MySQL 包和 MySQL 数据库分支 mariadb 的包

1、检查是否安装mysql

rpm -qa|grep mysql
rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64

rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps

2、依赖类库检查

AIO 检查

rpm -qa|grep libaio
yum install libaio

libnuma 检查

对于 MySQL 5.7.19 和更高版本：通用 Linux 版本中增加了对非统一内存访问（ NUMA ）的支持，该版本现在对 libnuma 库具有依赖性。

#libnuma或numactl 未考证不同系统对应类库名称
yum install libnuma

三、安装

1、创建用户和用户组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

2、解压安装包

cd /usr/local
tar xzvf /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64.tar

# 修改解压文件名，与前面定义的basedir相同
mv mysql-5.7.24-linux-glibc2.12-x86_64/ mysql

解压结果：

ls -l
 total 36
 drwxr-xr-x  2 root root   4096 Mar 28 13:48 bin
 -rw-r--r--  1 7161 31415 17987 Oct  4  2018 COPYING
 drwxr-xr-x  2 root root     55 Mar 28 13:48 docs
 drwxr-xr-x  3 root root   4096 Mar 28 13:48 include
 drwxr-xr-x  5 root root    230 Mar 28 13:48 lib
 drwxr-xr-x  4 root root     30 Mar 28 13:48 man
 -rw-r--r--  1 7161 31415  2478 Oct  4  2018 README
 drwxr-xr-x 28 root root   4096 Mar 28 13:48 share
 drwxr-xr-x  2 root root     90 Mar 28 13:48 support-files

3、创建数据文件存放路径

mkdir -p /mysql/{3306,3307}/data
chown -R mysql:mysql /mysql 
cd /mysql

tree
.
├── 3306
│   └── data
├── 3307

创建 MySQL 参数配置文件分别为3306、3307、3308各自创建一个 my.cnf 配置文件。配置文件位置为：

tree /mysql
/mysql
├── 3306
│   ├── data
│   └── my.cnf
├── 3307
│   ├── data
│   └── my.cnf

4、初始化数据库

注意，初始化结束的最后一行记录了root的密码

# 初始化3306数据库
/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/my.cnf \
--initialize --basedir=/usr/local/mysql/ \
--datadir=/mysql/3306/data

# 初始化3307数据库
/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf \
--initialize --basedir=/usr/local/mysql/ \
--datadir=/mysql/3307/data

初始化打印类似：

2020-03-28T08:19:11.202256Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-03-28T08:19:11.352108Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-03-28T08:19:11.383671Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-03-28T08:19:11.441325Z 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: cdab4062-70cc-11ea-aedf-000c29ea7752.
2020-03-28T08:19:11.442190Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-03-28T08:19:11.442995Z 1 [Note] A temporary password is generated for root@localhost: XE4kl>x4fo?v

5、设置环境变量

vim /etc/profile
# 在文件末尾添加下面信息
export PATH=/usr/local/mysql/bin:$PATH

#使环境变量生效
source /etc/profile

三、启动数据库

1、脚本启动

# 经过测试，mysql在初始化的时候新生成的部分文件权限为root,所以最好在启动之前重新将datadir路径授权给mysql
chown -R mysql:mysql /mysql

# 启动MySQL数据库实例
nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/my.cnf --user=mysql &
nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql &

检查进程，确认 MySQL 数据库状况

# 确认MySQL进程
ps -ef|grep mysql
mysql     11092   1371  0 16:36 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql
mysql     11182   1371  7 16:37 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/my.cnf --user=mysql
root      11211   1371  0 16:37 pts/0    00:00:00 grep --color=auto mysql

# 确认端口使用情况
netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN     
tcp6       0      0 :::3307                 :::*                    LISTEN     
tcp6       0      0 :::3308                 :::*                    LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 ::1:25                  :::*                    LISTEN     
tcp6       0      0 :::3306                 :::*                    LISTEN

2、启动及开机启动

编辑 /etc/rc.local ,在 touch /var/lock/subsys/local 行下添加

vi /etc/rc.local
#mysql
nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/my.cnf --user=mysql 2>&1 > /dev/null &
nohup /usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql 2>&1 > /dev/null &

※参考数据与延伸阅读

mysql官网-Running Multiple MySQL Instances on One Machine。https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html。
mysql官网-mysqld_multi — Manage Multiple MySQL Servers。https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

Linux下Mysql多实例安装

前言