Mysql數據備份與恢復

Mysql數據備份與恢復

備份/恢復策略：
1. 要定期做 mysql備份，並考慮系統可以承受的恢復時間。
2. 確保 mysql 打開log-bin，有了 binarylog，mysql才可以在必要的時候做完整恢復，或基於時間點的恢復，或基於位置的恢復。

3. 要經常做備份恢復測試，確保備份是有效的，並且是可以恢復的。

冷備份

備份：
1. 停掉 mysql服務，在操作系統級別備份 mysql的數據文件。
2. 重啟 mysql服務，備份重啟以後生成的 binlog。
恢復：
1. 停掉 mysql服務，在操作系統級別恢復 mysql的數據文件。
2. 重啟 mysql服務，使用 mysqlbinlog恢復自備份以來的 binlog。

邏輯備份

備份：
1. 選擇在系統空閒時，比如在夜間，使用 mysqldump –F(flush-logs)備份數據
庫。

mysqldump –u root –p*** pointcard –F > pointcard.sql

2. 並備份 mysqldump開始以後生成的 binlog。
恢復：
1. 停掉應用，執行 mysql 導入備份文件.

mysql –u root –p*** pointcard < pointcard.sql

2. 使用 mysqlbinlog 恢復自mysqldump 備份以來的binlog。

mysqlbinlog $HOME/data/mysql-bin.123456 | mysql -u root –p*** 

單個表的備份：
備份：
1. 方法 1：

mysql > select * into outfile ‘/tmp/order_tab’ fields-terminated-by=’,’ from order_tab;

2. 方法 2：

mysqldump –u root –p*** –T /tmp pointcard order_tab --fields-terminated-by=’,’;

恢復：

1. 方法 1：

mysql > load data [local] infile ‘/tmp/order_tab’ into table order_tab fields-terminated-by=’,’;

2. 方法 2：

mysqlimport –u root –p*** [--local] pointcatd order_tab.txt --fields-terminated-by=’,’;

注意：如果導入和導出是跨平台操作的（windows和 linux），那麼要注意設置參數line-
terminated-by， windows上設置為 line-terminated-by=’\r\n’，linux上設置為
line-terminated-by=’\n’.

使用備份工具ibbackup

ibbackup是 innodb公司（www.innodb.com)的一個熱備份工具，專門對 innodb存儲引擎進行物理熱備份，此工具是收費的，不能免費使用。現在innodb 公司已經被oracle收購
使用方法：
編輯用於啟動的配置文件my.cnf和用於備份的配置文件my2.cnf
my.cnf的例子如下：

[mysqld]
datadir = /home/heikki/data 

innodb_data_home_dir = /home/heikki/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/heikki/data
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M

如果想備份到/home/heikki/backup，則my2.cnf的例子如下：

[mysqld]datadir = /home/heikki/backupinnodb_data_home_dir = /home/heikki/backupinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /home/heikki/backupset-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M

開始備份ibbackup my.cnf my2.cnf如果需要恢復，則進行日志重做ibbackup --apply-log my2.cnf恢復後重啟數據庫服務./bin/mysqld_saft --defaults-file=my2.cnf &

時間點恢復：

1. 如果上午 10點發生了誤操作，可以用以下語句用備份和 binglog將數據恢復到故
障前：

mysqlbinlog --stop-date="2005-04-20 9:59:59"
/var/log/mysql/bin.123456 | mysql -u root –pmypwd

2. 跳過故障時的時間點，繼續執行後面的 binlog，完成恢復

mysqlbinlog --start-date="2005-04-20 10:01:00"
/var/log/mysql/bin.123456| mysql -u root -pmypwd \

位置恢復：

和時間點恢復類似，但是更精確，步驟如下：

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20
10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

該命令將在/tmp目錄創建小的文本文件，編輯此文件，找到出錯語句前後的位置號 ，例如前後位置號分別是368312 和368315。恢復了以前的備份文件後，你應從命令行輸入下面內容：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \

上面的第1 行將恢復到停止位置為止的所有事務。下一行將恢復從給定的起始位置
直到二進制日志結束的所有事務。因為mysqlbinlog 的輸出包括每個SQL 語句記錄
之前的SET TIMESTAMP 語句，恢復的數據和相關MySQL 日志將反應事務執行的原時
間。

MyISAM表修復：

一張損壞的表的症狀通常是查詢意外中斷並且能看到下述錯誤：
? “tbl_name.frm”被鎖定不能更改。
? 不能找到文件“tbl_name.MYI”（Errcode：nnn）。
? 文件意外結束。
? 記錄文件被毀壞。
? 從表處理器得到錯誤nnn
解決方法如下：

方法一：

myisamchk -r tablename

上面的方法幾乎能解決所有問題，如果不行，則使用：

myisamchk -o tablename

方法二：

1) CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
2) REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]