MySQL常用內建變數

Federico發表於2019-01-30
MySql變數

  MySQL用很多常用的內建變數，掌握這些內建變數後對於我們快速獲取當前MySQL的配置有很大幫助，下面就來列舉幾個常用的變數。

  • 檢視當前MySQL版本號資訊。show variables like 'version';
MariaDB [(none)]> show variables like 'version';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| version       | 5.5.60-MariaDB |
+---------------+----------------+
1 row in set (0.00 sec)
  • 檢視當前MySQL服務監聽的埠號資訊。show variables like 'port';
MariaDB [(none)]> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
  • 檢視當前MySQL資料的預設儲存路徑資訊。show variables like 'datadir';
MariaDB [(none)]> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
  • 檢視當前MySQL使用的引擎資訊。show variables like 'storage_engine';
MariaDB [(none)]> show variables like 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.01 sec)
  • 檢視當前MySQL的最大連線數資訊。show variables like 'max_connections';
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

修改max_connectionis變數值：

MariaDB [(none)]> set global max_connections=65535;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 65535 |
+-----------------+-------+
1 row in set (0.00 sec)

  掌握一些必要的變數後發現在使用過程中修改，是不用重啟MySQL服務的，這樣我們可以先進入MySQL將最大連線數進行修改，後續加入到配置檔案中，選擇維護時間將MySQL重啟，配置就可以永久生效了。

相關文章