mysql变量

MySql Variables

SHOW VARIABLES

什么是mysql变量

MySQL Variables在这里实际上指的是一些参数，用于初始化或设定数据库对系统资源的占用，文件存放位置等。在新安装好系统后，就已经被初始化好了。但是我们有时候不想采取默认值，所以就需要对这些值做出改变。

mysql变量的分类

MySQL的变量分为两种：

• 系统变量： 配置MySQL服务器的运行环境，可以用show variables查看

  按其作用域的不同可以分为以下两种：

  • 分为全局（GLOBAL）级：对整个MySQL服务器有效，注意：对新的连接有效
  • 会话（SESSION或LOCAL）级：只影响当前会话

  有些变量同时拥有以上两个级别，MySQL将在建立连接时用全局级变量初始化会话级变量，但一旦连接建立之后，全局级变量的改变不会影响到会话级变量。

• 状态变量： 监控MySQL服务器的运行状态，可以用show status查看，不可以被修改。

显示和设置变量

SHOW VARIABLES; 在 MySQL 中默认显示的是全局（global）的系统变量。

使用 SET SESSION 语句（但通常省略 SESSION 关键字，因为默认情况下 SET 语句设置的是会话变量）可以设置会话变量。

查询和设置全局变量（查询系统变量）

# 查询全局变量方法1
show variables like 'general_log';

# 查询全局变量方法2
SELECT @@general_log;

# 设置全局变量
set global general_log=on;

查询和设置会话变量（用户定义变量）

这个命令用于查询用户定义的变量值。用户定义的变量是以@开头的变量，它们在客户端连接期间存在，并在客户端断开连接时消失。它们不是MySQL服务器的系统变量，而是用户在SQL查询中定义和使用的变量。

例如，你可以先设置一个用户定义的变量：

SET @myVar = 100;

然后查询它的值：

SELECT @myVar;

error_log变量

mysql8.0通过配置文件中设置此变量启用错误日志输出，默认值为error_log=stderr

设置错误日志输出到/var/lib/mysql/error.log文件中，注意：mysql8.0会把通过innodb_print_all_deadlocks启用的死锁日志输出到该文件中。

log_error = error.log

innodb_status_output变量

mysql8.0启用这个变量后会每15秒输出一次show engine innodb status\G日志到log_error设置的错误文件中

set global innodb_status_output=ON;

innodb_status_output_locks变量

mysql8.0启用这个变量后，show engine innodb status\G命令的TRANSACTIONS栏目中会打印当前等待状态的锁对应的表中详细的持有锁信息。

启用变量

set global innodb_status_output_locks=ON;

未启用变量前的show engine innodb status\G的TRANSACTIONS输出

------------
TRANSACTIONS
------------
Trx id counter 1827
Purge done for trx's n:o < 1824 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421657787778640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787776216, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787775408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787774600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 1826, ACTIVE 148 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 13, OS thread handle 140182639568640, query id 320 172.20.36.1 root updating
/* ApplicationName=IntelliJ IDEA 2023.2.5 */ update course set name='xxx' where id=5
------- TRX HAS BEEN WAITING 141 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `testdb`.`course` trx id 1826 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000000714; asc       ;;
 2: len 7; hex 81000001060110; asc        ;;
 3: len 4; hex 6a617661; asc java;;
 4: len 4; hex 80000005; asc     ;;

------------------
--------

启用变量后的show engine innodb status\G的TRANSACTIONS输出

------------
TRANSACTIONS
------------
Trx id counter 1827
Purge done for trx's n:o < 1824 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421657787778640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787776216, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787775408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421657787774600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 1826, ACTIVE 191 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 13, OS thread handle 140182639568640, query id 320 172.20.36.1 root updating
/* ApplicationName=IntelliJ IDEA 2023.2.5 */ update course set name='xxx' where id=5
------- TRX HAS BEEN WAITING 184 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `testdb`.`course` trx id 1826 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000000714; asc       ;;
 2: len 7; hex 81000001060110; asc        ;;
 3: len 4; hex 6a617661; asc java;;
 4: len 4; hex 80000005; asc     ;;

------------------
# course表持有锁信息
TABLE LOCK table `testdb`.`course` trx id 1826 lock mode IX
TABLE LOCK table `testdb`.`course` trx id 1826 lock mode IS
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `testdb`.`course` trx id 1826 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000000714; asc       ;;
 2: len 7; hex 81000001060110; asc        ;;
 3: len 4; hex 6a617661; asc java;;
 4: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `testdb`.`course` trx id 1826 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000000714; asc       ;;
 2: len 7; hex 81000001060110; asc        ;;
 3: len 4; hex 6a617661; asc java;;
 4: len 4; hex 80000005; asc     ;;

--------

innodb_print_all_deadlocks变量

启用此变量后，当发生死锁时打印死锁信息到log_error配置的错误日志文件中。

set global innodb_print_all_deadlocks=ON;