数据库原理实验-第四次

2024-11-25

Uncategorized

20k words

[TOC]

实验解答

1. 编写一个事务处理实现转账操作

为了实现从银行卡向校园卡转账的功能，我们需要确保整个转账过程要么全部成功，要么全部失败。这可以通过使用事务来实现。下面是一个转账200元的例子：

START TRANSACTION;

-- 从icbc_card表中扣除200元
UPDATE icbc_card SET balance = balance - 200 WHERE sno = '20200032';

-- 在campus_card表中增加200元
UPDATE campus_card SET balance = balance + 200 WHERE sno = '20200032';

-- 检查事务是否正确执行，这里仅作为示例，实际应用中可能需要更复杂的检查
IF ROW_COUNT() = 1 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

2. 展现数据不一致问题

丢失修改：两个事务都读取同一记录，然后都更新该记录。如果后一个事务提交，前一个事务的更新就会被覆盖，导致前一个事务的修改丢失。
读脏数据：一个事务读取了另一个事务未提交的修改结果。如果后者回滚，前者读取的数据就是无效的。
不可重复读：在一个事务内多次读取同一记录，但由于其他事务的修改或删除操作，使得每次读取的结果不同。
幻读：在一个事务中两次执行相同的查询，但第二次查询返回的结果集中包含了第一次查询没有的记录，这是因为其他事务在这期间插入了新的记录。

3. 解决数据不一致问题

丢失修改：使用可重复读（Repeatable Read）隔离级别或更高的隔离级别，比如串行化（Serializable）。
读脏数据：使用读已提交（Read Committed）隔离级别或更高。
不可重复读：使用可重复读隔离级别。
幻读：使用可重复读隔离级别或更高，或者使用行级锁。

4. 查看事务及锁状态

SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

5. 构造死锁情形

死锁通常发生在两个或多个事务互相等待对方释放资源的情况下。例如：

事务A锁定表T1，请求锁定表T2。
事务B锁定表T2，请求锁定表T1。

6. 使用 Savepoint

START TRANSACTION;

-- 执行一些SQL语句
INSERT INTO some_table (col1, col2) VALUES ('value1', 'value2');

-- 设置保存点
SAVEPOINT my_savepoint;

-- 继续执行更多SQL语句
INSERT INTO another_table (col1, col2) VALUES ('value3', 'value4');

-- 如果需要回滚到保存点
ROLLBACK TO SAVEPOINT my_savepoint;

COMMIT;

7. 查看MySQL中的各种日志

查询日志：tail -f /var/log/mysql/query.log
错误日志：tail -f /var/log/mysql/error.log
慢查询日志：tail -f /var/log/mysql/slow-query.log

8. 用mysqlbinlog恢复数据

首先，你需要启用二进制日志功能。然后，根据提供的SQL步骤，可以使用如下命令恢复t1表的数据：

# 导出二进制日志到文本文件
mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-01 23:59:59" /path/to/binlog.000001 > binlog_output.sql

# 查找并编辑binlog_output.sql文件，去除掉drop table t1; 之后的所有语句

# 将恢复脚本导入数据库
mysql -u username -p db1 < binlog_output.sql

附：

数据不一致问题的具体例子

假设我们有两个事务T1和T2，我们将通过具体的SQL操作来展示四种常见的数据不一致问题：丢失修改、读脏数据、不可重复读和幻读。

1. 丢失修改 (Lost Update)

描述：两个事务都读取同一记录，然后都更新该记录。如果后一个事务提交，前一个事务的更新就会被覆盖，导致前一个事务的修改丢失。

示例：

初始状态：campus_card表中sno='20200032'的balance为1。
T1读取campus_card表中sno='20200032'的balance，值为1。
T2读取campus_card表中sno='20200032'的balance，值为1。
T1将balance更新为100。
T2将balance更新为200。
T1提交事务。
T2提交事务。

最终，campus_card表中sno='20200032'的balance为200，T1的更新被T2覆盖，导致T1的修改丢失。

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
COMMIT;

-- T2
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
UPDATE campus_card SET balance = 200 WHERE sno = '20200032';
COMMIT;

2. 读脏数据 (Dirty Read)

描述：一个事务读取了另一个事务未提交的修改结果。如果后者回滚，前者读取的数据就是无效的。

示例：

初始状态：campus_card表中sno='20200032'的balance为1。
T1读取campus_card表中sno='20200032'的balance，值为1。
T2将balance更新为100。
T1再次读取campus_card表中sno='20200032'的balance，值为100。
T2回滚事务。

最终，campus_card表中sno='20200032'的balance仍为1，但T1读取到了无效的100。

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
-- 不提交，保持打开状态

-- T1继续
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=100
COMMIT;

-- T2回滚
ROLLBACK;

3. 不可重复读 (Non-repeatable Read)

