在一台测试服务器上部署了2个实例，一个端口是默认的3306，另一个端口是3376。MySQL的版本是5.6.35
[root@MySQL56_L1 ~]# ps -ef | grep mysql | grep -v grep mysql 11176 9876 0 13:31 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf mysql 11262 9876 0 13:34 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3376/my3376.cnf
my3376.cnf的部分参数配置如下
[root@MySQL56_L1 ~]# more /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock # The MySQL server [mysqld] # Basic port = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data tmpdir = /data/mysql/mysql3306/tmp socket = /tmp/mysql.sock
my3376.cnf的部分参数配置如下
[root@MySQL56_L1 ~]# more /data/mysql/mysql3376/my3376.cnf [client] port = 3376 socket = /tmp/mysql3376.sock # The MySQL server [mysqld] # Basic port = 3376 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3376/data tmpdir = /data/mysql/mysql3376/tmp socket = /tmp/mysql3376.sock
两个数据库中的账号及密码如下
(product)root@localhost [(none)]> select host, user, password from mysql.user ; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | 127.0.0.1 | root | | +-----------+------+----------+
当使用账号、密码、端口的方式方式登录到端口为3376的实例时，发现登录的却是3306端口的实例下
[root@MySQL56_L1 ~]# mysql -uroot -p -P3376 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. (product)root@localhost mysql.sock [(none)]>
请注意上面代码块中的红色加粗字体，这是端口为3306实例的socket文件，3376端口实例使用socket方式登录如下：
[root@MySQL56_L1 ~]# mysql -S /tmp/mysql3376.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. (product)root@localhost mysql3376.sock [(none)]>
在需要登录到3376端口实例下时，已经明确指定了端口，为什么还是登录到3306端口实例下呢？
难道是因为没有加上“-h”?
[root@MySQL56_L1 ~]# mysql -hlocalhost -uroot -p -P3376 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. (product)root@localhost mysql.sock [(none)]>
可以看到，使用“-hlocalhost”是还是登录到3306端口实例下，若是修改为“-h IP”，又会怎么样呢？
[root@MySQL56_L1 ~]# mysql -h127.0.0.1 -uroot -p -P3376 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. (product)root@127.0.0.1 3376 [(none)]>
这次就能如愿登录到所期望的3376端口实例下了。
小结：安装了多实例的情况下，需要登录到特定端口实例下的方法
1、使用“-S” 参数，通过指定实例下的socket文件来登录到指定的实例
2、使用“-h”参数，注意，这里必须是使用'TCP/IP'的方式，不能是'localhost'，因为'localhost'会使用默认的socket文件登录
那么，为什么指定了端口“-P3376”，还是会登录到3306端口实例下呢？
原因是因为没有指定“-h”使用'TCP/IP'方式来登录时，是默认使用socket方式登录，问题又来了，是使用哪个socket文件呢？
[root@MySQL56_L1 ~]# mysql --verbose --help | grep socket --protocol=name The protocol to use for connection (tcp, socket, pipe, -S, --socket=name The socket file to use for connection. The buffer size for TCP/IP and socket communication. socket /tmp/mysql.sock
这个文件是在哪里指定呢？
[root@MySQL56_L1 ~]# mysql --verbose --help | grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注：以上的4个“my.cnf”后面的参数会覆盖前面的。
若是产生怀疑，可以试验一下在“~/.my.cnf”中添加如下代码
[root@MySQL56_L1 ~]# vi ~/.my.cnf [client] socket = /tmp/mysql3306.sock
再查看一下mysql读取的socket文件
[root@MySQL56_L1 ~]# mysql --verbose --help | grep socket --protocol=name The protocol to use for connection (tcp, socket, pipe, -S, --socket=name The socket file to use for connection. The buffer size for TCP/IP and socket communication. socket /tmp/mysql3306.sock
从上面的测试就可以看出是读取socket文件的路径
注意：测试完之后需要改回正确的socket文件名
看完上面的说明，现在应该能明白为什么为什么指定了“-P 3376”，却登录到了3306端口是实例下了。其实，正确的应该是这个路径“/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf”下的socket参数对应的是哪个端口，就会登录到哪个端口的实例下。
看完了这么多，可能还是不明白socket是什么？
socket是用于同一台主机的进程间通讯（IPC），不需要经过网络协议栈，不需要打包拆包、计算校验和、维护序号和应答等，只是将应用层数据从一个进程拷贝到另外一个进程，消息既不会丢失也不会顺序错乱。可用于两个没有亲缘关系的进程，是全双工的。
以上，若有错误，请不吝指出。
