【MySQL】如何使用Shared-memory協議(Windows)連接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

使用Shared-memory協議(Windows)連接MySQL

在Windows環境中支持共享內存（Shared-memory）連接連接MySQL。
如果服務器啟用了Shared-memory連接，客戶端就可以通過使用–protocol=MEMORY選項使用Shared-memory連接MySQL。

使用Shared-memory協議(Windows)連接MySQL時，能夠提高MySQL的性能，具體測評請參考如下官方鏈接的介紹。

參考：
Improving the Performance of MySQL on Windows
https://dev.mysql.com/blog-archive/improving-the-performance-of-mysql-on-windows/

步驟1：確認MySQL服務器已啟用Shared-memory連接

查看是否啟用Shared-memory連接。

mysql> show variables like 'shared_memory';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| shared_memory | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)mysql>

如果值是ON，則表示啟用；OFF表示禁用狀態。

啟動Shared-memory連接方法

1. 打開配置文件my.ini，將shared-memory變量和shared-memory-base-name變量前的注釋去掉并保存。

例：

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipeshared-memoryshared-memory-base-name=MYSQL

※　my.ini文件的位置可以通過【mysql --help】命令中的Default options內容查看。
例：

Default options are read from the following files in the given order:
C:\windows\my.ini C:\windows\my.cnf C:\my.ini C:\my.cnf E:\Soft\MySQL8.0\my.ini E:\Soft\MySQL8.0\my.cnf```

2. 重新啟動MySQL服務器。

net stop <MySQL服務名>
net start <MySQL服務名>

例：

C:\Users\Administrator>net stop mysql80
MySQL80 服務正在停止.
MySQL80 服務已成功停止。C:\Users\Administrator>net start mysql80
MySQL80 服務正在啟動 ..
MySQL80 服務已經啟動成功。

3.查看shared-memory連接啟用狀態。

C:\Users\Administrator>mysql  -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, 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>  show variables like 'shared_memory';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| shared_memory | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)mysql>

步驟2：客戶端使用shared-memory連接MySQL服務器。

例1：客戶端使用shared-memory連接MySQL服務器

通過指定–protocol=MEMORY，客戶端可以使用shared-memory連接MySQL服務器。

C:\Users\Administrator>mysql --protocol=MEMORY -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, 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.28 for Win64 on x86_64 (MySQL Community Server - GPL)Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         8.0.28 MySQL Community Server - GPL
Protocol version:       10
Connection:             Shared memory: MYSQL
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               0
Binary data as:         Hexadecimal
Uptime:                 1 min 58 secThreads: 2  Questions: 12  Slow queries: 0  Opens: 134  Flush tables: 3  Open tables: 53  Queries per second avg: 0.101
--------------mysql>

例2：默認連接MySQL服務器

啟用shared-memory連接后，不指定–protocol時本地連接也會使用shared-memory連接MySQL服務器。

C:\Users\Administrator>mysql  -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, 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.28 for Win64 on x86_64 (MySQL Community Server - GPL)Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         8.0.28 MySQL Community Server - GPL
Protocol version:       10
Connection:             Shared memory: MYSQL
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               0
Binary data as:         Hexadecimal
Uptime:                 3 min 52 secThreads: 2  Questions: 17  Slow queries: 0  Opens: 134  Flush tables: 3  Open tables: 53  Queries per second avg: 0.073
--------------mysql>

常見錯誤

當服務器shared memory連接未啟用的時候，使用shared memory連接會報如下的錯誤：

C:\Users\Administrator>mysql --protocol=MEMORY -u root -p
Enter password: ********
ERROR 2038 (HY000): Can't open shared memory; client could not create request event (2)

可參考如上的方法在服務器端進行相應的配置。

小結

本文介紹了在Windows環境中，如何使用shared memory協議連接MySQL數據庫的方法。