Mysql常用管理操作语句

oneNeko于 2021-10-13 发布

Mysql常用管理操作语句,以下操作均在mysql8.0下执行

用户操作

基本操作

使用root用户登录,运行以下命令

# 创建一个名为`test`密码为`1234`的用户:
create user test identified by '1234';

# 查询用户以及host
select user,host from mysql.user;

# 修改用户允许登录的主机
# % 代表所有主机
# localhost 代表只能在本机登录
# 192.168.1.1 代表只能在192.168.1.1这个ip登录
update mysql.user set host = 'localhost' where user = 'test';

# 修改用户密码
alter user 'test'@'localhost' identified by 'pswd';

# 删除用户test
drop user 'test'@'localhost';

权限操作

查询用户权限

# 查询当前用户权限
show grants;

# 查询其他用户权限
show grants for test;
select * from mysql.user where user='test';

授予用户权限

# 授予用户权限。
grant privilegesCode on dbName.tableName to username@host;

# 撤销用户权限。
revoke privilegesCode on dbName.tableName from username@host;

privilegesCode表示权限类型,常用的有以下几种:

dbName.tableName表示授予权限的具体库和表 username@host表示授予的用户以及允许该用户登录的IP地址。

flush privileges表示刷新权限变更。

注意事项

grant,revoke用户权限后,该用户只有重新连接数据库,权限才能生效。如果想让授权的用户,也可以将这些权限grant给其他用户,需要选项 grant option

grant select on testdb.* to dba@localhost with grant option;

备份和还原

mysqldumpmysql自带的逻辑备份工具。它的备份原理是通过协议连接到mysql数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert语句,当我们需要还原这些数据时,只要执行这些insert语句,即可将对应的数据还原。

备份

命令格式如下:

mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项]  > 脚本名
参数名 缩写 含义
–host -h 服务器地址
–port -P 服务器端口号
–user -u mysql用户名
–password -p mysql密码
–databases   指定要备份的数据库
–compact   压缩模式,产生更少输出
–comments   添加注释信息
–complete-insert   输出完成的插入语句
–lock-tables   备份前,锁定所有数据库表
–no-create-db/–no-create-info   禁止生成创建数据库语句
–force   当出现错误是仍然继续备份操作
–default-character-set   指定默认字符集
–add-locks   备份时锁定数据库表

还原

# 系统行命令
mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注:在导入备份数据库前,db_name如果没有,是需要创建的; 而且与db_name.db中数据库名是一样的才可以导入。
# soure 方法
use db_name
source /backup/mysqldump/db_name.db

查看容量

# 查看所有数据库容量大小
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;

# 查看所有数据库各表容量大小
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;

# 查看指定数据库容量大小
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授权和撤销权限操作 MySQL之mysqldump的使用 MySQL查看数据库表容量大小

上一篇

下一篇 最短路径算法-Dijkstra算法