Mysql查询数据库状态及信息

<p>使用MySQL时,需要了解当前数据库的情况,例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令</p><p>1:查看显示所有数据库</p><pre class="brush:bash;toolbar:false">mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |INVOICE| |mysql| |performance_schema| |test| +--------------------+ 5rowsinset(0.00sec) mysql&gt;</pre><p>2:查看当前使用的数据库</p><pre class="brush:bash;toolbar:false">mysql&gt;selectdatabase(); +------------+ |database()| +------------+ |INVOICE| +------------+ 1rowinset(0.00sec) mysql&gt; clip_image001[1]</pre><p>3:查看数据库使用端口</p><pre class="brush:bash;toolbar:false">mysql&gt;showvariableslike&#39;port&#39;; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |port|3306| +---------------+-------+ 1rowinset(0.00sec) clip_image002[1]</pre><p>4:查看当前数据库大小</p><p>例如,我要查看INVOICE数据库的大小,那么可以通过下面SQL查看</p><pre class="brush:bash;toolbar:false">mysql&gt;useinformation_schema Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql&gt;selectconcat(round(sum(data_length)/(1024*1024),2)+round(sum(index_length)/(1024*1024),2),&#39;MB&#39;)as&#39;DBSize&#39; -&gt;fromtables -&gt;wheretable_schema=&#39;INVOICE&#39;; +-----------+ |DBSize| +-----------+ |7929.58MB| +-----------+ 1rowinset,1warning(0.00sec)</pre><p>查看数据所占的空间大小</p><pre class="brush:bash;toolbar:false">mysql&gt;useinformation_schema; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql&gt;selectconcat(round(sum(data_length)/(1024*1024),2),&#39;MB&#39;)as&#39;DBSize&#39; -&gt;fromtables -&gt;wheretable_schema=&#39;INVOICE&#39;; +-----------+ |DBSize| +-----------+ |6430.26MB| +-----------+ 1rowinset,1warning(0.00sec) mysql&gt;</pre><p>查看索引所占的空间大小</p><pre class="brush:bash;toolbar:false">mysql&gt;selectconcat(round(sum(index_length)/(1024*1024),2),&#39;MB&#39;)as&#39;DBSize&#39; -&gt;fromtables -&gt;wheretable_schema=&#39;INVOICE&#39;; +-----------+ |DBSize| +-----------+ |1499.32MB| +-----------+ 1rowinset,1warning(0.13sec)</pre><p>mysql&gt;</p><p>5:查看数据库编码</p><pre class="brush:bash;toolbar:false">mysql&gt;showvariableslike&#39;character%&#39;; +--------------------------+----------------------------+ |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) clip_image003[1]</pre><p>character_set_client 为客户端编码方式;</p><p>character_set_connection 为建立连接使用的编码;</p><p>character_set_database 为数据库的编码;</p><p>character_set_results 为结果集的编码;</p><p>character_set_server 为数据库服务器的编码;</p><p>只要保证以上采用的编码方式一样,就不会出现乱码问题。</p><pre class="brush:bash;toolbar:false">mysql&gt;showvariableslike&#39;collation%&#39;; +----------------------+-------------------+ |Variable_name|Value| +----------------------+-------------------+ |collation_connection|utf8_general_ci| |collation_database|utf8_general_ci| |collation_server|latin1_swedish_ci| +----------------------+-------------------+ 3rowsinset(0.00sec) clip_image004</pre><p>status也可以查看数据库的编码</p><pre class="brush:bash;toolbar:false">mysql&gt;status; -------------- mysqlVer14.14Distrib5.6.20,forLinux(x86_64)usingEditLinewrapper Connectionid:1 Currentdatabase:INVOICE Currentuser:root@localhost SSL:Notinuse Currentpager:stdout Usingoutfile:&#39;&#39; Usingdelimiter:; Serverversion:5.6.20-enterprise-commercial-advancedMySQLEnterpriseServer-AdvancedEdition(Commercial) Protocolversion:10 Connection:LocalhostviaUNIXsocket Servercharacterset:latin1 Dbcharacterset:latin1 Clientcharacterset:utf8 Conn.characterset:utf8 UNIXsocket:/var/lib/mysql/mysql.sock Uptime:5hours18min51sec Threads:1Questions:10884Slowqueries:0Opens:650Flushtables:1Opentables:268Queriespersecondavg:0.568 -------------- mysql&gt;</pre><p>6:查看数据库的表信息</p><pre class="brush:bash;toolbar:false">mysql&gt;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)</pre><p>或者使用下面SQL语句查看某个数据库的表信息。</p><pre class="brush:bash;toolbar:false">select*frominformation_schema.tableswheretable_schema=&#39;databasename&#39;;</pre><p>查看某种具体表的信息</p><pre class="brush:bash;toolbar:false">select*frominformation_schema.tableswheretable_name=&#39;table_name&#39;</pre><p>7:查看数据库的所有用户信息</p><pre class="brush:bash;toolbar:false">mysql&gt;selectdistinctconcat(&#39;user:&#39;&#39;&#39;,user,&#39;&#39;&#39;@&#39;&#39;&#39;,host,&#39;&#39;&#39;;&#39;)asqueryfrommysql.user; +-------------------------------------+ |query| +-------------------------------------+ |user:&#39;root&#39;@&#39;127.0.0.1&#39;;| |user:&#39;root&#39;@&#39;::1&#39;;| |user:&#39;root&#39;@&#39;gettesx20.test.com&#39;;| |user:&#39;root&#39;@&#39;localhost&#39;;| +-------------------------------------+ 4rowsinset(0.00sec) mysql&gt;</pre><p></p><p>8: 查看某个具体用户的权限</p><pre class="brush:bash;toolbar:false">mysql&gt;showgrantsfor&#39;root&#39;@&#39;localhost&#39;; +---------------------------------------------------------------------------------------------------------------------------------+ |Grantsforroot@localhost| +---------------------------------------------------------------------------------------------------------------------------------+ |GRANTALLPRIVILEGESON*.*TO&#39;root&#39;@&#39;localhost&#39;IDENTIFIEDBYPASSWORD&#39;*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23&#39;WITHGRANTOPTION| |GRANTPROXYON&#39;&#39;@&#39;&#39;TO&#39;root&#39;@&#39;localhost&#39;WITHGRANTOPTION| +---------------------------------------------------------------------------------------------------------------------------------+ 2rowsinset(0.00sec)</pre><p>9: 查看数据库的最大连接数</p><pre class="brush:bash;toolbar:false">mysql&gt;showvariableslike&#39;%max_connections%&#39;; +-----------------+-------+ |Variable_name|Value| +-----------------+-------+ |max_connections|151| +-----------------+-------+ 1rowinset(0.00sec)</pre><p><br/></p><p>mysql&gt;</p><p>10:查看数据库当前连接数,并发数。</p><pre class="brush:bash;toolbar:false">mysql&gt;showstatuslike&#39;Threads%&#39;; +-------------------+-------+ |Variable_name|Value| +-------------------+-------+ |Threads_cached|0| |Threads_connected|1| |Threads_created|1| |Threads_running|1| +-------------------+-------+ 4rowsinset(0.00sec)</pre><p>Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。</p><p>Threads_connected :代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数。</p><p>Threads_created :代表从最近一次服务启动,已创建线程的数量。</p><p>Threads_running :代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数,有时候连接已建立,但是连接处于sleep状态,这里相对应的线程也是sleep状态。</p><p>clip_image005</p><p>11:查看数据文件存放路径</p><pre class="brush:bash;toolbar:false">mysql&gt;showvariableslike&#39;%datadir%&#39;; +---------------+-------------------+ |Variable_name|Value| +---------------+-------------------+ |datadir|/mysqldata/mysql/| +---------------+-------------------+ 1rowinset(0.00sec) mysql&gt;</pre>
RangeTime:0.007601s
RangeMem:215.56 KB
返回顶部 留言