Skip to content

Latest commit

 

History

History
325 lines (242 loc) · 14.2 KB

7-管理.md

File metadata and controls

325 lines (242 loc) · 14.2 KB

管理事务处理

事务处理

InnoDB支持事务处理

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

事务处理过程：

  1. 检查数据库中是否存在相应的客户，如果不存在，添加他/她。
  2. 提交客户信息。
  3. 检索客户的ID。
  4. 添加一行到orders表。
  5. 如果在添加行到orders表时出现故障，回退。
  6. 检索orders表中赋予的新订单ID。
  7. 对于订购的每项物品，添加新行到orderitems表。
  8. 如果在添加新行到orderitems时出现故障，回退所有添加的orderitems行和orders行。
  9. 提交订单信息。

在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语：

  • 事务（transaction）指一组SQL语句；
  • 回退（rollback）指撤销指定SQL语句的过程；
  • 提交（commit）指将未存储的SQL语句结果写入数据库表；
  • 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

控制事务处理

使用ROLLBACK

ROLLBACK撤回MySQL语句

SELECT * FROM ordertitals;
START TRANSCITION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动 进行的。 但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：

START TRAMSCATION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符 SAVEPOINT

SAVEPOINT delete1;
ROLLBACK TO delete1;

每个保留点都取标识它的唯一名字,以便在回退时，MySQL直到要回退到何处。

更改默认的提交行为

默认MySQL行为是自动提交所有更改，为指示MySQL不自动提交更改，需要使用

SET autocommit=0;

全球化和本地化

使用字符集和校对顺序

  • 字符集为字母和符号的集合；
  • 编码为某个字符集成员的内部表示；
  • 校对为规定字符如何比较的指令。
--查看所支持的字符集完整列表
SHOW CHARECTER SET;
--查看所支持校对的完整列表
SHOW COLLATION；
--确定所用的字符集和校对
SHOW VARIABLSE LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

为了给表指定字符集和校对，可以使用带子句的CREATE TABLE

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

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们

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

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

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

此SELECT使用COLLATE指定一个备用的校对顺序（在这个例子中，为区分大小写的校对） 最后，值得注意的是，如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。

安全管理

访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权

考虑以下内容：

  • 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
  • 某些用户需要读表，但可能不需要更新表；
  • 你可能想允许用户添加数据，但不允许他们删除数据；
  • 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
  • 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
  • 你可能想根据用户登录的地点限制对某些功能的访问。

不过在现实世界的日常工作中，决不能使用root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。

管理用户

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

USE mysql;
SELECT user FROM user;

创建用户账号

CREATE USER ben IDENTIFIED BY 'p@ssw0rd';

删除用户账号

DROP USER bforta;

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

设置访问权限

查看账号的权限

SHOW GRANTS FOR bforta
--output
+---------------------------------------------+
| Grants for bforta@%                         |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'          |
+---------------------------------------------+

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

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

  • 要授予的权限；
  • 被授予访问权限的数据库或表；
  • 用户名。

例：

GRANT SELECT ON crashcourse.* TO beforta;

-- 此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。


SHOW GRANTS FRO bforta;
+--------------------------------------------------+
| Grants for bforta@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'               |
| GRANT SELECT ON 'charshcourse'.* TO 'bforta'@'%' |
+--------------------------------------------------+

撤销特定权限 REVOKE

REVOKE SELECT ON crashcourse.* FROM bforta

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错

权限 说明
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 VEIW
DELETE 使用DELET
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 使用UPDATE
USAGE 无访问权限

更改口令

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

设置自己的口令

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

不指定用户名时SET PASSWORD更新当前登陆用户的口令

数据库维护

备份数据

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

进行数据维护

ANALYZE TABLE检查表键是否正确

ANALYZE TABLE orders;
--output
+-----------------------+-----------+-----------+-----------+
| Table                 | Op        | Msg_type  | Msg_text  |
+-----------------------+-----------+-----------+-----------+
| crashcourse.orders    | analyze   | status    | OK        |
+-----------------------+-----------+-----------+-----------+

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

CHECK TABLE orders, orderitems;

诊断启动问题

--help 显示帮助
--safe-mode 装在减去某些最佳配置的服务器
--verbose 显示全文本消息
--version显示版本信息然后推出

查看日志文件

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

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

改善性能

  • 首先，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。
  • 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
  • 最重要的规则就是，每条规则在某些条件下都会被打破。