第十章：触发器
同存储过程和函数类似，MySQL中的触发器也是存储在系统内部的一段程序代码，可以把它看作是一个特殊的存储过程。所不同的是，触发器无需人工调用，当程序满足定义条件时就会被MySQL自动调用。这些条件可以称为触发事件，包括INSERT、UPDATE和DELETE操作。
触发器常被用在数据库端确保数据的完整性。例如，在具体开发项目时，常会遇到如下情况，当在用户表中插入一个新用户后，用户总数必须要自动加1。就是在对表执行某项操作后，需要自动进行一些处理。此时就可以使用触发器处理数据库对象，可以创建一个触发器对象，每添加一条学生记录，就执行一次计算学生总数的操作，这样可以保证每次添加学生记录后，学生总数与学生记录数一致。
创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW trigger_body
触发器只能创建在永久表（Permanent Table）上，不能创建在临时表（Temporary Table）上。
从MySQL5.7.2开始，可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下，具有相同触发事件和触发时机的触发器按其创建顺序激活。
本节按照触发器触发时机的不同，分为AFTER和BEFORE两种情况，详细介绍触发器的创建。
AFTER触发器是指触发器监视的触发事件执行之后，再激活触发器，激活后所执行的操作无法影响触发器所监视的事件。以买家购买商品为例，首先向订单表中添加订单记录，再更新商品表中商品的数量，这种情况下无论商品是什么状态，订单都已经插入数据库。
AFTER触发器可以根据所监视的事件分为三种，分别是INSERT型、UPDATE型和DELETE型，下面分别介绍。
1、 INSERT型
创建如下两张测试表
goods表
字 段 名 |
数据类型 |
注 释 |
id |
INT(11) |
商品编号 |
name |
VARCHAR |
商品名称 |
num |
INT(11) |
商品库存 |
orders表
字 段 名 |
数据类型 |
注 释 |
oid |
INT(11) |
订单编号 |
gid |
INT(11) |
购买商品编号 |
amount |
INT(11) |
购买商品数量 |
创建数据库test_db
CREATE DATABASE test_db;
步骤1：登录MySQL，并选择数据库“test_db”。
use test_db;
步骤2：分别创建goods表与orders表
创建goods表
CREATE TABLE goods (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) UNIQUE,
num INT(11) DEFAULT 0
);
创建orders表
CREATE TABLE orders(
oid INT(11) PRIMARY KEY,
gid INT(11),
amount INT(11)
);
步骤3：执行以下SQL语句，向goods表中添加3条记录，完成表的创建。
INSERT INTO goods(id,name,num)
VALUES(1,'果粒橙', 10),
(2,'三元酸奶', 10),
(3,'加多宝',10);
创建AFTER触发器
为订单表创建触发器，就可以在向订单表插入记录的同时自动更新商品表中商品的库存。
示例：为订单表创建INSERT型触发器，并验证其应用。
步骤1：执行SQL语句，为订单表创建触发器。SQL语句及其执行结果如下：
DELIMITER //
CREATE TRIGGER tg1
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE goods SET num=num-1 WHERE id=1;
END //
DELIMITER ;
步骤2：执行SQL语句，在订单表orders中插入一条记录。SQL语句及其执行结果如下：
INSERT INTO orders(oid,gid,amount) values(1,1,1);
步骤3：执行SQL语句，查看goods表中数据。SQL语句及其执行结果如下：
为订单表重新创建触发器，使其更符合实际需要
步骤1：执行SQL语句，为订单表创建触发器tg2。SQL语句及其执行结果如下：
DELIMITER //
CREATE TRIGGER tg2
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE goods SET num=num-NEW.amount WHERE id=NEW.gid;
END //
DELIMITER ;
提示：对于INSERT型触发器而言，新插入的行使用NEW表示，引用行中的字段值可以使用“NEW.字段名”。
步骤2：激活触发器tg2之前，需要先把触发器tg1删除。执行以下SQL语句删除触发器tg1。
DROP TRIGGER tg1;
步骤3：执行以下SQL语句，向orders表中插入一条新记录。
INSERT INTO orders(oid,gid,amount) VALUES(2,2,3);
步骤4：执行SQL语句，查看goods表。SQL语句及其执行结果如下：
2、 UPDATE型
依然以前面创建的商品表和订单表为例进行介绍。假设买家修改了订单中的商品购买数量，系统首先需要修改orders表中的订单记录，然后goods表中对应的商品库存先要恢复为原来的数量，接着再减去新订单中商品的数量。
步骤1：创建UPDATE型触发器tg3，SQL语句及其执行结果如下：
DELIMITER //
CREATE TRIGGER tg3
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
UPDATE goods SET num = num+OLD.amount-NEW.amount WHERE id=NEW.gid;
END //
DELIMITER ;
对于UPDATE型触发器而言，修改操作之前的记录使用OLD表示，引用此条记录中的字段值可以使用“OLD.字段名”；修改操作后的记录使用NEW表示，引用此条记录中的字段值可以使用“NEW.字段名”。
步骤2：执行以下SQL语句，修改orders表中的第2条记录。
UPDATE orders SET amount = 5 WHERE oid = 2;
步骤3：执行SQL语句，查看goods表中的记录。SQL语句及其执行结果如下：
3、 DELETE型
依然以前面创建的商品表和订单表为例进行介绍。假设买家直接取消订单，系统要执行的操作是先删除orders表中的订单，然后goods表中对应的商品数量要恢复为原来的值。
为orders表创建DELETE型触发器tg4，并验证其效果。
步骤1：执行SQL语句，创建触发器tg4。SQL语句及其执行结果如下：
DELIMITER //
CREATE TRIGGER tg4
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
UPDATE goods SET num = num + OLD.amount WHERE id = OLD.gid;
END //
DELIMITER ;
对于DELETE型触发器而言，被删除的一行记录使用OLD表示，引用此条记录中的字段值可以使用“OLD.字段名”。
步骤2：执行以下SQL语句，删除orders表中的第2条记录。
DELETE FROM orders WHERE oid = 2;
步骤3：执行SQL语句，查看goods表中的记录。SQL语句及其执行结果如下：
创建BEFORE触发器
BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。
BEFORE触发器也可以根据监视事件分为三种，分别是INSERT型、UPDATE型和DELETE型。依然以前面创建的商品表和订单表为例进行介绍。假设订单中某商品的数量大于goods表中所对应商品的总量，goods表中的商品库存会出现负数。为避免这类问题，可以创建BEFORE触发器，这种情况下，系统会先判断订单中商品的购买数量，如果大于库存，则抛出异常，终止操作。
示例：为orders表创建BEFORE触发器tg5。
步骤1：执行SQL语句，创建触发器tg5。SQL语句及其执行结果如下：
DELIMITER //
CREATE TRIGGER tg5
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
UPDATE goods SET num=num-NEW.amount WHERE id=NEW.gid AND num>=NEW.amount;
IF ROW_COUNT() <> 1 THEN
SELECT CONCAT(name,'库存不足') INTO msg FROM goods WHERE id=NEW.gid;
SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = msg;
END IF;
END //
DELIMITER ;
提示：
1、ROW_COUNT()函数用于记录更新操作影响的行数，如果其值不等于1，就说明订单中商品的数量大于库存数量，goods表没有更新，此时将执行下面的语句，将“商品名+库存不足”赋给变量msg。
2、 SIGNAL语句用于在存储程序（例如存储过程、存储函数、触发器或事件）中向调用者返回错误或警告条件。此外，它还提供对错误特征（错误编号，SQLSTATE值，消息）的控制。
步骤2：执行SQL语句，在orders表中新增一条订单记录。SQL语句及其执行结果如下：
INSERT INTO orders (gid,amount) VALUES(3,20);
步骤3：执行SQL语句，查看goods表，结果如下：
查看触发器
在MySQL中，查看触发器有两种方法，一种是使用SHOW TRIGGERS语句，一种是在information_schema数据库的triggers表中查看触发器的详细信息。
在MySQL中，对同一个表相同触发时机的相同触发事件，只能定义一个触发器。例如，对于某个表的不同字段的AFTER更新触发器，只能定义成一个触发器，在触发器中通过判断更新的字段进行相应的处理。所以在创建触发器之前，最好能够查看MySQL中是否已经存在该触发器。
使用SHOW TRIGGERS语句可以查看MySQL中已经存在的触发器，基本语法形式如下：
SHOW TRIGGERS \G
使用SHOW TRIGGERS语句不仅可以查看所有触发器，也可以查看某个表上创建的触发器，其基本语法格式如下：
SHOW TRIGGERS FROM db_name LIKE 'table_name' \G
另外，如果用户需要精确查看某一个触发器，也可以使用SHOW TRIGGERS语句，其基本语法格式如下：
SHOW TRIGGERS WHERE `TRIGGER` LIKE ' trigger_name%'\G
执行SQL语句，查看触发器tg3，SQL语句及其执行结果如下：
SHOW TRIGGERS WHERE `TRIGGER` LIKE 'tg3%' \G
注意：精确查看某一个触发器时，WHERE子句中的列名TRIGGER需要使用反引号“`”，该符号位于键盘左上角。
MySQL中所有触发器的定义都存储在系统数据库information_schema中的triggers表中，可以通过查询语句SELECT查看，具体语法形式如下：
SELECT * FROM information_schema.triggers WHERE trigger_name='tri_name';
通过SELECT语句查看触发器tg5，SQL语句及其执行结果如下：
SELECT * FROM information_schema.triggers WHERE trigger_name='tg5' \G
删除触发器
使用DROP TRIGGER语句可以删除MySQL中定义的触发器，基本语法形式如下：
DROP TRIGGER data_name.trigger_name;
执行SQL语句，删除触发器tg3，SQL语句及其执行结果如下：
DROP TRIGGER test_db.tg3;
使用SHOW TRIGGERS语句查询触发器tg3，验证删除结果，SQL语句及其执行结果如下：
SHOW TRIGGERS WHERE `TRIGGER` LIKE 'tg3%' \G
第十一章：MySQL日志管理
错误日志
错误日志是MySQL最重要的日志之一，一般记录在MySQL服务器启动和停止时，以及MySQL在运行过程中发生任何严重错误时的相关信息。当数据库发生故障导致无法正常使用时，可以首先查看此日志。
在MySQL中，错误日志默认是开启的，并且该类型日志也无法被禁止。
默认情况下，错误日志文件一般放在MySQL服务器的数据文件夹（data）下，文件名为host_name.err，host_name表示服务器主机名。例如，MySQL所在的服务器主机名为ccy，则错误日志文件名为ccy.err。
可以通过修改配置文件/etc/my.cnf来自定义日志文件的名称和存储位置，具体方法为，在[mysqld]组下添加内容修改参数值，形式如下：
[mysqld]
#错误日志文件
log-error = [path / [filename]]
cat /etc/my.cnf
log-error=/var/log/mysqld.log
MySQL中的错误日志文件以文本文件形式存储，可以使用文本编辑器直接查看MySQL错误日志。
如果不知道日志文件的存储路径，可以使用SHOW VARIABLES语句查询错误日志的存储路径，语法形式如下：
SHOW VARIABLES LIKE 'log_error';
二进制日志
二进制日志（BINLOG）记录了所有DDL（对数据库内部对象进行创建、删除、修改等操作）和DML（对数据库中表记录的插入、更新、删除等操作）语句，但不包括数据查询语句。语句以“事件”形式存储，并且记录了语句发生时间、执行时长、操作的数据等。二进制日志对于数据损坏后的恢复起着至关重要的作用。本节主要介绍二进制日志的启动、设置、查看和删除方法。
默认情况下，二进制日志是关闭的，可以通过在配置文件my.ini（my.cnf）中[mysqld]组下添加以下内容，来启动二进制日志：
#开启二进制日志
log_bin [=path / [filename] ]
#此处必须指定server_id，这是MySQL 5.7.3版本之后的要求
server_id = 1
[root@liwenjing log]# cat /etc/my.cnf
log_bin=/var/log/mysql/binlogs/mysql
server_id=1
mkdir -p /var/log/mysql/binlogs
chown -R mysql:mysql /var/log/mysql
[root@liwenjing log]# systemctl restart mysqld.service
上述语句中，path表示二进制日志文件的存储路径，默认位于数据文件夹下；filename表示二进制日志文件名，具体格式为hostname-bin.number，其中number的格式可以为000001、000002、000003等。
另外，通过在配置文件/etc/my.cnf中[mysqld]组下添加以下内容，可以设置二进制日志的过期天数，以及单个日志文件的大小。具体形式如下：
expire_logs_days = 10
max_binlog_size = 100M
需要注意的是，某些二进制文件的大小可能会超出max_binlog_size值，因为一个事务所产生的所有事件都必须要记录在同一个二进制文件中。这种情况下，即便二进制文件的大小超出max_binlog_size的值，也会等到当前事务的所有操作全部写入当前日志文件中后才会重新创建文件。
配置完成后，需要重启MySQL服务器才会生效。执行SHOW VARIABLES语句可查询二进制日志的相关配置，具体语法形式如下：
SHOW VARIABLES LIKE '%bin%';
查看二进制文件
由于二进制日志以二进制方式存储，不能直接读取，需要使用mysqlbinlog命令查看，语法形式如下：
mysqlbinlog log-file;
使用mysqlbinlog命令查看二进制日志。
步骤1：打开命令窗口，在其中输入mysqlbinlog命令，查看最新的二进制日志，执行结果如下：
[root@liwenjing log]# mysqlbinlog /var/log/mysql/binlogs/mysql.000002
使用mysqlbinlog命令时不需要登录MySQL。
步骤2：登录MySQL，并对数据库中任意一张表执行数据修改操作。例如，将galaxy数据库中tb_goods表id值为1的记录中num值修改为8，SQL语句及其执行结果如下：
use galaxy;
UPDATE tb_goods SET num=8 WHERE id=1;
删除二进制日志
用户可以在配置文件中设置参数expire_log_days值，使系统自动删除过期日志文件，也可以手动删除。
1、 删除所有日志文件
登录MySQL后，执行RESET MASTER语句可以删除所有二进制日志文件，其语法形式如下：
RESET MASTER;
执行该语句后，系统会将所有二进制日志删除，MySQL会重新创建二进制日志，新的日志文件后缀名将重新从“000001”开始编号。
RESET MASTER;
2、 删除指定日志文件
实际应用中，通常不会一次性删除所有日志文件，使用PURGE MASTER LOGS语句可以删除指定的日志文件。其基本语法形式如下：
PURGE { MASTER | BINARY } LOGS {TO 'log_name' | BEFORE 'date' };
使用PURGE MASTER LOGS语句删除指定的日志文件。
步骤1：为了实现操作过程，用户可以多次重启MySQL服务，创建多个二进制日志文件，然后执行以下命令查看所有的二进制日志文件，结果如下：
SHOW BINARY LOGS
步骤2：执行PURGE MASTER LOGS语句，删除mysql.000003之前的日志文件，结果如下：
PURGE BINARY LOGS TO 'mysql.000003';
删除二进制日志文件后，可能导致数据库崩溃后无法恢复。因此，删除之前应将其和数据库备份。
通用查询日志
与二进制日志不同，通用查询日志会记录用户的所有操作。
通用查询日志记录了服务器接收到的每一个查询或命令，而不管这些查询或命令是否返回结果，甚至是否包含语法错误。因此，开启通用查询日志会产生很大的系统开销，一般在需要采样分析或跟踪某些特殊的SQL性能问题时才会开启。
默认情况下，通用查询日志是关闭的，可以通过在配置文件中[mysqld]组下添加以下内容开启通用查询日志：
cat /etc/my.cnf
#开启通用查询日志
general_log=1
默认情况下，通用查询日志位于data文件夹下，文件名为hostname.log。如要更改其位置和文件名，可在配置文件中[mysqld]组下设置general_log_file参数：
general_log_file [=path / [filename] ]
使用SHOW VARIABLES语句，可以查看通用查询日志是否开启，以及日志文件的路径，基本语法形式如下：
启动并设置通用查询日志。
SHOW VARIABLES LIKE '%general%';
通用查询日志是以文本文件的形式存储在文件系统中的，用户可以使用文本编辑器直接打开进行查看。
慢日志查询
查询日志记录查询时长超过指定时间的日志。通过慢查询日志，可以找出哪些语句执行时间较长、执行效率较低，以便进行优化。
MYSQL中的慢查询日志默认是关闭的，一般建议开启，它对服务器性能的影响微乎其微。
可以通过在配置文件中[mysqld]组下添加以下内容启动慢查询日志：
slow_query_log=1
另外，启动慢查询日志时，还需要在配置文件中设置long_query_time项指定记录阈值。该值默认为10，以秒为单位，可以精确到微秒。如果一个查询语句执行时间超过阈值，该查询语句将被记录到慢查询日志中。
默认情况下，慢查询日志文件位于data文件夹下，文件名为hostname-slow.log，也可以在配置文件中设置slow_query_log_file项，为日志文件指定存储路径和文件名，语法形式如下：
slow_query_log_file [ = path [ /filename ] ]
默认情况下，慢查询日志同样以文本文件的形式存储在文件系统中，可以使用文本编辑器直接打开查看。
设置慢查询日志，并使用记事本打开MySQL慢查询日志。
首先在配置文件中添加以下语句，开启慢查询日志，并设置long_query_time项指定记录阈值。
slow_query_log=1
long_query_time = 0.02
[root@liwenjing log]# systemctl restart mysqld.service
重新启动并登录MySQL服务，使用SHOW VARIABLES语句，可以查看通用查询日志是否开启，以及日志文件的路径
SHOW VARIABLES LIKE '%slow%';
执行一次超过参数long_query_time所设置时间的查询语句，SQL语句及其执行结果如下：
SELECT id,name FROM tb_goods WHERE type='糖类';
由于慢查询日志也是存储在文本文件中，所以可以直接删除，删除后在不重启MySQL服务的情况下，执行以下语句可重新创建日志文件。
FLUSH SLOW LOGS;
第十二章：备份与还原
对于一个数据库管理员来说，针对可能造成数据丢失的原因，制定合理的备份与恢复策略以防止数据丢失是非常必要的。
造成数据丢失的原因：
1、 存储介质损坏：人为或自然灾害导致存储数据的磁盘损坏。
2、 用户误操作：误删或修改了某些重要数据。
3、 服务器崩溃：高并发或者大流量导致数据库服务器崩溃。
4、 人为破坏：遭到特殊人员的恶意攻击。
常用解决方案：
1、 对特别重要的数据应保留多个备份。
2、 确定使用完整备份还是增量备份。
3、 可以考虑使用复制数据文件的方法作异地备份，但这种方法无法对误操作的数据进行恢复。
4、 要定期对数据进行备份，并且要在系统负载较小的时间段进行。
5、 确保开启二进制日志，这样可以基于时间点或位置对数据进行恢复。
6、 定期作备份恢复测试，保证备份是有效的，并且是可以恢复的。
知识库：增量备份是指在一次完整备份或上一次增量备份后，以后每次只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行完整备份后所增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所增加和修改的文件，依此类推。
数据备份
mysqldump是MySQL自身提供的一个非常好用的数据库备份工具。它可以将数据备份为一个文本文件，其中包含一组能够被执行以再现原始数据库对象定义和表数据的SQL语句，如CREATE和INSERT语句。
使用mysqldump备份数据库的基本语法形式如下：
mysqldump -u user -p password [options] db_name1[ db_name2… ]
> [path/]db_name.sql
示例：
使用mysqldump备份数据库。
[root@liwenjing ~]# mysqldump -u root -p galaxy > /opt/mysql-2022-09-27.sql
Enter password:
使用mysqldump工具也可以备份所有数据库，语法格式如下：
[root@liwenjing opt]# mysqldump -u root -p --all-databases > /opt/all.2022-09-27.sql
使用mysqldump备份数据表
使用mysqldump还可以备份数据表，基本语法形式如下：
mysqldump -u username -p [options] db_name tb_name[ tb_name2 …]
> [path/]tb_name.sql
示例：
使用mysqldump备份命令并执行，备份galaxy数据库中的tb_goods表和orders表。结果如下：
[root@liwenjing opt]# mysqldump -u root -p galaxy tb_goods orders > /opt/tb_goods_orders.2022-09-27.sql
提示：在MySQL服务运行的情况下，为了保证数据的一致性，需要特别注意的是：备份MyISAM存储引擎类型的表时，要在mysqldump命令中加上--lock-tables参数，用于将所有的数据表加上读锁，这样在备份期间，所有表将只能读取而不能进行数据更新；而对于InnoDB存储引擎类型的表，最好使用参数--single-transaction，这样可以使InnoDB存储引擎生成一个快照。
数据恢复
数据库管理员操作失误、计算机故障以及其他意外情况，都有可能导致数据丢失或破坏。当数据丢失或遭到意外破坏时，可以使用数据备份恢复数据以减少损失。
使用MySQL命令恢复数据非常简单，基本语法形式如下：
mysql -u user -p password db_name < [path/]db_name.sql
示例：
1、 登录MySQL，并执行以下语句，删除galaxy数据库。
DROP DATABASE galaxy;
2、 恢复数据库之前，首先执行以下语句创建一个空数据库galaxy，然后退出MySQL
CREATE DATABASE galaxy;
3、 执行SQL语句，使用MySQL命令恢复数据库。SQL语句及其执行结果如下：
[root@liwenjing opt]# mysql -u root -p galaxy < mysql-2022-09-27.sql
4、 登录MySQL，选择数据库，并查看表中数据是否恢复，结果如下。
也可以使用如下命令恢复误删的数据表
mysql -u root -p galaxy < tb_goods_orders.2022-09-27.sql
使用二进制日志恢复数据
第十一章介绍了二进制日志的相关操作，本节介绍其在实际操作中的应用。我们知道，开启二进制日志后，系统会自动记录用户执行的数据更新操作。可以将二进制日志看作一个备份，使用mysqlbinlog命令恢复数据。
mysqlbinlog log_name | mysql -u user -p pass
使用二进制日志恢复数据。本实例将模拟存储介质损坏导致数据丢失后，如何使用二进制日志恢复数据。
步骤1：登录MySQL，创建demo数据库，然后创建tb_demo表并插入两条记录。SQL语句及其执行结果如下：
CREATE DATABASE demo;
USE demo;
CREATE TABLE tb_demo(
id INT(11) PRIMARY KEY,
name varchar(20)
);
INSERT INTO tb_demo(id,name) VALUES(1,'mary');
INSERT INTO tb_demo(id,name) VALUES(2,'lucy');
步骤2：退出MySQL，并执行mysqlbinlog命令，查看二进制日志文件，其中详细记录了MySQL执行的每一步操作，关键代码如下：
[root@liwenjing opt]# mysqlbinlog /var/log/mysql/binlogs/mysql.000013
步骤3：登录MySQL，并执行SQL语句暂停二进制日志，然后删除demo数据库，模拟存储介质损坏导致数据丢失。SQL语句及其执行结果如下：
SET SQL_LOG_BIN =0;
DROP DATABASE demo;
注意：此处必须要暂停二进制日志，否则删除数据库的操作也将被记录进日志中。
步骤4：执行SQL语句开启二进制日志，然后退出MySQL，并执行mysqlbinlog命令，将demo数据库中的数据完全恢复。SQL语句及其执行结果如下：
SET SQL_LOG_BIN =1;
EXIT
[root@liwenjing ~]# mysqlbinlog /var/log/mysql/binlogs/mysql.000013 |mysql -uroot -p
Enter password:
步骤5：重新登录MySQL，并查看tb_demo表中的数据。SQL语句及其执行结果如下：
USE demo;
