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

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;  

# 授管理员权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
# 创建用户
CREATE USER 'testuser'@'%' IDENTIFIED BY '123456';
# 重置密码 
SET PASSWORD FOR 'testuser'@'%' = PASSWORD('newpassword');
# 查看权限
SHOW GRANTS FOR 'testuser'@'%'; 
# 普通用户授权 
GRANT USAGE ON *.* TO 'testuser'@'%';                                 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `t%`.* TO 'testuser'@'%' ;
# 删除用户
delete from mysql.user where User='testuser';
# 刷新权限
FLUSH PRIVILEGES;