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;
备份恢复
备份
# 使用 root 用户备份 my 数据库 mysqldump -uroot -p my > ./my.sql # 备份全部数据库 mysqldump -uroot -p --all-databases > all_database.sql mysqldump -uroot -p -A > all_database.sql # 备份部分数据库 mysqldump -uroot -p --databases my1 my2 > two_database.sql mysqldump -uroot -p -B my1 my2 > two_database.sql # 备份 my 数据库下的 book 表 mysqldump -uroot -p my book > book.sql # 备份多张表 mysqldump -uroot -p my book account > 2_tables_bak.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 > no_stu_bak.sql # grep "student" no_stu_bak.sql # 判定文件中没有student表结构 # 只备份结构可以使用 --no-data 简写为 -d mysqldump -uroot -p my --no-data > atguigu_no_data_bak.sql # 只备份数据可以使用 --no-create-info 简写为 -t mysqldump -uroot -p my --no-create-info > atguigu_no_create_info_bak.sql # mysqldump 备份默认是不包含存储过程、自定义函数及事件的。 # 可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。 mysqldump -uroot -p -R -E --databases my > fun_my_bak.sql # grep -C 5 "rand_num" fun_my_bak.sql
恢复
# 单库备份中恢复单库 # 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称 mysql -uroot -p < my.sql # 否则需要指定数据库名称，如下所示 mysql -uroot -p my < my.sql # 全量备份恢复 mysql –u root –p < 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;
常用选项
mysqldump --help
--add-drop-database：在每个 CREATE DATABASE 语句前添加 DROP DATABASE 语句。
--add-drop-tables：在每个 CREATE TABLE 语句前添加 DROP TABLE 语句。
--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options 或者 no_field_options。
--complete_insert, -c：使用包括列名的完整的INSERT语句。
--debug[=debug_options], -#[debug_options]：写调试日志。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出--database或--all-database选项，就包含到输出中。
--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
--no-data，-d：不写表的任何行信息，只转储表的结构。
--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。
--password[=password]，-p[password]：当连接服务器时使用的密码。-port=port_num，-P port_num：用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s：沉默模式。只有出现错误时才输出。
--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
--verbose，-v：冗长模式，打印出程序操作的详细信息。
--xml，-X：产生XML输出。
https://dev.mysql.com/doc/refman/8.0/en/account-management-statements.html
