mysql 获取server信息_MySQL 获取服务器元数据

如果想要获取服务器的元数据，可以使用下表的语句

命令

说明

SELECT VERSION()

返回服务器版本信息

SELECT DATABASE()

返回当前数据库名 (或者返回空)

SELECT USER()

返回当前用户名

SHOW STATUS

返回服务器状态

SHOW VARIABLES

返回服务器配置变量

SELECT VERSION() 获取服务器版本信息

MariaDB [twle]> SELECT VERSION();

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

| VERSION() |

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

| 10.2.13-MariaDB |

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

1 row in set (0.01 sec)

SELECT DATABASE() 获取当前数据库名

MariaDB [twle]> SELECT DATABASE();

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

| DATABASE() |

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

| twle |

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

1 row in set (0.00 sec)

如果当前没有选择任何数据库，则返回 NULL

MariaDB [(none)]> SELECT DATABASE();

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

| DATABASE() |

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

| NULL |

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

1 row in set (0.00 sec)

SELECT USER() 获取当前用户名

MariaDB [(none)]> SELECT USER();

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

| USER() |

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

| root@localhost |

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

1 row in set (0.00 sec)

SHOW STATUS 获取服务器状态

MariaDB [(none)]> SHOW STATUS;

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

| Variable_name | Value |

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

| Aborted_clients | 14 |

| Aborted_connects | 0 |

| Access_denied_errors | 0 |

| Acl_column_grants | 0 |

| Acl_database_grants | 2 |

| Acl_function_grants | 0 |

| Acl_procedure_grants |

| Binlog_commits | 0 |

....

....

483 rows in set (0.02 sec)

非常多数据，看不完，我们一般会使用 LIKE 语句查询某些具体的值，比如使用下面的语句查询缓存命中率相关的信息

SHOW STATUS LIKE '%Qcache%';

运行结果如下

MariaDB [(none)]> SHOW STATUS LIKE '%Qcache%';

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

| Variable_name | Value |

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

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 1031312 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 0 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

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

8 rows in set (0.00 sec)

SHOW VARIABLES 获取服务器配置变量

MariaDB [(none)]> SHOW VARIABLES\G;

*********************** 626. row ***********************

Variable_name: wsrep_slave_threads

Value: 1

*********************** 627. row ***********************

Variable_name: wsrep_sst_auth

Value:

*********************** 628. row ***********************

Variable_name: wsrep_sst_donor

Value:

*********************** 629. row ***********************

Variable_name: wsrep_sst_donor_rejects_queries

Value: OFF

*********************** 630. row ***********************

Variable_name: wsrep_sst_method

Value: rsync

*********************** 631. row ***********************

Variable_name: wsrep_sst_receive_address

Value: AUTO

*********************** 632. row ***********************

Variable_name: wsrep_start_position

Value: 00000000-0000-0000-0000-000000000000:-1

*************************** 633. row ********************

Variable_name: wsrep_sync_wait

Value: 0

633 rows in set (0.01 sec)

...

...

633 rows in set (0.02 sec)

非常多数据，看不完，我们一般会使用 LIKE 语句查询某些具体的值，比如使用下面的语句查询编码相关信息

SHOW VARIABLES LIKE '%character%';

运行结果如下

MariaDB [(none)]> SHOW VARIABLES LIKE '%character%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | .../share/mysql/charsets/ |

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

8 rows in set (0.00 sec)