mysql基础之数据库变量（参数）管理

数据库的数据存放路径：
[root@ren7 mysql]# pwd

/var/lib/mysql

[root@ren7 mysql]# ls

aria_log.00000001  ibdata1      multi-master.info   ren       test1

aria_log_control   ib_logfile0  mysql               ren7.err  test2

help               ib_logfile1  mysql.sock          ren7.pid  yan

ib_buffer_pool     ibtmp1       performance_schema  tc.log

配置文件的路径：
[root@ren7 ~]# vim /etc/my.cnf.d/server.cnf

　　如果我们想要查看mysql的某些属性或状态，我们可以查看mysql的变量，我们也可以通过改变这些变量的值，来设置mysql的工作属性，我们可以把mysql变量理解成mysql的参数或者属性值。

一、mysql中变量的分类

mysql中的变量分为系统变量与状态变量

　　系统变量：配置了mysql的运行环境属性

　　状态变量：显示了mysql运行过程中的状态信息

系统变量从作用域划分，又可以分为全局变量和会话变量

　　全局级别的变量：global variables，作用域为整个mysql服务器

　　会话级别的变量：session variables，作用域为当前会话

我们可以通过修改这些系统变量达到设置mysql属性的目的

状态变量也分为全局级别与会话级别，但是用户无法设置状态变量，只能查看

二、查看变量

1、配置文件参数

　　/etc/my.cnf.d/server.cnf
　　获取可选参数列表：
　　　　mysqld --verbose --help

2、获取运行中的mysql进程使用各服务器参数及其值
MariaDB [(none)]> show global variables;　　#全局变量
MariaDB [(none)]> show session variables;　　#会话变量（或者：show variables;）
note:

　　其中有些参数支持运行时更改，会立即生效，有些参数不支持，且只能通过修改配置文件，并且重启服务才能生效；
　　有些参数作用域是全局的，且不可以改变，有些可以为用户提供单独的设置。

3、根据变量名查看变量值

查看所有跟“版本”相关的属性值：
MariaDB [ren]> show global variables like '%version%';

+-------------------------+----------------------------------+

| Variable_name           | Value                            |

+-------------------------+----------------------------------+

| innodb_version          | 5.7.27                           |

| protocol_version        | 10                               |

| slave_type_conversions  |                                  |

| version                 | 10.2.26-MariaDB                  |

| version_comment         | MariaDB Server                   |

| version_compile_machine | x86_64                           |

| version_compile_os      | Linux                            |

| version_malloc_library  | system                           |

| version_ssl_library     | OpenSSL 1.0.2k-fips  26 Jan 2017 |

| wsrep_patch_version     | wsrep_25.24                      |

+-------------------------+----------------------------------+

10 rows in set (0.00 sec)

查看字符集相关的全局设置：
MariaDB [ren]> show global variables where variable_name like '%character%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | latin1                     |

| character_set_filesystem | binary                     |

| character_set_results    | latin1                     |

| character_set_server     | latin1                     |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

查看某些日志是否开启或关闭：

MariaDB [ren]> show global variables where variable_name like '%log%' and value='on';

+-----------------------------------+-------+

| Variable_name                     | Value |

+-----------------------------------+-------+

| binlog_annotate_row_events        | ON    |

| binlog_optimize_thread_scheduling | ON    |

| innodb_log_checksums              | ON    |

| innodb_log_compressed_pages       | ON    |

| innodb_log_optimize_ddl           | ON    |

| log_slow_admin_statements         | ON    |

| log_slow_slave_statements         | ON    |

| relay_log_purge                   | ON    |

| sql_log_bin                       | ON    |

+-----------------------------------+-------+

4、另一种查看单个全局系统变量或者会话系统变量的语法（需要准确的指明变量名称）

格式：SELECT @@[global.|session.]system_var_name

查看全局系统变量pid_file的值：
MariaDB [ren]> select @@global.pid_file;

+-------------------------+

| @@global.pid_file       |

