MySQL備份和還原操作

目標(biāo)

備份的概念
Mysqldump備份操作
MySQL還原操作
MySQL雙機(jī)熱備份

概述

在數(shù)據(jù)庫表丟失或損壞的情況下，備份數(shù)據(jù)庫是很重要的。如果發(fā)生系統(tǒng)崩潰，能夠?qū)⒈肀M可能丟失最少的數(shù)據(jù)恢復(fù)到崩潰發(fā)生時的狀態(tài)。

備份數(shù)據(jù)庫兩個主要方法是用mysqldump程序或直接拷貝數(shù)據(jù)庫文件

Mysqldump備份

導(dǎo)出庫表（mysqldump）
mysqldump -u用戶名 -p密碼 -h主機(jī) 數(shù)據(jù)庫 a -w “sql條件” –lock-all-tables > 路徑
mysqldump -hhostname -uusername -p dbname tbname>xxxx.sql
** 按指定條件導(dǎo)出數(shù)據(jù)庫表內(nèi)容。(-w選項 –where）
mysqldump -hhostname -uusername-p dbname tbname -w’id >= 1 and id<= 10000’–skip-lock-tables > xxxx.sql
或
mysqldump -hhostname -uusername -p dbname tbname --where=‘unit_id >= 1 and unit_id <= 10000’> ~/xxxx.sql

Mysqldump備份庫表詳細(xì)舉例

導(dǎo)出整個數(shù)據(jù)庫
mysqldump -u 用戶名 -p數(shù)據(jù)庫名 > 導(dǎo)出的文件名
mysqldump -u breezelark-p mydb > mydb.sql

導(dǎo)出一個表（包括數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)）
mysqldump -u 用戶名 -p數(shù)據(jù)庫名 表名> 導(dǎo)出的文件名
mysqldump -u lingxi -p mydb mytb> mytb.sql

導(dǎo)出一個數(shù)據(jù)庫結(jié)構(gòu)（無數(shù)據(jù)只有結(jié)構(gòu)）
mysqldump -u lingxi -p -d --add-drop-table mydb >mydb.sql
-d 沒有數(shù)據(jù)–add-drop-table 在每個create語句之前增加一個drop table

數(shù)據(jù)庫備份

完全備份數(shù)據(jù)庫
增量備份

完全備份數(shù)據(jù)庫

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >fullbackup_sunday_11_PM.sql
在sql文件中我們會看到兩行：
– Position to start replication or point-in-time recovery from
– CHANGE MASTER TO MASTER_LOG_FILE=’bin-log.000002′， MASTER_LOG_POS=107;
第二行包含了我們需要的信息，是指備份后所有的更改將會保存到bin-log.000002二進(jìn)制文件中。

增量 備份

然后在星期一下午11點我們來做一次增量備份：
mysqladmin flush-logs
這時將會產(chǎn)生一個新的二進(jìn)制日志文件bin-log.000003，bin-log.000002則保存了自星期天下午11點到現(xiàn)在的所有更
改，我們只需要把這個文件備份到安全的地方就行了。然后星期二我們又做增量備份，還是執(zhí)行同樣的命令，這時我們
保存bin-log.000003文件。
如果mysqld在運(yùn)行則停止，然后用–log-bin[=file_name]選項來啟動。二進(jìn)制日志文件中提供了執(zhí)行mysqldump之后對數(shù)據(jù)庫的更改進(jìn)行復(fù)制所需要的信息。
對于InnoDB表，可以進(jìn)行在線備份，不需要對表進(jìn)行鎖定
MySQL支持增量備份:需要用–log-bin選項來啟動服務(wù)器以便啟用二進(jìn)制日志“二進(jìn)制日志”。當(dāng)想要進(jìn)行增量備份時(包含上一次完全備份或增量備份之后的所有更改)，應(yīng)使用FLUSH LOGS回滾二進(jìn)制日志。然后，你需要將從最后的完全或增量備份的某個時刻到最后某個點的所有二進(jìn)制日志復(fù)制到備份位置。這些二進(jìn)制日志為增量備份；恢復(fù)時，按照下面的解釋應(yīng)用。下次進(jìn)行完全備份時，還應(yīng)使用FLUSH LOGS–flushlogs回滾二進(jìn)制日志。
“mysqldump：數(shù)據(jù)庫備份程序”，

數(shù)據(jù)庫還原

備份的程序，在出現(xiàn)崩潰后，可以恢復(fù)數(shù)據(jù)：
· 操作系統(tǒng)崩潰
· 電源故障
· 文件系統(tǒng)崩潰
· 硬件問題(硬盤、母板等等)
該命令不包括mysqldump和mysql程序的–user和—password等選項。應(yīng)包括必要的選項讓MySQL服務(wù)器允許你連接它。
我們假定數(shù)據(jù)保存在MySQL的InnoDB存儲引擎中，支持事務(wù)和自動崩潰恢復(fù)。我們假定崩潰時MySQL服務(wù)器帶負(fù)載。如果不帶負(fù)載，則不需要恢復(fù)。
出現(xiàn)操作系統(tǒng)崩潰或電源故障時，我們可以假定重啟后硬盤上的MySQL?數(shù)據(jù)仍可用。由于崩潰，InnoDB數(shù)據(jù)文件中的數(shù)據(jù)可能不再保持一致性，但I(xiàn)nnoDB讀取它的日志并會查到掛起的提交的和未提交的事務(wù)清單，它們沒有清空到數(shù)據(jù)文件中。InnoDB自動卷回未提交的事務(wù)，并清空到它的數(shù)據(jù)文件中。通過MySQL錯誤日志將該恢復(fù)過程相關(guān)信息傳達(dá)給用戶

