数据库

mysql查看数据库表大小

SELECT TABLE_NAME AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS Size_in_MB FROM information_schema.TABLES  WHERE table_schema = '数据库名' ORDER BY Size_in_MB DESC LIMIT 10;

msyql数据库常用命令

查看表结构

desc 表名;

修改表名

alter table 表名 rename 新表名;

修改表字段

alter table 表名 change 字段名 新字段名 integer;

修改表内容

update 表名 set 字段='新内容' where 字段='条件';

添加表字段

alter table 表名 add column 新建字段名 int not null default 0 after 新建字段上一个字段名;

删除表字段

alter table 表名 drop column 字段名;

查看库的大小

use information_schema;
select  concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where  table_schema='库名';

查看库下面表的大小

use information_schema;
select  concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where  table_schema='库名' and table_name='表名';

查看库索引大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'mb') as 'Total Index Size' from tables where table_schema = '库名';

查看所有用户

select user,host,plugin from mysql.user;

查看用户权限

show grants for user_uild;

show grants for user_uild@'127.0.0.1';

创建用户

create user 'username'@'%' identified with by '123456';

create user 'username'@'%' identified with mysql_native_password by '123456';

修改指定用户密码

set password for 'root'@'localhost' = password('123456');

修改当前用户密码

alter user user() identified by '123456';

授权用户

grant all privileges on *.* to root@'%' identified by '123456';

grant select,insert,update,delete on *.* to root@localhost identified by '123456';

取消用户授权

revoke all privileges on *.* from root@'127.0.0.1';

revoke select on xhl_guild.* from user_guild@'%';

修改加密方式

update mysql.user set plugin='mysql_native_password' where user='root' and host='localhost';

刷新授权

flush privileges;

mysql8设置简单密码策略

set global validate_password.policy=0;
set global validate_password.length=1;

创建监控用户

create user 'exporter'@'localhost' identified by 'xxxxxxxx' with max_user_connections 3;

grant process, replication client, select on *.* to 'exporter'@'localhost';

全局读锁

flush tables with read lock;

单个表加锁

lock tables db_a.tbl_aaa read;

释放锁

unlock tables;