3、在數(shù)據(jù)庫(kù)中插入一條記錄，再執(zhí)行flush-logs操作，生成新的二進(jìn)制增量備份文件。
mysql> insert into yx(name,score) values('tom',87);
Query OK, 1 row affected (0.00 sec)
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| tom | 87.00 |
+----------+--------+
5 rows in set (0.00 sec)
[root@promote data]# mysqladmin -u root -p flush-logs #生成二進(jìn)制文件
Enter password:
[root@promote data]# ls
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 performance_schema test
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.index sys
4、用delete刪除剛才插入的數(shù)據(jù)。模擬完全備份后數(shù)據(jù)丟失。
mysql> delete from yx where name='tom';
Query OK, 1 row affected (0.00 sec)
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
4 rows in set (0.00 sec)
5、使用二進(jìn)制文件進(jìn)行恢復(fù)操作
[root@promote data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
6、查看數(shù)據(jù)庫(kù)內(nèi)容，刪除的數(shù)據(jù)有了。說(shuō)明數(shù)據(jù)恢復(fù)成功。
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| tom | 87.00 |
+----------+--------+
5 rows in set (0.00 sec）
完全備份之后丟失所有數(shù)據(jù)的恢復(fù)步驟
1、使用drop刪除表yx，模擬數(shù)據(jù)完全丟失
mysql> drop table yx;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
2、先使用mysql命令進(jìn)行完全備份恢復(fù)操作。
[root@promote data]# mysql -u root -p test < /opt/test.sql
mysql> use test;
Database changed
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
4 rows in set (0.00 sec)
3、使用二進(jìn)制文件進(jìn)行增量備份操作。
[root@promote data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| tom | 87.00 |
+----------+--------+
5 rows in set (0.00 sec)
基于時(shí)間點(diǎn)與位置的恢復(fù)
利用二進(jìn)制日志實(shí)現(xiàn)局域時(shí)間點(diǎn)與位置的恢復(fù)，假如需要往數(shù)據(jù)庫(kù)中插入兩條數(shù)據(jù)，但是由于誤操作，兩條插入語(yǔ)句中間刪除一條數(shù)據(jù)，而這條數(shù)據(jù)不應(yīng)該刪除，這時(shí)候，需要基于時(shí)間點(diǎn)與位置進(jìn)行恢復(fù)。
–start-datetime=datetime
從二進(jìn)制日志中第1個(gè)日期時(shí)間等于或晚于datetime參量的事件開(kāi)始讀。
–stop-datetime=datetime
從二進(jìn)制日志中第1個(gè)日期時(shí)間等于或晚于datetime參量的事件起停止讀。
–start-position=N從二進(jìn)制日志中第1個(gè)位置等于N參量時(shí)的事件開(kāi)始讀。
–stop-position=N從二進(jìn)制日志中第1個(gè)位置等于和大于N參量時(shí)的事件起停止讀。
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
5 rows in set (0.00 sec)
mysql> insert into yx values('test01',87);
Query OK, 1 row affected (0.00 sec)
mysql> delete from yx where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
mysql> insert into yx values('test02',99);
Query OK, 1 row affected (0.17 sec)
mysql> select * from yx;
+---------+-------+
| name | score |
+---------+-------+
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| test01 | 87.00 |
| test02 | 99.00 |
+---------+-------+
6 rows in set (0.00 sec)
1、基于時(shí)間點(diǎn)的恢復(fù)。18-07-03 21:56:04是錯(cuò)誤語(yǔ)句節(jié)點(diǎn)，18-07-03 21:56:11第二句正確語(yǔ)句節(jié)點(diǎn)
[root@promote data]# mysqlbinlog --no-defaults --base64-output=decode-rows mysql-bin.000003
# at 298
#180703 21:55:35 server id 1 end_log_pos 406 CRC32 0x257c67ab Query thread_id=46 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1530626135/*!*/;
insert into yx values('test01',87)
/*!*/;
# at 406
#180703 21:55:35 server id 1 end_log_pos 437 CRC32 0xdd7913a3 Xid = 392
COMMIT/*!*/;
# at 437
#180703 21:56:04 server id 1 end_log_pos 502 CRC32 0x0d09bd0b Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 502
#180703 21:56:04 server id 1 end_log_pos 581 CRC32 0xe6040c79 Query thread_id=46 exec_time=0 error_code=0
SET TIMESTAMP=1530626164/*!*/;
BEGIN
/*!*/;
# at 581
#180703 21:56:04 server id 1 end_log_pos 691 CRC32 0x2d99f699 Query thread_id=46 exec_time=0 error_code=0
SET TIMESTAMP=1530626164/*!*/;
delete from yx where name='zhangsan'
/*!*/;
# at 691
#180703 21:56:04 server id 1 end_log_pos 722 CRC32 0x4a742173 Xid = 393
COMMIT/*!*/;
# at 722
#180703 21:56:11 server id 1 end_log_pos 787 CRC32 0x6d0b47d8 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 787
#180703 21:56:11 server id 1 end_log_pos 866 CRC32 0x97e2deb7 Query thread_id=46 exec_time=0 error_code=0
SET TIMESTAMP=1530626171/*!*/;
BEGIN
/*!*/;
# at 866
#180703 21:56:11 server id 1 end_log_pos 974 CRC32 0x9e24e8af Query thread_id=46 exec_time=0 error_code=0
SET TIMESTAMP=1530626171/*!*/;
insert into yx values('test02',99)
[root@promote data]# mysql -u root -p test < /opt/test.sql #先進(jìn)行完全恢復(fù)
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
4 rows in set (0.00 sec)
[root@promote data]# mysqlbinlog --no-defaults --stop-datetime='18-07-03 21:56:04' mysql-bin.000003 | mysql -u root -p #結(jié)束節(jié)點(diǎn)
Enter password:
[root@promote data]# mysqlbinlog --no-defaults --start-datetime='18-07-03 21:56:11' mysql-bin.000003 | mysql -u root -p #重新開(kāi)始節(jié)點(diǎn)
Enter password:
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| test01 | 87.00 |
| test02 | 99.00 |
+----------+--------+
6 rows in set (0.00 sec)
2、基于位置恢復(fù)，其中581是錯(cuò)誤語(yǔ)句的節(jié)點(diǎn)，866是第二句正確語(yǔ)句的節(jié)點(diǎn)
[root@promote data]# mysql -u root -p test < /opt/test.sql
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
4 rows in set (0.01 sec)
[root@promote data]# mysqlbinlog --no-defaults --stop-position='581' mysql-bin.000003 | mysql -u root -p
Enter password:
[root@promote data]# mysqlbinlog --no-defaults --start-position='866' mysql-bin.000003 | mysql -u root -p
Enter password:
mysql> select * from yx;
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
| test01 | 87.00 |
| test02 | 99.00 |
+----------+--------+
6 rows in set (0.00 sec)
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 51dongshi.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬(wàn)商天勤律師事務(wù)所王興未律師提供法律服務(wù)
