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

曾经终败给现在 2022-06-13 14:46 335阅读 0赞

本篇主要整理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语句自身中进行：

![这里写图片描述][SouthEast]

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

### **二、安全管理** ###

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

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

![这里写图片描述][SouthEast 1]

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）用户名。  
实例：

![这里写图片描述][SouthEast 2]  
可通过列出各权限并用逗号分隔，将多条 GRANT语句串在一起:  
GRANT SELECT, INSERT ON crashcourse.\* TO bforta;

撤销特定的权限：REVOKE

![这里写图片描述][SouthEast 3]

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

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

![这里写图片描述][SouthEast 4]  
![这里写图片描述][SouthEast 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帮助文档][MySQL]

[SouthEast]: /images/20220613/3f4a8d6e1ecb4fe9ad697f87bca06cef.png
[SouthEast 1]: /images/20220613/921de5b1258b4c75aad4d5393e10e49e.png
[SouthEast 2]: /images/20220613/cb40e7b8629b429196fa762cb23a127a.png
[SouthEast 3]: /images/20220613/2b2666cd551a41c9ac9de1645d0cbb94.png
[SouthEast 4]: /images/20220613/bea7da1aedc94eda82cfd728620ca336.png
[SouthEast 5]: /images/20220613/ca1c2b2658a5495ebe6e5c5f45ce9824.png
[MySQL]: https://dev.mysql.com/doc/%E7%9A%84MySQL/