MySQL入门02:关于MySQL连接的ABC
【MySQL】如何使用Named Pipe协议(Windows)连接MySQL数据库
【MySQL】如何使用Shared-memory协议(Windows)连接MySQL数据库
【MySQL】如何使用Unix Sockets 协议连接MySQL数据库
【免责声明】文章仅供学习交流,观点代表个人,与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
【MySQL】如何使用Unix Sockets 协议连接MySQL数据库
连接MySQL的协议
在MySQL中可以通过指定 --protocol参数,选择连接MySQL的协议。
连接协议(Connection Protocals):
--protocol={TCP|SOCKET|PIPE|MEMORY}
TCP/IP (ALL)
-Transmission Control Protocal/Internet Protocal
-Connection:local & remote
-Supports Clasic & X protocol
Socket file (Unix including Linux/Mac)
-Connection:local
-Supports Clasic & X protocol
Named Pipe (Win)
-Connection:local
-Supports Clasic
Shared Memory (Win)
-Connection:local
-Supports Clasic
参考:
https://dev.mysql.com/doc/refman/8.0/en/connection-options.html#option_general_protocol
>4.2.3 Command Options for Connecting to the Server
使用Unix Sockets 连接MySQL
使用Unix Sockets连接MySQL避免了网络通信的开销和潜在的安全风险,因此可以提高性能和安全性。
下面介绍如何使用Unix Sockets连接MySQL的步骤。
步骤1:确认MySQL服务器已启用Unix Sockets支持
可以通过检查MySQL配置文件中的socket选项来确认MySQL服务器已启用Unix Sockets支持。
默认情况下,socket选项设置为/var/run/mysqld/mysqld.sock。
例:
ubuntu@mysql-vm:~$ mysql -u root -p -h 127.0.0.1 -e "select @@socket"
Enter password:
+-----------------------------+
| @@socket |
+-----------------------------+
| /var/run/mysqld/mysqld.sock |
+-----------------------------+
或者
mysql> show variables like 'socket';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| socket | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
1 row in set (0.00 sec)
mysql>
或者
查看MySQL配置文件的socket 设置。
sudo more /etc/mysql/mysql.conf.d/mysql.cnf
sudo more /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
#
# * Basic Settings
#
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
步骤2 :客户端使用Unix Sockets连接MySQL服务器。
大多数MySQL客户端都支持Unix Sockets,但是需要使用正确的选项来指定路径。
例如,使用mysql客户端时,可以使用–socket选项来指定Unix Sockets路径。
mysql --socket=/var/run/mysqld/mysqld.sock -u username -p
其中,–socket选项指定Unix Sockets路径,-u选项指定用户名,-p选项提示输入密码。
例1:指定socket路径连接
ubuntu@mysql-vm:/etc/mysql/mysql.conf.d$ mysql --socket=/var/run/mysqld/mysqld.sock -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2023, 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.
mysql> \s
--------------
mysql Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
Connection id: 24
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 2 hours 13 min 49 sec
Threads: 2 Questions: 54 Slow queries: 0 Opens: 158 Flush tables: 3 Open tables: 77 Queries per second avg: 0.006
--------------
mysql>
列2: 不指定socket路径 (Linux环境下,hostname为localhost时候,默认是socket连接)
在Linux环境下,hostname为localhost时候,默认是socket连接
ubuntu@mysql-vm:/etc/mysql/mysql.conf.d$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2023, 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.
mysql> \s
--------------
mysql Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
Connection id: 25
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 2 hours 16 min 42 sec
Threads: 2 Questions: 59 Slow queries: 0 Opens: 158 Flush tables: 3 Open tables: 77 Queries per second avg: 0.007
--------------
mysql>
例3:指定TCP/IP 连接MySQL
ubuntu@mysql-vm:/etc/mysql/mysql.conf.d$ mysql -u root -p --protocol=TCP
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2023, 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.
mysql> \s
--------------
mysql Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
Connection id: 26
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 2 hours 17 min 45 sec
Threads: 2 Questions: 64 Slow queries: 0 Opens: 158 Flush tables: 3 Open tables: 77 Queries per second avg: 0.007
--------------
mysql>
连接成功进入MySQL客户端提示符后,就可以使用标准的MySQL命令来执行查询和操作。
※注意:
使用Unix Sockets连接MySQL需要在同一台机器上运行MySQL服务器和客户端(Sockets仅支持本地连接)。
如果需要在不同的机器上连接MySQL服务器,则需要使用TCP/IP连接(TCP/IP支持本地或远程连接)。文章来源:https://www.toymoban.com/news/detail-668549.html
小结
本文介绍了如何使用Unix Sockets连接MySQL数据库的方法。文章来源地址https://www.toymoban.com/news/detail-668549.html
到了这里,关于【MySQL】如何使用Unix Sockets 协议连接MySQL数据库的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!