描述：在一个事务内多次读取同一记录，但由于其他事务的修改或删除操作，使得每次读取的结果不同。

示例：

初始状态：campus_card表中sno='20200032'的balance为1。
T1读取campus_card表中sno='20200032'的balance，值为1。
T2将balance更新为100。
T1再次读取campus_card表中sno='20200032'的balance，值为100。
T2提交事务。

最终，T1在同一个事务中读取到了不同的balance值。

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
COMMIT;

-- T1继续
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=100
COMMIT;

4. 幻读 (Phantom Read)

描述：在一个事务中两次执行相同的查询，但第二次查询返回的结果集中包含了第一次查询没有的记录，这是因为其他事务在这期间插入了新的记录。

示例：

初始状态：campus_card表中只有两条记录，分别是sno='20200032'和sno='20200033'。
T1查询所有balance大于0的记录。
T2插入一条新记录sno='20200034'，balance=50。
T1再次查询所有balance大于0的记录，发现多了一条记录。

最终，T1在同一个事务中两次查询得到了不同的结果集。

-- T1
START TRANSACTION;
SELECT * FROM campus_card WHERE balance > 0; -- 返回2条记录
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
INSERT INTO campus_card (sno, balance) VALUES ('20200034', 50);
COMMIT;

-- T1继续
SELECT * FROM campus_card WHERE balance > 0; -- 返回3条记录
COMMIT;

无法展现的情况说明

读未提交（Read Uncommitted）隔离级别：在这种隔离级别下，事务可以看到其他事务未提交的修改，因此会出现读脏数据的问题。但在大多数数据库系统中，默认的隔离级别较高，不会允许这种情况发生。
串行化（Serializable）隔离级别：在这种隔离级别下，事务完全串行执行，不会出现上述任何数据不一致问题。因此，在这种隔离级别下，无法展示这些数据不一致问题。

解决数据不一致问题的设计方案

在关系型数据库中，可以通过设置适当的事务隔离级别或使用锁机制来解决数据不一致问题。以下是针对丢失修改、读脏数据、不可重复读和幻读问题的解决方案。

1. 丢失修改 (Lost Update)

问题描述：两个事务都读取同一记录，然后都更新该记录。如果后一个事务提交，前一个事务的更新就会被覆盖，导致前一个事务的修改丢失。

解决方案：

隔离级别：使用可重复读（Repeatable Read）或更高隔离级别（如串行化 Serializable）。
锁机制：使用行级锁（如 SELECT ... FOR UPDATE）来确保在事务提交之前其他事务不能修改同一记录。

示例：

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032' FOR UPDATE; -- 加锁
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
COMMIT;

-- T2
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032' FOR UPDATE; -- 加锁
UPDATE campus_card SET balance = 200 WHERE sno = '20200032';
COMMIT;

2. 读脏数据 (Dirty Read)

问题描述：一个事务读取了另一个事务未提交的修改结果。如果后者回滚，前者读取的数据就是无效的。

解决方案：

隔离级别：使用读已提交（Read Committed）或更高隔离级别（如可重复读 Repeatable Read 或串行化 Serializable）。

示例：

-- 设置隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
ROLLBACK; -- 回滚事务

-- T1继续
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
COMMIT;

3. 不可重复读 (Non-repeatable Read)

问题描述：在一个事务内多次读取同一记录，但由于其他事务的修改或删除操作，使得每次读取的结果不同。

解决方案：

隔离级别：使用可重复读（Repeatable Read）或更高隔离级别（如串行化 Serializable）。

示例：

-- 设置隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- T1
START TRANSACTION;
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
UPDATE campus_card SET balance = 100 WHERE sno = '20200032';
COMMIT;

-- T1继续
SELECT balance FROM campus_card WHERE sno = '20200032'; -- 读取balance=1
COMMIT;

4. 幻读 (Phantom Read)

问题描述：在一个事务中两次执行相同的查询，但第二次查询返回的结果集中包含了第一次查询没有的记录，这是因为其他事务在这期间插入了新的记录。

解决方案：

隔离级别：使用可重复读（Repeatable Read）或更高隔离级别（如串行化 Serializable）。
锁机制：使用范围锁（如 SELECT ... FOR UPDATE）来确保在事务提交之前其他事务不能插入新的记录。

示例：

-- 设置隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- T1
START TRANSACTION;
SELECT * FROM campus_card WHERE balance > 0 FOR UPDATE; -- 加锁
-- 假设T1暂停一段时间

-- T2
START TRANSACTION;
INSERT INTO campus_card (sno, balance) VALUES ('20200034', 50);
COMMIT;

-- T1继续
SELECT * FROM campus_card WHERE balance > 0; -- 仍然返回2条记录
COMMIT;

构造两个事务同时更新一条数据

