[轉]MySQL查看資料庫相關資訊

標籤：通過   sql語句   .com   row   taf   建立線程   get   form   man   

原文連結：MySQL查看資料庫相關資訊

使用MySQL時，需要瞭解當前資料庫的情況，例如當前的資料庫大小、字元集、使用者等等。下面總結了一些查看資料庫相關資訊的命令

1：查看顯示所有資料庫

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || INVOICE            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec) mysql> 

2：查看當前使用的資料庫

mysql> select database();+------------+| database() |+------------+| INVOICE    |+------------+1 row in set (0.00 sec) mysql> 

3：查看資料庫使用連接埠

mysql> show variables  like ‘port‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| port          | 3306  |+---------------+-------+1 row in set (0.00 sec)

4：查看當前資料庫大小

例如，我要查看INVOICE資料庫的大小，那麼可以通過下面SQL查看

mysql> use  information_schemaReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),‘MB‘) as ‘DB Size‘    -> from tables     -> where table_schema=‘INVOICE‘;+-----------+| DB Size   |+-----------+| 7929.58MB |+-----------+1 row in set, 1 warning (0.00 sec)

查看資料所佔的空間大小

mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> select concat(round(sum(data_length)/(1024*1024),2),‘MB‘) as ‘DB Size‘    -> from tables    -> where table_schema=‘INVOICE‘;+-----------+| DB Size   |+-----------+| 6430.26MB |+-----------+1 row in set, 1 warning (0.00 sec) mysql> 

查看索引所佔的空間大小

mysql> select concat(round(sum(index_length)/(1024*1024),2),‘MB‘) as ‘DB Size‘     -> from tables     -> where table_schema=‘INVOICE‘;+-----------+| DB Size   |+-----------+| 1499.32MB |+-----------+1 row in set, 1 warning (0.13 sec) mysql> 

5：查看資料庫編碼

mysql> 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     | latin1                     || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

character_set_client      為用戶端編碼方式；

character_set_connection  為建立串連使用的編碼；

character_set_database    為資料庫的編碼；

character_set_results     為結果集的編碼；

character_set_server      為資料庫伺服器的編碼；

只要保證以上採用的編碼方式一樣，就不會出現亂碼問題。

mysql> show variables like ‘collation%‘;+----------------------+-------------------+| Variable_name        | Value             |+----------------------+-------------------+| collation_connection | utf8_general_ci   || collation_database   | utf8_general_ci   || collation_server     | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.00 sec)

status也可以查看資料庫的編碼

mysql> status;--------------mysql  Ver 14.14 Distrib 5.6.20, for Linux (x86_64) using  EditLine wrapper Connection id:          1Current database:       INVOICECurrent user:           [email protected]SSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ‘‘Using delimiter:        ;Server version:         5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)Protocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /var/lib/mysql/mysql.sockUptime:                 5 hours 18 min 51 sec Threads: 1  Questions: 10884  Slow queries: 0  Opens: 650  Flush tables: 1  Open tables: 268  Queries per second avg: 0.568--------------mysql> 

6：查看資料庫的表資訊

mysql> show tables;+---------------------------------------+| Tables_in_information_schema          |+---------------------------------------+| CHARACTER_SETS                        || COLLATIONS                            || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS                               || COLUMN_PRIVILEGES                     || ENGINES                               || EVENTS                                || FILES                                 || GLOBAL_STATUS                         || GLOBAL_VARIABLES                      || KEY_COLUMN_USAGE                      || OPTIMIZER_TRACE                       || PARAMETERS                            || PARTITIONS                            || PLUGINS                               || PROCESSLIST                           || PROFILING                             || REFERENTIAL_CONSTRAINTS               || ROUTINES                              || SCHEMATA                              || SCHEMA_PRIVILEGES                     || SESSION_STATUS                        || SESSION_VARIABLES                     || STATISTICS                            || TABLES                                || TABLESPACES                           || TABLE_CONSTRAINTS                     || TABLE_PRIVILEGES                      || TRIGGERS                              || USER_PRIVILEGES                       || VIEWS                                 || INNODB_LOCKS                          || INNODB_TRX                            || INNODB_SYS_DATAFILES                  || INNODB_LOCK_WAITS                     || INNODB_SYS_TABLESTATS                 || INNODB_CMP                            || INNODB_METRICS                        || INNODB_CMP_RESET                      || INNODB_CMP_PER_INDEX                  || INNODB_CMPMEM_RESET                   || INNODB_FT_DELETED                     || INNODB_BUFFER_PAGE_LRU                || INNODB_SYS_FOREIGN                    || INNODB_SYS_COLUMNS                    || INNODB_SYS_INDEXES                    || INNODB_FT_DEFAULT_STOPWORD            || INNODB_SYS_FIELDS                     || INNODB_CMP_PER_INDEX_RESET            || INNODB_BUFFER_PAGE                    || INNODB_CMPMEM                         || INNODB_FT_INDEX_TABLE                 || INNODB_FT_BEING_DELETED               || INNODB_SYS_TABLESPACES                || INNODB_FT_INDEX_CACHE                 || INNODB_SYS_FOREIGN_COLS               || INNODB_SYS_TABLES                     || INNODB_BUFFER_POOL_STATS              || INNODB_FT_CONFIG                      |+---------------------------------------+59 rows in set (0.00 sec)

或者使用下面SQL語句查看某個資料庫的表資訊。

select * from information_schema.tables where table_schema=‘databasename‘;

查看某種具體表的資訊

select * from information_schema.tables where table_name =‘table_name‘

7：查看資料庫的所有使用者資訊

mysql>  select distinct concat(‘user: ‘‘‘,user,‘‘‘@‘‘‘,host,‘‘‘;‘) as query from mysql.user;+-------------------------------------+| query                               |+-------------------------------------+| user: ‘root‘@‘127.0.0.1‘;           || user: ‘root‘@‘::1‘;                 || user: ‘root‘@‘gettesx20.test.com‘; || user: ‘root‘@‘localhost‘;           |+-------------------------------------+4 rows in set (0.00 sec) mysql> 

 

8: 查看某個具體使用者的許可權

mysql> show grants for ‘root‘@‘localhost‘;+---------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                                              |+---------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION                                                                           |+---------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

9： 查看資料庫的最大串連數

mysql>  show variables like ‘%max_connections%‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.00 sec) mysql> 

10：查看資料庫當前串連數，並發數。

mysql> show status like ‘Threads%‘;+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 0     || Threads_connected | 1     || Threads_created   | 1     || Threads_running   | 1     |+-------------------+-------+4 rows in set (0.00 sec)

Threads_cached : 代表當前此時此刻線程緩衝中有多少空閑線程。

Threads_connected :代表當前已建立串連的數量，因為一個串連就需要一個線程，所以也可以看成當前被使用的線程數。

Threads_created :代表從最近一次服務啟動，已建立線程的數量。

Threads_running :代表當前啟用的（非睡眠狀態）線程數。並不是代表正在使用的線程數，有時候串連已建立，但是串連處於sleep狀態，這裡相對應的線程也是sleep狀態。

11：查看資料檔案存放路徑

mysql> show variables like ‘%datadir%‘;+---------------+-------------------+| Variable_name | Value             |+---------------+-------------------+| datadir       | /mysqldata/mysql/ |+---------------+-------------------+1 row in set (0.00 sec) mysql> 

 

[轉]MySQL查看資料庫相關資訊