mysql tcp_MySQL登录之socket与TCP

在一台测试服务器上部署了2个实例，一个端口是默认的3306，另一个端口是3376。MySQL的版本是5.6.35

[root@MySQL56_L1 ~]# ps -ef | grep mysql | grep -v grepmysql11176 9876 0 13:31 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

mysql11262 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= 3306socket = /tmp/mysql.sock

# The MySQL server

[mysqld]

# Basic

port= 3306user=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= 3376socket = /tmp/mysql3376.sock

# The MySQL server

[mysqld]

# Basic

port= 3376user=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 connectionid is 5Server 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 clearthe 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 connectionid is 3Server 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 clearthe 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 connectionid is 6Server 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 clearthe 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 connectionid is 4Server 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 clearthe 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 | grepsocket--protocol=name The protocol to use forconnection (tcp, socket, pipe,-S, --socket=name The socket file to use forconnection.

The buffer sizefor TCP/IP and socket communication.

socket/tmp/mysql.sock

这个文件是在哪里指定呢？

[root@MySQL56_L1 ~]# mysql --verbose --help | grepmy.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 | grepsocket--protocol=name The protocol to use forconnection (tcp, socket, pipe,-S, --socket=name The socket file to use forconnection.

The buffer sizefor 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)，不需要经过网络协议栈，不需要打包拆包、计算校验和、维护序号和应答等，只是将应用层数据从一个进程拷贝到另外一个进程，消息既不会丢失也不会顺序错乱。可用于两个没有亲缘关系的进程，是全双工的。

以上，若有错误，请不吝指出。