Cách hoàn tác lần xóa cuối cùng trong mysql?

Kết quả có thể xảy ra khi một kỹ sư phần mềm hoặc quản trị viên cơ sở dữ liệu xóa dữ liệu mà anh ta không cố ý khỏi cơ sở dữ liệu sản xuất, là một cơn đau tim ngay lập tức. Các bệnh viện có rất nhiều lập trình viên bỏ qua mệnh đề 'where' do nhầm lẫn

Nếu bạn đang đọc bài đăng này, tôi cho rằng bạn muốn biết một trong hai điều

1. Cách khôi phục dữ liệu bạn vừa xóa khỏi cơ sở dữ liệu MySQL của mình
2. Cách tránh xóa dữ liệu mà bạn không muốn xóa khỏi cơ sở dữ liệu sản xuất

Vì vậy, hãy giải quyết cả hai

[email protected]:~# ls -alh /var/lib/mysql/binlog.* -rw-r----- 1 mysql mysql 1.1G Apr 23 10:32 /var/lib/mysql/binlog.000001 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:33 /var/lib/mysql/binlog.000002 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:35 /var/lib/mysql/binlog.000003 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:38 /var/lib/mysql/binlog.000004 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:39 /var/lib/mysql/binlog.000005 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:41 /var/lib/mysql/binlog.000006 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:43 /var/lib/mysql/binlog.000007 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:45 /var/lib/mysql/binlog.000008 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:47 /var/lib/mysql/binlog.000009 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:49 /var/lib/mysql/binlog.000010 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:51 /var/lib/mysql/binlog.000011 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:53 /var/lib/mysql/binlog.000012 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:55 /var/lib/mysql/binlog.000013 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:57 /var/lib/mysql/binlog.000014 -rw-r----- 1 mysql mysql 1.1G Apr 23 10:59 /var/lib/mysql/binlog.000015 -rw-r----- 1 mysql mysql 306M Apr 23 13:18 /var/lib/mysql/binlog.000016

Có thể thấy, chúng tôi quan tâm đến tệp binlog cuối cùng

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

Sau khi hoàn tất, chúng ta hãy xem nội dung của tệp này. Chúng tôi sẽ tìm kiếm 'drop schema' trong vim. Đây là một phần có liên quan của tập tin

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

Như chúng ta có thể thấy, chúng ta muốn khôi phục đến vị trí 320358785. Chúng tôi có thể chuyển dữ liệu này đến giao diện người dùng ClusterControl

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out0

Vì vậy, chúng tôi muốn khôi phục bản sao lưu cho lần xác nhận trước đó ở vị trí 29600687. Hãy làm điều đó ngay bây giờ. Chúng tôi sẽ sử dụng máy chủ bên ngoài cho việc đó. Chúng tôi sẽ khôi phục bản sao lưu đến vị trí đó và chúng tôi sẽ duy trì hoạt động của máy chủ khôi phục để sau này chúng tôi có thể trích xuất dữ liệu bị thiếu

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out1

có vẻ tốt. Bây giờ chúng tôi có thể trích xuất dữ liệu này thành một tệp mà chúng tôi sẽ tải lại trên máy chủ

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

2

Đã xảy ra lỗi - điều này là do máy chủ được định cấu hình để chỉ có thể ghi tệp ở một vị trí cụ thể - đó là vấn đề bảo mật, chúng tôi không muốn cho phép người dùng lưu nội dung ở bất kỳ đâu họ muốn. Hãy kiểm tra nơi chúng tôi có thể lưu tệp của mình

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

3

Ok, hãy thử một lần nữa

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

4

Bây giờ nó trông tốt hơn nhiều. Hãy sao chép dữ liệu vào master

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

5

Bây giờ là lúc tải các hàng còn thiếu trên bản gốc và kiểm tra xem nó có thành công không

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

6

Đó là tất cả, chúng tôi đã khôi phục dữ liệu bị thiếu của mình

Mất dữ liệu một phần - Sao lưu hợp lý

Trong phần trước, chúng tôi đã khôi phục dữ liệu bị mất bằng sao lưu vật lý và máy chủ bên ngoài. Điều gì sẽ xảy ra nếu chúng tôi đã tạo bản sao lưu hợp lý? . Trước tiên, hãy xác minh rằng chúng tôi có một bản sao lưu hợp lý

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

7

Vâng, nó ở đó. Bây giờ, đã đến lúc giải nén nó

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

8

Khi bạn nhìn vào nó, bạn sẽ thấy rằng dữ liệu được lưu trữ ở định dạng INSERT đa giá trị. Ví dụ

