Mysql全量備份、binlog如何恢復數(shù)據(jù)

本文主要給大家介紹MySQL 全量備份、binlog如何恢復數(shù)據(jù)，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關注我的更新文章的。    

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：主機域名、虛擬空間、營銷軟件、網(wǎng)站建設、米東網(wǎng)站維護、網(wǎng)站推廣。

某天工作時間，一個二貨犯暈登錯生產(chǎn)當測試環(huán)境了，直接drop了一個數(shù)據(jù)庫,需要緊急恢復!可利用備份的數(shù)據(jù)文件以及增量的 binlog 文件進行數(shù)據(jù)恢復。

具體思路歸納幾點：

1、恢復條件為 MySQL 要開啟 binlog 日志功能，并且要全備和增量的所有數(shù)據(jù)。

2、恢復時建議對外停止更新，即禁止更新數(shù)據(jù)庫。（這點很重要）

3、先恢復全量，然后把全備時刻點以后的增量日志，按順序恢復成 SQL 文件，

4、然后把文件中有問題的SQL語句刪除(也可通過時間和位置點)，再恢復到數(shù)據(jù)庫。

具體實例演示：

1、首先要確保MySQL開啟了binlog日志功能，檢查如下結果

Mysql 全量備份、binlog如何恢復數(shù)據(jù)

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /mysql_data/mysql-bin       |
| log_bin_index                   | /mysql_data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

2、查看當前測試表里面數(shù)據(jù)信息后面好做對比

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 劉晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 張立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大師    | 男   |   35 | IS    |
| 200215128 | 老謝      | 男   |   33 | MA    |
| 200215129 | 小賈      | 男   |   30 | CS    |
| 200215130 | 會民      | 男   |   23 | CS    |
| 200215131 | 陳興      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 國良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
+-----------+-----------+------+------+-------+
15 rows in set (0.00 sec)

3、現(xiàn)在進行全備份

mysqldump -u root -p -B -F -R -x  student|gzip > /mysql_backup/student_$(date +%Y%m%d_%H%M%S).sql.gz

參數(shù)說明：
-B：指定數(shù)據(jù)庫
-F：刷新日志
-R：備份存儲過程等
-x：鎖表

4、再次插入新數(shù)據(jù)

INSERT INTO Student VALUES ('200215137','程程','女',30,'IS');
INSERT INTO Student VALUES ('200215138','琪琪','男',29,'MA');
INSERT INTO Student VALUES ('200215139','龍龍','男',27,'IS');

5、檢查是否插入成功，如下可以看出已經(jīng)插入成功

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 劉晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 張立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大師    | 男   |   35 | IS    |
| 200215128 | 老謝      | 男   |   33 | MA    |
| 200215129 | 小賈      | 男   |   30 | CS    |
| 200215130 | 會民      | 男   |   23 | CS    |
| 200215131 | 陳興      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 國良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
| 200215137 | 程程      | 女   |   30 | IS    |
| 200215138 | 琪琪      | 男   |   29 | MA    |
| 200215139 | 龍龍      | 男   |   27 | IS    |
+-----------+-----------+------+------+-------+
18 rows in set (0.00 sec)

6.此時誤操作，刪除了student數(shù)據(jù)庫

mysql> drop database student;
Query OK, 3 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

7.查看全備之后備份文件

[root@ocbsdb01 mysql_backup]# cd  /mysql_backup
[root@ocbsdb01 mysql_backup]# gzip -d student_20170829_090319.sql.gz 
[root@ocbsdb01 mysql_backup]# ls
student_20170829_090319.sql
[root@ocbsdb01 mysql_backup]# vim student_20170829_090319.sql

8.檢查并移動binlog文件，并導出為 SQL 文件剔除其中的 drop 語句，查看 MySQL 的數(shù)據(jù)存放目錄，

由下面可知是在/mysql_data下，將 binlog 文件導出SQL文件，并vim編輯它刪除其中的 drop 語句。

/home/mysql/mysql5/bin/mysqlbinlog   --no-defaults  /tmp/mysql-bin.000004 > /tmp/04.sql

注意：在恢復全備數(shù)據(jù)之前必須將該 binlog文件移出，否則恢復過程中，會繼續(xù)寫入語句到 binlog，最終導致增量恢復數(shù)據(jù)部分變得比較混亂。

在轉換sql的時候可能會報錯，具體信息如下：

[root@ocbsdb01 tmp]# /home/mysql/mysql5/bin/mysqlbinlog   /tmp/mysql-bin.000004 > /tmp/04.sql

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

原因是mysqlbinlog這個工具無法識別binlog中的配置中的default-character-set=utf8這個指令。