數(shù)據(jù)恢復(fù)

Mysql -u用戶名 –p密碼 數(shù)據(jù)庫名 < 導(dǎo)出的文件名

恢復(fù)備份

比如星期三中午12點出現(xiàn)了故障，這時需要恢復(fù)，我們首先導(dǎo)入星期天的完整備份：
mysql < fullbackup_sunday_3_AM.sql
接著我們導(dǎo)入星期一和星期二的增量備份：
mysqlbinlog bin-log.000002 bin-log.000003 | mysql
注意： “mysqlbinlog：用于處理二進(jìn)制日志文件的實用工具”

備份策略

對于中等級別業(yè)務(wù)量的系統(tǒng)來說，備份策略可以這么定：第一次全量備份，每天一次增量備份，每周再做一次全量備份，如此一直重復(fù)。而對于重要的且繁忙的系統(tǒng)來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業(yè)務(wù)，實現(xiàn)在線備份，并且能增量備份，最好的辦法就是采用主從復(fù)制機(jī)制(replication)，在 slave 機(jī)器上做備份
出現(xiàn)操作系統(tǒng)崩潰或電源故障時，InnoDB自己可以完成所有數(shù)據(jù)恢復(fù)工作。應(yīng)遵從下面的指導(dǎo)：
· 一定用–log-bin或甚至–log-bin=log_name選項運(yùn)行MySQL服務(wù)器，其中日志文件名位于某個安全媒介上，不同于數(shù)據(jù)目錄所在驅(qū)動器。如果你有這樣的安全媒介，最好進(jìn)行硬盤負(fù)載均衡(這樣能夠提高性能)。
· 定期進(jìn)行完全備份，使用mysqldump命令進(jìn)行在線非塊備份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志進(jìn)行定期增量備份。

備份策略

對于中等級別業(yè)務(wù)量的系統(tǒng)來說，備份策略可以這么定：第一次全量備份，每天一次增量備份，每周再做一次全量備份，如此一直重復(fù)。而對于重要的且繁忙的系統(tǒng)來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業(yè)務(wù)，實現(xiàn)在線備份，并且能增量備份，最好的辦法就是采用主從復(fù)制機(jī)制(replication)，在 slave 機(jī)器上做備份
出現(xiàn)操作系統(tǒng)崩潰或電源故障時，InnoDB自己可以完成所有數(shù)據(jù)恢復(fù)工作。應(yīng)遵從下面的指導(dǎo)：
· 一定用–log-bin或甚至–log-bin=log_name選項運(yùn)行MySQL服務(wù)器，其中日志文件名位于某個安全媒介上，不同于數(shù)據(jù)目錄所在驅(qū)動器。如果你有這樣的安全媒介，最好進(jìn)行硬盤負(fù)載均衡(這樣能夠提高性能)。
· 定期進(jìn)行完全備份，使用mysqldump命令進(jìn)行在線非塊備份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志進(jìn)行定期增量備份。

MySQL雙機(jī)熱備份

雙機(jī)熱備份通過日志文件來傳輸入服務(wù)器上數(shù)據(jù)的變化。主服務(wù)器進(jìn)程在數(shù)據(jù)被更新時觸發(fā)。并將相應(yīng)的日志文件發(fā)送到從服務(wù)器。從服務(wù)器進(jìn)程接收到主服務(wù)器發(fā)送的日志文件，做出相應(yīng)的更改操作

雙機(jī)熱備份的工作原理

主服務(wù)器的配置

找到配置文件my.ini
編輯my.ini文件,定義到mysqld，加入內(nèi)容
Server-id=1
Log-bin=c:/log-bin.log
Binlog-do-db=dbname
創(chuàng)建dbname數(shù)據(jù)庫
進(jìn)入mysql操作，為從服務(wù)器授權(quán)訪問數(shù)據(jù)庫的用戶名和密碼
grant replication slave on . to ‘root’@從服務(wù)器IP identified by ‘密碼’
重啟MySQL服務(wù)器，使更改生效
重新進(jìn)入到MySQL的命令，執(zhí)行命令檢測配置是否生效:
Show master status\G

從服務(wù)器的配置

在從服務(wù)器中創(chuàng)建與主服務(wù)器中相同的數(shù)據(jù)庫
找到從服務(wù)器中的my.ini文件，定位到[mysqld],加入以下內(nèi)容：
Server-id=2
Master-host=主服務(wù)器IP
Master-port=3306(主服務(wù)器端口號)
Master-user=root(主服務(wù)器用戶名)
Master-password=密碼(主服務(wù)器密碼)
Master-connect-retry=60(等待嘗試重新連接的秒數(shù))
重新啟動MySQL服務(wù)器，使配置生效
進(jìn)入到從服務(wù)器MySQL操作,執(zhí)行啟動進(jìn)程
Slave start

到此這篇關(guān)于MySQL備份和還原操作的文章就介紹到這了,更多相關(guān)MySQL備份和還原內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！