https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html
多实例安装即是一台服务器安装多个mysql实例；这样可以充分利用硬件资源；通过mysqld_multi程序即可。
例如我们现在有一台配置较高的机器：32C，512G，8SSD做RIAND5，而4C 8G才是主流服务器配置。这么一台服务器仅仅跑一个mysql是非常奢侈的。现在我们就可以在这台机器上同时部署三个mysql实例：mysqld3306、mysqld3307、mysqld3308。
mysqld_multi配置
1、配置mysqld_multi节点；mysqld_safe命令启动mysql、mysqladmin停止mysql、mysqld_multi.log日志（有问题都可以到这里排查）。关于mysqld_safe、mysqladmin见 https://www.jianshu.com/p/a5f24d0f736e。当然这里使用mysqld=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqd也是可以的，只不过mysqld没有守护的功能。
2、配置三个实例：mysqld3306、mysqld3307、mysqld3308；分别指定port、datadir、socket。
3、此时的[mysqld]节点下的配置是mysqld3306、mysqld3307、mysqld3308三个实例所共享的。
4、[client] 节点配置了默认客户端连接参数。我这里配置了root用户、密码还有指定socket为/tmp/mysql.sock3306。则随后可以直接使用mysql命令连接到mysql3306实例而不需要额外指定参数。
[mysqld]
user = mysql
basedir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64
log_error=error.log
plugin-load=validate_password.so
[mysqld_multi]
mysqld=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe
mysqladmin=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqladmin
log=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/mysqld_multi.log
[mysqld3306]
port=3306
datadir=/mdata/mysql57-3306
socket= /tmp/mysql.sock3306
[mysqld3307]
port=3307
datadir=/mdata/mysql57-3307
socket= /tmp/mysql.sock3307
[mysqld3308]
port=3308
datadir=/mdata/mysql57-3308
socket= /tmp/mysql.sock3308
[client]
user=root
password=1111aaA_
socket= /tmp/mysql.sock3306
[mysql]
prompt=(\\u@\\h) [\\d]>\\
启动过程
1、以mysqld3306实例为例，初始化数据如下。注意指定datadir。
[root@localhost bin]# ./mysqld --initialize --datadir=/mdata/mysql57-3306
注意：[mysqld]下配置了plugin-load=validate_password.so 密码插件的话就不能使用--initialize-insecure，请使用--initialize。否则初始化后密码是空的，然后连不上。
mysqld3306实例生成密码如下：
[root@localhost mysql57-3306]# cat error.log
2021-05-01T03:22:15.245117Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-01T03:22:16.562503Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-01T03:22:16.692390Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-01T03:22:16.750221Z 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: 6e1b5ce4-aa2c-11eb-befe-000c292882e9.
2021-05-01T03:22:16.751331Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-01T03:22:17.412496Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-01T03:22:17.657496Z 1 [Note] A temporary password is generated for root@localhost: Ye-7i!4-gtw)
2、使用mysqld_multi 命令启动实例，start后面跟的3306和[mysqld3306]中的3306对应起来，这里可以使用任何名字。如[mysqld1]就这样启动：./mysqld_multi start 1。
[root@localhost bin]# ./mysqld_multi start 3306
3、查看状态
[root@localhost bin]# ./mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
4、查看进程。如下启动mysqld3306 实例后就出现了两个进程：mysqld_safe 和 mysqld 。
[root@localhost bin]# ps -ef |grep mysql
root 11675 1 0 10:55 pts/0 00:00:00 /bin/sh /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe --port=3306 --datadir=/mdata/mysql57-3306 --socket=/tmp/mysql.sock3306
mysql 11827 11675 2 10:55 pts/0 00:00:00 /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld --basedir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64 --datadir=/mdata/mysql57-3306 --plugin-dir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/lib/plugin --user=mysql --log-error=error.log --pid-file=localhost.localdomain.pid --socket=/tmp/mysql.sock3306 --port=3306
root 11874 7035 0 10:55 pts/0 00:00:00 grep --color=auto mysql
5、继续初始化和启动其它进程。
./mysqld_multi start 3307
./mysqld_multi start 3308
6、最终的mysql相关进程有6个。如下，分别3个mysqld、3个mysqld_safe 。
[root@localhost bin]# ps -ef |grep mysql
root 11675 1 0 10:55 pts/0 00:00:00 /bin/sh /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe --port=3306 --datadir=/mdata/mysql57-3306 --socket=/tmp/mysql.sock3306
mysql 11827 11675 0 10:55 pts/0 00:00:00 /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld --basedir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64 --datadir=/mdata/mysql57-3306 --plugin-dir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/lib/plugin --user=mysql --log-error=error.log --pid-file=localhost.localdomain.pid --socket=/tmp/mysql.sock3306 --port=3306
root 11948 1 0 10:56 pts/0 00:00:00 /bin/sh /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe --port=3307 --datadir=/mdata/mysql57-3307 --socket=/tmp/mysql.sock3307
mysql 12104 11948 2 10:56 pts/0 00:00:00 /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld --basedir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64 --datadir=/mdata/mysql57-3307 --plugin-dir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/lib/plugin --user=mysql --log-error=error.log --pid-file=localhost.localdomain.pid --socket=/tmp/mysql.sock3307 --port=3307
root 12199 1 0 10:57 pts/0 00:00:00 /bin/sh /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe --port=3308 --datadir=/mdata/mysql57-3308 --socket=/tmp/mysql.sock3308
mysql 12352 12199 7 10:57 pts/0 00:00:00 /home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld --basedir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64 --datadir=/mdata/mysql57-3308 --plugin-dir=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/lib/plugin --user=mysql --log-error=error.log --pid-file=localhost.localdomain.pid --socket=/tmp/mysql.sock3308 --port=3308
root 12398 7035 0 10:57 pts/0 00:00:00 grep --color=auto mysql
7、 netstat -anl 看下端口。3306、3307、3308 都被占用了。
[root@localhost tmp]# netstat -anl
Active Internet connections (servers and established)
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 192.168.6.128:22 192.168.6.1:1042 ESTABLISHED
tcp 0 0 192.168.6.128:22 192.168.6.1:1410 ESTABLISHED
tcp 0 0 192.168.6.128:22 192.168.6.1:2135 ESTABLISHED
tcp 0 0 192.168.6.128:22 192.168.6.1:1043 ESTABLISHED
tcp6 0 0 :::3306 :::* LISTEN
tcp6 0 0 :::3307 :::* LISTEN
tcp6 0 0 :::3308 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
udp 0 0 127.0.0.1:323 0.0.0.0:*
udp6 0 0 ::1:323 :::*
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags Type State I-Node Path
unix 2 [ ACC ] STREAM LISTENING 75933 /tmp/mysql.sock3306
unix 3 [ ] DGRAM 11503 /run/systemd/notify
unix 2 [ ACC ] STREAM LISTENING 48932 /tmp/mysql.sock3307
unix 2 [ ] DGRAM 11505 /run/systemd/cgroups-agent
unix 2 [ ACC ] STREAM LISTENING 19191 /run/systemd/private
unix 2 [ ACC ] STREAM LISTENING 76809 /tmp/mysql.sock3308
unix 2 [ ] DGRAM 33496
8、连接后修改密码。
使用sock连接。如mysql3306则指定/tmp/mysql.sock3306。
并set password = '1111aaA_'; 修改当前用户密码。
[root@localhost bin]# ./mysql -S /tmp/mysql.sock3306 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost bin]# ./mysql -S /tmp/mysql.sock3306 -pYe-7i!4-gtw)
-bash: !4: event not found
[root@localhost bin]# ./mysql -S /tmp/mysql.sock3306 -p'Ye-7i!4-gtw)'
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.33
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
(root@localhost) [(none)]>set password = '1111aaA_';
9、随后可以直接输入mysql登录了。
[root@localhost /]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.33 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
(root@localhost) [(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7.33, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.33 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock3306
Uptime: 14 min 19 sec
Threads: 2 Questions: 22 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.025
--------------
拓展：
1、同理，其它的mysql3307、mysql3308也是一样的操作。另外想要直接使用mysql -S /tmp/mysql.sock3307、mysql -S /tmp/mysql.sock3308 直接登录。那么需要将其它两个实例的密码都改为1111aaA_。
2、有人喜欢使用mysqld –defaults-file=/etc/my.cnf 的defaults-file参数来指定特定的my.cnf配置文件来实现多实例安装。但是这样的话实例一多就不好管理了。所以推荐使用mysqld_multi。
3、多实例IO竞争问题怎么解决？
对于资源管理、资源调度问题可以通过操作系统层面的技术解决。
LXC
docker
Cgroup
4、MHA、keepalive 这些集群软件请不要使用多实例安装，这种高可用软件肯定是要部署到多个机器上。
5、每次初始化会不会对已经存在的实例有影响？
初始化实例时请指定datadir：
./mysqld --initialize --datadir=/mdata/mysql57-3306
当然不指定的话，就算原来目录已经存在数据。那么此次执行会失败的。并不会对已经存在的数据目录产生影响。
[root@localhost bin]# ./mysqld --initialize --datadir=/mdata/mysql57-3308
2021-05-01T04:18:01.028139Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-01T04:18:01.029870Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-05-01T04:18:01.029901Z 0 [ERROR] Aborting
6、使用mysqld_multi stop 3306 停止实例时可能会失败，因为实际上是去调用mysqladmin来停止，mysqladmin会去读取[client]下的用户名和密码 ，若不符合自然是关闭不了。可以选择使用kill -0 pid来停止，或者去修改[client]配置:
[client]
user=root
password=1111aaA_
socket= /tmp/mysql.sock3306
随后关闭是成功的。
[root@localhost bin]# ./mysqld_multi stop 3307
[root@localhost bin]# ./mysqld_multi report 3307
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
另外有一点一定要注意下，mysql官方文档是把登录用户名密码配置在[mysqld_multi]节点下而不是[client]节点下，当然这样也可以。
[mysqld_multi]
mysqld=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqld_safe
mysqladmin=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/bin/mysqladmin
log=/home/mysql5.7/mysql-5.7.33-linux-glibc2.12-x86_64/mysqld_multi.log
user=root
password=1111aaA_
这里有人把用户名密码放到[mysqld_multi] 后执行mysqld_multi stop 关不掉了。尝试把password改为pass就行了。当然我没有遇到这个问题，我照样使用password还能正常关闭。
7、选择单机多实例，还是建立多个虚拟机？
这就是不同的资源调度的方式。
像淘宝的RDS云数据库就是使用LXC Container 来进行资源隔离的，其实就是安装多个实例，这种方式性能会更好。当然虚拟机也有它的好处。
8、一个实例建立多个数据库。或者多个数据库分别放到不同实例下。
这个还是得看业务。
网友评论