前文提到mac電腦啟動mysql的方式為
brew services start mysql
其實我們也可以執行可執行檔(/usr/local/mysql/bin/mysqld)的方式來啟動
(補充:Windows系統的安裝目錄可能為:C:\Program Files\MySQL\MySQL Server8.0[這邊替換成你的版本號])
(base) ➜ ~ cd /usr/local/mysql/bin
(base) ➜ bin mysqld
2021-09-03T03:08:18.913099Z 0 [System] [MY-010116] [Server] /usr/local/Cellar/mysql/8.0.26/bin/mysqld (mysqld 8.0.26) starting as process 40516
2021-09-03T03:08:18.999095Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2021-09-03T03:08:19.224466Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-09-03T03:08:19.648026Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-09-03T03:08:19.875857Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-09-03T03:08:19.889520Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-09-03T03:08:19.910900Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-09-03T03:08:19.911360Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-09-03T03:08:19.914875Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-09-03T03:08:19.915505Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-09-03T03:08:19.921172Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/usr/local/var/mysql' in the path is accessible to all OS users. Consider choosing a different directory.
2021-09-03T03:08:19.964443Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-09-03T03:08:19.964512Z 0 [System] [MY-010931] [Server] /usr/local/Cellar/mysql/8.0.26/bin/mysqld: ready for connections. Version: '8.0.26' socket: '/tmp/mysql.sock' port: 3306 Homebrew.
這邊要注意的是如果要關掉服務,我試了ctrl+Z、C都無法關閉,因此我是直接kill -9 40516把程序砍掉的。我想這應該有更好的做法,如有大大們告訴我該怎麼做實在萬分感謝。
進入今天的正題,mysql有兩種變數
有2種調整mysql系統變數的方式
(base) ➜ ~ mysqld --default-storage-engine=MyISAM
可以看到預設儲存引擎變更為MyISAM
mysql> show variables like 'default%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
| default_password_lifetime | 0 |
| default_storage_engine | MyISAM |
| default_table_encryption | OFF |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
+-------------------------------+-----------------------+
7 rows in set (0.02 sec)
只在當次啟動生效唷!因次每次都要重新指定參數,很麻煩呀,因此常態性的設定我們可以考慮第二種方式-使用設定檔
設定檔需要自行建立,而系統吃設定檔也是有其固定目錄順序
這邊以mac電腦列出以下幾個為例(由上到下優先生效)
(1)/etc/my.cnf
(2)/etc/mysql/my.cnf
(3)defaults-extra-file[mysqld --default-extra-file=你的設定檔目錄]
(4)~/.my.cnf[特定於使用者的選項]
我這邊在~/.my.cnf建立設定檔(只對目前使用者生效)
(base) ➜ ~ vi ~/.my.cnf
//檔案內容為
[server]
default-storage-engine=MyISAM
max-connections=10
砍掉程序再重新啟動登入後可以看到設定值已調整
mysql> show variables like 'default%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
| default_password_lifetime | 0 |
| default_storage_engine | MyISAM |
| default_table_encryption | OFF |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
+-------------------------------+-----------------------+
7 rows in set (0.01 sec)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
主要就是這兩種方式
此外你可以針對不同的用戶端設定不同的參數(透過GLOBAL[全域] or SESSION[當前的連接]的方式)
像這樣子
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.04 sec)
mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.02 sec)
mysql> set session default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.01 sec)
只變更了當前連接(SESSION)的儲存引擎
而要改變global則是將session替換成global即可
mysql> set global default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.03 sec)
要注意!並不是所有參數都有GLOBAL與SESSION的範圍
像max_connections只在GLOBAL而不在SESSION,而insert_id則只在SESSION不在GLOBAL唷。
用途是顯示系統狀態的,我們不能調整,跟系統變數一樣也有分GLOBAL與SESSION。
可以查看一些重要資訊:
mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+-------------------+-------+
4 rows in set (0.04 sec)
mysql> show status like 'Innodb_rows_updated%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Innodb_rows_updated | 0 |
+---------------------+-------+
1 row in set (0.01 sec)
Innodb_rows_updated(更新了多少筆以Innodb為引擎表的資料)
由於變數相當的多,今天只是初略提到一些最基本的系統變數、狀態變數
會在未來實際上遇到有需要調整變數的情況再來更新此篇補充說明。
喔耶~可以去吃蒼蠅了