写点什么

误删 GreatSQL 数据?别慌,Binlog 来帮忙

作者:GreatSQL
  • 2024-10-14
    福建
  • 本文字数:10863 字

    阅读完需:约 36 分钟

误删 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=binlogbinlog-format=ROWserver-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 = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1G#控制binlog总大小,避免磁盘空间被撑爆binlog_space_limit = 500Gbinlog_rows_query_log_events = 1binlog_expire_logs_seconds = 604800binlog_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 并执行以下命令


  1. 检查 Binlog 启用状态


greatsql> SHOW VARIABLES LIKE 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)
复制代码


如果返回结果为 ON,表示 binlog 已启用。


  1. 检查 Binlog 格式


greatsql> SHOW VARIABLES LIKE 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.01 sec)
复制代码


确保 Binlog 格式为 ROW


  1. 查看 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 文件的存储位置和命名规则

  1. 存储位置


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
复制代码


  1. 命名规则


Binlog 文件名由 log-bin 选项的值和一个数字序列组成。例如,如果 log-bin 设置为 binlog,则生成的 binlog 文件名类似于 binlogn.000001binlog.000002 等。


序列号是自动递增的,当一个 Binlog 文件达到最大大小(由 max_binlog_size 变量控制)时,GreatSQL 会创建一个新的 Binlog 文件,并将序列号递增。

模拟数据误删场景

此次测试环境情况如下:


  • 数据库:GreatSQL 8.0.32-25

  • 操作系统: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-97751 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=0SET 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 = 10249COMMIT/*!*/;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.sqlINSERT 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 = 10238use `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_modeenforce_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-9813greatsql> 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-98131 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-98141 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: 103306End_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: 103306End_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: 103306End_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: 103306End_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: 103306End_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: 103306End_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
复制代码

恢复总结

  1. DELETE 语句误删除单行/多行数据


​ 可以查看 Binlog 日志,把 DELETE 语句转为 INSERT 语句重新插入 GreatSQL 数据库恢复


  1. 删表(DROP / DELETE / TRUNCATE)只能重新重放 Binlog 或者是找到备份恢复

  2. 删库只能重新重放 Binlog 或者是找到备份恢复

  3. UPDATE 语句误修改单行/多行数据只能重新重放 Binlog 或者是找到备份恢复

避免数据丢失的最佳实践

在管理 GreatSQL 数据库时,避免数据丢失是至关重要的任务。以上演示的都是基于 Binlog 完整保存的情况下,才可以做到完整恢复,如果 Binlog 有丢失,则没有办法恢复数据,所以完整备份数据是至关重要的工作。

定期备份和测试恢复

全量备份

定期进行全量备份,通常每周一次。全量备份捕获数据库的完整状态,可以使用 mysqldumpxtrabackup 工具。


$ mysqldump -u root -p --all-databases > /backup/alldb_backup.sql
复制代码


若有需要,也可以定期备份一下 Binlog 日志文件


使用FLUSH LOGS刷新一下日志,此时会启用一个新的 Binlog


greatsql> FLUSH LOGSQuery 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 等强大的工具来帮助恢复误删的数据,但最好的办法还是预防。在进行任何数据操作时,应该保持高度的谨慎,充分备份数据,严格遵循操作规范。数据是企业的重要资产,任何疏忽都可能带来巨大的损失。因此,务必对每一次操作保持敬畏之心,防患于未然:)


发布于: 刚刚阅读数: 4
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
误删GreatSQL数据?别慌,Binlog来帮忙_GreatSQL_InfoQ写作社区