MySQL備份和還原操作小結(jié)
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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL自定義序列數(shù)的實(shí)現(xiàn)方式
這篇文章主要介紹了MySQL自定義序列數(shù)的實(shí)現(xiàn)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-12-12教你如何讓spark?sql寫mysql的時(shí)候支持update操作
spark提供了一個(gè)枚舉類,用來(lái)支撐對(duì)接數(shù)據(jù)源的操作模式,本文重點(diǎn)給大家介紹如何讓spark?sql寫mysql的時(shí)候支持update操作,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友參考下吧2022-02-02MySQL Innodb表導(dǎo)致死鎖日志情況分析與歸納
發(fā)現(xiàn)當(dāng)備份表格的sql語(yǔ)句與刪除該表部分?jǐn)?shù)據(jù)的sql語(yǔ)句同時(shí)運(yùn)行時(shí),mysql會(huì)檢測(cè)出死鎖,并打印出日志2012-12-12安裝MySQL后,需要調(diào)整的10個(gè)性能配置項(xiàng)
這篇文章主要介紹了安裝MySQL后,需要調(diào)整的10個(gè)性能配置項(xiàng),幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下2020-12-12mysql myisam 優(yōu)化設(shè)置設(shè)置
mysql myisam 優(yōu)化設(shè)置設(shè)置,需要的朋友可以參考下。2010-03-03MySQL DATEDIFF函數(shù)獲取兩個(gè)日期的時(shí)間間隔的方法
這篇文章主要介紹了MySQL DATEDIFF函數(shù)獲取兩個(gè)日期的時(shí)間間隔的方法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01