Mysql怎么查询数据库连接状态及连接信息

Mysql怎么查询数据库连接状态及连接信息

查看显示所有数据库

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

查看当前使用的数据库

mysql> select database();+------------+| database() |+------------+| INVOICE |+------------+1 row in set (0.00 sec) mysql>登录后复制

查看数据库使用端口

mysql> show variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port | 3306 |+---------------+-------+1 row in set (0.00 sec)登录后复制

查看当前数据库大小

例如，我要查看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>登录后复制

查看数据库编码

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: root@localhostSSL: 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>登录后复制

查看数据库的表信息

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'登录后复制

查看数据库的所有用户信息

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>登录后复制

查看某个具体用户的权限

mysql> show grants for 'root'@'localhost';+---------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost |+---------------------------------------------------------------------------------------------------------------------------------+| 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)登录后复制

查看数据库的最大连接数

mysql> show variables like '%max_connections%';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in set (0.00 sec) mysql>登录后复制

查看数据库当前连接数，并发数。

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状态。

查看数据文件存放路径

mysql> show variables like '%datadir%';+---------------+-------------------+| Variable_name | Value |+---------------+-------------------+| datadir | /mysqldata/mysql/ |+---------------+-------------------+1 row in set (0.00 sec) mysql>登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。