MySQL基础（10）——字符集和语言、安全管理、数据库维护

本篇主要整理MySQL中处理不同字符集和语言的基础知识、访问控制和用户管理以及常见的数据库维护。

一、字符集和校对

字符集：字母和符号的集合。
编码：某个字符集成员的内部表示。
校队：规定字符如何比较的指令。

查看所支持的字符集完整列表：
SHOW CHARACTER SET;
查看所支持校队的完整列表：
SHOW COLLATION;

_cs表示区分大小写， _ci表示不区分大小写。
通

指定默认的字符集和校对：
SHOW VARIABLES LIKE ‘character%’;
SHOW VARIABLES LIKE ‘collation%’;
create table mytable
(
column1 int,
column2 varchar(10)
)default character set hebrew
collate hebrew_general_ci;

MySQL使用什么样的字符集和校对的规则：
（1）如果指定CHARACTER SET和COLLATE两者，则使用这些值。
（2）如果只指定CHARACTER SET，则使用此字符集及其默认的校对。
（3）如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

MySQL还允许对每个列设置字符集和校对：
create table mytable
(
  column1 int,
  column2 varchar(10),
  column3 varchar(10) character set latin1
         collate latin1_general_ci
)default character set hebrew
collate hebrew_general_ci;

校对在对用ORDER BY子句检索出来的数据排序时起重要 的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：

该例子在通常不区分大小写的表上进行区分大小写搜索的一种技术。
注意：COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等

二、安全管理

访问控制：对用户提供他们所需的访问权，且仅提供他们所需的访问权。管理访问控制需要创建和管理用户账号。
访问控制的目的不仅仅是：防止用户的恶意企图。避免无意识错误的结果，如错打MySQL语句，在不合适的数据库中操作或其他一些用户错误等情况的发生。

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

2、 创建用户账号：
create user ben identified by ‘p@$$w0rd’;（创建账户时不一定需要口令，即”identified by ‘p@$$w0rd’部分）

IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD。

重新命名一个用户账号：
RENAME USER ben TO bforta;

仅MySQL 5或之后的版本支持RENAME USER。 为了在以前的MySQL中重命名一个用户，可使用UPDATE直接更新user表

3、 删除用户账号：
drop user bforta;

自MySQL 5以来，DROP USER删除用户账号和所有相关的账号权限。在MySQL 5以前，DROP USER只能用来 删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

4、设置访问权限
SHOW GRANTS FOR bforta;

新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作，因此在创建用户账号后，必须接着分配访问权限。
用户定义为user@host：MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。

GRANT所需的必要信息：
（1）要授予的权限；
（2）被授予访问权限的数据库或表；
（3）用户名。
实例：

可通过列出各权限并用逗号分隔，将多条 GRANT语句串在一起:
GRANT SELECT, INSERT ON crashcourse.* TO bforta;

撤销特定的权限：REVOKE

注意：被撤销的访问权限必须存在，否则会出错。

注意：GRANT和REVOKE可在几个层次上控制访问权限：
（1）整个服务器，使用GRANT ALL和REVOKE ALL；
（2）整个数据库，使用ON database.*；
（3）特定的表，使用ON database.table；
（4）特定的列；
（5）特定的存储过程。
具体权限如下表：

注意：在使用GRANT和REVOKE时，用户账号必须存在， 但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。 这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

5、更改口令
set password for bforta = Password(‘n3w p@$$w0rd’);

在不指定用户名时，SET PASSWORD更新当前登录用户的口令。 因此SET PASSWORD也可以用来设置自己的口令：
set password = Password(‘n3w p@$$w0rd’);

三、数据库维护

1、 备份数据

MySQL数据库是基于磁盘的文件

为解决数据库文件总是处于打开和使用的状态：
（1）使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
（2）可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
（3）可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。

首先刷新未写数据 ：为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

2、数据库维护

检查表键是否正确：ANALYZE TABLE +表名（多个表用逗号隔开）；
针对许多问题对表进行检查（在MyISAM表上还对索引进行检查)：CHECK TABLE +表名（多个表用逗号隔开）;
其他：
CHANGED：检查自最后一次检查以来改动过的表EXTENDED：执行最彻底的检查
FAST：只检查未正常关闭的表
MEDIUM：检查所有被删除的链接并进行键检验
QUICK：只进行快速扫描
REPAIR TABLE：修复表。这条语句不应该经常使用，如果 需要经常使用，可能会有更大的问题要解决。
OPTIMIZE TABLE：收回所用的空间，从而优化表的性能（适用于从一个表中删除大量数据时）

3、其他常用命令：

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。在排除系统启动问题时，首先应该尽量用手动启动服务.

MySQL 服务器自身通过在命令行上执行mysqld启动。
几个重要的mysqld 命令行选项：
–help显示帮助——一个选项列表；
–safe-mode装载减去某些最佳配置的服务器；
–verbose显示全文本消息（为获得更详细的帮助消息与–help 联合使用）；
–version显示版本信息然后退出。

日志文件相关命令：
（1）错误日志：它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用 –log-error命令行选项更改。
（2）查询日志：它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用 –log命令行选项更改。
（3）二进制日志：它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。
（4）缓慢查询日志：顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ，位于data 目录中。此名字可以用 –log-slow-queries命令行选项更改。
（5）在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

---

改善数据库性能的一些注意事项
1. MySQL（与所有DBMS一样）具有特定的硬件建议。用于生产的服务器来说，应该坚持遵循这些硬件建议。
2. 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
3. MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;。）
4. MySQL一个多用户多线程的DBMS，如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录） 。
5. 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
6. 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
7. 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL 语句快。
8. 应该总是使用正确的数据类型。
9. 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非真正需要每个列）。
10. 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
11. 在导入数据时，应该关闭自动提交。若还想删除索引（包括 FULLTEXT索引），则在导入完成后再重建它们。
12. 必须索引数据库表以改善数据检索的性能。需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
13. 通过使用多条SELECT语句和连接它们的UNION语句，可以得到极大的性能改进。
14. 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。对于一些收集数据但不经常被搜索的表，尽量减少索引它们。（索引可根据需要添加和删除。）
15. LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
16. 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
17. 最重要的规则就是，每条规则在某些条件下都会被打破。

其他内容可参考网址：MySQL帮助文档