MySQL 管理：用户、权限、备份

用户管理

-- 创建名称为 zhang3 的用户，密码设为 123123，默认 caching_sha2_password，其它：mysql_native_password、sha256_password
create user zhang3 identified WITH caching_sha2_password by '123123';

-- 查看用户和权限的相关信息
select host,user,select_priv,insert_priv,drop_priv from mysql.user;

-- 修改当前用户的密码
set password = password('123456');

-- 修改其他用户的密码。所有通过 user 表的修改，必须用 flush privileges; 命令才能生效
-- update mysql.user set password = password('123456') where user = 'li4';
ALTER USER 'li4'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';
flush privileges;

-- 修改用户名
update mysql.user set user = 'li4' where user = 'wang5';
flush privileges;

-- 删除用户，不要通过 delete from user where user='li4 进删除，系统会有残留信息保留。
drop user li4;

host：表示连接类型

• %：表示所有远程通过 TCP 方式的连接
• IP 地址：如 (192.168.1.2,127.0.0.1) 通过制定 ip 地址进行的 TCP 方式的连接
• 机器名：通过制定网络中的机器名进行的 TCP 方式的连接
• ::1：IPv6 的本地 ip 地址 等同于 IPv4 的 127.0.0.1
• localhost：本地方式通过命令行方式的连接 ，比如 mysql -u xxx -p 123xxx 方式的连接。

user：表示用户名

• 同一用户通过不同方式链接的权限是不一样的。

password：密码

• 所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为 MYSQLSHA1 ，不可逆 。
• mysql 5.7 的密码保存到 authentication_string 字段中不再使用 password 字段。

*_priv：该用户所拥有的权限

 

权限管理

授予权限

-- 格式：grant 权限 1,权限 2,…权限 n on 数据库名称. 表名称 to 用户名@用户地址 identified by ‘连接口令’
-- 该权限如果发现没有该用户，则会直接新建一个用户。
-- 例：给 li4 用户用本地命令行方式下，授予 mydb 这个库下的所有表的插删改查的权限。
grant select,insert,delete,alter,drop on mydb.* to li4@localhost;


-- 授予通过网络方式登录的的 joe 用户 ，对所有库所有表的全部权限，密码设为 123
grant all privileges on *.* to joe@'%' identified by '123';


-- 针对字段授权
grant update(limitation) on mydb.inventory to li4@'%';

收回权限

权限收回后，必须用户重新登录后，才能生效。

-- 查看当前用户权限
show grants;

-- 收回权限命令
revoke [权限 1,权限 2,…权限 n] on 库名.表名 from 用户名@用户地址;

-- 收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;

-- 收回 mysql 库下的所有表的插删改查权限
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;

查看权限

-- 查看当前用户权限
show grants;

select * from mysql.user;

 

备份恢复

备份

mysqldump --help：https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html

# 使用 root 用户备份 my 数据库
mysqldump -uroot -p my > ./my.sql
# 备份全部数据库(-A, --all-databases)
mysqldump -uroot -p -A > all_database.sql
# 备份部分数据库(-B, --databases)
mysqldump -uroot -p -B my1 my2 > two_database.sql

# 备份 my 数据库下的 book account 表
mysqldump -uroot -p my book account > two_tables.sql

# 备份 student 表中 id 小于 10 的数据
mysqldump -uroot -p my student --where="id < 10" > student_part_id10_low_bak.sql
# 排除某些表的备份
mysqldump -uroot -p my --ignore-table=my.student --ignore-table=my.student2 > no_stu_bak.sql
# grep "student" no_stu_bak.sql # 判定文件中没有 student 表结构

# 只备份结构 -d, --no-data
mysqldump -uroot -p my --no-data > atguigu_no_data_bak.sql
# 只备份数据 -t, --no-create-info
mysqldump -uroot -p my --no-create-info > atguigu_no_create_info_bak.sql

# 存储过程和自定义函数(-R, --routines)、事件(-E, --events)、触发器(--triggers)。
mysqldump -h 127.0.0.1 -P 3306 -uroot -p -R -E --triggers -A -r dump.sql -v
# grep -C 5 "rand_num" dump.sql

恢复

https://dev.mysql.com/doc/refman/8.0/en/reloading-sql-format-dumps.html

# 单库备份中恢复单库
# 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < my.sql
# 否则需要指定数据库名称，如下所示
mysql -uroot -p my < my.sql

# 全量备份恢复，-vvv 输出详情(数据量大可能会造成终端卡顿)
mysql –u root –p -vvv < all.sql

# 从全量备份中恢复单库
# 只想恢复某一个库，但有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。
sed -n '/^-- Current Database: `my`/,/^-- Current Database: `/p' all_database.sql > my.sql
# 分离完成后我们再导入 my.sql 即可恢复单个库

# 从单库备份中恢复单表
cat my.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat my.sql | grep --ignore-case 'insert into `class`' > class_data.sql
# 用 shell 语法分离出创建表的语句及插入数据的语句后再依次导出即可完成恢复
use my;
source class_structure.sql;
source class_data.sql;

 

---

https://dev.mysql.com/doc/refman/8.0/en/account-management-statements.html