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;
• 特定的列;
• 特定的存储过程。
• 下表列出可以授予或撤销的每个权限:

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用本身
DELETE	使用本身
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用本身
LOCK TABLES	使用本身
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用本身
SHOW DATABASES	使用本身
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown(用于关闭MySQL)
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用本身
USAGE	无访问权限

根据上表配合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 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语句来刷新和重新开始所有日志文件。