mysql匯入匯出備份與恢複

標籤：mysql   mysqlbinlog   mysqldump   資料恢複   資料備份   

原理：差異備份與增量備份不同的是起始點，一個是完全備份時候的點為起始點，一個是以最近一次備份的點為起始點，若訪問量大的時候應該混合備份：

星期1的零點完全備份一次

星期2，3，4的零點進行增量備份

星期5零點進行差異備份

星期6,7零點進行增量備份

若星期6的8點誤刪了某個表或者記錄庫等的可以先

星期1的零點資料匯入到資料庫

再星期5零點差異備份的資料匯入資料庫

再星期6零點增量備份的資料匯入資料庫

再查看當前日誌資訊找到那個刪除操作的時間點前一個操作的時間點

再備份這段時間內的資料，然後匯入資料庫即可恢複

1.mysqldump常用選項

u,-h,-p                                  #指定串連資料庫的使用者名稱密碼，地址

-A, --all-databases                    #備份所有資料庫

-x, --lock-all-tables                   #鎖定所有表

 -l, --lock-tables                     #鎖定備份的表（只有備份單張表才建議使用這個選項）

-B, --databases db_name1 db_name2 ...    #備份指定的資料庫

--single-transaction                    #啟動一個大的單一事務實現備份（熱備，只能針對                       #InnoDB儲存引擎來實現，如果備份的表中有MyISAM的表，則對於這些表沒有任何意義）

-C, --compress                   #壓縮傳輸（在伺服器端會佔用更過的cup刻度）

-E, --events                     #備份指定庫的事件調度器；

-R, --routines                 #備份預存程序和儲存函數；

--triggers                     #備份觸發器

-F,--flush-logs             #鎖定表之後執行flush logs命令（日誌滾動）；

--master-data[=num]   #在完全備份的備份檔案中記錄下來，在備份那一時刻（鎖定所有表時），對應的二進位記錄檔的檔案名稱及其位置。num=0表示不記錄，num=1，記錄CHANGEMASTER TO語句（語句未被注釋），num=2，記錄為備註陳述式；

完全備份

mysqldump -A -x --master-data=2 >/backup/`date +%F`_all.sql

 

2. mysqlbinlog：

--start-position=#                                 #從指定的事件位置查看

--stop-position=#                                 #只顯示到指定的事件位置

--start-datetime=YYYY-MM-DD hh:mm:ss               #從指定的時間查看

--stop-datetime=YYYY-MM-DD hh:mm:ss               #只顯示到指定的時間

根據位置來增量備份與差異備份（位置根據你二進位裡自己需要的位置）

[[email protected] backup]#mysqlbinlog --start-position=120 --stop-position=1169/data/mysql/mysql-bin.000004 >/backup/`date +%F`_inc.sql

根據日期來增量備份與差異備份（時間根據你備份的時間）

/usr/local/mysql/bin/mysqlbinlog  --start-date="2015-09-16 00:00:00"--stop-date="2015-09-17 00:00:00" /data/mysql/mysql-bin.000004>/backup/`date +%F`_inc.log

若在16號的10：40左右出的錯誤，我們可以先找出要恢複的點（比如刪除某個動作前的那個時間點）

（1）先匯出部分資料查看操作

mysqlbinlog--start-date="2015-09-16 10:35:00" --stop-date="2015-09-1610:45:00" /data/mysql/mysql-bin.000004 >/backup/error_time.sql

（2）找到操作的點

vi /backup/error_time.sql

/*!*/;

# at 1138

#150916 10:40:51 server id 22  end_log_pos 1169 CRC32 0x49372e7e        Xid = 2175

COMMIT/*!*/;

# at 1169

#150916 10:43:14 server id 22  end_log_pos 1282 CRC32 0xe413656e        Query  thread_id=4     exec_time=0     error_code

=0

SET TIMESTAMP=1442371394/*!*/;

DROP TABLE `cj` /* generated by server */         ///刪除表cj的操作點，往上找時間或者點

（3）利用備份刪除前到00：00:00的資料

mysqlbinlog --start-date="2015-09-1600:00:00" --stop-date="2015-09-16 10:40:52"/data/mysql/mysql-bin.000004 >/backup/right.sql    ///加一秒恢複刪除前的操作

（4）匯入到資料庫

  mysql先停止二進位日誌滾動 

   mysql>set sql_log_bin=0;

   QueryOK, 0 rows affected (0.00 sec)

   用戶端操作：

   mysql </backup/2015-09-16_all.sql            ///恢複我今天做的完全備份

   mysql </backup/right.sql                     ///恢複錯誤前的備份

本文出自 “創新分享馳騁裡外” 部落格，請務必保留此出處http://10554846.blog.51cto.com/10544846/1695376

mysql匯入匯出備份與恢複