備份對(duì)于數(shù)據(jù)庫來說是相當(dāng)重要的工作。如果數(shù)據(jù)庫在使用過程中出現(xiàn)了問題，比如系統(tǒng)崩潰、硬件故障或錯(cuò)誤的刪除了數(shù)據(jù)。這時(shí)，如果我們進(jìn)行了數(shù)據(jù)備份，就能比較方便的使數(shù)據(jù)庫恢復(fù)工作，并使我們的數(shù)據(jù)損失到最
備份對(duì)于數(shù)據(jù)庫來說是相當(dāng)重要的工作。如果數(shù)據(jù)庫在使用過程中出現(xiàn)了問題，比如系統(tǒng)崩潰、硬件故障或錯(cuò)誤的刪除了數(shù)據(jù)。這時(shí)，如果我們進(jìn)行了數(shù)據(jù)備份，就能比較方便的使數(shù)據(jù)庫恢復(fù)工作，并使我們的數(shù)據(jù)損失到最小。下面，我從備份類型、備份方法及一些常用的例子來和大家詳細(xì)探討下數(shù)據(jù)庫的備份與恢復(fù)。
一、備份類別
1、物理備份與邏輯備份
物理備份用人話來形容就是復(fù)制數(shù)據(jù)庫的數(shù)據(jù)文件。如果我們需要備份名為test的數(shù)據(jù)庫，則我們可以將數(shù)據(jù)目錄下的test目錄復(fù)制到備份設(shè)備中。如果我們需要備份test庫下名為user的表，則可以根據(jù)不同的存儲(chǔ)引擎選取不同的數(shù)據(jù)文件來復(fù)制。如user是myisam，則可以復(fù)制user.frm，user.MYD，user.MYI文件。如user是innodb，則可以復(fù)制user.frm，user.ibd。其中.frm是包含數(shù)據(jù)結(jié)構(gòu)的文件。.MYD是myisam引擎中包含具體數(shù)據(jù)的文件。.MYI是myisam引擎中包含索引內(nèi)容的文件。.ibd是innodb引擎中包含具體數(shù)據(jù)的文件。
邏輯備份是將數(shù)據(jù)庫的結(jié)構(gòu)對(duì)象及數(shù)據(jù)對(duì)象（所有的事件、觸發(fā)器、存儲(chǔ)過程等）轉(zhuǎn)化為SQL語句后再保存到備份文件中。所以邏輯備份與具體的操作平臺(tái)無關(guān)。
物理備份的特征：
1、它是直接復(fù)制二進(jìn)制數(shù)據(jù)文件的，所以我們?cè)诨謴?fù)數(shù)據(jù)時(shí)只能原樣恢復(fù)，不能自定義修改數(shù)據(jù)文件再恢復(fù)。
2、物理備份比邏輯備份速度更快，因?yàn)闆]有轉(zhuǎn)化SQL這一過程。
3、物理備份不支持細(xì)粒度的備份，如果你只是想備份某個(gè)表中的某一范圍的行，可能會(huì)難以辦到。
4、物理備份可能會(huì)丟失最新寫入到數(shù)據(jù)庫的數(shù)據(jù)。因?yàn)樵谖锢韨浞輹r(shí)，可能會(huì)有新增的數(shù)據(jù)還駐留在內(nèi)存中，沒有寫入硬盤。
5、物理備份對(duì)平臺(tái)有一定的要求，如果兩個(gè)操作系統(tǒng)的文件系統(tǒng)不一樣，有可能會(huì)不能恢復(fù)數(shù)據(jù)。所以要求盡可能在相同的操作系統(tǒng)及文件系統(tǒng)上實(shí)現(xiàn)數(shù)據(jù)的備份與恢復(fù)。
6、最好在服務(wù)器停止運(yùn)行時(shí)執(zhí)行物理備份，否則可能需要正確的加鎖策略來確保數(shù)據(jù)一致。
邏輯備份的特征：
1、通過將需備份的數(shù)據(jù)轉(zhuǎn)換成SQL來完成備份，所以在恢復(fù)數(shù)據(jù)時(shí)，我們可以通過修改SQL的方式來自定義恢復(fù)的內(nèi)容，有更大的靈活性。
2、因?yàn)樘卣?的原因，所以在速度方面會(huì)比物理備份慢，因此不太適合大量數(shù)據(jù)的備份。
3、備份力度方面比物理備份更細(xì)，可以到行級(jí)。
4、不依賴具體的操作系統(tǒng)平臺(tái)。
5、備份時(shí)無需停止服務(wù)器。
2、在線備份與離線備份
在線備份是通過遠(yuǎn)程客戶端連到服務(wù)器進(jìn)行備份，如在客戶端使用導(dǎo)出工具導(dǎo)出SQL備份文件。在備份時(shí)應(yīng)該在SQL上加合適的鎖防止數(shù)據(jù)不一致的情況。比如寫數(shù)據(jù)時(shí)應(yīng)加寫鎖以防止導(dǎo)出數(shù)據(jù)集時(shí)發(fā)生數(shù)據(jù)不一致。
離線備份是在服務(wù)器停運(yùn)的狀態(tài)下進(jìn)行備份，因?yàn)榇藭r(shí)不會(huì)有數(shù)據(jù)操作，所以它在實(shí)現(xiàn)上更簡單。
3、完全備份與增量備份
完全備份就是備份到目前為止的所有數(shù)據(jù)。增量備份只備份自上一次完全備份或增量備份到至今，期間有所變化的數(shù)據(jù)。實(shí)現(xiàn)完全備份的方法有很多，如使用邏輯備份或者物理備份都可以實(shí)現(xiàn)完全備份。完全備份在恢復(fù)時(shí)是最方便的，但在備份時(shí)通常需要耗費(fèi)很長的時(shí)間，所以如果數(shù)據(jù)庫的數(shù)據(jù)量非常大，每天都執(zhí)行完全備份是不現(xiàn)實(shí)的。應(yīng)采用完全備份與增量備份相結(jié)合的策略。如在每個(gè)星期天執(zhí)行一次完全備份，禮拜一到禮拜五實(shí)行增量備份。增量備份的特性與完全備份剛好相反，在備份時(shí)會(huì)非常的快捷方便，但在還原時(shí)會(huì)比較繁瑣。而且要實(shí)現(xiàn)增量備份，我們必須在服務(wù)器運(yùn)行時(shí)開啟二進(jìn)制日志。這樣我們?cè)诨謴?fù)時(shí)才能根據(jù)二進(jìn)制日志來進(jìn)行實(shí)時(shí)恢復(fù)。
二、備份方法
物理備份方法通常是用專門的MySQL備份工具或操作系統(tǒng)的相關(guān)指令進(jìn)行的，這里強(qiáng)調(diào)一點(diǎn)，就是在進(jìn)行物理復(fù)制的時(shí)候，在執(zhí)行復(fù)制之前應(yīng)給需要備份的表加讀鎖，并且需要將駐留在內(nèi)存的索引信息寫入I/O，具體命令為FLUSH TABLES WITH READ LOCK。下面我主要介紹下MySQL普通版本下的備份方法。
使用mysqldump實(shí)現(xiàn)數(shù)據(jù)庫的邏輯備份。
mysqldump命令在mysql安裝目錄的bin目錄下。主要作用是導(dǎo)出SQL文件。具體語法格式如下：
mysqldump [options] db_name [tbl_name ...] mysqldump [options] --databases db_name ... mysqldump [options] --all-databases
常用的可選參數(shù)如下：
| 參數(shù) | 描述 | 適用 | 廢棄 |
|---|---|---|---|
| --add-drop-database | 在每個(gè) CREATE DATABASE 聲明前加上DROP DATABASE 聲明 | ||
| --add-drop-table | 在每個(gè) CREATE TABLE 聲明前加上DROP TABLE 聲明 | ||
| --add-drop-trigger | 在每個(gè) CREATE TRIGGER 聲明前加上DROP TRIGGER 聲明 | ||
| --add-locks | 在備份時(shí)所表 | ||
| --all-databases | 備份所有數(shù)據(jù)庫里的所有表 | ||
| --allow-keywords | 將表名作為列名的前綴 | ||
| --apply-slave-statements | 將STOP SLAVE放在CHANGE MASTER聲明前，并將START SLAVE放在結(jié)尾 | ||
| --comments | 增加注釋到備份文件 | ||
| --compact | 生成更加緊湊的輸出 | ||
| --compatible=name[,name,...] | 生成更加兼容的格式 | ||
| --complete-insert | 使用完整的 INSERT 聲明 | ||
| --create-options | 在CREATE TABLE聲明里包含所有的MySQL指定選項(xiàng) | ||
| --databases | 備份指定的數(shù)據(jù)庫 | ||
| --delete-master-logs | 在執(zhí)行dump操作后刪除master節(jié)點(diǎn)的binlog | ||
| --disable-keys | 對(duì)于每一個(gè)表的INSERT聲明前加上/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */，這樣可以更快的載入dump文件，但僅僅對(duì)具有非唯一索引的MyISAM表有效。 | ||
| --dump-date | 包含dump時(shí)間 | ||
| --extended-insert | 使用批量插入語法 | ||
| --flush-logs | 在開始導(dǎo)出前刷新MYSQL server日志文件 | ||
| --flush-privileges | 在導(dǎo)出后刷新權(quán)限 | ||
| --hex-blob | 使用16進(jìn)制導(dǎo)出二進(jìn)制列 | ||
| --ignore-error=error[,error]... | 跳過指定的錯(cuò)誤 | 5.7.1 | |
| --ignore-table=db_name.tbl_name | 不導(dǎo)出指定的表 | ||
| --insert-ignore | 寫INSERT IGNORE 聲明，而不是 INSERT 聲明 | ||
| --lock-all-tables | 鎖所有的數(shù)據(jù)庫表 | ||
| --lock-tables | 導(dǎo)出前所數(shù)據(jù)庫內(nèi)的表 | ||
| --login-path=name | 同mysql命令 | ||
| --max_allowed_packet=value | 同mysql命令 | ||
| --net_buffer_length=value | 同mysql命令 | ||
| --no-autocommit | 添加 SET autocommit = 0 和COMMIT 聲明在每個(gè) INSERT 聲明前 | ||
| --no-create-db | 不創(chuàng)建 CREATE DATABASE 聲明 | ||
| --no-data | 不導(dǎo)出表的數(shù)據(jù) | ||
| --no-defaults | 同mysql命令 | ||
| --no-set-names | 不設(shè)置字符集 | ||
| --no-tablespaces | 不寫任何 CREATE LOGFILE GROUP 或 CREATE TABLESPACE 聲明 | ||
| --opt | --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset的縮寫 | ||
| --order-by-primary | 按逐漸或唯一索引排序?qū)С鰯?shù)據(jù)行 | ||
| --quote-names | 添加引用標(biāo)識(shí) | ||
| --replace | 用 REPLACE 聲明代替 INSERT 聲明 | ||
| --result-file=file | 輸出文件到指定位置 | ||
| --secure-auth | 同mysql命令 | 5.7.4 | 5.7.5 |
| --set-charset | 設(shè)置字符集 | ||
| --set-gtid-purged=value | 增加 SET @@GLOBAL.GTID_PURGED | ||
| --shared-memory-base-name=name | 同mysql命令 | ||
| --single-transaction | 在導(dǎo)出前寫 BEGIN SQL 聲明 | ||
| --skip-add-drop-table | add-drop-table 反向操作 | ||
| --skip-add-locks | add-locks 反向操作 | ||
| --skip-comments | comments 反向操作 | ||
| --skip-compact | compact 反向操作 | ||
| --skip-disable-keys | disable-keys 反向操作 | ||
| --skip-extended-insert | extended-insert 反向操作 | ||
| --skip-opt | 關(guān)閉 --opt 選項(xiàng) | ||
| --skip-quick | quick 反向操作 | ||
| --skip-quote-names | quote-names 反向操作 | ||
| --skip-set-charset | 不設(shè)置字符集 | ||
| --skip-triggers | 不導(dǎo)出觸發(fā)器 | ||
| --skip-tz-utc | 關(guān)閉 tz-utc 選項(xiàng) | ||
| --tab=path | 生成一個(gè)用tab隔開的數(shù)據(jù)文件。 | ||
| --tables | 覆蓋 --databases 選項(xiàng) | ||
| --triggers | 導(dǎo)出觸發(fā)器 | ||
| --tz-utc | 針對(duì)列中的TIMESTAMP字段，增加SET TIME_ZONE='+00:00'到導(dǎo)出文件中 | ||
| --where='where_condition' | 導(dǎo)出符合where條件的行 | ||
| --xml | 生成XML輸出 |
以下是一個(gè)簡單的備份例子，如下所示：
/usr/local/mysql/bin/mysql -uroot -p123456 \ -h127.0.0.1 -e"flush tables with read lock” \ #將駐留在內(nèi)存中的數(shù)據(jù)寫入表中并給所有表添加讀鎖
/usr/local/mysql/bin/mysqldump -uroot -p123456 \ -h127.0.0.1 --databases test --add-drop-database \ --add-drop-table --extended-insert --create-options > \ /usr/local/mysql/backup/backup.sql #備份test庫到backup.sql文件。加了—databases后，會(huì)在備份文件里 生成創(chuàng)建數(shù)據(jù)庫CREATE DATABASE test;的聲明。否則，如果直接使用 mysqldump test > backup.sql命令則不會(huì)生成create database語句。 具體參數(shù)的運(yùn)用可以參考上表，如果數(shù)據(jù)較大，我們可以使用—opt選項(xiàng)進(jìn)行備份。
/usr/local/mysql/bin/mysql -uroot -p123456 \ -h127.0.0.1 -e"unlock tables” #解除讀鎖
三、數(shù)據(jù)恢復(fù)方法
1、mysqldump恢復(fù)法
使用mysqldump備份的數(shù)據(jù)，通常在一個(gè).sql文件中。要恢復(fù)數(shù)據(jù)，只用利用mysql客戶端執(zhí)行備份文件即可。如：
/usr/local/mysql/bin/mysql -uroot -p123456 \ -h127.0.0.1 < /usr/local/mysql/backup/backup.sql
即可
2、二進(jìn)制日志恢復(fù)法
使用此方法的前提是你的服務(wù)器是以—log-bin參數(shù)運(yùn)行的，這樣服務(wù)器才會(huì)生成二進(jìn)制日志。使用二進(jìn)制日志恢復(fù)法需使用mysqlbinlog命令，此命令也在mysql安裝目錄的bin目錄下?；謴?fù)命令如下：
/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \ | /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
諸如此類xxx-bin.000001的格式均為二進(jìn)制日志文件。xxx-bin.000001里是以二進(jìn)制存儲(chǔ)的對(duì)數(shù)據(jù)庫的所有操作，所以此命令執(zhí)行后會(huì)將歷史上對(duì)數(shù)據(jù)庫的操作進(jìn)行重做，以此達(dá)到恢復(fù)數(shù)據(jù)的目的。值得注意的是，如果你有多個(gè)二進(jìn)制日志文件，如有xxx-bin.000001和xxx-bin.000002這兩個(gè)文件，如果按以下方式進(jìn)行恢復(fù)將是不安全的：
/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \ | /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1 /usr/local/mysql/bin/mysqlbinlog xxx-bin.000002 \ | /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
這樣利用兩次客戶端連線去恢復(fù)數(shù)據(jù)是不安全的，如果在xxx-bin.000001內(nèi)有一個(gè)生成臨時(shí)表的聲明，而在xxx-bin.000002內(nèi)需要使用這個(gè)臨時(shí)表，則這樣操作將出現(xiàn)問題，執(zhí)行mysqlbinlog xxx-bin.000002時(shí)將會(huì)找不到這個(gè)臨時(shí)表。所以正確的做法應(yīng)該是：
/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 xxx-bin.000002 \ | /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
mysqlbinlog恢復(fù)數(shù)據(jù)的方法很靈活，可以通過時(shí)間點(diǎn)與位置點(diǎn)進(jìn)行恢復(fù)如：
/usr/local/mysql/bin/mysqlbinlog —start-datetime=‘2014-01-20 8:00:00’\ —stop-datetime=‘2014-01-22 19:00:00' xxx-bin.000001\ | /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1 #表示重做xxx-bin.000001文件里從2014-01-20 8:00:00到 2014-01-22 19:00:00這個(gè)時(shí)間范圍內(nèi)的操作。
/usr/local/mysql/bin/mysqlbinlog --start-position=368315\ xxx-bin.000001 | /usr/local/mysql/bin/mysql\ -uroot -p123456 -h127.0.0.1 #表示重做xxx-bin.000001文件內(nèi)從位置368315開始直到文件末尾的操作。
關(guān)于備份與恢復(fù)，一般采用完全備份和增量備份結(jié)合的方法。這樣可以在保證備份效率的基礎(chǔ)上達(dá)到實(shí)時(shí)恢復(fù)要求。比如讓服務(wù)器以—log-bin參數(shù)運(yùn)行，然后每個(gè)禮拜天進(jìn)行一次mysqldump。這樣，比如2014年12月7號(hào)23點(diǎn)59分完成的備份，2014年12月9日7點(diǎn)需要進(jìn)行恢復(fù)。則首先運(yùn)行mysql < backup.2014-12-7.sql，恢復(fù)到2014年12月7日23點(diǎn)59分前的數(shù)據(jù)。然后利用mysqlbinlog hostname-bin.000001 —start-datetime=‘2014-12-8 00:00:00’ | mysql 命令恢復(fù)之后的數(shù)據(jù)。
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2023 51dongshi.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)