为了演示如何使用SQL命令查看和理解当前系统中事务以及锁的状态，我们可以构造两个事务同时更新同一条数据。我们将使用以下步骤来实现这一目标，并查看相关的锁和事务状态信息。

1. 准备环境

首先，确保你的MySQL服务器已经启动，并且你有足够的权限执行这些操作。

2. 创建测试数据

假设我们使用的是icbc_card表，初始数据如下：

CREATE TABLE icbc_card (
    icbcid INT,
    sno VARCHAR(8),
    balance INT
);

INSERT INTO icbc_card (icbcid, sno, balance) VALUES (1, '20200032', 300);

3. 构造两个事务

我们将使用两个终端窗口（或标签页）来模拟两个并发事务。

终端1（事务T1）

-- 开始事务
START TRANSACTION;

-- 更新数据
UPDATE icbc_card SET balance = 200 WHERE sno = '20200032';

-- 暂停，模拟事务长时间运行
-- SELECT SLEEP(30);

终端2（事务T2）

-- 开始事务
START TRANSACTION;

-- 更新数据
UPDATE icbc_card SET balance = 400 WHERE sno = '20200032';

-- 提交事务
COMMIT;

4. 查看事务和锁的状态

在第三个终端窗口中，使用以下SQL命令查看当前系统中事务和锁的状态。

查看InnoDB引擎状态

1	SHOW ENGINE INNODB STATUS;

查看InnoDB事务

1	SELECT * FROM information_schema.INNODB_TRX;

查看InnoDB锁信息（MySQL 8.0）

1	SELECT * FROM performance_schema.data_locks;

查看InnoDB锁等待信息（MySQL 8.0）

1	SELECT * FROM sys.innodb_lock_waits;

查看InnoDB锁等待信息（MySQL 5.7）

1	SELECT * FROM information_schema.INNODB_LOCK_WAITS;

查看InnoDB锁信息（MySQL 5.7）

1	SELECT * FROM information_schema.INNODB_LOCKS;

示例输出解释

`SHOW ENGINE INNODB STATUS;`

这个命令会显示InnoDB引擎的详细状态信息，包括当前的事务、锁、死锁检测等。你可以查找TRANSACTIONS部分来查看当前活动的事务。

`SELECT * FROM information_schema.INNODB_TRX;`

这个查询会列出当前所有活跃的InnoDB事务，包括事务ID、事务开始时间、事务状态等。

`SELECT * FROM performance_schema.data_locks;`

这个查询会显示当前所有数据锁的信息，包括锁类型、锁模式、锁对象等。

`SELECT * FROM sys.innodb_lock_waits;`

这个查询会显示当前所有锁等待的信息，帮助你识别哪些事务在等待哪些锁。

`SELECT * FROM information_schema.INNODB_LOCK_WAITS;`

这个查询会显示当前所有InnoDB锁等待的信息，类似于sys.innodb_lock_waits，但格式略有不同。

`SELECT * FROM information_schema.INNODB_LOCKS;`

这个查询会显示当前所有InnoDB锁的信息，包括锁类型、锁模式、锁对象等。

实际操作步骤

启动事务T1：
- 在终端1中执行T1的SQL命令，开始事务并更新数据，但不提交。
启动事务T2：
- 在终端2中执行T2的SQL命令，开始事务并更新数据。由于T1已经加锁，T2会进入等待状态。
查看事务和锁的状态：
- 在第三个终端中执行上述SQL命令，查看当前的事务和锁状态。

结果分析

