MySQL備份和還原操作

目標

備份的概念
Mysqldump備份操作
MySQL還原操作
MySQL雙機熱備份

概述

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

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

Mysqldump備份

導出庫表（mysqldump）
mysqldump -u用戶名 -p密碼 -h主機 數(shù)據(jù)庫 a -w “sql條件” –lock-all-tables > 路徑
mysqldump -hhostname -uusername -p dbname tbname>xxxx.sql
** 按指定條件導出數(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備份庫表詳細舉例

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

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

導出一個數(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二進制文件中。

增量 備份

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

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

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

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

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

恢復備份

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

備份策略

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

備份策略

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

MySQL雙機熱備份

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

雙機熱備份的工作原理

主服務器的配置

找到配置文件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ù)庫
進入mysql操作，為從服務器授權訪問數(shù)據(jù)庫的用戶名和密碼
grant replication slave on . to ‘root’@從服務器IP identified by ‘密碼’
重啟MySQL服務器，使更改生效
重新進入到MySQL的命令，執(zhí)行命令檢測配置是否生效:
Show master status\G

從服務器的配置

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

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

最新評論