[email protected]:~# mysqlbinlog --start-datetime='2018-04-23 13:00:00' --verbose /var/lib/mysql/binlog.000016 > sql.out

9

Tất cả những gì chúng ta cần làm bây giờ là xác định chính xác vị trí của bảng và sau đó là nơi lưu trữ các hàng mà chúng ta quan tâm. Trước tiên, khi biết các mẫu mysqldump [thả bảng, tạo bảng mới, vô hiệu hóa chỉ mục, chèn dữ liệu], hãy tìm ra dòng nào chứa câu lệnh CREATE TABLE cho bảng 'sbtest1'

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

0

Bây giờ, sử dụng phương pháp thử và sai, chúng ta cần tìm ra nơi để tìm các hàng của mình. Chúng tôi sẽ cho bạn thấy lệnh cuối cùng mà chúng tôi đã nghĩ ra. Toàn bộ thủ thuật là thử và in nhiều dòng khác nhau bằng cách sử dụng sed và sau đó kiểm tra xem dòng mới nhất có chứa các hàng gần đó nhưng muộn hơn những gì chúng tôi đang tìm kiếm không. Trong lệnh bên dưới, chúng tôi tìm kiếm các dòng giữa 971 [TẠO BẢNG] và 993. Chúng tôi cũng yêu cầu sed thoát khi nó đến dòng 994 vì chúng tôi không quan tâm đến phần còn lại của tệp

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

1

Đầu ra trông như dưới đây

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

2

Điều này có nghĩa là phạm vi hàng của chúng tôi [đến hàng có id là 23145] đã gần. Tiếp theo, đó là tất cả về làm sạch tệp thủ công. Chúng tôi muốn nó bắt đầu với hàng đầu tiên chúng tôi cần khôi phục

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

3

Và kết thúc với hàng cuối cùng để khôi phục

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

4

Chúng tôi đã phải cắt bớt một số dữ liệu không cần thiết [đó là phần chèn nhiều dòng] nhưng sau tất cả những điều này, chúng tôi có một tệp mà chúng tôi có thể tải lại trên bản gốc

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

5

Cuối cùng, kiểm tra lần cuối

# at 320358785
#180423 13:18:58 server id 1  end_log_pos 320358850 CRC32 0x0893ac86    GTID    last_committed=307804   sequence_number=307805  rbr_only=no
SET @@SESSION.GTID_NEXT= '52d08e9d-46d2-11e8-aa17-080027e8bf1b:443415'/*!*/;
# at 320358850
#180423 13:18:58 server id 1  end_log_pos 320358946 CRC32 0x487ab38e    Query   thread_id=55    exec_time=1     error_code=0
SET TIMESTAMP=1524489538/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop schema sbtest
/*!*/;

6

Tất cả đều tốt, dữ liệu đã được khôi phục

Mất dữ liệu một phần, nô lệ bị trì hoãn

Trong trường hợp này, chúng tôi sẽ không trải qua toàn bộ quá trình. Chúng tôi đã mô tả cách xác định vị trí của sự kiện mất dữ liệu trong nhật ký nhị phân. Chúng tôi cũng đã mô tả cách dừng một nô lệ bị trì hoãn và bắt đầu sao chép lại, cho đến thời điểm trước sự kiện mất dữ liệu. Chúng tôi cũng đã giải thích cách sử dụng SELECT INTO OUTFILE và LOAD DATA INFILE để xuất dữ liệu từ máy chủ bên ngoài và tải nó trên máy chủ. Đó là tất cả những gì bạn cần. Miễn là dữ liệu vẫn còn trên nô lệ bị trì hoãn, bạn phải dừng nó. Sau đó, bạn cần xác định vị trí trước sự kiện mất dữ liệu, khởi động nô lệ cho đến thời điểm đó và sau khi hoàn thành việc này, hãy sử dụng nô lệ bị trì hoãn để trích xuất dữ liệu đã bị xóa, sao chép tệp vào chủ và tải nó để khôi phục dữ liệu

Sự kết luận

Khôi phục dữ liệu bị mất không phải là điều thú vị, nhưng nếu bạn làm theo các bước chúng tôi đã thực hiện trong blog này, bạn sẽ có cơ hội tốt để khôi phục những gì bạn đã mất

Tôi có thể hoàn tác XÓA trong MySQL không?

Làm cách nào để hoàn tác thao tác cuối cùng trong MySQL?

Làm cách nào để khôi phục dữ liệu đã xóa trong MySQL?

Có hoàn tác trong MySQL không?