MySQL必知必会——安全管理、数据库维护

一、安全管理

1、访问控制

MySQL服务器的安全基础是： 用户应该对他们需要的数据具有适当的访问权限，不能多也不能少。
即用户不能对过多的数据具有过多的访问权限。
管理访问控制需要创建和管理用户账号。
在现实的日常工作中，需要创建一系列的账号，有的用于管理，有的供用户使用，还有的供开发人员使用。

2、管理用户

MySQL用户账号和信息存储在名为mysql的数据库中。一般不需要直接访问mysql数据库和其中的表。在要获得所有用户账号列表时才需要直接访问。

USE mysql；
SELECT user FROM user；

2.1、创建用户账号

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

CREATE USER创建了一个新用户账号，在创建用户账号时不一定需要给出口令，比不过这里使用了IDENTIFIED BY给出了。
重新命名一个用户账号：

RENAME USER ben TO bforta；

2.2、删除用户账号

为了删除一个用户账号(以及其相关的权限)可以使用DROP USER语句：

DROP USER bforta;

2.3、设置访问权限

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有任何的访问权限，它们能登录MySQL，但是不能看到数据，不能执行任何数据库操作。
为了看到赋予用户账号的权限：

MariaDB [mysql]> SHOW  GRANTS FOR kobe;
+-----------------------------------------------------------------------------------------------------+
| Grants for kobe@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kobe'@'%' IDENTIFIED BY PASSWORD '*665C8C3B7EC07AEEEF14B405DE85A239BEBFA1F3' |
+-----------------------------------------------------------------------------------------------------+

输出结果显示用户kobe有一个权限USAGE ON *.*。USAGE表示根本没有权限，即该结果表示在任意数据库和任意表上对任何东西没有权限。

为了设置权限使用GRANT语句，需要给出被授予的权限 、被授予访问权限的数据库或表、用户名。

GRANT SELECT ON course.* TO kobe;

使得kobe用户可以在course数据库中使用SELECT权限。

MariaDB [(none)]> SHOW  GRANTS FOR kobe;
+-----------------------------------------------------------------------------------------------------+
| Grants for kobe@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kobe'@'%' IDENTIFIED BY PASSWORD '*665C8C3B7EC07AEEEF14B405DE85A239BEBFA1F3' |
| GRANT SELECT ON `course`.* TO 'kobe'@'%'                                                            |
+-----------------------------------------------------------------------------------------------------+

每个GRANT添加用户的一个权限，MySQL读取所有授权，并根据它们确定权限。

GRANT的反操作为REVOKE用来撤销特定的权限。

REVOKE SELECT ON course.* FROM kobe;

被撤销的访问权限必须存在，否则会出错。
GRANT 和 REVOKE可在几个层次上控制访问权限：
　　　　整个服务器，使用 GRANT ALL 和 REVOKE ALL;
　　　　整个数据库，使用 ON database.*;
　　　　特定的表，使用 ON database.table;
　　　　特定的列；
　　　　特定的存储过程。
可对用户设定的权限表：

2.4、更改口令

SET PASSWORD更新用户口令，新口令必须传递到Password()函数进行加密。

SET PASSWORD FOR kobe = Password('n3w p@$$w0rd');

如果不指定用户，则SET PASSWORD 更改当前用户口令

二、数据库维护

1、备份数据

命令行程序mysqldump,转储所有数据库内容到某个外部文件。
命令行程序mysqlhostcopy，从一个数据库复制所有数据。
可使用SQL语句BACKUP TABLE或者SELECT INTO OUTFILE转储所有数据到某个外部文件，这俩个语句都接受将要创建的系统文件。 数据可以用RESTORE TABLE来复原。
在进行备份前需要使用FLUSH TABLES语句来刷新未写数据，以保证所有数据被写到磁盘中。

2、进行数据库维护

MySQL提供了一些语句来保证 数据库正确和正确运行。
检查表键是否正确ANALYZE TABLE

MariaDB [course]> ANALYZE TABLE orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| course.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+

针对许多问题对表进行检查CHECK TABLE。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表，EXTENDED执行最彻底的检查，FAST只检查为正常关闭的表。MEDIUM检查所有被删除的链接并进行键检查。QUICK只进行快速扫描。

MariaDB [course]> CHECK TABLE orders,orderitems;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| course.orders     | check | status   | OK       |
| course.orderitems | check | status   | OK       |
+-------------------+-------+----------+----------+

如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIRE TABLE来修复相应的表。
如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间。从而优化表的性能。

3、诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时 报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能 看不到。
在排除系统启动问题时，首先应该尽量手动启动服务器，MySQL服务器自身通过在命令行上执行mysqld启动。
下面是几个重要的mysqld命令行选项：
--help显示帮助
--safe-mode装载减去某些最佳配置的服务器
--verbose显示全文信息
--version显示版本信息然后退出。

4、查看日志文件

MySQL维护管理员依赖一系列的日志文件，主要的日志文件有以下几种：

• 错误日志。包含启动和关闭问题以及任意关键错误的细节，此日志通常名为hostname.err，位于data目录中。此日志名可以用--log-error命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用，此日志文件可能会很快地变得非常大，因此不应该长期使用。通常名为hostname.log，位于data目录。此名字可以用--log命令行选项进行修改。
• 二进制日志。它记录更新过数据的所有语句。此日志通常名为hostname-bin，位于data目录中。可以用--log-bin命令行选项进行修改。
• 缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处优化很有用，此日志通常命名为hostname-slow.log,位于data目录中。此名字可以用--log-=slow-queries命令行选项进行修改。

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