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_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed mysql> select concat(round(sum(data_length)/(10241024),2) + round(sum(index_length)/(10241024),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 names
You can turn off this feature to get a quicker startup with -A
Database changed mysql> 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> 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: 1 Current database: INVOICE Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: ‘’ Using delimiter: ; Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 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
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 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)
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)
mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | /mysqldata/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)