运维

ccj 5/15/2022 Mysql

摘要

Mysql Version：5.7.36
官方文档：https://dev.mysql.com/doc/refman/ (opens new window)
样例表：下载

# 一：全球化和本地化

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

# 1.1 字符集和校对顺序

查看所支持的所有字符集：

SHOW CHARACTER SET;

查看所支持校对的完整列表：

SHOW COLLATION;

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句：

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

1
2

可以在创建表指定字符集和校对

CREATE TABLE mytable (
	columnn1 INT,
	columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew 
  COLLATE hebrew_general_ci;

1
2
3
4
5

指定了 hebrew 字符集和 hebrew_general_ci 校对顺序。

确认字符集和校对顺序：

如果指定 CHARACTER SET 和 COLLATE 两者，则使用这些值；
如果只确定 CHARACTER SET，则使用此字符集及其默认的校对；
如果既不指定 CHARACTER SET，也不指定 COLLATE，则使用数据库默认。

对列设置字符集：

CREATE TABLE mytable (
	columnn1 INT,
	columnn2 VARCHAR(10),
	columnn3 VARCHAR(10), CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

1
2
3
4
5
6

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

SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;

COLLATE 除了用于 ORDER BY 子句中使用以外，COLLATE 还可以用于 GROUP BY、HAVING、聚集函数、别名等。

# 二：安全管理

限制用户的权限，区分管理员、开发人员、用户等。

# 2.1 查看用户

MySQL用户账号和信息存储在名为 mysql 的MySQL数据库中。一般不需要直接访问 mysql 数据库和表，但有时需要直接访问。例如，需要获得所有用户账号列表时。可使用以下代码：

USE mysql;
SELECT host, user FROM user;

1
2

# 2.2 新增

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

IDENTIFIED BY：指定的密码为纯文本，MySQL将在保存到user表之前对其进行加密。

可以直接 INSERT INTO 到user表来新增用户，不过为了安全起见，一般不建议这样做。

# 2.3 重命名

RENAME USER ben TO bforta;

MySQL 5 及之后版本才支持 RENAME，之前的版本可使用 UPDATE 更新 user 表

# 2.4 删除

删除一个用户账号（以及相关的权限），使用 DROP USER 语句

DROP USER bforta;

MySQL 5 之前的版本，需要先用 REVOKE 删除与账号相关的权限，然后再用 DROP USER 删除账号。

# 2.5 设置访问权限

新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

查看用户账号的权限，使用 SHOW GRANTS FOR

SHOW GRANTS FOR bforta;

上面的结果表明该用户无任何权限。其中 'bforta'@'%' 表示，MySQL的权限用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名 %

使用 GRANTS 语句设置权限：

GRANT SELECT ON ccj_test.* TO bforta;

说明：授予用户 bforta 在 ccj_test 数据库的所有表使用 SELECT 访问权限。

撤销特定权限

REVOKE SELECT ON ccj_test.* FROM bforta;

被撤销的权限必须存在，否则会报错

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

简化多次授权

GRANT SELECT, INSERT ON ccj_test.* TO bforta;

# 2.6 更改密码

修改指定用户的密码

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

新密码必须传递到 Password 函数进行加密

修改当前登录用户的密码

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

# 三：数据库维护

# 3.1 备份数据

常用方式：

可用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。
可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据。
可以使用MySQL的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件。这两条语句都要衔接将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用 RESTORE TABLE 来复原。

为了保证所有数据被写到磁盘(包括索引数据)，需要在进行备份前使用 FLUSH TABLES 语句

# 3.2 数据库维护

ANALYZE TABLE：用来检查表键是否正确

ANALYZE TABLE orders;

CHECK TABLE：用来对表进行检查，在MyISAM表上还对索引进行检查。支持一系列的用于MyISAM表的方式。

CHECK TABLE orders, orderitems;

CHANGED：检查自最后一次检查以来改动过的表； EXTENDED：执行最彻底的检查； FAST：只检查未正常关闭的表； MEDIUM：检查所有被删除的链接并进行键检验； QUICK：只进行快速扫描。

如果MyISAM表访问产生不正确和不一致的结果，可能需要用 REPAIR TABLE 来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决；
如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来收回所用的空间，从而优化表的性能。

# 3.3 诊断启动问题

可以通过在命令行上执行 mysqld 启动

参数选项：

--help：显示帮助；
--safe-mode：装载减去某些最佳配置的服务器；
--verbose：显示全文本消息(为获得更详细的帮助消息与 --help 联合使用)；
--version：显示版本信息然后退出

# 3.4 查看日志文件

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

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

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

# 四：性能优化

性能优化探讨和分析的一些出发点：

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

# 五：参考文献

《MySQL 是怎样运行的 - 小孩子4919》
《MySQL 必知必会 - Ben Forta著（刘晓霞、钟鸣）译》
官方文档 (opens new window)

事务 WHERE 1=1

湫枫染墨

运维

运维

# 一：全球化和本地化

# 1.1 字符集和校对顺序

# 二：安全管理

# 2.1 查看用户

# 2.2 新增

# 2.3 重命名

# 2.4 删除

# 2.5 设置访问权限

# 2.6 更改密码

# 三：数据库维护

# 3.1 备份数据

# 3.2 数据库维护

# 3.3 诊断启动问题

# 3.4 查看日志文件

# 四：性能优化

# 五：参考文献