事务T1：你会看到T1处于活动状态，持有对icbc_card表中sno='20200032'记录的排他锁。
事务T2：你会看到T2处于等待状态，等待T1释放锁。
锁信息：performance_schema.data_locks和`information

构造一个出现死锁的情形

死锁通常发生在两个或多个事务互相等待对方释放资源的情况下。为了构造一个死锁的情形，我们可以设计两个事务，每个事务分别锁定不同的记录，然后互相请求对方已经锁定的记录。

1. 准备环境

确保你的MariaDB服务器已经启动，并且你有足够的权限执行这些操作。

2. 创建测试数据

假设我们使用的是icbc_card表，初始数据如下：

CREATE TABLE icbc_card (
    icbcid INT,
    sno VARCHAR(8),
    balance INT
);

INSERT INTO icbc_card (icbcid, sno, balance) VALUES (1, '20200032', 300);
INSERT INTO icbc_card (icbcid, sno, balance) VALUES (2, '20200033', 400);

3. 构造两个事务

终端1（事务T1）

-- 开始事务
START TRANSACTION;

-- 锁定记录1
SELECT * FROM icbc_card WHERE sno = '20200032' FOR UPDATE;

-- 暂停，模拟事务长时间运行
-- SELECT SLEEP(30);

终端2（事务T2）

-- 开始事务
START TRANSACTION;

-- 锁定记录2
SELECT * FROM icbc_card WHERE sno = '20200033' FOR UPDATE;

-- 暂停，模拟事务长时间运行
-- SELECT SLEEP(30);

终端1（事务T1继续）

1 2	-- 尝试锁定记录2 SELECT * FROM icbc_card WHERE sno = '20200033' FOR UPDATE;

终端2（事务T2继续）

1 2	-- 尝试锁定记录1 SELECT * FROM icbc_card WHERE sno = '20200032' FOR UPDATE;

4. 观察死锁

在终端1和终端2中，两个事务都会进入等待状态，因为它们互相等待对方释放锁。此时，你可以使用以下命令查看死锁情况：

查看InnoDB引擎状态

1	SHOW ENGINE INNODB STATUS;

查看InnoDB事务

1	SELECT * FROM information_schema.INNODB_TRX;

查看InnoDB锁信息

1	SELECT * FROM information_schema.INNODB_LOCKS;

查看InnoDB锁等待信息

1	SELECT * FROM information_schema.INNODB_LOCK_WAITS;

示例输出解释

`SHOW ENGINE INNODB STATUS;`

在SHOW ENGINE INNODB STATUS;的输出中，你可以找到LATEST DETECTED DEADLOCK部分，它会显示最近检测到的死锁信息。例如：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-25 18:30:00 0x7feaac75c6c0
*** (1) TRANSACTION:
TRANSACTION 935, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MariaDB thread id 142, OS thread handle 140645892781760, query id 513 localhost 127.0.0.1 newuser Updating
SELECT * FROM icbc_card WHERE sno = '20200033' FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 3 n bits 8 index GEN_CLUST_INDEX of table `student`.`icbc_card` trx id 935 lock_mode X waiting
...
*** (2) TRANSACTION:
TRANSACTION 934, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1128, 1 row lock(s)
MariaDB thread id 144, OS thread handle 140645892167360, query id 512 localhost 127.0.0.1 newuser Updating
SELECT * FROM icbc_card WHERE sno = '20200032' FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 98 page no 3 n bits 8 index GEN_CLUST_INDEX of table `student`.`icbc_card` trx id 934 lock_mode X
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 3 n bits 8 index GEN_CLUST_INDEX of table `student`.`icbc_card` trx id 934 lock_mode X waiting
...
*** WE ROLL BACK TRANSACTION (1)

`SELECT * FROM information_schema.INNODB_TRX;`

这个查询会列出当前所有活跃的InnoDB事务，包括事务ID、事务状态、开始时间等。

`SELECT * FROM information_schema.INNODB_LOCKS;`

这个查询会显示当前所有InnoDB锁的信息，包括锁类型、锁模式、锁对象等。

`SELECT * FROM information_schema.INNODB_LOCK_WAITS;`

这个查询会显示当前所有InnoDB锁等待的信息，帮助你识别哪些事务在等待哪些锁。

解决死锁

一旦检测到死锁，MariaDB会自动选择一个事务进行回滚。你可以手动回滚其中一个事务来解除死锁：

1 2	-- 在终端1或终端2中执行 ROLLBACK;

总结

通过上述步骤，你可以构造一个死锁的情形，并使用相关命令查看和理解死锁的详细信息。这有助于你更好地理解和解决数据库中的死锁问题。

构造含有 Savepoint 的事务并在某时刻回滚到某个 Savepoint

Savepoint 是事务中的一个标记点，可以在事务中设置多个 savepoint，以便在需要时回滚到特定的 savepoint 而不是整个事务。以下是如何在 MariaDB 中构造一个含有 savepoint 的事务，并在某时刻回滚到某个 savepoint 的示例。

1. 准备环境

确保你的 MariaDB 服务器已经启动，并且你有足够的权限执行这些操作。

2. 创建测试数据

假设我们使用的是 icbc_card 表，初始数据如下：

CREATE TABLE icbc_card (
    icbcid INT,
    sno VARCHAR(8),
    balance INT
);

INSERT INTO icbc_card (icbcid, sno, balance) VALUES (1, '20200032', 300);
INSERT INTO icbc_card (icbcid, sno, balance) VALUES (2, '20200033', 400);

3. 构造含有 savepoint 的事务

终端1（事务T1）

-- 开始事务
START TRANSACTION;

-- 第一步：更新第一个账户的余额
UPDATE icbc_card SET balance = 200 WHERE sno = '20200032';

-- 设置第一个 savepoint
SAVEPOINT sp1;

-- 第二步：更新第二个账户的余额
UPDATE icbc_card SET balance = 500 WHERE sno = '20200033';

-- 设置第二个 savepoint
SAVEPOINT sp2;

-- 第三步：尝试一个可能出错的操作
UPDATE icbc_card SET balance = -100 WHERE sno = '20200032';

-- 发现错误，回滚到 sp2
ROLLBACK TO SAVEPOINT sp2;

-- 提交事务
COMMIT;

解释

开始事务：
1
START TRANSACTION;
开始一个新的事务。
第一步：更新第一个账户的余额：
1
UPDATE icbc_card SET balance = 200 WHERE sno = '20200032';
更新 sno = '20200032' 的余额为 200。
设置第一个 savepoint：
1
SAVEPOINT sp1;
设置一个名为 sp1 的 savepoint。
第二步：更新第二个账户的余额：
1
UPDATE icbc_card SET balance = 500 WHERE sno = '20200033';
更新 sno = '20200033' 的余额为 500。
设置第二个 savepoint：
1
SAVEPOINT sp2;
设置一个名为 sp2 的 savepoint。
第三步：尝试一个可能出错的操作：
1
UPDATE icbc_card SET balance = -100 WHERE sno = '20200032';
尝试将 sno = '20200032' 的余额设置为 -100，这是一个可能出错的操作。
发现错误，回滚到 sp2：
1
ROLLBACK TO SAVEPOINT sp2;
发现错误后，回滚到 sp2，撤销第三步的操作。
提交事务：
1
COMMIT;
提交事务，确保所有更改永久保存。

验证结果

执行上述事务后，可以查询 icbc_card 表来验证结果：

1	SELECT * FROM icbc_card;

预期结果：

sno = '20200032' 的余额应为 200。
sno = '20200033' 的余额应为 500。

通过这种方式，你可以使用 savepoint 来控制事务中的回滚点，从而更好地管理事务中的复杂操作。

通过实验查看 MySQL（MariaDB）中的各种日志

MySQL 和 MariaDB 提供了多种日志文件，用于记录数据库的各种操作和事件。这些日志文件包括查询日志、错误日志和慢查询日志。以下是如何配置和查看这些日志的步骤。

1. 准备环境

确保你的 MariaDB 服务器已经启动，并且你有足够的权限执行这些操作。

2. 配置日志

2.1 查询日志（General Query Log）

查询日志记录了所有发送到数据库服务器的SQL语句，包括查询、更新和其他操作。

启用查询日志：
编辑 MariaDB 配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf），添加或修改以下配置：
1
2
3
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log
重启 MariaDB 服务：
1
sudo systemctl restart mariadb
查看查询日志：
1
tail -f /var/log/mysql/query.log

2.2 错误日志（Error Log）

错误日志记录了数据库服务器启动、运行和关闭过程中发生的错误信息。

启用错误日志：
编辑 MariaDB 配置文件，添加或修改以下配置：
1
2
[mysqld]
log_error = /var/log/mysql/error.log
重启 MariaDB 服务：
1
sudo systemctl restart mariadb
查看错误日志：
1
tail -f /var/log/mysql/error.log

2.3 慢查询日志（Slow Query Log）

慢查询日志记录了执行时间超过指定阈值的SQL语句。

启用慢查询日志：
编辑 MariaDB 配置文件，添加或修改以下配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1  # 记录执行时间超过1秒的查询

重启 MariaDB 服务：
1
sudo systemctl restart mariadb
查看慢查询日志：
1
tail -f /var/log/mysql/slow-query.log

3. 实验步骤

3.1 查询日志

启用查询日志：

1	sudo nano /etc/my.cnf

添加或修改以下配置：

1
2
3

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log

重启 MariaDB 服务：
1
sudo systemctl restart mariadb

执行一些SQL查询：

USE test;
CREATE TABLE test_table (id INT, name VARCHAR(50));
INSERT INTO test_table (id, name) VALUES (1, 'Alice');
SELECT * FROM test_table;

查看查询日志：
1
tail -f /var/log/mysql/query.log

3.2 错误日志

启用错误日志：

1	sudo nano /etc/my.cnf

添加或修改以下配置：

1 2	[mysqld] log_error = /var/log/mysql/error.log

重启 MariaDB 服务：
1
sudo systemctl restart mariadb

故意引发一个错误：

1 2	USE test; CREATE TABLE test_table (id INT, name VARCHAR(50)); -- 重复创建表

查看错误日志：
1
tail -f /var/log/mysql/error.log

3.3 慢查询日志

启用慢查询日志：

1	sudo nano /etc/my.cnf

添加或修改以下配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1

重启 MariaDB 服务：
1
sudo systemctl restart mariadb

执行一个慢查询：

USE test;
CREATE TABLE slow_test (id INT, data VARCHAR(10000));
INSERT INTO slow_test (id, data) VALUES (1, REPEAT('a', 10000));
SELECT * FROM slow_test WHERE data LIKE '%a%';

查看慢查询日志：
1
tail -f /var/log/mysql/slow-query.log

示例输出

查询日志（General Query Log）

2024-11-25T18:30:00 UTC [Note] [Server] [ID] Query   USE test
2024-11-25T18:30:01 UTC [Note] [Server] [ID] Query   CREATE TABLE test_table (id INT, name VARCHAR(50))
2024-11-25T18:30:02 UTC [Note] [Server] [ID] Query   INSERT INTO test_table (id, name) VALUES (1, 'Alice')
2024-11-25T18:30:03 UTC [Note] [Server] [ID] Query   SELECT * FROM test_table

错误日志（Error Log）

1	2024-11-25T18:30:04 UTC [Note] [Server] [ID] Error Table 'test.test_table' already exists

慢查询日志（Slow Query Log）

# Time: 2024-11-25T18:30:05 UTC
# User@Host: root[root] @ localhost []
# Query_time: 1.000000  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 1
SET timestamp=1706293805;
SELECT * FROM slow_test WHERE data LIKE '%a%';

总结

通过上述步骤，你可以配置和查看 MariaDB 中的查询日志、错误日志和慢查询日志。这些日志文件对于监控数据库的性能和诊断问题非常有用。根据需要，你可以调整日志配置以满足特定的需求。

使用 `mysqlbinlog` 查看数据库的事务日志并进行数据恢复

以下是如何使用 mysqlbinlog 查看数据库的事务日志，并在删除表 t1 之后恢复其数据的详细步骤。

1. 准备环境

确保你的 MariaDB 服务器已经启动，并且你有足够的权限执行这些操作。此外，确保二进制日志（binlog）已经启用。

2. 启用二进制日志

编辑 MariaDB 配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf），添加或修改以下配置：

1
2
3

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server_id = 1

重启 MariaDB 服务：

1	sudo systemctl restart mariadb

3. 执行 SQL 操作

按照提供的步骤执行 SQL 操作：

创建数据库和表：

CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);

插入数据：

INSERT INTO t1 VALUES (11);
INSERT INTO t1 VALUES (12);
INSERT INTO t1 VALUES (13);
INSERT INTO t2 VALUES (21);
INSERT INTO t2 VALUES (22);
INSERT INTO t2 VALUES (23);

**删除表 t1**：
1
DROP TABLE t1;
**继续插入数据到 t2**：
1
INSERT INTO t2 VALUES (24);

4. 使用 `mysqlbinlog` 查看二进制日志

首先，确定二进制日志文件的位置和名称。通常，日志文件位于 /var/log/mysql/ 目录下，文件名类似于 mysql-bin.000001。

使用 mysqlbinlog 查看二进制日志：

1	mysqlbinlog /var/log/mysql/mysql-bin.000001

5. 恢复表 `t1` 的数据

找到删除表 t1 的日志位置：
在二进制日志中找到 DROP TABLE t1; 的位置。记下该位置的前后日志位置。
导出需要恢复的日志：
使用 mysqlbinlog 导出从创建表 t1 到删除表 t1 之间的日志。假设 DROP TABLE t1; 的位置是 12345，创建表 t1 的位置是 10000：
1
mysqlbinlog --start-position=10000 --stop-position=12345 /var/log/mysql/mysql-bin.000001 > restore.sql
编辑恢复脚本：
打开 restore.sql 文件，删除 DROP TABLE t1; 之前的日志，保留创建表 t1 和插入数据的语句。确保最终的 restore.sql 文件内容如下：
1
2
3
4
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (11);
INSERT INTO t1 VALUES (12);
INSERT INTO t1 VALUES (13);
执行恢复脚本：
使用 MySQL 客户端执行恢复脚本：
1
mysql -u your_username -p db1 < restore.sql

6. 验证恢复

执行以下查询验证 t1 表是否成功恢复：

1 2	USE db1; SELECT * FROM t1;

预期结果：

+----+
| id |
+----+
| 11 |
| 12 |
| 13 |
+----+

总结

通过上述步骤，你可以使用 mysqlbinlog 查看数据库的事务日志，并在删除表 t1 之后恢复其数据。这些步骤包括启用二进制日志、执行 SQL 操作、查看二进制日志、导出需要恢复的日志、编辑恢复脚本以及执行恢复脚本。这样可以确保在发生意外删除或其他数据丢失情况下，能够有效地恢复数据。

利用 `mysqldump` 和 `mysqlbinlog` 完成逻辑增量备份和恢复

以下是一个详细的步骤指南，帮助你完成一个逻辑增量备份和恢复的过程。我们将使用 mysqldump 进行全量备份，然后使用 mysqlbinlog 进行增量备份，并最终将备份恢复到 MySQL。

1. 使用 `mysqldump` 完成一个基础的全量备份

创建全量备份：

1	mysqldump -u your_username -p --all-databases > full_backup.sql

备份特定数据库（假设数据库名为 db1）：

1	mysqldump -u your_username -p db1 > db1_full_backup.sql

2. 备份当前的 binlog 日志文件位置

登录到 MySQL：
1
mysql -u your_username -p

查看当前的 binlog 日志文件位置：

1	SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 12345    |              |                  |
+------------------+----------+--------------+------------------+

记下 File 和 Position 的值，例如 mysql-bin.000001 和 12345。

3. 对库中的某些表进行一些数据增删改操作

插入数据：

1
2
3

USE db1;
INSERT INTO t1 VALUES (14);
INSERT INTO t2 VALUES (25);

更新数据：

1 2	UPDATE t1 SET id = 15 WHERE id = 14; UPDATE t2 SET id = 26 WHERE id = 25;

删除数据：

1 2	DELETE FROM t1 WHERE id = 15; DELETE FROM t2 WHERE id = 26;

4. 使用 `mysqlbinlog` 执行增量备份

确定增量备份的起始位置：
假设全量备份时的 binlog 文件位置为 mysql-bin.000001 和 12345。

执行增量备份：

1	mysqlbinlog --start-position=12345 /var/log/mysql/mysql-bin.000001 > incr1.sql

5. 将全量备份和增量备份合并还原到 MySQL

停止 MariaDB 服务（可选，但推荐在生产环境中避免数据冲突）：
1
sudo systemctl stop mariadb

恢复全量备份：

1	mysql -u your_username -p < db1_full_backup.sql

恢复增量备份：
1
mysql -u your_username -p < incr1.sql
启动 MariaDB 服务（如果之前停止了服务）：
1
sudo systemctl start mariadb

6. 验证恢复

登录到 MySQL：
1
mysql -u your_username -p

验证数据：

1
2
3

USE db1;
SELECT * FROM t1;
SELECT * FROM t2;

预期结果：

t1 表应该包含最初插入的数据（11, 12, 13）。
t2 表应该包含最初插入的数据（21, 22, 23, 24）。

总结

通过上述步骤，你可以完成一个逻辑增量备份和恢复的过程。具体步骤包括：

使用 mysqldump 进行全量备份。
备份当前的 binlog 日志文件位置。
对库中的某些表进行数据增删改操作。
使用 mysqlbinlog 执行增量备份。
将全量备份和增量备份合并还原到 MySQL。

这些步骤确保了在数据丢失或损坏时，可以有效地恢复数据库到最新的状态。

实验总结与感悟

实验目的

本次实验的目的是通过一系列操作，熟悉和掌握 MariaDB 的基本配置、日志管理、事务处理、备份与恢复等高级功能。具体包括配置日志、使用 mysqlbinlog 查看和恢复事务日志，以及利用 mysqldump 和 mysqlbinlog 进行逻辑增量备份和恢复。

实验步骤与结果

配置 MariaDB 以启用二进制日志
- 编辑 MariaDB 配置文件 /etc/mysql/mariadb.cnf，添加必要的配置项以启用二进制日志。
- 重启 MariaDB 服务以应用新的配置。
- 验证二进制日志是否已启用，检查 /var/log/mysql/mysql-bin.000001 文件是否存在。
使用 mysqlbinlog 查看和恢复事务日志
- 创建数据库 db1 和表 t1、t2，并插入一些数据。
- 删除表 t1，然后继续插入数据到 t2。
- 使用 mysqlbinlog 查看二进制日志文件，找到删除表 t1 的日志位置。
- 导出从创建表 t1 到删除表 t1 之间的日志，编辑恢复脚本，执行恢复脚本以恢复表 t1 的数据。
- 验证恢复结果，确保表 t1 的数据已成功恢复。
利用 mysqldump 和 mysqlbinlog 进行逻辑增量备份和恢复
- 使用 mysqldump 对数据库 db1 进行全量备份。
- 备份当前的 binlog 日志文件位置，记录 File 和 Position 的值。
- 对库中的某些表进行数据增删改操作。
- 使用 mysqlbinlog 执行增量备份，导出从上次备份结束的位置到当前的 binlog 日志。
- 将全量备份和增量备份合并还原到 MySQL，先执行全量 SQL 文件恢复，再执行增量 SQL 文件恢复。
- 验证恢复结果，确保数据已成功恢复到最新的状态。

实验感悟

配置的重要性
- 通过配置文件的编辑，可以灵活地控制 MariaDB 的各种行为，如启用二进制日志、设置日志文件路径等。配置文件的正确性直接影响到数据库的正常运行和维护。
日志管理的价值
- 启用和管理日志文件（如查询日志、错误日志、慢查询日志、二进制日志）对于监控数据库的性能和诊断问题非常重要。通过查看日志文件，可以快速定位和解决数据库中的问题。
事务日志的作用
- 二进制日志记录了所有对数据库的修改操作，通过 mysqlbinlog 可以查看和恢复这些操作。这对于数据恢复和灾难恢复具有重要意义。
备份与恢复的必要性
- 全量备份和增量备份相结合，可以有效地减少备份所需的时间和存储空间。通过先执行全量备份，再执行增量备份，可以在数据丢失或损坏时，快速恢复到最新的状态。
实践操作的重要性
- 通过实际操作，可以更好地理解和掌握理论知识。在实验过程中，遇到的问题和解决方法都是宝贵的经验，有助于提高实际工作中的问题解决能力。
工具的灵活性
- mysqldump 和 mysqlbinlog 是非常强大的工具，可以灵活地用于数据库的备份和恢复。熟练掌握这些工具的使用方法，可以大大提高数据库管理和维护的效率。

结论

通过本次实验，不仅加深了对 MariaDB 配置、日志管理、事务处理、备份与恢复的理解，还积累了实际操作的经验。这些技能对于数据库管理员和开发人员来说都是非常重要的，有助于在实际工作中更好地管理和维护数据库系统。

Tags

实验解答

1. 编写一个事务处理实现转账操作

2. 展现数据不一致问题

3. 解决数据不一致问题

4. 查看事务及锁状态

5. 构造死锁情形

6. 使用 Savepoint

7. 查看MySQL中的各种日志

8. 用mysqlbinlog恢复数据

数据不一致问题的具体例子

1. 丢失修改 (Lost Update)

2. 读脏数据 (Dirty Read)

3. 不可重复读 (Non-repeatable Read)

4. 幻读 (Phantom Read)

无法展现的情况说明

解决数据不一致问题的设计方案

1. 丢失修改 (Lost Update)

2. 读脏数据 (Dirty Read)

3. 不可重复读 (Non-repeatable Read)

4. 幻读 (Phantom Read)

构造两个事务同时更新一条数据

1. 准备环境

2. 创建测试数据

3. 构造两个事务

终端1（事务T1）

终端2（事务T2）

4. 查看事务和锁的状态

查看InnoDB引擎状态

查看InnoDB事务

查看InnoDB锁信息（MySQL 8.0）

查看InnoDB锁等待信息（MySQL 8.0）

查看InnoDB锁等待信息（MySQL 5.7）

查看InnoDB锁信息（MySQL 5.7）

示例输出解释

SHOW ENGINE INNODB STATUS;

SELECT * FROM information_schema.INNODB_TRX;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM sys.innodb_lock_waits;

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

SELECT * FROM information_schema.INNODB_LOCKS;

实际操作步骤

结果分析

构造一个出现死锁的情形

1. 准备环境

2. 创建测试数据

3. 构造两个事务

终端1（事务T1）

终端2（事务T2）

终端1（事务T1继续）

终端2（事务T2继续）

4. 观察死锁

查看InnoDB引擎状态

查看InnoDB事务

查看InnoDB锁信息

查看InnoDB锁等待信息

示例输出解释

SHOW ENGINE INNODB STATUS;

SELECT * FROM information_schema.INNODB_TRX;

SELECT * FROM information_schema.INNODB_LOCKS;

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

解决死锁

总结

构造含有 Savepoint 的事务并在某时刻回滚到某个 Savepoint

1. 准备环境

2. 创建测试数据

3. 构造含有 savepoint 的事务

终端1（事务T1）

解释

验证结果

通过实验查看 MySQL（MariaDB）中的各种日志

1. 准备环境

2. 配置日志

2.1 查询日志（General Query Log）

2.2 错误日志（Error Log）

2.3 慢查询日志（Slow Query Log）

3. 实验步骤

3.1 查询日志

3.2 错误日志

3.3 慢查询日志

`SHOW ENGINE INNODB STATUS;`

`SELECT * FROM information_schema.INNODB_TRX;`

`SELECT * FROM performance_schema.data_locks;`

`SELECT * FROM sys.innodb_lock_waits;`

`SELECT * FROM information_schema.INNODB_LOCK_WAITS;`

`SELECT * FROM information_schema.INNODB_LOCKS;`

`SHOW ENGINE INNODB STATUS;`

`SELECT * FROM information_schema.INNODB_TRX;`

`SELECT * FROM information_schema.INNODB_LOCKS;`

`SELECT * FROM information_schema.INNODB_LOCK_WAITS;`

使用 `mysqlbinlog` 查看数据库的事务日志并进行数据恢复

4. 使用 `mysqlbinlog` 查看二进制日志

5. 恢复表 `t1` 的数据

利用 `mysqldump` 和 `mysqlbinlog` 完成逻辑增量备份和恢复

1. 使用 `mysqldump` 完成一个基础的全量备份

4. 使用 `mysqlbinlog` 执行增量备份