MySQL備份和還原操作

目標(biāo)

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

概述

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

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

Mysqldump備份

導(dǎo)出庫(kù)表（mysqldump）
mysqldump -u用戶名 -p密碼 -h主機(jī) 數(shù)據(jù)庫(kù) a -w “sql條件” –lock-all-tables > 路徑
mysqldump -hhostname -uusername -p dbname tbname>xxxx.sql
** 按指定條件導(dǎo)出數(shù)據(jù)庫(kù)表內(nèi)容。(-w選項(xiàng) –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備份庫(kù)表詳細(xì)舉例

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

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

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

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

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

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

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >fullbackup_sunday_11_PM.sql
在sql文件中我們會(huì)看到兩行：
– Position to start replication or point-in-time recovery from
– CHANGE MASTER TO MASTER_LOG_FILE=’bin-log.000002′， MASTER_LOG_POS=107;
第二行包含了我們需要的信息，是指?jìng)浞莺笏械母膶?huì)保存到bin-log.000002二進(jìn)制文件中。

增量 備份

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

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

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

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

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

恢復(fù)備份

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

備份策略

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

備份策略

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

MySQL雙機(jī)熱備份

雙機(jī)熱備份通過(guò)日志文件來(lái)傳輸入服務(wù)器上數(shù)據(jù)的變化。主服務(wù)器進(jìn)程在數(shù)據(jù)被更新時(shí)觸發(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ù)庫(kù)
進(jìn)入mysql操作，為從服務(wù)器授權(quán)訪問數(shù)據(jù)庫(kù)的用戶名和密碼
grant replication slave on . to ‘root’@從服務(wù)器IP identified by ‘密碼’
重啟MySQL服務(wù)器，使更改生效
重新進(jìn)入到MySQL的命令，執(zhí)行命令檢測(cè)配置是否生效:
Show master status\G

從服務(wù)器的配置

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

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

最新評(píng)論