* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
数据丢失是每一个数据库管理员和开发者都不愿面对的噩梦。然而，意外总是难免，当不小心删除了重要的数据，如何才能迅速而有效地进行恢复呢？在数据库中有二进制日志 (Binlog)，它不仅记录了所有更改数据的事件，还可以帮助将数据库恢复到任何一个特定的时间点。本篇文章将带您深入了解如何利用 Binlog 来应对数据丢失问题，在面对数据误删时不再慌张。
启用 Binlog
Binlog (二进制日志)的介绍在这里就不过多描述了，不了解 Binlog 的同学，可以前往GreatSQL用户手册中 GreatSQL 日志章节查看：（https://greatsql.cn/docs/8.0.32-26/2-about-greatsql/4-3-greatsql-binary-log.html）
为了利用 Binlog (二进制日志) 进行数据恢复，首先需要确保 Binlog 已经在 GreatSQL 数据库中启用并正确配置。以下是详细的配置步骤、状态检查方法及 Binlog 文件的存储位置和命名规则。
配置 Binlog 的步骤
找到并编辑 GreatSQL 配置文件my.cnf。该文件的路径因系统和安装方式不同而有所不同，常见路径包括 /etc/my.cnf、/etc/mysql/my.cnf。
添加或修改以下配置项：
[mysqld]
log-bin=binlog
binlog-format=ROW
server-id=103306
log-bin：指定启用 Binlog，并设置 Binlog 文件的基本名称。这里使用 binlog 作为前缀。
binlog-format：设置 Binlog 的记录格式。推荐使用 ROW 格式，因为它记录的是行级别的变更，更详细和准确。
server-id：为服务器设置唯一的 ID，必须设置该选项才能启用 Binlog。对于单个服务器，任何正整数都可以。对于主从复制环境，确保每个服务器的 server-id 唯一。
推荐 Binlog 配置
但关于 Binlog 的配置还不止这些，在 GreatSQL 推荐 my.cnf 模板(https://greatsql.cn/docs/8.0.32-26/3-quick-start/3-4-quick-start-with-cnf.html)中还有以下几个关于 Binlog 的配置
$ cat /etc/my.cnf |grep bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#控制binlog总大小，避免磁盘空间被撑爆
binlog_space_limit = 500G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
sync_binlog = 1
配置 GreatSQL 每次提交事务后都将 binlog 同步到磁盘。确保在系统崩溃时不会丢失已提交的事务，但可能会略微影响性能。同时配合innodb_flush_log_at_trx_commit=1即所说的双1，这是最安全的设置。
binlog_cache_size = 4M
设置 Binlog 缓存大小为 4MB。当事务中的 SQL 语句较多时，事务的所有更改会被暂时保存在 Binlog 缓存中，然后一次性写入 Binlog 文件。
max_binlog_cache_size = 2G
设置 Binlog 缓存的最大大小为 2GB。这限制了单个事务可以使用的 Binlog 缓存大小，防止过大的事务占用过多内存。
max_binlog_size = 1G
设置单个 Binlog 文件的最大大小为 1GB。当 Binlog 文件达到此大小时，GreatSQL 会自动创建一个新的 Binlog 文件。这有助于管理和分割日志文件，使其更易于处理和备份。
binlog_space_limit = 500G
设置 Binlog 文件的总存储空间限制为 500GB。如果 Binlog 文件的总大小超过此限制，GreatSQL 会自动删除最旧的 Binlog 文件。这可以防止 Binlog 文件占用过多磁盘空间。
binlog_rows_query_log_events = 1
启用 Binlog 中的行查询日志事件。这将记录生成的行更改时的原始 SQL 语句，有助于调试和审计。
binlog_expire_logs_seconds = 604800
设置 Binlog 文件的过期时间为 604800 秒（7 天）。超过此时间的 Binlog 文件将自动删除。这有助于管理存储空间并限制 Binlog 文件的数量。
binlog_checksum = CEC32
控制二进制日志 (binlog) 文件的校验和机制。启用 CRC32 校验和，以确保 Binlog 文件的数据完整性和正确性
配置完成后需要重启 GreatSQL 服务，使配置生效
$ systemctl restart greatsql
检查 Binlog 状态
配置完 Binlog 并重启 GreatSQL 服务后，可以通过以下方法检查 Binlog 是否已正确启用
登录 GreatSQL 并执行以下命令
检查 Binlog 启用状态
greatsql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
如果返回结果为 ON，表示 binlog 已启用。
检查 Binlog 格式
greatsql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
确保 Binlog 格式为 ROW
查看 Binlog 文件列表
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 | 2347 | No |
| binlog.000038 | 220 | No |
| binlog.000039 | 703 | No |
| binlog.000040 | 428473707 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
该命令将列出所有当前存在的 Binlog 文件及其大小
Binlog 文件的存储位置和命名规则
存储位置：
Binlog 文件的存储位置通常由 log-bin 选项的值和 GreatSQL 数据目录共同决定。如果在 my.cnf 中未指定路径，binlog 文件会存储在 GreatSQL 数据目录下。
可以通过以下命令查看 GreatSQL 数据目录：
greatsql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /data/GreatSQL/ |
+---------------+-----------------+
1 row in set (0.00 sec)
如果 log-bin 选项指定了路径，则 binlog 文件存储在该路径下。例如：
log-bin=/data/GreatSQL/binlog
命名规则：
Binlog 文件名由 log-bin 选项的值和一个数字序列组成。例如，如果 log-bin 设置为 binlog，则生成的 binlog 文件名类似于 binlogn.000001、binlog.000002 等。
序列号是自动递增的，当一个 Binlog 文件达到最大大小（由 max_binlog_size 变量控制）时，GreatSQL 会创建一个新的 Binlog 文件，并将序列号递增。
模拟数据误删场景
此次测试环境情况如下：
数据库：GreatSQL 8.0.32-26 操作系统：Linux myarch 6.6.3-arch1-1 x86_64 GNU/Linux
创建测试数据
创建一个testdb库和employees表
greatsql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)
greatsql> USE testdb;
Database changed
greatsql> CREATE TABLE employees (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(100),
-> position VARCHAR(100),
-> salary DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.07 sec)
并插入几条示例数据
greatsql> INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Manager', 60000.00),
('Bob', 'Developer', 50000.00),
('Charlie', 'Analyst', 40000.00),
('greatsql', 'DBA', 66666.00);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
确认插入成功
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 1 | Alice | Manager | 60000.00 |
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)
模拟数据误删除
这时候要删除一条id=2的字段，而你却不小心删除了id=1的字段
greatsql> DELETE FROM employees WHERE name = 'Alice';
查看表确认被误删除了
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
3 rows in set (0.00 sec)
恢复数据的步骤
在数据误删后，恢复数据的关键在于使用 GreatSQL 的二进制日志 (Binlog)。二进制日志记录了所有对数据库进行更改的操作，包括插入、更新和删除。因此，通过解析和重放 Binlog，可以恢复误删的数据。
确定误删的时间点
记录下 GreatSQL 服务器的时间
greatsql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2024-06-07 14:04:23 |
+---------------------+
1 row in set (0.00 sec)
记录下当前时间，以确定误删操作发生的大致时间范围。
查找相应的 Binlog 文件
GreatSQL 的 Binlog 文件按时间顺序记录了所有对数据库的更改。你需要找到包含误删操作的 Binlog 文件。
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 | 2347 | No |
| binlog.000038 | 220 | No |
| binlog.000039 | 703 | No |
| binlog.000040 | 428477097 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
根据误删操作的大致时间，确定可能包含误删操作的 Binlog 文件。例如，如果误删操作发生在最近，可能需要检查 binlog.000040。
或使用SHOW MASTER STATUS命令确认当前正在使用的 Binlog
greatsql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: binlog.000040
Position: 428477097
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9775
1 row in set (0.00 sec)
使用 mysqlBinlog 工具读取 Binlog
使用 mysqlbinlog 工具可以读取并解析 binlog 文件，以查找并提取相关的 SQL 语句。
$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql
--base64-output=decode-rows：对二进制日志文件中的事件进行更详细的解码和输出 --verbose：会输出更详细的日志信息（简写 -v）
这条命令将从 binlog.000040 文件中提取指定时间范围内的日志，并将其保存到 binlog.sql 文件中。
如果没办法确定误操作的具体时间，可以把 Binlog 全部提取
打开 binlog.sql 文件，查找并确认包含误删操作的 SQL 语句。
$ vim /tmp/binlog.sql
# at 428476791
#240607 13:55:21 server id 103306 end_log_pos 428476868 CRC32 0x78030015 Query thread_id=26 exec_time=0 error_code=0
SET TIMESTAMP=1717739721/*!*/;
BEGIN
/*!*/;
# at 428476868
# at 428476934
#240607 13:55:21 server id 103306 end_log_pos 428477005 CRC32 0xf2c276a8 Table_map: `testdb`.`employees` mapped to number 133
# has_generated_invisible_primary_key=0
# at 428477005
#240607 13:55:21 server id 103306 end_log_pos 428477066 CRC32 0x3eea7695 Delete_rows: table id 133 flags: STMT_END_F
### DELETE FROM `testdb`.`employees`
### WHERE
### @1=1
### @2='Alice'
### @3='Manager'
### @4=60000.00
# at 428477066
#240607 13:55:21 server id 103306 end_log_pos 428477097 CRC32 0xa495408d Xid = 10249
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
生成恢复数据的 SQL 语句
可以手工将查到的误删除数据转换为INSTER 语句重新插入回 GreatSQL 数据库。
或用以下命令，将解析后的 SQL 文件中的 DELETE 语句全部转换为 INSTER 语句
$ cat /tmp/binlog.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/VALUE(/g;' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' | sed 's/ \+/ /g'|sed ":a;N;s/\,\n ,/\n,/g;s/\,\n;/);/g;ta"|sed '0,/;/s/;//'|sed '$ s/.$/);/' > binlog_insert.sql
查看转换后的 SQL 语句结果
$ cat /tmp/binlog_insert.sql
INSERT INTO `testdb`.`employees`
VALUE(
1
,'Alice'
,'Manager'
,60000.0);
此时将该 SQL 语句在 GreatSQL 中执行即可恢复被误删除的数据
高级恢复技巧
通过 Pos 恢复
如果不小心将所有数据都删除了，那就可以使用重放 Binlog 让数据全部回来
例如本想删除ID=1的数据，结果忘记加WHERE语句
greatsql> DELETE FROM testdb.employees;
Query OK, 3 rows affected (0.05 sec)
greatsql> SELECT * FROM testdb.employees;
Empty set (0.00 sec)
导致了testdb.employees表数据都没了，但是没关系。需要找到 Binlog 中employees表的建表语句，从建表开始重放 Binlog 到删除语句为止
同样需要解析 Binlog
$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql
找到employees建表语句
$ cat /tmp/binlog.sql
# at 428474721
#240607 13:46:42 server id 103306 end_log_pos 428474954 CRC32 0x2ce790d3 Query thread_id=26 exec_time=0 error_code=0 Xid = 10238
use `testdb`/*!*/;
SET TIMESTAMP=1717739202/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10, 2)
)
/*!*/;
可以看到该建表语句的开始 Pos 在428474954 结束，删除语句是在428476791开始。确定了起止 Pos 直接重放 Binlog 即可
$ mysqlbinlog --skip-gtids --start-position=428474954 --stop-position=428476791 --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
因为 GreatSQL 启用了 GTID 模式（@@GLOBAL.GTID_MODE = ON），而 mysqlbinlog 工具默认尝试将 @@SESSION.GTID_NEXT 设置为 ANONYMOUS，这与启用 GTID 模式的服务器不兼容，所以加上--skip-gtids，在下方 GTID 恢复也解释为什么需要添加这个参数
因为表没有被删除，所以不用重放建表语句。若表也不存在了，可以从建表语句开始重放
再次查看数据，可以看到数据都回来了
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 1 | Alice | Manager | 60000.00 |
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)
以上展示的是根据指定位置恢复数据
通过 GTID 恢复
查看 GTID 开启前的设定
greatsql> SHOW VARIABLES LIKE '%gtid%';
+--------------------------------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_broadcast_gtid_executed_period | 1000 |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9755 |
| secondary_engine_read_delay_gtid_threshold | 100 |
| session_track_gtids | OFF |
+--------------------------------------------------+---------------------------------------------+
12 rows in set (0.00 sec)
主要是 gtid_mode 与 enforce_gtid_consistency 需要开启
gtid_mode：控制 GTID 的开启和关闭。 enforce_gtid_consistency：确保 GTID（全局事务标识符）的一致性。
同时开启后，也能看到 gtid_next=AUTOMATIC 可知 GTID 自动递增
在 GreatSQL 中，mysql.gtid_executed 表用于记录已执行的 GTID（全局事务标识符）。这是 GTID 复制机制中的一个关键部分，用于跟踪服务器上已经执行的所有 GTID，以确保数据一致性和事务的顺利复制。
greatsql> DESC mysql.gtid_executed;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| source_uuid | char(36) | NO | PRI | NULL | |
| interval_start | bigint | NO | PRI | NULL | |
| interval_end | bigint | NO | | NULL | |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
source_uuid：表示 GTID 的源服务器 UUID。这是生成 GTID 的服务器的唯一标识符。
interval_start：GTID 范围的起始值。它表示该事务范围的起始 GTID。
interval_end：GTID 范围的结束值。它表示该事务范围的结束 GTID。
一个 DDL 语句会产生一个 GTID ，同样 DML 一个事务也会产生一个 GTID
# 目前为 1-9813
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000040
Position: 428495255
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813
1 row in set (0.00 sec)
# 执行一个 DDL 语句
greatsql> CREATE TABLE test1 (id int);
Query OK, 0 rows affected (0.03 sec)
# 可以看到 GTID 值增加了
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000040
Position: 428495580
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9814
1 row in set (0.00 sec)
同时也可以查看 Binlog
greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
Log_name: binlog.000040
Pos: 428495255
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495334
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
Log_name: binlog.000040
Pos: 428495334
Event_type: Query
Server_id: 103306
End_log_pos: 428495580
Info: use `testdb`; CREATE TABLE `test1` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`id` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
2 rows in set (0.00 sec)
当看到SET @@SESSION.GTID_NEXT=这样的命令时，它意味着接下来的事务（或下一个将被写入二进制日志（Binlog）的事务）将被赋予指定的GTID。
启用GTID后，GreatSQL 在恢复 Binlog 时会过滤掉重复的 GTID 事务，这意味着相同的GTID语句只会被执行一次。然而，若多个GTID中存在相同的记录语句，这些语句将被忽略，可能因为语句缺失，从而导致数据恢复失败。因此，在进行 Binlog 备份时，添加 –skip-gtids 参数，确保忽略GTID的幂等性检查。
此时将创建test1表删除，接着在使用 GTID 恢复
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees |
| test1 |
+------------------+
2 rows in set (0.00 sec)
greatsql> DROP TABLE test1;
Query OK, 0 rows affected (0.06 sec)
查询 Binlog 可以看到 GTID 值已经到了 9815
greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
Log_name: binlog.000040
Pos: 428495255
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495334
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
Log_name: binlog.000040
Pos: 428495334
Event_type: Query
Server_id: 103306
End_log_pos: 428495580
Info: use `testdb`; CREATE TABLE `test1` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`id` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
*************************** 3. row ***************************
Log_name: binlog.000040
Pos: 428495580
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495657
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9815'
*************************** 4. row ***************************
Log_name: binlog.000040
Pos: 428495657
Event_type: Query
Server_id: 103306
End_log_pos: 428495791
Info: use `testdb`; DROP TABLE `test1` /* generated by server */ /* xid=10950 */
4 rows in set (0.00 sec)
因此需要跳过删除语句，则需要选取 GTID 值小于9815的 GTID 值，既 9814。
$ mysqlbinlog --skip-gtids --include-gtids='9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814' /data/GreatSQL/binlog.000040 | mysql -uroot -p
进入 GreatSQL 数据库查看
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees |
| test1 |
+------------------+
2 rows in set (0.00 sec)
可以看到 test1 表已经恢复了
其它方式恢复
重放整个 Binlog 恢复数据
$ mysqlbinlog --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
根据指定时间恢复数据
mysqlbinlog --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
恢复总结
DELETE 语句误删除单行/多行数据
可以查看 Binlog 日志，把 DELETE 语句转为 INSERT 语句重新插入 GreatSQL 数据库恢复
删表（DROP / DELETE / TRUNCATE）
只能重新重放 Binlog 或者是找到备份恢复 删库
只能重新重放 Binlog 或者是找到备份恢复 UPDATE 语句误修改单行/多行数据
只能重新重放 Binlog 或者是找到备份恢复
避免数据丢失的最佳实践
在管理 GreatSQL 数据库时，避免数据丢失是至关重要的任务。以上演示的都是基于 Binlog 完整保存的情况下，才可以做到完整恢复，如果 Binlog 有丢失，则没有办法恢复数据，所以完整备份数据是至关重要的工作。
定期备份和测试恢复
全量备份
定期进行全量备份，通常每周一次。全量备份捕获数据库的完整状态，可以使用 mysqldump 或 xtrabackup 工具。
$ mysqldump -u root -p --all-databases > /backup/alldb_backup.sql
若有需要，也可以定期备份一下 Binlog 日志文件
使用FLUSH LOGS刷新一下日志，此时会启用一个新的 Binlog
greatsql> FLUSH LOGS
Query OK, 0 rows affected (0.03 sec)
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000040 | 428496353 | No |
| binlog.000041 | 197 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
这时再把旧的 Binlog 备份下即可
测试恢复
定期测试恢复，确保备份文件是可用的，并且可以成功恢复。每个月进行一次恢复测试，确保备份策略的有效性。
避免误操作
使用 DELETE 语句或 UPDATE 语句，都需要加上WHERE 条件，避免误删除/误修改数据。
可以开启sql_safe_updates参数，
greatsql> SET GLOBAL sql_safe_updates = ON;
Query OK, 0 rows affected (0.00 sec)
greatsql> SHOW GLOBAL VARIABLES LIKE "sql_safe_updates";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.01 sec)
此时重开一个会话，使用DELETE 语句没有加上 WHERE 条件，会报错
greatsql> DELETE FROM testdb.employees ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
使用 UPDATE 语句没有加上 WHERE 条件，会报错
greatsql> UPDATE employees SET salary =1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
在管理 GreatSQL 数据库时，虽然有 Binlog 等强大的工具来帮助恢复误删的数据，但最好的办法还是预防。在进行任何数据操作时，应该保持高度的谨慎，充分备份数据，严格遵循操作规范。数据是企业的重要资产，任何疏忽都可能带来巨大的损失。因此，务必对每一次操作保持敬畏之心，防患于未然 :)
数据丢失是每一个数据库管理员和开发者都不愿面对的噩梦。然而，意外总是难免，当不小心删除了重要的数据，如何才能迅速而有效地进行恢复呢？在数据库中有二进制日志 (Binlog)，它不仅记录了所有更改数据的事件，还可以帮助将数据库恢复到任何一个特定的时间点。本篇文章将带您深入了解如何利用 Binlog 来应对数据丢失问题，在面对数据误删时不再慌张。
启用 Binlog
Binlog (二进制日志)的介绍在这里就不过多描述了，不了解 Binlog 的同学，可以前往GreatSQL用户手册中 GreatSQL 日志章节查看：（https://greatsql.cn/docs/8.0.32-26/2-about-greatsql/4-3-greatsql-binary-log.html）
为了利用 Binlog (二进制日志) 进行数据恢复，首先需要确保 Binlog 已经在 GreatSQL 数据库中启用并正确配置。以下是详细的配置步骤、状态检查方法及 Binlog 文件的存储位置和命名规则。
配置 Binlog 的步骤
找到并编辑 GreatSQL 配置文件my.cnf。该文件的路径因系统和安装方式不同而有所不同，常见路径包括 /etc/my.cnf、/etc/mysql/my.cnf。
添加或修改以下配置项：
[mysqld]
log-bin=binlog
binlog-format=ROW
server-id=103306
log-bin：指定启用 Binlog，并设置 Binlog 文件的基本名称。这里使用binlog作为前缀。binlog-format：设置 Binlog 的记录格式。推荐使用ROW格式，因为它记录的是行级别的变更，更详细和准确。server-id：为服务器设置唯一的 ID，必须设置该选项才能启用 Binlog。对于单个服务器，任何正整数都可以。对于主从复制环境，确保每个服务器的server-id唯一。
推荐 Binlog 配置
但关于 Binlog 的配置还不止这些，在 GreatSQL 推荐 my.cnf 模板(https://greatsql.cn/docs/8.0.32-26/3-quick-start/3-4-quick-start-with-cnf.html)中还有以下几个关于 Binlog 的配置
$ cat /etc/my.cnf |grep bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#控制binlog总大小，避免磁盘空间被撑爆
binlog_space_limit = 500G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
sync_binlog = 1配置 GreatSQL 每次提交事务后都将 binlog 同步到磁盘。确保在系统崩溃时不会丢失已提交的事务，但可能会略微影响性能。同时配合
innodb_flush_log_at_trx_commit=1即所说的双1，这是最安全的设置。binlog_cache_size = 4M设置 Binlog 缓存大小为 4MB。当事务中的 SQL 语句较多时，事务的所有更改会被暂时保存在 Binlog 缓存中，然后一次性写入 Binlog 文件。
max_binlog_cache_size = 2G设置 Binlog 缓存的最大大小为 2GB。这限制了单个事务可以使用的 Binlog 缓存大小，防止过大的事务占用过多内存。
max_binlog_size = 1G设置单个 Binlog 文件的最大大小为 1GB。当 Binlog 文件达到此大小时，GreatSQL 会自动创建一个新的 Binlog 文件。这有助于管理和分割日志文件，使其更易于处理和备份。
binlog_space_limit = 500G设置 Binlog 文件的总存储空间限制为 500GB。如果 Binlog 文件的总大小超过此限制，GreatSQL 会自动删除最旧的 Binlog 文件。这可以防止 Binlog 文件占用过多磁盘空间。
binlog_rows_query_log_events = 1启用 Binlog 中的行查询日志事件。这将记录生成的行更改时的原始 SQL 语句，有助于调试和审计。
binlog_expire_logs_seconds = 604800设置 Binlog 文件的过期时间为 604800 秒（7 天）。超过此时间的 Binlog 文件将自动删除。这有助于管理存储空间并限制 Binlog 文件的数量。
binlog_checksum = CEC32控制二进制日志 (binlog) 文件的校验和机制。启用 CRC32 校验和，以确保 Binlog 文件的数据完整性和正确性
配置完成后需要重启 GreatSQL 服务，使配置生效
$ systemctl restart greatsql
检查 Binlog 状态
配置完 Binlog 并重启 GreatSQL 服务后，可以通过以下方法检查 Binlog 是否已正确启用
登录 GreatSQL 并执行以下命令
检查 Binlog 启用状态
greatsql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
如果返回结果为 ON，表示 binlog 已启用。
检查 Binlog 格式
greatsql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
确保 Binlog 格式为 ROW
查看 Binlog 文件列表
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 | 2347 | No |
| binlog.000038 | 220 | No |
| binlog.000039 | 703 | No |
| binlog.000040 | 428473707 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
该命令将列出所有当前存在的 Binlog 文件及其大小
Binlog 文件的存储位置和命名规则
存储位置：
Binlog 文件的存储位置通常由 log-bin 选项的值和 GreatSQL 数据目录共同决定。如果在 my.cnf 中未指定路径，binlog 文件会存储在 GreatSQL 数据目录下。
可以通过以下命令查看 GreatSQL 数据目录：
greatsql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /data/GreatSQL/ |
+---------------+-----------------+
1 row in set (0.00 sec)
如果 log-bin 选项指定了路径，则 binlog 文件存储在该路径下。例如：
log-bin=/data/GreatSQL/binlog
命名规则：
Binlog 文件名由 log-bin 选项的值和一个数字序列组成。例如，如果 log-bin 设置为 binlog，则生成的 binlog 文件名类似于 binlogn.000001、binlog.000002 等。
序列号是自动递增的，当一个 Binlog 文件达到最大大小（由 max_binlog_size 变量控制）时，GreatSQL 会创建一个新的 Binlog 文件，并将序列号递增。
模拟数据误删场景
此次测试环境情况如下：
数据库：GreatSQL 8.0.32-26 操作系统：Linux myarch 6.6.3-arch1-1 x86_64 GNU/Linux
创建测试数据
创建一个testdb库和employees表
greatsql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)
greatsql> USE testdb;
Database changed
greatsql> CREATE TABLE employees (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(100),
-> position VARCHAR(100),
-> salary DECIMAL(10, 2)
-> );
Query OK, 0 rows affected (0.07 sec)
并插入几条示例数据
greatsql> INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Manager', 60000.00),
('Bob', 'Developer', 50000.00),
('Charlie', 'Analyst', 40000.00),
('greatsql', 'DBA', 66666.00);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
确认插入成功
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 1 | Alice | Manager | 60000.00 |
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)
模拟数据误删除
这时候要删除一条id=2的字段，而你却不小心删除了id=1的字段
greatsql> DELETE FROM employees WHERE name = 'Alice';
查看表确认被误删除了
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
3 rows in set (0.00 sec)
恢复数据的步骤
在数据误删后，恢复数据的关键在于使用 GreatSQL 的二进制日志 (Binlog)。二进制日志记录了所有对数据库进行更改的操作，包括插入、更新和删除。因此，通过解析和重放 Binlog，可以恢复误删的数据。
确定误删的时间点
记录下 GreatSQL 服务器的时间
greatsql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2024-06-07 14:04:23 |
+---------------------+
1 row in set (0.00 sec)
记录下当前时间，以确定误删操作发生的大致时间范围。
查找相应的 Binlog 文件
GreatSQL 的 Binlog 文件按时间顺序记录了所有对数据库的更改。你需要找到包含误删操作的 Binlog 文件。
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 | 2347 | No |
| binlog.000038 | 220 | No |
| binlog.000039 | 703 | No |
| binlog.000040 | 428477097 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
根据误删操作的大致时间，确定可能包含误删操作的 Binlog 文件。例如，如果误删操作发生在最近，可能需要检查 binlog.000040。
或使用SHOW MASTER STATUS命令确认当前正在使用的 Binlog
greatsql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: binlog.000040
Position: 428477097
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9775
1 row in set (0.00 sec)
使用 mysqlBinlog 工具读取 Binlog
使用 mysqlbinlog 工具可以读取并解析 binlog 文件，以查找并提取相关的 SQL 语句。
$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql
--base64-output=decode-rows：对二进制日志文件中的事件进行更详细的解码和输出 --verbose：会输出更详细的日志信息（简写 -v）
这条命令将从 binlog.000040 文件中提取指定时间范围内的日志，并将其保存到 binlog.sql 文件中。
如果没办法确定误操作的具体时间，可以把 Binlog 全部提取
打开 binlog.sql 文件，查找并确认包含误删操作的 SQL 语句。
$ vim /tmp/binlog.sql
# at 428476791
#240607 13:55:21 server id 103306 end_log_pos 428476868 CRC32 0x78030015 Query thread_id=26 exec_time=0 error_code=0
SET TIMESTAMP=1717739721/*!*/;
BEGIN
/*!*/;
# at 428476868
# at 428476934
#240607 13:55:21 server id 103306 end_log_pos 428477005 CRC32 0xf2c276a8 Table_map: `testdb`.`employees` mapped to number 133
# has_generated_invisible_primary_key=0
# at 428477005
#240607 13:55:21 server id 103306 end_log_pos 428477066 CRC32 0x3eea7695 Delete_rows: table id 133 flags: STMT_END_F
### DELETE FROM `testdb`.`employees`
### WHERE
### @1=1
### @2='Alice'
### @3='Manager'
### @4=60000.00
# at 428477066
#240607 13:55:21 server id 103306 end_log_pos 428477097 CRC32 0xa495408d Xid = 10249
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
生成恢复数据的 SQL 语句
可以手工将查到的误删除数据转换为INSTER 语句重新插入回 GreatSQL 数据库。
或用以下命令，将解析后的 SQL 文件中的 DELETE 语句全部转换为 INSTER 语句
$ cat /tmp/binlog.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/VALUE(/g;' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' | sed 's/ \+/ /g'|sed ":a;N;s/\,\n ,/\n,/g;s/\,\n;/);/g;ta"|sed '0,/;/s/;//'|sed '$ s/.$/);/' > binlog_insert.sql
查看转换后的 SQL 语句结果
$ cat /tmp/binlog_insert.sql
INSERT INTO `testdb`.`employees`
VALUE(
1
,'Alice'
,'Manager'
,60000.0);
此时将该 SQL 语句在 GreatSQL 中执行即可恢复被误删除的数据
高级恢复技巧
通过 Pos 恢复
如果不小心将所有数据都删除了，那就可以使用重放 Binlog 让数据全部回来
例如本想删除ID=1的数据，结果忘记加WHERE语句
greatsql> DELETE FROM testdb.employees;
Query OK, 3 rows affected (0.05 sec)
greatsql> SELECT * FROM testdb.employees;
Empty set (0.00 sec)
导致了testdb.employees表数据都没了，但是没关系。需要找到 Binlog 中employees表的建表语句，从建表开始重放 Binlog 到删除语句为止
同样需要解析 Binlog
$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql
找到employees建表语句
$ cat /tmp/binlog.sql
# at 428474721
#240607 13:46:42 server id 103306 end_log_pos 428474954 CRC32 0x2ce790d3 Query thread_id=26 exec_time=0 error_code=0 Xid = 10238
use `testdb`/*!*/;
SET TIMESTAMP=1717739202/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10, 2)
)
/*!*/;
可以看到该建表语句的开始 Pos 在428474954 结束，删除语句是在428476791开始。确定了起止 Pos 直接重放 Binlog 即可
$ mysqlbinlog --skip-gtids --start-position=428474954 --stop-position=428476791 --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
因为 GreatSQL 启用了 GTID 模式（@@GLOBAL.GTID_MODE = ON），而 mysqlbinlog 工具默认尝试将 @@SESSION.GTID_NEXT 设置为 ANONYMOUS，这与启用 GTID 模式的服务器不兼容，所以加上--skip-gtids，在下方 GTID 恢复也解释为什么需要添加这个参数
因为表没有被删除，所以不用重放建表语句。若表也不存在了，可以从建表语句开始重放
再次查看数据，可以看到数据都回来了
greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name | position | salary |
+----+----------+-----------+----------+
| 1 | Alice | Manager | 60000.00 |
| 2 | Bob | Developer | 50000.00 |
| 3 | Charlie | Analyst | 40000.00 |
| 4 | greatsql | DBA | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)
以上展示的是根据指定位置恢复数据
通过 GTID 恢复
查看 GTID 开启前的设定
greatsql> SHOW VARIABLES LIKE '%gtid%';
+--------------------------------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_broadcast_gtid_executed_period | 1000 |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9755 |
| secondary_engine_read_delay_gtid_threshold | 100 |
| session_track_gtids | OFF |
+--------------------------------------------------+---------------------------------------------+
12 rows in set (0.00 sec)
主要是 gtid_mode 与 enforce_gtid_consistency 需要开启
gtid_mode：控制 GTID 的开启和关闭。 enforce_gtid_consistency：确保 GTID（全局事务标识符）的一致性。
同时开启后，也能看到 gtid_next=AUTOMATIC 可知 GTID 自动递增
在 GreatSQL 中，mysql.gtid_executed 表用于记录已执行的 GTID（全局事务标识符）。这是 GTID 复制机制中的一个关键部分，用于跟踪服务器上已经执行的所有 GTID，以确保数据一致性和事务的顺利复制。
greatsql> DESC mysql.gtid_executed;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| source_uuid | char(36) | NO | PRI | NULL | |
| interval_start | bigint | NO | PRI | NULL | |
| interval_end | bigint | NO | | NULL | |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
source_uuid：表示 GTID 的源服务器 UUID。这是生成 GTID 的服务器的唯一标识符。
interval_start：GTID 范围的起始值。它表示该事务范围的起始 GTID。
interval_end：GTID 范围的结束值。它表示该事务范围的结束 GTID。
一个 DDL 语句会产生一个 GTID ，同样 DML 一个事务也会产生一个 GTID
# 目前为 1-9813
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000040
Position: 428495255
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813
1 row in set (0.00 sec)
# 执行一个 DDL 语句
greatsql> CREATE TABLE test1 (id int);
Query OK, 0 rows affected (0.03 sec)
# 可以看到 GTID 值增加了
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000040
Position: 428495580
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9814
1 row in set (0.00 sec)
同时也可以查看 Binlog
greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
Log_name: binlog.000040
Pos: 428495255
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495334
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
Log_name: binlog.000040
Pos: 428495334
Event_type: Query
Server_id: 103306
End_log_pos: 428495580
Info: use `testdb`; CREATE TABLE `test1` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`id` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
2 rows in set (0.00 sec)
当看到SET @@SESSION.GTID_NEXT=这样的命令时，它意味着接下来的事务（或下一个将被写入二进制日志（Binlog）的事务）将被赋予指定的GTID。
启用GTID后，GreatSQL 在恢复 Binlog 时会过滤掉重复的 GTID 事务，这意味着相同的GTID语句只会被执行一次。然而，若多个GTID中存在相同的记录语句，这些语句将被忽略，可能因为语句缺失，从而导致数据恢复失败。因此，在进行 Binlog 备份时，添加 –skip-gtids 参数，确保忽略GTID的幂等性检查。
此时将创建test1表删除，接着在使用 GTID 恢复
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees |
| test1 |
+------------------+
2 rows in set (0.00 sec)
greatsql> DROP TABLE test1;
Query OK, 0 rows affected (0.06 sec)
查询 Binlog 可以看到 GTID 值已经到了 9815
greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
Log_name: binlog.000040
Pos: 428495255
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495334
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
Log_name: binlog.000040
Pos: 428495334
Event_type: Query
Server_id: 103306
End_log_pos: 428495580
Info: use `testdb`; CREATE TABLE `test1` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`id` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
*************************** 3. row ***************************
Log_name: binlog.000040
Pos: 428495580
Event_type: Gtid
Server_id: 103306
End_log_pos: 428495657
Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9815'
*************************** 4. row ***************************
Log_name: binlog.000040
Pos: 428495657
Event_type: Query
Server_id: 103306
End_log_pos: 428495791
Info: use `testdb`; DROP TABLE `test1` /* generated by server */ /* xid=10950 */
4 rows in set (0.00 sec)
因此需要跳过删除语句，则需要选取 GTID 值小于9815的 GTID 值，既 9814。
$ mysqlbinlog --skip-gtids --include-gtids='9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814' /data/GreatSQL/binlog.000040 | mysql -uroot -p
进入 GreatSQL 数据库查看
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees |
| test1 |
+------------------+
2 rows in set (0.00 sec)
可以看到 test1 表已经恢复了
其它方式恢复
重放整个 Binlog 恢复数据
$ mysqlbinlog --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
根据指定时间恢复数据
mysqlbinlog --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p
恢复总结
DELETE 语句误删除单行/多行数据
可以查看 Binlog 日志，把 DELETE 语句转为 INSERT 语句重新插入 GreatSQL 数据库恢复
删表（DROP / DELETE / TRUNCATE） 只能重新重放 Binlog 或者是找到备份恢复 删库 只能重新重放 Binlog 或者是找到备份恢复 UPDATE 语句误修改单行/多行数据 只能重新重放 Binlog 或者是找到备份恢复
避免数据丢失的最佳实践
在管理 GreatSQL 数据库时，避免数据丢失是至关重要的任务。以上演示的都是基于 Binlog 完整保存的情况下，才可以做到完整恢复，如果 Binlog 有丢失，则没有办法恢复数据，所以完整备份数据是至关重要的工作。
定期备份和测试恢复
全量备份
定期进行全量备份，通常每周一次。全量备份捕获数据库的完整状态，可以使用 mysqldump 或 xtrabackup 工具。
$ mysqldump -u root -p --all-databases > /backup/alldb_backup.sql
若有需要，也可以定期备份一下 Binlog 日志文件
使用FLUSH LOGS刷新一下日志，此时会启用一个新的 Binlog
greatsql> FLUSH LOGS
Query OK, 0 rows affected (0.03 sec)
greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000040 | 428496353 | No |
| binlog.000041 | 197 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
这时再把旧的 Binlog 备份下即可
测试恢复
定期测试恢复，确保备份文件是可用的，并且可以成功恢复。每个月进行一次恢复测试，确保备份策略的有效性。
避免误操作
使用 DELETE 语句或 UPDATE 语句，都需要加上WHERE 条件，避免误删除/误修改数据。
可以开启sql_safe_updates参数，
greatsql> SET GLOBAL sql_safe_updates = ON;
Query OK, 0 rows affected (0.00 sec)
greatsql> SHOW GLOBAL VARIABLES LIKE "sql_safe_updates";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.01 sec)
此时重开一个会话，使用DELETE 语句没有加上 WHERE 条件，会报错
greatsql> DELETE FROM testdb.employees ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
使用 UPDATE 语句没有加上 WHERE 条件，会报错
greatsql> UPDATE employees SET salary =1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
在管理 GreatSQL 数据库时，虽然有 Binlog 等强大的工具来帮助恢复误删的数据，但最好的办法还是预防。在进行任何数据操作时，应该保持高度的谨慎，充分备份数据，严格遵循操作规范。数据是企业的重要资产，任何疏忽都可能带来巨大的损失。因此，务必对每一次操作保持敬畏之心，防患于未然 :)
《用三分钟学会一个MySQL知识》
GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。
⏩GitHub : https://github.com/GreatSQL/
🆙BiliBili : https://space.bilibili.com/1363850082
（对文章有疑问或见解可去社区官网提出哦~）
| 加入微信交流群 | 加入QQ交流群 |
