Mysql有那些变数？如何设定？以及有那些值得认识的配置选项呢？

前文提到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)

• Threads_cached 第一天有提到mysql断开连接的时候不会马上释放掉执行绪，而是存在快取池内供下一个人使用。这参数就是快取池内可以给别人使用的空闲执行绪。
• Threads_connected跟Threads_running这两个可能会混淆一起说明。Threads_connected是连接的数量，而Threads_running是并发数量(也就是说只有正在执行CRUD动作时去查询可能才看得出来)。
• Threads_created 建立过的执行绪。这边测试了一下执行语法新增资料库或是表格也都会增加此数量。

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为引擎表的资料)

由於变数相当的多，今天只是初略提到一些最基本的系统变数、状态变数
会在未来实际上遇到有需要调整变数的情况再来更新此篇补充说明。

喔耶～可以去吃苍蝇了