----------------------------------------恢復(fù)到某個(gè)時(shí)間點(diǎn)
全備
[root@zqr-cs-1 binlog]# mysqldump -uroot -predhat --default-character-set=utf8 --max-allowed-packet=128M --flush-logs --set-gtid-purged=OFF --hex-blob --triggers --routines --events --master-data --single-transaction --databases test > /backup/test_20200611.sql
全備之后埃碱，數(shù)據(jù)庫(kù)發(fā)生了dml操作
mysql> insert into a values ('test1',1), ('test2',2), ('test3',3);
Query OK, 3 rows affected (0.01 sec)
突發(fā)：刪除數(shù)據(jù)庫(kù)test；杳！！? ?
查看全備到刪除數(shù)據(jù)庫(kù)這個(gè)時(shí)間段的操作＝庠濉孝治！
[root@zqr-cs-1 binlog]# mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS --start-datetime="2020-06-11 16:44:00" --stop-datetime="2020-06-11 16:52:00" mysql-bin.000018
利用binlog進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)。
[root@zqr-cs-1 binlog]# mysqlbinlog --no-defaults --start-datetime="2020-06-11 16:44:00" --stop-datetime="2020-06-11 16:52:00" mysql-bin.000018? | mysql -uroot -predhat
完成恢復(fù)８３蕖酒来！
---------------------------------------------恢復(fù)到某個(gè)position
mysql>show binlog events in 'mysql-bin.000004';
mysqlbinlog -d db --start-position=219--stop-position=907 /usr/local/mysql/binlog/mysql-bin.000004>/tmp/recover.sql
set sql_log_bin=0;
source?/tmp/recover.sql;
-----------------------------------誤刪數(shù)據(jù)后，利用binlog肪凛，恢復(fù)數(shù)據(jù)
flush logs; 開啟新的binlog
定位誤刪sql語(yǔ)句的位置堰汉！
sudo mysqlbinlog --base64-output=DECODE-ROWS -v -d dbname mysql-bin.000001 | grep --ignore-case -A3 -B4 '錯(cuò)誤的sql語(yǔ)句'??
恢復(fù)數(shù)據(jù)時(shí)，可能會(huì)有重復(fù)數(shù)據(jù)的報(bào)錯(cuò)伟墙，建議用-f參數(shù)忽略翘鸭。
# mysql -uroot -p -f dbname < data.sql
如果要恢復(fù)表級(jí)別的數(shù)據(jù)，導(dǎo)出成sql后再進(jìn)行過濾grep即可戳葵。
# more data.sql | grep --ignore-case -E 'insert|update|delete' | grep table
NOTE!? ?推薦使用postion位置點(diǎn)進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)就乓。
Only use the?--start-datetime?and?--stop-datetime?options to help you find the actual event positions of interest. Using the two options to specify the range of binary log segment to apply is not recommended: there is a higher risk of missing binary log events when using the options. Use?--start-position?and?--stop-position?instead.
