mysql指定登陆端口却登陆到另一个端口——mysql的两种登陆方式

尝试登录3308端口

[root@lzl mysql]# mysql -P 3308 -uroot -poracle
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 23
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

mysql> 
mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3306  |
| report_host              |       |
| report_password          |       |
| report_port              | 3306  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.15 sec)

看上去是登陆了，但是登进去发现是端口是3306

感觉很奇怪，--help查看了-P确实是指定端口的

 

加个空格试下

[root@lzl mysql]# mysql  -uroot -poracle -P 3308
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 25
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3306  |
| report_host              |       |
| report_password          |       |
| report_port              | 3306  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.00 sec)

还是3306

 

后来想到可能是socket的问题，指定socket登陆

[root@lzl mysql]# mysql  -uroot -poracle -P 3308 -S/tmp/mysql3308.sock

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3308  |
| report_host              |       |
| report_password          |       |
| report_port              | 3308  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.00 sec)

登陆成功！！！

 

试一下不指定port，仅指定socket

[root@lzl mysql]# mysql  -uroot -p  -S/tmp/mysql3308.sock       
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3308  |
| report_host              |       |
| report_password          |       |
| report_port              | 3308  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.00 sec)

mysql> 

也可以

 

问题：为什么当我-P指定端口3308时登陆3306？

当我指定-P3308时，mysql去找了默认的socket /tmp/mysql.sock，这个socket是我3306的，于是就登陆了3306。

 

总结：

1.mysql登陆有2种方式，socket和tcp/ip。

2.socket一般都是本地登陆使用，socket是本地文件，每个mysql实例一个socket。比如mysql -uroot -poracle这样，默认不指定socket的话用的是/tmp/mysql.sock这个文件。只要不指定-h，都会用socket方式登录
3.还有一种方式是tcp/ip，一般都是远程登陆使用。只要指定-h host，mysql就会使用tcpip去登陆数据库。即使是本机指定-h，也是tcp/ip登陆

 

所有我做了些实验：
如果从远程登陆，应该是直接指定-P port即可，不需要指定socket的
[root@lzl ~]# mysql -h192.168.43.6 -P3308 -uroot -poracle
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 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3308  |
| report_host              |       |
| report_password          |       |
| report_port              | 3308  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.06 sec)

mysql> 

 

所以，现在有2种方式登录3308端口数据库

mysql -hlocalhost -P3308 -uroot -poracle

mysql -S/tmp/mysql3308.sock -uroot -poracle