備份與恢復
還原邏輯備份
如果還原的是邏輯備份而不是物理備份,則與使用操作系統簡單地復制文件到適當位置的方式不同,需要使用MySQL服務器本身來加載數據到表中。在加載導出文件之前,應該先花一點時間考慮文件有多大,需要多久加載完,以及在啟動之前還需要做什么事情,例如通知用戶或禁用掉部分應用。禁掉二進制日志也是個好主意,除非需要將還原操作復制到備庫:服務器加載一個巨大的導出文件的代價很高,并且寫二進制日志會增加更多的(可能沒有必要的)開銷。加載巨大的文件對于一些存儲引擎也有影響。例如,在單個事務中加載100GB數據到InnoDB就不是個好想法,因為巨大的回滾段將會導致問題。應該以可控大小的塊來加載,并且逐個提交事務。有兩種類型的邏輯備份,所以相應地有兩種類型的還原操作。
加載SQL文件
如果有一個SQL導出文件,它將包含可執行的SQL.需要做的就是運行這個文件。假設備份Sakila示例數據庫和Schema到單個文件,下面是用來還原的常用命令。
mysql < sakila-backup.sql
也可以從mysql米精靈行客戶端用SOURCE命令加載文件。這只是做相同事情的不同方法,不過該方法使得某些事情更簡單。例如,如果你是MySQL管理用戶,就可以關閉用客戶端連接執行時的二進制記錄,然后加載文件而不需要重啟MySQL服務器。
mysql>SET SQL_LOG_BIN =0;
mysql>SOURCE sakila-backup.sql;
mysql>SET SQL_LOG_BIN=1;
需要注意的時,如果使用SOURCE,當定向文件到mysql時,默認秦廣下,發生一個錯誤不會導致一批語句退出。如果備份做過壓縮,那么不要分別解壓縮和加載。應該在單個操作中完成解壓縮和加載,這樣做會快很多。
gunzip -c sakila-backup.sql.gz | mysql
如果想用SOURCE命令加載一個壓縮文件,請見下面關于命名管道的討論。如果只想恢復單個表(例如,actor表),要怎么做呢?如果數據沒有分行但有schema信息,那么還原數據并不難。
grep 'INSERT INTO `actor` sakila-backup.sql | mysql sakila'
或者,如果文件是壓縮過的,那么命令如下:
gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`' | mysql sakila
如果需要創建表并還原數據,而在單個文件中有整個數據庫,則必須先編輯這個文件。這也是有一些人喜歡導出每個表到各自文件中的原因。大部分編輯器無法應付巨大的文件,尤其如果它們是壓縮過的。另外,也不會想實際地編輯文件本身——只想抽取相關地行——因此可能必須做一些命令工作。使用grep來僅抽出給定表的INSERT語句較簡單,就像我們在前面命令中做的那樣,但得到CREATE TABLE語句比較難。下面是抽取所需段落的sed腳本。
sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `actor`/!d;q' sakila-backup.sql
我們得承認這條命令非常隱晦。如果必須以這種方式還原數據,那只能說明備份設計非常糟糕。如果有一點規劃,可能就不會需要痛苦地區嘗試弄清楚sed如何工作了。只需要備份每個表到各自地文件,或者可以更進異步,分別備份數據和Schema.
加載符號分隔文件
(符號分隔樣式)
(sql文件)
如果是通過SELECT INTO OUTFILE導出的符號分隔文件,可以使用LOAD DATA INFILE通過相同的參數來加載。也可以用mysqlimport,這是LOAD DATA INFILE的一個包裝。這種方式依賴命名約定決定從哪里加載一個文件的數據。我們希望你導出了Schema,而不僅是數據。如果是這樣,那應該是一個SQL導出,就可以使用上一節中描述的技術來加載。使用LOAD DATA FILE有一個非常好的優化技巧。LOAD DATA INFILE必須直接從文本文件中讀取,因此,如果是壓縮文件很多人會在加載前先解壓縮,這是非常慢的磁盤密集型操作。然而,在支持FIFO"命名管道"文件的系統如GNU/Linux上,對這種操作有個很好的方法。首先,創建一個命名管道并將解壓縮數據流到它里面。
mkfifo /tmp/backup/default/sakila/payment.fifo
chmod 666 /tmp/backup/default/sakila/payment.fifo
gunzip -c /tmp/backup/default/sakila/payment.txt.gz > /tmp/backup/default/sakila/payment.fifo
注意到我們使用了一個大于號字符(>)來重定向解壓縮輸出到payment.fifo文件中——而不是在不同程序之間創建匿名管道的管道符號。管道會等待,直到其他程序打開它并從另外一段讀取數據。簡單一點說,MySQL服務器可以從管道中讀取解壓縮后的數據,就像其他文件一樣。如果可能,不要忘記盡調二進制日志。
mysql>SET SQL_LOG_BIN = 0; -- Optional> LOAD DATA INFILE 'tmp/backup/defualt/sakila/payment/fifo'> INTO TABLE sakila.payment;
一旦MySQL加載完數據,gunzip就會退出,然后可以刪除該命令管道。在MySQL命令行客戶端使用SOURCE命令加載壓縮的文件也可以使用此技術。Percona Toolkit中的pt-fifo-split程序還可以幫助分塊加載大文件,而不是在單個大事務中操作,這樣效率更高
你無法從這里到達那里
從DATETIME變為TIMESTAMP.以節約空間并使處理過程更快,表定義如下:
CREATE TABLE tbl(
col1 timestamp NOT NULL,
col2 timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTMAP
......
這個表帝國一在MySQL5.0.40版本上導致了一個語法錯誤,而這是創建時的版本。可以執行導出,但無法加載。這很奇怪,諸如這樣無法預料的錯誤也是測試備份重要的原因之一。你永遠不會直到什么會阻止你還原數據
基于時間點的恢復
對MySQL做基于時間點的恢復常見的方法是還原最近一次全備份,然后從那個時間點開始重放二進制日志(有時較"前滾恢復")。只要有二進制日志,就可以恢復到任何希望的時間點。甚至可以不太費力地恢復單個數據庫。主要的缺點是二進制日志重放可能會是一個很慢的過程。它大體上等同于復制。如果有一個備庫,并且已經測量到SQL線程的利用率有多高,那么對重放二進制日志會有多快就會心里有數了。例如,,如果SQL線程約有50%被利用,則恢復一周二進制日志的工作可能在三到四天內完成。一個典型場景是對有害的語句的結果做回滾操作,例如DROP TABLE。讓我們看一個簡化的例子,看只有MyISAM表的情況下該如何做。假如是在半夜,備份任務在運行與下面所列相當的語句,復制數據庫到同一服務器上的其他地方。
mysql> FLUSH TABLES WITH READ LOCK;> server1# cp -a /var/lib/mysql/sakila /backup/sakila;
mysql> FLUSH LOGS;> server1# mysql -e "SHOW MASTER STATUS" --vertical > /backup/master.info
mysql> UNLOCK TABLES;
然后,假設有人在晚些時間運行下列語句.
mysql>USE sakila;
mysql>DROP TABLE sakila.payment;
為了便于說明,我們先假設可以單獨地恢復這個數據庫(即此庫中地表不涉及跨庫查詢)。再假設是直到后來出問題才意識到這個有問題地語句。目標是恢復數據庫中除了有問題地語句之外所有發生地事務。也就是說,其他表已經做的所有修改都必須保持,包括有問題的語句運行之后的修改。這并不是很難做到。首先,停掉MySQL以阻止更多的修改,然后從備份中僅恢復sakila數據庫。
server1# /etc/init.d/mysql stop
server1# mv /var/lib/mysql/sakila /var lib/mysql/sakila.tmp
server1# cp -a /backup/sakila /var/lib/mysql
再到運行的服務器的my.cnf中添加如下配置以禁止正常的連接
skip-networking
socket=/tmp/mysql_recover.sock
現在可以安全地啟動服務器了。
server1# /etc/init.d/mysql start
下一個任務是從二進制日志中分出需要重放和忽略的語句。事發時,自半夜的備份依賴服務器只創建了一個二進制日志。我們可以用grep來檢查二進制日志文件以找到問題語句
server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 | grep -B3 -i 'drop table sakila.payment'
我們可以看到,想忽略的語句在日志文件中的某個位置,下一個語句的位置是多少。可以用下面的命令重放二進制日志直到某個位置,然后從某個位置繼續
server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --stop-position=352 | mysql -uroot -p
server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --start-position=429 | mysql -uroot -p
接下來要做的是檢測數據以確保沒問題,然后關閉服務器并撤銷對my.cnf的改變,最后重啟服務器