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

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||INVOICE||mysql||performance_schema||test|+--------------------+5rowsinset(0.00sec)mysql>

mysql>selectdatabase();+------------+|database()|+------------+|INVOICE|+------------+1rowinset(0.00sec)mysql>

mysql>showvariableslike'port';+---------------+-------+|Variable_name|Value|+---------------+-------+|port|3306|+---------------+-------+1rowinset(0.00sec)

例如，我要查看INVOICE数据库的大小，那么可以通过下面SQL查看

mysql>useinformation_schemaReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>selectconcat(round(sum(data_length)/(1024*1024),2)+round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'->fromtables->wheretable_schema='INVOICE';+-----------+|DBSize|+-----------+|7929.58MB|+-----------+1rowinset,1warning(0.00sec)

mysql>useinformation_schema;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>selectconcat(round(sum(data_length)/(1024*1024),2),'MB')as'DBSize'->fromtables->wheretable_schema='INVOICE';+-----------+|DBSize|+-----------+|6430.26MB|+-----------+1rowinset,1warning(0.00sec)mysql>

mysql>selectconcat(round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'->fromtables->wheretable_schema='INVOICE';+-----------+|DBSize|+-----------+|1499.32MB|+-----------+1rowinset,1warning(0.13sec)mysql>

mysql>showvariableslike'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/|+--------------------------+----------------------------+8rowsinset(0.00sec)

character_set_client 为客户端编码方式；

character_set_connection 为建立连接使用的编码；

character_set_database 为数据库的编码；

character_set_results 为结果集的编码；

character_set_server 为数据库服务器的编码；

只要保证以上采用的编码方式一样，就不会出现乱码问题。

mysql>showvariableslike'collation%';+----------------------+-------------------+|Variable_name|Value|+----------------------+-------------------+|collation_connection|utf8_general_ci||collation_database|utf8_general_ci||collation_server|latin1_swedish_ci|+----------------------+-------------------+3rowsinset(0.00sec)

status也可以查看数据库的编码

mysql>status;--------------mysqlVer14.14Distrib5.6.20,forLinux(x86_64)usingEditLinewrapperConnectionid:1Currentdatabase:INVOICECurrentuser:root@localhostSSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.6.20-enterprise-commercial-advancedMySQLEnterpriseServer-AdvancedEdition(Commercial)Protocolversion:10Connection:LocalhostviaUNIXsocketServercharacterset:latin1Dbcharacterset:latin1Clientcharacterset:utf8Conn.characterset:utf8UNIXsocket:/var/lib/mysql/mysql.sockUptime:5hours18min51secThreads:1Questions:10884Slowqueries:0Opens:650Flushtables:1Opentables:268Queriespersecondavg:0.568--------------mysql>

查看数据库的表信息

mysql>showtables;+---------------------------------------+|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|+---------------------------------------+59rowsinset(0.00sec)

或者使用下面SQL语句查看某个数据库的表信息。

select*frominformation_schema.tableswheretable_schema=‘databasename';

查看某种具体表的信息

select*frominformation_schema.tableswheretable_name=‘table_name'

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

mysql>selectdistinctconcat('user:''',user,'''@''',host,''';')asqueryfrommysql.user;+-------------------------------------+|query|+-------------------------------------+|user:'root'@'127.0.0.1';||user:'root'@'::1';||user:'root'@'gettesx20.test.com';||user:'root'@'localhost';|+-------------------------------------+4rowsinset(0.00sec)mysql>

查看某个具体用户的权限

mysql>showgrantsfor'root'@'localhost';+---------------------------------------------------------------------------------------------------------------------------------+|Grantsforroot@localhost|+---------------------------------------------------------------------------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD'*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23'WITHGRANTOPTION||GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|+---------------------------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)

查看数据库的最大连接数

mysql>showvariableslike'%max_connections%';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|max_connections|151|+-----------------+-------+1rowinset(0.00sec)mysql>

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

mysql>showstatuslike'Threads%';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|Threads_cached|0||Threads_connected|1||Threads_created|1||Threads_running|1|+-------------------+-------+4rowsinset(0.00sec)

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created :代表从最近一次服务启动，已创建线程的数量。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

查看数据文件存放路径

mysql>showvariableslike'%datadir%';+---------------+-------------------+|Variable_name|Value|+---------------+-------------------+|datadir|/mysqldata/mysql/|+---------------+-------------------+1rowinset(0.00sec)mysql>




上一篇：mysql prepare的作用是什么

下一篇：安装mysql无法启动服务如何解决

---

mysql 数据库