用户创建、修改、删除

为了避免用户恶意使用root权限控制数据库,通常需要创建一系列适当权限的账号来确保数据的安全访问。

用户创建

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
  1. 用户名的格式: 'user_name'@'host_name',username代表用户名,hostname为主机名,如果只指定了username则hostname默认为 % ,表示一组主机。使用过SSH连接远程服务器的时候ssh root@183.xx.xx.xxx -p端口号这里的root@xxx就代表一个用户。
  2. 创建用户成功后,会在user 表中添加一条新记录。
    CREATE USER 'shafish'@'localhost'
    -> IDENTIFIED BY 'qwexc195';
    //创建一个用户名为shafish 密码为qwexc195的用户。
    mysql -h localhost -u shafish -p
    //使用新建用户登录mysql

用户修改

  • 使用RENAME USER语句修改已存在的用户名。

    RENAME USER <旧用户> TO <新用户>
    RENAME USER shafish@'localhost'
    -> TO xiaoqi@'localhost';
    //将shafish用户名改为xiaoqi
  • 使用SET PASSWORD语句修改用户登录口令
    SET PASSWORD [ FOR <用户名> ] =
    {
    PASSWORD('新明文口令')
    | OLD_PASSWORD('旧明文口令')
    | '加密口令值'
    }
    SET PASSWORD FOR 'xiaoqi'@'localhost'=
    -> PASSWORD('lion');

    然后使用用户名为xiaoqi,密码为lion 登录mysql。

用户删除

使用DROP USER语句来删除用户

drop user 'shafish'@'localhost';

用户授权

当新用户创建后,需要为该用户分配适当的权限后才可以进行相关的操作(否则就只有登录的权限)。

新建用户授权

使用GRANT语句进行用户授权

GRANT
<权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]
ON <对象> <权限级别> TO <用户>
  1. 权限级别

    • 列权限:表中一个具体列相关。
    • 表权限:一个具体表中的所有数据相关。
    • 数据库权限:一个具体数据库中的所有表相关。
    • 用户权限:跟mysql中所有的数据库相关。
  2. 级别对应的权限类型

    • 列权限:只有SELECT、INSERT 和 UPDATE
    • 表权限:SELECT、INSERT、DELETE、DROP、UPDATE、ALTER、REFERENCES/创建外键、CREATE/建表、INDEX/定义索引
    • 数据库权限:SELECT、INSERT、DELETE、UPDATE、REFERENCES、CREATE、ALTER、SHOW VIEW/查看视图、CREATE ROUTINE/创建存储过程、ALTER ROUTINE、INDEX、DROP/删除所有表和视图、CREATE TEMPORARY TABLES/创建临时表、CREATE VIEW、EXECUTE ROUTINE/调用存储过程和函数、LOCK TABLES/锁定数据表
    • 用户权限:除了有数据库权限外还有CREATE USER/创建删除用户、SHOW DATABASES/查看已有数据库定义
  3. 权限级别对象对应的值
说明
* 当前数据库中的所有表
*.* 所有数据库的所有表
db_name.* 指定数据库中的所有表
db_name.tbl_name 指定数据库中的指定表或视图
tbl_name 指定表或视图
db_name.rutine_name 指定数据库中指定的存储过程或函数

例子:

grant select,insert on *.*
to "fisha"@"localhost"
identified by "shafish"
with grant option;
//新建一个用户,授予新用户对所有数据查询、插入的权限,并授予grant权限。
//请对应前面的语法格式查看。
select host,user,select_priv,insert_priv,grant_priv from mysql.user where user='fisha';
//查看用户权限

撤销用户授权

  • 用于回收某些特定的授权

    REVOKE <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]…
    ON <对象类型> <权限名> FROM <用户1> [ , <用户2> ]…
  • 用于回收特定用户的所有授权
    revoke all priliges,grant,option
    from user 用户,用户2

    例子:

    revoke insert on *.* 
    from 'fisha'@'localhost';
    //撤销用户fisha的数据插入权限

备份与恢复

备份

  • 使用select into outfile将表数据导出到一个文件文本中进行保存。注意文件权限问题。
    这种方法只能导出/入表数据,不包括表结构

    select * from fisha_tb1 into outfile '/home/fisha/mysqlFile.txt'
    fieles terminated by '"'
    lines terminated by '?';
    //将指定表中的数据导出到一个文件中,而且要求每个字段y用逗号隔开,如果是字符类型用双引号标明,每行以问号结束。
  • mysqldump
    mysqldump -uroot 数据库 -p > 输出文件
    mysqldump -uroot fisha -p > fisha.sql;

恢复

使用LOAD DATA…INFILE语句恢复先前备份的数据
首先需要建立表结构,再导入数据

CREATE TABLE fisha_tb_copy
  LIKE fisha_tb1;
LOAD DATA INFILE '/home/mysql/file.txt'
  INTO TABLE test_db.tb_students_copy
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '?';
  • fields-terminated-by=string:用来设置字段的分割符为字符串对象(string),默认值为"\t"。
  • fields-enclosed-by=char:用来设置括上字段值的字符符号,默认情况下不使用任何符号。
  • fields-optionally-enclosed-by=char:用来设置括上CHAR/VARCHAR/TEXT等字段值的字符符号,默认情况下不使用任何符号。
  • lines-terminated-by=string:用来设置每行结束的字符串符号,默认情况下使用"\n"字符串。