MySQL相关

查看数据库的所有用户信息
select distinct concat(‘user: ”’,user,”’@”’,host,”’;’) as query from mysql.user;

查看特定用户权限

show grants for ‘root’@’localhost’;

use information_schema;

select * from RDS_PROCESSLIST;

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables;
select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’givdo’;
select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’stg02gvido’;
select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’givdo’ and table_name=’members’;

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’sys’;

show status like ‘Threads_connected’;
show status like ‘Uptime’;
show status like ‘Connections’;
show variables like ‘character%’;

status

MySQL [information_schema]> 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 | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+————————–+——————————–+
8 rows in set (0.00 sec)

查看数据所占的空间大小
select concat(round(sum(data_length)/(1024*1024),2),’MB’) as ‘DB Size’ from tables where table_schema=’INVOICE’;

查看索引所占的空间大小
select concat(round(sum(index_length)/(1024*1024),2),’MB’) as ‘DB Size’ from tables where table_schema=’INVOICE’;

最大连接数
show variables like ‘%max_connections%’;

查看数据库当前连接数，并发数。
show status like ‘Threads%’;
Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。

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

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

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

查看数据文件存放路径

show variables like ‘%datadir%’;

SELECT
t.TABLE_NAME,
t.CONSTRAINT_TYPE,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
information_schema.TABLES AS ts
WHERE
t.TABLE_NAME = c.TABLE_NAME
— AND t.TABLE_SCHEMA = score
AND t.CONSTRAINT_TYPE = ‘PRIMARY KEY’;

查询表结构：

SELECT column_name FROM information_schema.columns WHERE table_name=’表名’;
SELECT column_name FROM information_schema.columns WHERE table_name=’wp_users’;

‘MySQL [(none)]> SELECT column_name FROM information_schema.columns WHERE table_name=’wp_users’;
+———————+
| COLUMN_NAME |
+———————+
| display_name |
| ID |
| user_activation_key |
| user_email |
| user_login |
| user_nicename |
| user_pass |
| user_registered |
| user_status |
| user_url |
+———————+
10 rows in set (0.00 sec)

MySQL [(none)]>
‘

查询主键字段名

SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name=’表名’;
SELECT column_name FROM information_schema.columns WHERE table_name=’wp_users’;

‘MySQL [(none)]> SELECT column_name FROM information_schema.columns WHERE table_name=’wp_users’;
+———————+
| COLUMN_NAME |
+———————+
| display_name |
| ID |
| user_activation_key |
| user_email |
| user_login |
| user_nicename |
| user_pass |
| user_registered |
| user_status |
| user_url |
+———————+
10 rows in set (0.00 sec)

MySQL [(none)]>
‘

SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name=’wp_users’ AND constraint_name=’PRIMARY’;
‘MySQL [(none)]> SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name=’wp_users’ AND constraint_name=’PRIMARY’;
+————-+
| column_name |
+————-+
| ID |
+————-+
1 row in set (0.03 sec)

MySQL [(none)]>

‘

SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name=’wp_users’
AND CONSTRAINT_SCHEMA=’wordpress’
AND constraint_name=’PRIMARY’;

checksum table from ;