如何利用MySQL的binlog恢復(fù)誤刪數(shù)據(jù)庫詳解
發(fā)布日期：2022-02-06 14:47 | 文章來源：腳本之家
1 查看當(dāng)前數(shù)據(jù)庫內(nèi)容并備份數(shù)據(jù)庫
查看數(shù)據(jù)庫信息：
備份數(shù)據(jù)庫：
[root@localhost ~]# mysqldump -u root -p t > /mnt/t.sql Enter password: [root@localhost ~]# ll /mnt/t.sql -rw-r--r-- 1 root root 1771 Aug 25 11:56 /mnt/t.sql
2 開啟bin_log功能
首先查看數(shù)據(jù)庫是否開啟bin_log功能
mysql> show variables like "%log_bin%";
需要修改mysql的配置文件，/etc/的my.cnf，添加一句log_bin = mysql_bin即可
3 模擬誤操作（插入3條數(shù)據(jù)，刪除數(shù)據(jù)庫）
mysql> insert into t1 values (3); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (4); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (5); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 5 | | 4 | | 3 | +------+ 5 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql_bin.000003 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
刪除數(shù)據(jù)：
mysql> truncate t1; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; Empty set (0.00 sec)
此時突然數(shù)據(jù)庫損壞或者人為刪除
mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec)
4 數(shù)據(jù)恢復(fù)
1 用已經(jīng)備份的/mnt/t.sql來恢復(fù)數(shù)據(jù)
mysql> source /mnt/t.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-------------+ | Tables_in_t | +-------------+ | t1 | +-------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
2 還有三條數(shù)據(jù)沒有恢復(fù)，怎么辦。只能用bin-log來恢復(fù)
[root@localhost ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000002 | mysql -u root -p123.com t
mysql> use t; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) mysql>
5 總結(jié)
備份數(shù)據(jù)
mysqldump -uroot -p123456 test -l -F '/tmp/test.sql' -l:讀鎖（只能讀取，不能更新） -F:即flush logs，可以重新生成新的日志文件，當(dāng)然包括log-bin日志
查看binlog日志
mysql>show master status;
導(dǎo)入之前備份數(shù)據(jù)
mysql -uroot -p t -v -f </mnt/t.sql -v查看導(dǎo)入的詳細(xì)信息 -f是當(dāng)中間遇到錯誤時，可以skip過去，繼續(xù)執(zhí)行下面的語句
恢復(fù)binlog-file二進(jìn)制日志文件
mysqlbinlog --no-defaults binlog-file | mysql -uroot -p t
從某一(367)點開始恢復(fù)
mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p t
先查好那一點，用more來查看
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more
然后恢復(fù)
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p t
重置binlog日志
mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+
mysql> flush logs;#關(guān)閉當(dāng)前的二進(jìn)制日志文件并創(chuàng)建一個新文件，新的二進(jìn)制日志文件的名字在當(dāng)前的二進(jìn)制文件的編號上加1。
到此這篇關(guān)于如何利用MySQL的binlog恢復(fù)誤刪數(shù)據(jù)庫的文章就介紹到這了,更多相關(guān)MySQL binlog恢復(fù)誤刪數(shù)據(jù)庫內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信