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;