Mysql数据库使用量查询及授权

最新推荐文章于 2024-12-13 10:41:17 发布

weixin_30455023

最新推荐文章于 2024-12-13 10:41:17 发布

阅读量204

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Mrhuangrui/p/8514176.html

Mysql数据库使用量查询及授权

使用量查询

查看实例下每个库的大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;

查询单个库大小

SELECT concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') FROM information_schema.TABLES where TABLE_SCHEMA='DATABASE';

查看具体某个库所有表的大小并排序

SELECT table_schema,table_name,(data_length/1024/1024),(index_length/1024/1024) FROM information_schema.tables where table_schema='DATABASE' order by data_length;

查看单个表大小

SELECT sum(DATA_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='DATABASE' AND table_name='TABLE';

单个表数据加索引大小

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='DATABASE' AND table_name='TABLE';

单个表索引大小

select ENGINE from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='DATABASE' and table_name='TABLE';

常用授权

设置root账号能从任何主机登陆数据库

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'MYSQL@2018';

设置root账户能从10网段登陆数据库

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.%' IDENTIFIED BY 'MYSQL@2018' WITH GRANT OPTION;

设置dbadmin账户能从10网段登陆数据库,且拥有所有权限

GRANT ALL PRIVILEGES ON *.* TO dbadmin@'10.%' IDENTIFIED BY 'MYSQL@2018';

设置dbreader账户能从10网段登陆数据库,且拥有只读权限

GRANT SELECT ON *.* TO dbreader@'10.%' IDENTIFIED BY 'MYSQL@2018';

使添加的账号生效

FLUSH PRIVILEGES;

清空当前表中的所有数据

Truncate tables;

转载于:https://www.cnblogs.com/Mrhuangrui/p/8514176.html