MySQL增量備份與恢復例項
小量的資料庫可以每天進行完整備份,因為這也用不了多少時間,但當資料庫很大時,就不太可能每天進行一次完整備份了,這時候就可以使用增量備份。增量備份的原理就是使用了mysql的binlog日誌。
本次操作的MySQL版本為5.5.40 for Linux (x86_64)
。
增量備份要確保開啟了二進位制日誌,參考mysql的日誌系統:
mysql> show variables like `%log_bin%`;
首先對pak資料庫做一個完整備份:
$ mysqldump -h localhost -upak -ppwd -P3306 --master-data=2 --single-transaction --opt pak > pak_bak_full.sql
這時候就會得到一個全備檔案pak_bak_full.sql。mysqldump操作會導致滾動一次log,假設新的binlog檔案是mysql-bin.000002。
模擬插入資料和誤操作
a. 在pak庫的某個表插入一些資料,然後執行flush logs
命令。這時將會產生一個新的二進位制日誌檔案mysql-bin.000003,mysql-bin.000002則儲存了全備過後的所有更改,既增加記錄的操作也儲存在了mysql-bin.00002中。
b. 再在pak庫中的t_user表中增加兩條記錄,然後誤刪除t_user表。t_user中增加記錄的操作和刪除表的操作都記錄在mysql-bin.000003中。
開始恢復
恢復過程不要記錄日誌:
mysql > set global sql_log_bin=0;
首先匯入全備資料
$ mysql -h localhost -upak -ppwd < pak_bak_full.sql
或
mysql> source /path/backup/pak_bak_full.sql
我們也可以看到全備時的binlog位置:
head -50 backup-file.sql |grep `CHANGE MASTER`
-- CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000001`, MASTER_LOG_POS=4321;
檢視當前所在二進位制日誌中的位置:
mysql> show master status;
根據上面兩個position能大概確定需要完整恢復哪幾個binlog檔案。
恢復mysql-bin.000002
在待恢復的position或時間點以前、全備以後的binlog需要全部恢復,多個檔案以空格隔開
$ mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p
此時查詢可以得到前兩條資料。
恢復部分mysql-bin.000003
這個日誌中包括了新增記錄和誤刪表兩個部分,我們需要恢復到新增記錄之後、誤刪操作以前的位置。
如果知道誤操作的命令如DROP TABLE
,則可以通過下面的方法在binlog檔案中找到誤操作之前的那個position:
(如下面的資訊顯示,誤操作DROP TABLE
之前的pos是775,在datetime 141204 15:08:04或pos 882時完成DROP TABLE
操作)
$ mysqlbinlog /var/lib/mysql/mysql-bin.000003 |grep -C 5 `DROP TABLE`
#141204 15:07:05 server id 1 end_log_pos 775 Xid = 376
COMMIT/*!*/;
# at 775
#141204 15:08:04 server id 1 end_log_pos 882 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1417676884/*!*/;
DROP TABLE `t_user` /* generated by server */
/*!*/;
# at 882
恢復命令:
$ mysqlbinlog /var/lib/mysql/mysql-bin.000003 --stop-position=775 | mysql -h localhost -uroot -p
如果position難以確定,但知道需要恢復到的確切(伺服器)時間,也可以使用datetime:
$ mysqlbinlog /var/lib/mysql/mysql-bin.000003 --stop-datetime="2014-12-04 15:08:00" | mysql -uroot -p
如果不是誤操作導致的,而是遷移資料庫,那麼不需要position或datetime,使用所有binlog檔案增量恢復即可。
確定恢復成功後記得開啟日誌記錄:
mysql > set global sql_log_bin=1;
報錯
1. unknown variable `default-character-set=utf8`
在使用mysqlbinlog
檢視二進位制日誌的時候,提示下面的錯誤:
/usr/local/mysql/bin/mysqlbinlog: unknown variable `default-character-set=utf8`
原因是在我為了統一mysql客戶端到服務端的的字元編碼,在/etc/my.cnf
檔案的[client]
、[mysqld]
等節加入了default-character-set = utf8
,mysqlbinlog
會從my.cnf
中的[client]
讀取配置,但奈何mysqlbinlog並不認識這個選項(據說是個bug)導致的。
應對這個bug的方法有兩個:
第一,自然是註釋到[client]
中的這個字符集配置;
第二,改用loose-default-character-set = utf8
。在選項前加了loose-
,表示當程式不認識此選項時會略過此選項,並給出一個警告。
相關文章
- rman 增量備份恢復
- MySQL 備份與恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- oracle 增量備份恢復驗證Oracle
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- 《入門MySQL—備份與恢復》MySql
- MySQL備份與恢復——實操MySql
- 入門MySQL——備份與恢復MySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- 用增量備份來快速恢復dg
- Mysql備份恢復MySql
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- 從nub備份恢復(同平臺)恢復RAC至單例項單例
- Mysql的幾種備份與恢復MySql
- MySQL入門--備份與恢復(三)MySql
- MySQL入門--備份與恢復(一)MySql
- MySQL入門--備份與恢復(二)MySql
- MySQL 日誌管理、備份與恢復MySql
- RAC備份恢復之Voting備份與恢復
- 【MySQL】MySQL備份和恢復MySql
- 備份與恢復:polardb資料庫備份與恢復資料庫
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- 東商專案mysql例項庫(dingding)增量備份的實現MySql
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- 將RAC備份集恢復為單例項資料庫單例資料庫
- mysql學習筆記之備份與恢復MySql筆記
- MySQL-19.資料庫備份與恢復MySql資料庫
- dg丟失歸檔,使用rman增量備份恢復
- Oracle邏輯備份與恢復選項說明Oracle
- PostgreSQL12中實現增量備份與任意時間點恢復SQL
- docker 中 MySQL 備份及恢復DockerMySql
- Oracle 備份 與 恢復 概述Oracle
- DB的備份與恢復