Linux下 mysql的常规操作[5.7版本]

Linux下修改 mysql密码

• 修改/etc/my.cnf文件，最后一行加上 skip-grant-tables
• 重启MYSQL， service mysqld restart
• cd /usr/local/mysql/bin
• mysql -u root -p 提示输入密码，直接Enter
• use mysql
• update user set authentication_string=password("Li2022") where user='root';
• flush privileges; 刷新
• exit 退出mysql；
• 改my.cnf文件；删掉最后一行。

创建用户 dev，密码1234

 create user 'dev'@'*' identified by '1234';

创建数据库

create database dbname; //创建名为dbname数据库

删除数据库

drop database dbname;  //删除dbname数据库

出现 1146 table 'performance_schema.session_variables' doesn t exist 的问题

解决：cd /usr/local/mysql/bin;

./mysql_upgrade -u root -p [密码] --force

1、查看用户

select user, authentication_string from user;

2、查看所有数据库容量大小

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

group by table_schema

order by sum(data_length) desc, sum(index_length) desc;

3、查看所有数据库各表容量大小

select

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

order by data_length desc, index_length desc;

4、查看指定数据库容量大小

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='mysql';-- 查看mysql库容量大小

5、查看指定数据库各表容量大小

select

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

where table_schema='mysql' -- 查看mysql库各表容量大小

order by data_length desc, index_length desc;