兩個方法可以解決這個問題

  • 在MySQL的配置/etc/my.cnf中將default-character-set=utf8 修改為 character-set-server = utf8，但是這需要重啟MySQL服務，如果你的MySQL服務正在忙，那這樣的代價會比較大。

  • 用mysqlbinlog --no-defaults mysql-bin.000004 命令打開

9、開始恢復全備數(shù)據(jù)

[root@ocbsdb01 tmp]# mysql -u root -p < /mysql_backup/student_20170829_090319.sql
Enter password:

查看數(shù)據(jù)看看，student在不在,可以看到已經(jīng)在了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

11、開始恢復增量數(shù)據(jù)

使用04.sql文件恢復全備時刻到刪除數(shù)據(jù)庫之間新增的數(shù)據(jù)，編輯04bin.sql #刪除里面的drop語句

[root@ocbsdb01 tmp]# vim 04.sql 

將drop 操作下面的內(nèi)容刪除
drop database student
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

不然會報如下錯誤：

ERROR 1790 (HY000) at line 96: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. 

The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

我的binlog文件具體內(nèi)容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170829  9:03:22 server id 201609  end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829  9:03:22
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARg6m2Y=
'/*!*/;
# at 123
#170829  9:03:22 server id 201609  end_log_pos 154 CRC32 0xf909764e Previous-GTIDs
# [empty]
# at 154
#170829  9:04:42 server id 201609  end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170829  9:04:42 server id 201609  end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#170829  9:04:42 server id 201609  end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225
# at 359
#170829  9:04:42 server id 201609  end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HlJ2da0=
qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ
U/PtqV0=
'/*!*/;
# at 421
#170829  9:04:42 server id 201609  end_log_pos 452 CRC32 0xfcd87186 Xid = 168
COMMIT/*!*/;
# at 452
#170829  9:04:42 server id 201609  end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 517
#170829  9:04:42 server id 201609  end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 592
#170829  9:04:42 server id 201609  end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225
# at 657
#170829  9:04:42 server id 201609  end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HihH2Zg=
qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN
QQ51xzI=
'/*!*/;
# at 719
#170829  9:04:42 server id 201609  end_log_pos 750 CRC32 0x92ebbf95 Xid = 169
COMMIT/*!*/;
# at 750
#170829  9:04:42 server id 201609  end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 815
#170829  9:04:42 server id 201609  end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 890
#170829  9:04:42 server id 201609  end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225
# at 955
#170829  9:04:42 server id 201609  end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
Bv48HtB3xGc=
qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ
UwMlP10=
'/*!*/;
# at 1017
#170829  9:04:42 server id 201609  end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170
COMMIT/*!*/;
# at 1048
#170829  9:06:30 server id 201609  end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1113
#170829  9:06:30 server id 201609  end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968790/*!*/;
drop database student
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

調整好后開始恢復增量數(shù)據(jù)

[root@ocbsdb01 tmp]# mysql -u root -p  < 04.sql

Enter password: 

再次查看數(shù)據(jù)庫，發(fā)現(xiàn)全備份到刪除數(shù)據(jù)庫之間的那三條數(shù)據(jù)也恢復了!!

mysql> select * from Student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇      | 男   |   20 | CS    |
| 200215122 | 劉晨      | 女   |   19 | CS    |
| 200215123 | 王敏      | 女   |   18 | MA    |
| 200215125 | 張立      | 女   |   19 | IS    |
| 200215126 | 虎威      | 男   |   25 | CS    |
| 200215127 | 魏大師    | 男   |   35 | IS    |
| 200215128 | 老謝      | 男   |   33 | MA    |
| 200215129 | 小賈      | 男   |   30 | CS    |
| 200215130 | 會民      | 男   |   23 | CS    |
| 200215131 | 陳興      | 男   |   33 | MA    |
| 200215132 | 阿帆      | 男   |   36 | IS    |
| 200215133 | 國良      | 男   |   40 | IS    |
| 200215134 | 老宋      | 男   |   40 | IS    |
| 200215135 | 光光      | 男   |   35 | IS    |
| 200215136 | 王老板    | 女   |   27 | IS    |
| 200215137 | 程程      | 女   |   30 | IS    |
| 200215138 | 琪琪      | 男   |   29 | MA    |
| 200215139 | 龍龍      | 男   |   27 | IS    |
+-----------+-----------+------+------+-------+
18 rows in set (0.00 sec)

看了以上關于Mysql 全量備份、binlog如何恢復數(shù)據(jù)，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時售前售后，隨時幫您解答問題的。

網(wǎng)頁名稱：Mysql全量備份、binlog如何恢復數(shù)據(jù)
本文來源：http://muchs.cn/article20/gedcjo.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、網(wǎng)站內(nèi)鏈、電子商務、移動網(wǎng)站建設、外貿(mào)網(wǎng)站建設、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁設計