MySQL常用查询

admin

--查看数据库

1. show databases;

复制代码

--查看数据文件位置

1. show variables like 'datadir';

复制代码

--查看数据库错误日志

1. show variables like 'log_error';

复制代码

--查看从库主机

1. show slave hosts;

复制代码

--查看默认存储引擎

1. show variables like 'default_storage_engine';

复制代码

--查询查询系统变量

1. show global status;
   
2. show variables \G

复制代码

--查询版本

1. select version();

复制代码

--查询插件使用情况

1. select plugin_name,plugin_status,load_option from information_schema.plugins;

复制代码

--查询用户和密码

1. mysql> select user,host,password[authentication_string] from mysql.user;

复制代码

--查询数据库数据量

1. select concat(round(sum((data_length+index_length) / 1024 / 1024), 2), 'MB') as tb_id_size from information_schema.tables;

复制代码

--查询用户数据量

1. select table_schema,
   
2.        concat(round(sum((data_length + index_length) / 1024 / 1024), 2),
   
3.               'MB') as data_size
   
4.   from information_schema.tables
   
5. group by table_schema
   
6. order by 2 desc;

复制代码

--查询当前在线用户

1. SELECT DISTINCT CONCAT('User: ''', user, '''@''', host, ''';') AS query FROM mysql.user;
   
2. select user,current_connections from performance_schema.users where user is not null;

复制代码

--查询进程

1. show full processlist;

复制代码

--查询全局状态

1. select * from performance_schema.global_status\G;

复制代码

--查询全局变量

1. select * from performance_schema.global_variables\G;

复制代码

--查询TPS

1. use information_schema;  
   
2. select VARIABLE_VALUE
   
3.   into @num_com
   
4.   from GLOBAL_STATUS
   
5. where VARIABLE_NAME = 'COM_COMMIT';
   
6. select VARIABLE_VALUE
   
7.   into @num_roll
   
8.   from GLOBAL_STATUS
   
9. where VARIABLE_NAME = 'COM_ROLLBACK';
   
10. select VARIABLE_VALUE
    
11.   into @uptime
    
12.   from GLOBAL_STATUS
    
13. where VARIABLE_NAME = 'UPTIME';
    
14. select (@num_com + @num_roll) / @uptime;

复制代码

--查询QPS

1. use information_schema;  
   
2. select VARIABLE_VALUE
   
3.   into @num_queries
   
4.   from GLOBAL_STATUS
   
5. where VARIABLE_NAME = 'QUESTIONS';
   
6. select VARIABLE_VALUE
   
7.   into @uptime
   
8.   from GLOBAL_STATUS
   
9. where VARIABLE_NAME = 'UPTIME';
   
10. select @num_queries / @uptime;

复制代码