Mysql 备查

-- mysql 8.0 改 root 密码
select host,user,plugin,authentication_string from mysql.user;

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

update mysql.user set host="%" where user="root" and host="localhost";

--服务端登录
mysql -u root -p


-- 数据库表占用空间大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='db_name';

-- 清空表(无日志)
truncate table tablename;


-- 显示进程
show processlist;
-- 统计锁
show status like 'innodb_row_lock%';

FLUSH PRIVILEGES;