查看显示所有数据库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>
以上就是mysql怎么查询数据库连接状态及连接信息的详细内容。