MySQL权限安全总结- 用户权限管理及数据库备份(mysqldump)与恢复

1.安全管理

一、管理用户

mysql的用户账号信息存储在名为mysql的数据库中。
获得所有用户账号列表：

mysql> use mysql;
Database changed
mysql> select user from user;
+-------+
| user  |
+-------+
| root  |
|       |
| cacti |
| root  |
|       |
| root  |
+-------+
6 rows in set (0.00 sec)

mysql>

二、创建用户账号

使用CREATE USER语句：

mysql> create user rokas identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> select user from user;
+-------+
| user  |
+-------+
| rokas |
| root  |
|       |
| cacti |
| root  |
|       |
| root  |
+-------+
7 rows in set (0.00 sec)

mysql>

identified by指定用户密码，省略indentified by语句则为空密码
identified by指定的用户密码为纯文本，在保存到user表之前会对其加密处理。 identified by password 'xxx'则为指定加密过后的密码

三、重命名用户账号(RENAME)

示例：

mysql> rename user rokas to anthony;
Query OK, 0 rows affected (0.00 sec)

mysql>

这仅对于mysql5之后的版本有效。也可以用UPDATE来更新user表，这样对版本无要求。

四、删除用户账号(DROP USER)

删除用户账号(以及权限)，使用DROP USER语句：

mysql> drop user anthony;
Query OK, 0 rows affected (0.00 sec)

mysql>

五、设置及撤销访问权限(GRANT和REVOKE)

在创建用户账号后，必须接着分配访问权限。不然它们只能登录MySQL，不能看到数据，不能执行任何数据库操作。
为看到赋予用户账号的权限，使用SHOW GRANTS FOR语句：

mysql> create user rokas identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for rokas;
+------------------------------------------------------------------------------------------------------+
| Grants for rokas@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rokas'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>flush privileges;      ###刷新权限

输出结果显示用户rokas有一个权限USAGE ON *.*。USAGE表示没有任何权限，所以，此结果表示rokas在任意数据库及表上没有任何权限。
用户定义为user@host：
MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%(授予用户访问权限而不管主机名)

• localhost：只允许该用户在本地登录，不能远程登录。

• %：允许在除本机之外的任何一台机器远程登录。

• 192.168.0.12：具体的IP表示只允许该用户从特定IP登录。

示例，创建用户只允许本地登录：

create user 'rokas'@'localhost' identified by 'password';

为设置权限，使用GRANT语句。要求给出以下信息：

• 要授予的权限；
• 被授予访问权限的数据库或表；
• 用户名。

示例：

mysql> grant select on crashcourse.* to rokas;
Query OK, 0 rows affected (0.01 sec)

mysql>flush privileges;

此GRANT允许用户在crashcourse.*(crashcourse数据库所有表)上使用SELECT。
通过只授予SELECT访问权限，用户rokas对crashcourse数据库中的所有数据具有只读访问权限。

显示这个更改：

mysql> show grants for rokas;
+------------------------------------------------------------------------------------------------------+
| Grants for rokas@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rokas'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT ON `crashcourse`.* TO 'rokas'@'%'                                                       |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

GRANT的反操作为REVOKE，用来撤销特定权限，示例：

mysql> revoke select on crashcourse.* from rokas;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

这条语句撤销用户rokas对crashcourse数据库的SELECT访问权限。

GRANT和REVOKE可在以下几个层次上控制访问权限：

• 整个服务器，使用GRANT ALL和REVOKE ALL;

• 整个数据库，使用ON database.*;

• 特定的表，使用ON database.table;

• 特定的列；

• 特定的存储过程。

• 下表列出可以授予或撤销的每个权限：

根据上表配合GRANT和REVOKE，可以完全控制用户权限。

如果有多个权限，可以用逗号分隔：

GRANT SELECT,INSERT ON crashcourse.* to rokas;

注意：在使用GRANT和REVOKE时，用户账号必须存在，而涉及的对象没有要求。这允许管理员在创建数据库和表之前设计和实现安全控制。

六、更改密码(SET PASSWORD)

更改用户密码，使用SET PASSWORD语句。
示例：

mysql> set password for rokas=password('new password');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

set password更新用户密码，新密码必须传递到Password函数进行加密。

设置当前用户的口令：

mysql> set password=password('123456');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

2.数据库备份及维护

一、数据库备份及还原(mysqldump)

1)备份

格式：
mysqldump -h主机名 -P端口 -u用户名 -p密码(后不跟密码则会交互输入密码) --database 数据库名 >文件名.sql

示例：

mysqldump -hlocalhost -P3306 -uroot -p --database www >www.bak.sql

• 备份所有数据库：

  mysqldump --all-databases > allbackupfile.sql

• 使用mysql数据库压缩备份：

  mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz

  2)还原
  格式：mysql -h主机名 -u用户名 -p密码 <文件名.sql

示例：

mysql -hlocalhost -uroot -p123456 <www.bak.sql

或者使用source:

mysql>use mysql;
mysql>source test.sql;

• 对于.gz格式的sql文件，使用zcat配合管道进行还原：

zcat bakupfile.sql.gz | mysql -uuser -ppassword

二、确保数据库正确及正常运行，使用以下语句来检查健康状态

1）ANALYZE TABLE，用来检查表键是否正确

示例：

mysql> analyze table orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| course.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql>

2）CHECK TABLE用来针对许多问题对表进行检查。

示例：

mysql> check table orders,orderitems;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| course.orders     | check | status   | OK       |
| course.orderitems | check | status   | OK       |
+-------------------+-------+----------+----------+
2 rows in set (0.00 sec)

mysql>

如果MyISAM引擎的表产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。但不应该经常使用，可能会造成更大的问题要解决。

3）OPTIMIZE TABLE
如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用空间，优化表的性能。
示例：

mysql>optimize table orders;

三、诊断启动问题

在排除系统启动问题时，首先应该尽量手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动，下面是几个重要的mysqld命令行选项：
--help 显示帮助
--safe-mode装载减去某些最佳配置的服务器
--verbose显示全文本消息（为获得更详细的帮助消息与--help联合使用）
--version显示版本信息

四、查看日志文件

MySQL主要日志文件有以下几种：

1)错误日志。包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err(可用--log-error命令行选项更改)，位于data目录中。

2)查询日志。记录所有mysql活动，在诊断问题时非常有用。此日志通常名为hostname.log(可用--log命令行选项更改)，位于data目录。

3)二进制日志。记录更新过数据(或者可能更新过数据)的所有语句，名为hostname-bin(可用--log-bin命令行选项更改)，位于data目录。

4)缓慢查询日志。此日志记录执行缓慢的任何查询，名为hostname-slow.log(用--log-slow-queries选项更改)

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。