MySQL多实例安装

一、环境

基于Centos7.4

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core)

系统配置

关闭防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
关闭selinux
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/selinux/config
SELINUX=disabled

二、下载软件包

网址：http://mirrors.163.com/

[root@localhost ~]# wget -c http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

三、创建用户和组

[root@localhost ~]# groupadd mysql -r -g 306
[root@localhost ~]# useradd -r -g mysql -s /bin/false -u 306 mysql

四、解压、创建软链接

解压
[root@localhost ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C  /usr/local/
创建软链接
[root@localhost ~]# ln -sv /usr/local/mysql-5.7.37-linux-glibc2.12-x86_64/  /usr/local/mysql
‘/usr/local/mysql’ -> ‘/usr/local/mysql-5.7.37-linux-glibc2.12-x86_64/’

五、准备数据目录

[root@localhost ~]# mkdir -p /data/330{6..8}/data
[root@localhost ~]# tree /data
/data
├── 3306
│   └── data
├── 3307
│   └── data
└── 3308
    └── data

方法一、每一MySQL多实例的配置文件

[root@localhost ~]# vim /data/3306/my.cnf
编辑内容：
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
server-id = 3306

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid
将/data/3306/my.cnf分别复制到/data/3307/my.cnf、/data/3308/my.cnf
[root@localhost ~]# cp /data/3306/my.cnf /data/3307/my.cnf
[root@localhost ~]# cp /data/3306/my.cnf /data/3308/my.cnf
分别修改/data/3307/my.cnf和/data/3308/my.cnf文件内容
[root@localhost ~]# sed 's/3306/3307/g' /data/3307/my.cnf
[client] 
port = 3307 
socket = /data/3307/mysql.sock 

[mysqld] 
user = mysql 
port = 3307 
socket = /data/3307/mysql.sock 
basedir = /usr/local/mysql 
datadir = /data/3307/data 
server-id = 3307 

[mysqldump] 
quick 
max_allowed_packet = 16M 

[mysqld_safe] 
log-error=/data/3307/mysql_3307.err 
pid-file=/data/3307/mysqld.pid
[root@localhost ~]# sed -i 's/3306/3307/g' /data/3307/my.cnf
[root@localhost ~]# sed -i 's/3306/3308/g' /data/3308/my.cnf

修改文件权限

[root@localhost ~]# chown -R mysql.mysql /data/330{6..8}
[root@localhost ~]# find /data/330{6..8} -name mysqld

配置环境变量

[root@localhost ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
立即生效
[root@localhost ~]# source /etc/profile.d/mysql.sh

初始化多实例

[root@localhost ~]# mysqld --initialize --datadir=/data/3306/data --basedir=/usr/local/mysql --user=mysql
 aVQ.WIsio7/h
[root@localhost ~]# mysqld --initialize --datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql
CmFfeqjiZ3;R
[root@localhost ~]# mysqld --initialize --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql
Gd#:ykQ:h9ZB

MySQL启动多实例

若直接启动会报错（没有错误日志）
在这之前先创建相应的错误日志

[root@localhost ~]# touch /data/3306/mysql_3306.err /data/3307/mysql_3307.err /data/3308/mysql_3308.err
[root@localhost ~]# chown mysql.mysql /data/3306/mysql_3306.err /data/3307/mysql_3307.err /data/3308/mysql_3308.err
启动多实例
[root@localhost ~]# mysqld_safe --defaults-file=/data/3306/my.cnf &
[root@localhost ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@localhost ~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
查看相关进程
[root@localhost ~]# netstat -lnpt | grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      21382/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      21572/mysqld        
tcp6       0      0 :::3306                 :::*                    LISTEN      21192/mysqld 

登录

[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock
修改密码
[root@localhost ~]# mysqladmin -uroot -p'aVQ.WIsio7/h' password 'MySQL@123' -S /data/3306/mysql.sock
[root@localhost ~]# mysqladmin -uroot -p'CmFfeqjiZ3;R' password 'MySQL@123' -S /data/3307/mysql.sock
[root@localhost ~]# mysqladmin -uroot -p'Gd#:ykQ:h9ZB' password 'MySQL@123' -S /data/3308/mysql.sock

方法二、使用官方mysqld_multi

使用同一个配置文件

1.创建多实例配置文件

查看帮助

[root@localhost ~]# mysqld_multi --example

编辑配置文件

mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
pass       = MySQL@123

[mysqld3306]
socket     = /data/3306/mysql.sock
port       = 3306
pid-file   = /data/3306/mysqld.pid
datadir    = /data/3306/data
log-error  = /data/3306/mysql_3306.err

[mysqld3307]
socket     = /data/3307/mysql.sock
port       = 3307
pid-file   = /data/3307/mysqld.pid
datadir    = /data/3307/data
log-error  = /data/3307/mysql_3307.err

[mysqld3308]
socket     = /data/3308/mysql.sock
port       = 3308
pid-file   = /data/3308/mysqld.pid
datadir    = /data/3308/data
!includedir /etc/my.cnf.d
log-error  = /data/3308/mysql_3308.err

2.修改MySQL多实例文件权限

[root@localhost ~]# chown -R mysql.mysql /data/330{6..8}

3.配置MySQL 环境变量

[root@localhost ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
立即生效
[root@localhost ~]# source /etc/profile.d/mysql.sh

4.初始化多实例

[root@localhost ~]# mysqld --initialize --datadir=/data/3306/data --basedir=/usr/local/mysql --user=mysql
 aVQ.WIsio7/h
[root@localhost ~]# mysqld --initialize --datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql
CmFfeqjiZ3;R
[root@localhost ~]# mysqld --initialize --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql
Gd#:ykQ:h9ZB

5.启动多实例

[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# netlnp -lnupt | grep mysqld
-bash: netlnp: command not found
[root@localhost ~]# netstat -lnupt | grep mysqld
tcp6       0      0 :::3307                 :::*                    LISTEN      21903/mysqld  
[root@localhost ~]# mysqld_multi start 3306-3308
[root@localhost ~]# 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

6.授权用户

授权用户可以查看帮助文档

[root@localhost ~]# mysql -uroot -p'MySQL@123' -S /data/3306/mysql.sock -e "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'MySQL@123'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p'MySQL@123' -S /data/3307/mysql.sock -e "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'MySQL@123'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p'MySQL@123' -S /data/3308/mysql.sock -e "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'MySQL@123'"

7.登录多实例

[root@localhost ~]# mysql -uroot -p'MySQL@123' -S /data/3306/mysql.sock

8.停止多实例

[root@localhost ~]# mysqld_multi stop 3306,3308