Kết hợp sao lưu và nhật ký Binary Log để khôi phục dữ liệu
Tình huống: Người dùng vô tình xóa bảng dữ liệu;
Giải pháp: Phục hồi toàn bộ cơ sở dữ liệu và nhật ký binlog trên một máy khác, sau đó xuất bảng và nhập vào môi trường sản xuất;
Môi trường thử nghiệm: Một máy chủ cơ sở dữ liệu sản xuất và một máy chủ cơ sở dữ liệu kiểm thử
1.1 Sao lưu cơ sở dữ liệu
[root@localhost ~]# mysqldump -uroot -p --databases jl > jl_backup.sql
1.2 Kiểm tra thông tin nhật ký
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| BIN_LOG.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.3 Thực hiện thay đổi dữ liệu
mysql> select * from test_table;
+------+---------------------+
| id | timestamp_field |
+------+---------------------+
| 6 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
| 9 | 2022-10-18 21:45:44 |
+------+---------------------+
4 rows in set (0.00 sec)
1.4 Mô phỏng cập nhật dữ liệu
mysql> update test_table set id=10 where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from test_table where id=9;
Query OK, 1 row affected (0.01 sec)
Dữ liệu sau khi cập nhật:
mysql> select * from test_table;
+------+---------------------+
| id | timestamp_field |
+------+---------------------+
| 10 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
+------+---------------------+
3 rows in set (0.00 sec)
1.5 Xóa bảng dữ liệu
mysql> drop table test_table;
Query OK, 0 rows affected (0.04 sec)
Tìm kiếm trong nhật ký để xác định thời điểm xóa bảng:
mysql> show binlog events in 'BIN_LOG.000001';
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| BIN_LOG.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| BIN_LOG.000001 | 123 | Previous_gtids | 6 | 154 | |
| BIN_LOG.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 219 | Query | 6 | 289 | BEGIN |
| BIN_LOG.000001 | 289 | Table_map | 6 | 337 | table_id: 110 (jl.test_table) |
| BIN_LOG.000001 | 337 | Update_rows | 6 | 393 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 393 | Xid | 6 | 424 | COMMIT /* xid=1320 */ |
| BIN_LOG.000001 | 424 | Anonymous_Gtid | 6 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 489 | Query | 6 | 559 | BEGIN |
| BIN_LOG.000001 | 559 | Table_map | 6 | 607 | table_id: 110 (jl.test_table) |
| BIN_LOG.000001 | 607 | Delete_rows | 6 | 652 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 652 | Xid | 6 | 683 | COMMIT /* xid=1321 */ |
| BIN_LOG.000001 | 683 | Anonymous_Gtid | 6 | 748 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 748 | Query | 6 | 862 | use `jl`; DROP TABLE `test_table` /* generated by server */ |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
14 rows in set (0.00 sec)
1.6 Sao chép dữ liệu sao lưu sang máy kiểm thử
[root@localhost ~]# scp jl_backup.sql root@192.168.43.9:/root
root@192.168.43.9's password:
jl_backup.sql 100% 4730 426.9KB/s 00:00
1.7 Kiểm tra câu lệnh tạo cơ sở dữ liệu trên máy sản xuất
mysql> show create database jl;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| jl | CREATE DATABASE `jl` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
Tạo cơ sở dữ liệu cần khôi phục trên máy kiểm thử:
mysql> CREATE DATABASE `jl` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)
1.8 Khôi phục cơ sở dữ liệu trên máy kiểm thử
[root@localhost ~]# mysql -uroot -p -o jl < /root/jl_backup.sql
Enter password:
Sau khi nhập dữ liệu, truy cập cơ sở dữ liệu sẽ gặp lỗi:
mysql> use jl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Thêm tham số -A để giải quyết vấn đề:
[root@localhost ~]# mysql -uroot -p -A
Enter password:
Kiểm tra dữ liệu là dữ liệu trước khi sao lưu:
mysql> select * from test_table;
+------+---------------------+
| id | timestamp_field |
+------+---------------------+
| 6 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
| 9 | 2022-10-18 21:45:44 |
+------+---------------------+
4 rows in set (0.00 sec)
1.9 Sao chép nhật ký binary log sang máy kiểm thử
[root@localhost binlog]# scp BIN_LOG.000001 root@192.168.43.9:/root
root@192.168.43.9's password:
BIN_LOG.000001 100% 862 11.1KB/s 00:00
[root@localhost binlog]#
Kiểm tra vị trí khôi phục, khôi phục đến vị trí trước khi xóa bảng tại vị trí 683:
mysql> show binlog events in 'BIN_LOG.000001';
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| BIN_LOG.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| BIN_LOG.000001 | 123 | Previous_gtids | 6 | 154 | |
| BIN_LOG.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 219 | Query | 6 | 289 | BEGIN |
| BIN_LOG.000001 | 289 | Table_map | 6 | 337 | table_id: 110 (jl.test_table) |
| BIN_LOG.000001 | 337 | Update_rows | 6 | 393 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 393 | Xid | 6 | 424 | COMMIT /* xid=1320 */ |
| BIN_LOG.000001 | 424 | Anonymous_Gtid | 6 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 489 | Query | 6 | 559 | BEGIN |
| BIN_LOG.000001 | 559 | Table_map | 6 | 607 | table_id: 110 (jl.test_table) |
| BIN_LOG.000001 | 607 | Delete_rows | 6 | 652 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 652 | Xid | 6 | 683 | COMMIT /* xid=1321 */ |
| BIN_LOG.000001 | 683 | Anonymous_Gtid | 6 | 748 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 748 | Query | 6 | 862 | use `jl`; DROP TABLE `test_table` /* generated by server */ |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
14 rows in set (0.00 sec)
1.10 Sử dụng nhật ký binary log để khôi phục, vị trí dừng là 683 (trước khi xóa bảng)
[root@localhost ~]# mysqlbinlog --stop-position=683 --database=jl BIN_LOG.000001 |mysql -uroot -p
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case.
If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
Enter password:
Kiểm tra bảng sau khi khôi phục hoàn tất:
mysql> select * from test_table;
+------+---------------------+
| id | timestamp_field |
+------+---------------------+
| 10 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
+------+---------------------+
3 rows in set (0.00 sec)
1.11 Xuất bảng này từ cơ sở dữ liệu kiểm thử:
[root@localhost ~]# mysqldump -uroot -p jl test_table > recovered_table.sql
Enter password:
1.12 Sao chép bảng đã xuất sang cơ sở dữ liệu sản xuất
Sao chép dữ liệu:
[root@localhost ~]# scp recovered_table.sql root@192.168.43.10:/root
The authenticity of host '192.168.43.10 (192.168.43.10)' can't be established.
ECDSA key fingerprint is SHA256:YLyHddZaxFIT0VawsRWWUTz7WBtDKWzAuGdNyw9zLwU.
ECDSA key fingerprint is MD5:39:0b:eb:4d:fa:95:1b:61:1e:b0:5f:af:2c:51:05:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.43.10' (ECDSA) to the list of known hosts.
root@192.168.43.10's password:
recovered_table.sql 100% 1923 502.4KB/s 00:00
[root@localhost ~]#
Nhập dữ liệu:
[root@localhost backup_dir]# mysql -uroot -p jl < recovered_table.sql
Enter password:
Dữ liệu đã được khôi phục thành công:
mysql> select * from test_table;
+------+---------------------+
| id | timestamp_field |
+------+---------------------+
| 10 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
+------+---------------------+
3 rows in set (0.00 sec)