+-------------------------+

| /var/lib/mysql/ren7.pid |

+-------------------------+

查看会话变量warning_count的值：
MariaDB [ren]> select @@session.warning_count;

+-------------------------+

| @@session.warning_count |

+-------------------------+

|                       0 |

+-------------------------+

5、查询mysql运行中的统计信息或状态数据（查看状态变量）

MariaDB [ren]> show status;

状态变量：用来保存mysql运行中的统计数据的变量

show global status;

show session status;

三、修改变量值/设定变量值

修改数据库变量的方法：

1、运行时修改对应的变量：

运行时修改，修改是否立即生效取决于修改的变量类型：

（1）运行时修改global级别的变量，对修改之前建立的会话没有影响，仅对修改后新建立的会话有效；

（2）运行时修改session级别的变量，仅对当前会话有效，且立即生效；如果想要使设定永久有效，需要设置配置文件。

可以通过set命令，修改运行时的对应的变量：

MariaDB [ren]> select @@session.autocommit;

+----------------------+

| @@session.autocommit |

+----------------------+

|                    1 |

+----------------------+

1 row in set (0.00 sec)

MariaDB [ren]> set @@session.autocommit=0;

Query OK, 0 rows affected (0.00 sec)

MariaDB [ren]> select @@session.autocommit;

+----------------------+

| @@session.autocommit |

+----------------------+

|                    0 |

+----------------------+

1 row in set (0.00 sec)

设置系统变量的语法：

SET GLOBAL var_name=value;

SET @@GLOBAL.var_name-value;

SET SESSION var_name=value;

SET @@SESSION.var_name=value;

不指定级别表示默认设置会话级别的变量：SET var_name=value;

2、通过配置文件修改：需重启后生效，但是永久有效

四、查看表状态

MariaDB [ren]> show table status like 'students'\G;

*************************** 1. row ***************************

           Name: students

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 15

 Avg_row_length: 1092

    Data_length: 16384

Max_data_length: 0

   Index_length: 32768

      Data_free: 0

 Auto_increment: 21

    Create_time: 2019-09-01 14:58:39

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

标识                说明

Name                表名

Engine                表的存储引擎类型，在旧版本中，该列的名字叫 Type ， 而不是 Engine。

Row_format            行的格式。对于 MyISAM 表，可选的值为 Dynamic、Fixed或者 Compressed。

Dynamic             的长度是可变的，一般包含可变长度的字段，如 VARCHAR 或 BLOB。

Fixed                 的行长度则是固定的，只包含固定长度的列，如 CHAR 和 INTEGER。

Compressed             的行则只在压缩表中存在。

Rows                表中的行数。对于 MyISAM 和其他一些存储引擎，该值是精确的，但对于 InnoDB，该值是估计值。

Avg_row_length        平均每行包含的字节数

Data_length            表数据大小(以字节为单位)

Max_data_length        表数据的最大容量，该值和存储引擎有关。

Index_length        索引的大小(以字节为单位)

Data_free            对于 MyISAM 表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被 INSERT 利用到的空间。

Auto_increment        下一个 AUTO_INCREMENT 的值

Create_time            表的创建时间

Update_time            表数据的最后修改时间

Check_time            使用 CHECK TABLE 命令或者 myisamchk 工具最后一次检查表的时间。

Collation            表的默认字符集和字符列排列规则

Checksum            如果启用，保存的是整个表的实时校验和。

Create_options        创建表时的其他选项

Comment                该列包含了一些其他的额外信息。对于 MyISAM 表，保存的是表在创建时带的注释。对于 InnoDB 表，则保存的是 InnoDB 表空间的剩余空间信息。如果是一个视图，则该列包含 “VIEW” 的文本字样。

Max_index_length    最大索引长度

Temporary            是否是临时表

巴特西

mysql基础之数据库变量（参数）管理

一、mysql中变量的分类

二、查看变量

三、修改变量值/设定变量值

四、查看表状态

最新文章

热门文章