下文內(nèi)容主要給大家?guī)鞰ySQL備份方式、策略、恢復(fù)等簡(jiǎn)析，這里所講到的知識(shí)，與書籍略有不同，都是創(chuàng)新互聯(lián)專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來幫助。
成都創(chuàng)新互聯(lián)公司"三網(wǎng)合一"的企業(yè)建站思路。企業(yè)可建設(shè)擁有電腦版、微信版、手機(jī)版的企業(yè)網(wǎng)站。實(shí)現(xiàn)跨屏營(yíng)銷，產(chǎn)品發(fā)布一步更新，電腦網(wǎng)絡(luò)+移動(dòng)網(wǎng)絡(luò)一網(wǎng)打盡，滿足企業(yè)的營(yíng)銷需求！成都創(chuàng)新互聯(lián)公司具備承接各種類型的做網(wǎng)站、成都做網(wǎng)站項(xiàng)目的能力。經(jīng)過十多年的努力的開拓，為不同行業(yè)的企事業(yè)單位提供了優(yōu)質(zhì)的服務(wù)，并獲得了客戶的一致好評(píng)。
rmysql的備份：
誤操作、mysql崩潰、******、軟件故障、硬件故障、升級(jí)數(shù)據(jù)庫(kù)，測(cè)試等都會(huì)造成mysql數(shù)據(jù)的損壞，這時(shí)如果有備份的話還好，沒備份的話就尷尬了。
備份類型：
完全備份：備份整個(gè)數(shù)據(jù)庫(kù)
部分備份：只備份數(shù)據(jù)庫(kù)中的幾張表或庫(kù)
增量備份：相對(duì)于上一次完全備份或增量備份，只備份變化的數(shù)據(jù)集 （可以是二進(jìn)制日志）
差異備份：相對(duì)于上一次完全備份，僅備份變化的數(shù)據(jù)集
備份方式：
物理備份：直接復(fù)制數(shù)據(jù)文件進(jìn)行備份：速度快
缺點(diǎn)：當(dāng)在備份的過程中有用戶通過應(yīng)用程序訪問更新數(shù)據(jù)，這樣就無法備份當(dāng)時(shí)的數(shù)據(jù)，如果數(shù)據(jù)庫(kù)表在文件系統(tǒng)備份過程中被修改，進(jìn)入備份的表文件主語不一致的狀態(tài)，而對(duì)以后的恢復(fù)表將失去意義，備份過的數(shù)據(jù)集還原時(shí)得跟備份之前數(shù)據(jù)庫(kù)用到的存儲(chǔ)引擎一致。
邏輯備份：從數(shù)據(jù)庫(kù)中“導(dǎo)出”數(shù)據(jù)另存而進(jìn)行的備份，速度比較慢
缺點(diǎn)：備份的速度比較慢。如果是數(shù)據(jù)量很多的時(shí)候。就很耗時(shí)間。如果數(shù)據(jù)庫(kù)云服務(wù)器處在提供給用戶服務(wù)狀態(tài)，在這段長(zhǎng)時(shí)間操作過程中，意味著要鎖定表(一般是讀鎖定，只能讀不能寫入數(shù)據(jù))。那么服務(wù)就會(huì)影響的，備份過的數(shù)據(jù)集還原時(shí)無需擔(dān)心存儲(chǔ)引擎的問題。
備份策略:
冷備份： 備份過程中讀寫操作均不可執(zhí)行，好處是能保證數(shù)據(jù)的完整性，不會(huì)出現(xiàn)事務(wù)未提交的請(qǐng)求，壞處是需要mysql停止工作
熱備份： 備份過程中讀寫操作均可執(zhí)行，好處是不需要停止mysql
溫備份： 備份過程中讀操作可執(zhí)行，寫操作不可執(zhí)行。
備份時(shí)需要考慮的問題：
備份時(shí)鎖表需要多久、備份需要多長(zhǎng)、備份時(shí)產(chǎn)生多大的負(fù)載、恢復(fù)時(shí)需要多長(zhǎng)時(shí)間
備份的方案：
對(duì)于數(shù)據(jù)集是完全備份加增量備份，還是完全備份加差異備份
備份的方式：物理備份還是邏輯備份
備份時(shí)的策略：選擇冷備份、溫備份、還是熱備份
備份的工具：
mysqldump：mysql官方自己提供的工具，是一款邏輯備份工具，適用于所有存儲(chǔ)引擎，對(duì)MyISAM引擎支持溫備，不支持熱備，支持完全備份，部分備份，對(duì)InnoDB存儲(chǔ)引擎是支持熱備的
cp，tar等復(fù)制歸檔工具：物理備份工具，對(duì)所有引擎都支持備份
lvm2的快照：幾乎熱備，需要記住與文件系統(tǒng)管理工具，cp、mv等
mysqlhotcopy：冷備工具
xtarbackup：percona提供的快速備份工具，可以熱備、溫備
一、mysqldump實(shí)現(xiàn)數(shù)據(jù)備份和還原
mysqldump是客戶端命令，通過mysql協(xié)議來連接msql數(shù)據(jù)庫(kù)實(shí)現(xiàn)備份， 是采用SQL級(jí)別的備份機(jī)制，它將數(shù)據(jù)表導(dǎo)成 SQL 腳本文件
mysqldump [option] [db_name [tbl_name..]] ]# mysqldump [options] db_name [tbl_name ...] //備份單個(gè)表，還原時(shí)需要手動(dòng)創(chuàng)建數(shù)據(jù)庫(kù) ]# mysqldump [options] --databases db_name . //備份指定數(shù)據(jù)庫(kù)，還原無需創(chuàng)建數(shù)據(jù)庫(kù) ]# mysqldump [options] --all-databases //備份所有數(shù)據(jù)庫(kù)，還原無需創(chuàng)建數(shù)據(jù)庫(kù) [options] --event -E：備份指定數(shù)據(jù)相關(guān)的所有事件 --default-character-set=charset：指定導(dǎo)出數(shù)據(jù)時(shí)采用何種字符集，如果數(shù)據(jù)表不是采用默認(rèn)的 latin1 字符集的話，那么導(dǎo)出時(shí)必須指定該選項(xiàng)，否則再次導(dǎo)入數(shù)據(jù)后將產(chǎn)生亂碼問題 -R，--routines：備份指定數(shù)據(jù)庫(kù)相關(guān)的所有存儲(chǔ)過程和存儲(chǔ)函數(shù) --triggers：備份表相關(guān)的觸發(fā)器 --skip-triggers：跳過備份觸發(fā)器 --lock-all-tables：鎖定所有庫(kù)的所有表 --lock-tables：鎖定指定數(shù)據(jù)庫(kù)的所有表 --no-create-info，-t：只導(dǎo)出數(shù)據(jù)，而不添加 CREATE TABLE 語句 --no-data，-d：不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫(kù)表結(jié)構(gòu)。 --single-transaction：該選項(xiàng)在導(dǎo)出數(shù)據(jù)之前提交一個(gè) BEGIN SQL語句，BEGIN 不會(huì)阻塞任何應(yīng)用程序且能保證導(dǎo)出時(shí)數(shù)據(jù)庫(kù)的一致性狀態(tài)。它只適用于事務(wù)表，例如 InnoDB 和 BDB。 注意：本選項(xiàng)和 --lock-tables 選項(xiàng)是互斥的，因?yàn)?nbsp;LOCK TABLES 會(huì)使任何掛起的事務(wù)隱含提交 --master-data[=value]：記錄二進(jìn)制日志和文件的位置 0：不啟用 1、記錄為CHANGE MASTER TO語句，此語句不被注釋 2、記錄為注釋的CHANGE MASTER TO語句 --flush-logs: 對(duì)二進(jìn)制日志進(jìn)行日志滾動(dòng)
示例：完全備份數(shù)據(jù)庫(kù)，然后恢復(fù)
]# mysqldump -uadmin -padmin --databases hellodb -R --triggers --master-data=2 >/backup/mysqlback.sql ]# service mariadb stop Redirecting to /bin/systemctl stop mariadb.service ]# rm -rf /var/lib/mysql/* //刪除mysql數(shù)據(jù) ]# service mariadb start Redirecting to /bin/systemctl start mariadb.service ]# mysql show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | //直接還原回來 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 還原時(shí)還有一種方式： MariaDB [(none)]> source /backup/mysqlback.sql //兩種還原方式都是mysql客戶端提供的
備份完后，在下次備份之前數(shù)據(jù)庫(kù)崩潰需要還原，那么中間產(chǎn)生數(shù)據(jù)需要通過二進(jìn)制日志根據(jù)時(shí)間點(diǎn)還原。
二進(jìn)制日志： 記錄導(dǎo)致數(shù)據(jù)改變或潛在導(dǎo)致數(shù)據(jù)改變的SQL語句。
重慶分公司
重慶分公司
]# mysqldump -uroot --databases hellodb -R --triggers --master-data=2 >/backup/mysqlbackv2.sql
MariaDB [hellodb]> insert into students (Name,Age) values ("Hello",20);
Query OK, 1 row affected (0.01 sec)
備份完后又做了寫操作，這時(shí)數(shù)據(jù)發(fā)生了改變，需要通過二進(jìn)制日志進(jìn)行重放
]# less /backup/mysqlbackv2.sql
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.44-MariaDB-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=7925; //顯示備份時(shí)二進(jìn)制的文件及二進(jìn)制事件開始的位置
]# mysqlbinlog --start-position=245 /root/master.000003 >/backup/mysqlbin.sql
//把記錄的之后事件的二進(jìn)制日志導(dǎo)成sql語句
]# mysql show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | //數(shù)據(jù)庫(kù)沒問題
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
| 26 | Hello | 20 | F | NULL | NULL | //新添加的數(shù)據(jù)也恢復(fù)了
+-------+---------------+-----+--------+---------+-----------+二、lvm2實(shí)現(xiàn)數(shù)據(jù)的備份與恢復(fù)
創(chuàng)建lvm邏輯卷，把mysql數(shù)據(jù)存放目錄掛載到邏輯卷上
]# pvcreate /dev/sdb1 Physical volume "/dev/sdb1" successfully created ]# vgcreate myvg /dev/sdb1 Volume group "myvg" successfully created ]# lvcreate -L 1G -n mysql_lvm /dev/m mapper/ mcelog mem midi mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/m mapper/ mcelog mem midi mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/myvg Logical volume "mysql_lvm" created. ]# mke2fs -t ext4 /dev/myvg/mysql_lvm mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 65536 inodes, 262144 blocks 13107 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=268435456 8 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376 Allocating group tables: done Writing inode tables: done Creating journal (8192 blocks): done Writing superblocks and filesystem accounting information: done ]# mkdir /data/mysql ]# mount /dev/myvg/mysql_lvm /data/mysql/ ]# chown -R mysql.mysql /data/mysql/ ]# service mariadb start //啟動(dòng)mysql報(bào)錯(cuò) Redirecting to /bin/systemctl start mariadb.service Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details. [root@localhost /]# tail -f /var/log/mariadb/ tail: error reading ‘/var/log/mariadb/’: Is a directory tail: /var/log/mariadb/: cannot follow end of this type of file; giving up on this name tail: no files remaining [root@localhost /]# tail -f /var/log/mariadb/mariadb.log 160609 18:14:55 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended 160609 18:18:21 mysqld_safe Starting mysqld daemon with databases from /data/mysql 160609 18:18:21 [Note] /usr/libexec/mysqld (mysqld 5.5.44-MariaDB-log) starting as process 5585 ... 160609 18:18:21 [Warning] Can't create test file /data/mysql/localhost.lower-test 160609 18:18:21 [ERROR] mysqld: File './master-bin.index' not found (Errcode: 13) 發(fā)生這個(gè)原因可能是/data/mysql這個(gè)目錄的問題，但我上面已經(jīng)設(shè)置好了還報(bào)錯(cuò) ]# getenforce //selinux的問題，關(guān)閉就行 Enforcing ]# setenforce 0 ]# service mariadb start Redirecting to /bin/systemctl start mariadb.service
備份數(shù)據(jù)庫(kù)：
MariaDB [hellodb]> flush tables with read lock; //先鎖表 MariaDB [hellodb]> flsuh logs; //滾動(dòng)二進(jìn)制日志 ]# mysql -e "show master status;" >/root/mysqlbin.date+ "%F" //記錄二進(jìn)制日志文件及位置 ]# lvcreate -L 500M -s -p r -n mysql_lvm_snap /dev/myvg/mysql_lvm //快照 Logical volume "mysql_lvm_snap" created. ]# mount /dev/myvg/mysql_lvm_snap /backup/ ]# cp -r * /data/mysqlback/ //數(shù)據(jù)備份成功
恢復(fù)數(shù)據(jù)：
]# cp -r /data/mysqlback/* /data/mysql/ ]# chown -R mysql.mysql ./* ]# service mariadb start MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | //數(shù)據(jù)沒問題 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) 然后根據(jù)二進(jìn)制日志恢復(fù)完全備份之后丟失的數(shù)據(jù)就行
三、xtrabackup實(shí)現(xiàn)數(shù)據(jù)備份和恢復(fù)
Xtrabackup是由percona提供的mysql數(shù)據(jù)庫(kù)備份工具，據(jù)官方介紹，這也是世界上惟一一款開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫(kù)進(jìn)行熱備的工具。特點(diǎn)：
(1)備份過程快速、可靠；
(2)備份過程不會(huì)打斷正在執(zhí)行的事務(wù)；
(3)能夠基于壓縮等功能節(jié)約磁盤空間和流量；
(4)自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)；
(5)還原速度快；
]# ls percona-xtrabackup-2.3.2-1.el7.x86_64.rpm ]# yum -y install *.rpm
xtrabackup完全備份：
]# innobackupex --user=root /backup/ //如果數(shù)據(jù)庫(kù)有密碼則加上--password 160609 19:08:43 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ................. 160609 19:08:55 Executing UNLOCK TABLES 160609 19:08:55 All tables unlocked 160609 19:08:55 Backup created in directory '/data/mysql//2016-06-09_19-08-43' MySQL binlog position: filename 'master-bin.000005', position '245' 160609 19:08:55 [00] Writing backup-my.cnf 160609 19:08:55 [00] ...done 160609 19:08:56 [00] Writing xtrabackup_info 160609 19:08:56 [00] ...done xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied. 160609 19:08:56 completed OK!
數(shù)據(jù)還原：
還原準(zhǔn)備： 在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處理不一致狀態(tài)。“準(zhǔn)備”的主要作用正是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。 ]# innobackupex --apply-log /backup/2016-06-09_19-16-30/ InnoDB: Setting log file ./ib_logfile101 size to 48 MB InnoDB: Setting log file ./ib_logfile1 size to 48 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 //事務(wù)合并時(shí)innobackupex會(huì)把事務(wù)日志文件設(shè)置為48M，事務(wù)日志默認(rèn)為5M，這樣會(huì)導(dǎo)致mariadb啟動(dòng)不了 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1598486 160609 19:17:40 completed OK! 刪除數(shù)據(jù)集，恢復(fù) ]# innobackupex --copy-back /backup/2016-06-09_19-16-30/ 160609 19:22:27 [01] ...done 160609 19:22:28 [01] Copying ./performance_schema/threads.frm to /data/mysql/performance_schema/threads.frm 160609 19:22:28 [01] ...done 160609 19:22:28 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info 160609 19:22:28 [01] ...done 160609 19:22:28 completed OK! ]# chown -R mysql.mysql ./* ]# service mariadb start MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
xtrabackup進(jìn)行增量備份:
插入數(shù)據(jù)：
MariaDB [hellodb]> insert into students (Name,Age) values ("Hello",21);
Query OK, 1 row affected (0.01 sec)
]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-06-09_19-16-30/
//basedir后面跟最近一次完全備份的目錄，如果上次備份的增量備份，則指向上一次增量備份注意：恢復(fù)時(shí)mysql是不能啟動(dòng)的，增量備份僅能應(yīng)用于InnoDB或XtraDB表，對(duì)于MyISAM表而言，執(zhí)行增量備份時(shí)其實(shí)進(jìn)行的是完全備份。
增量備份的整合事務(wù)時(shí)和完全備份有一些不同，需要注意：
1、需要在每個(gè)備份上（增量備份和完全備份），將已提交的事務(wù)進(jìn)行“重放”，“重放”之后，所有的備份數(shù)據(jù)都合并到完全備份上
2、合并完后在基于所有的備份上將有些還未提交的事務(wù)進(jìn)行“回滾”
為什么不能把增量備份上未提交的事務(wù)進(jìn)行“回滾”？
因?yàn)檫@次增量備份上事務(wù)未提交就備份完了，在下次增量備份上事務(wù)就可能已經(jīng)提交了，所有不能進(jìn)行事務(wù)回滾，只有把所有的備份的事務(wù)都“重放”完后，在基于所有備份上把所有的未完成事務(wù)進(jìn)行“回滾”。
]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38 //先完全備份事務(wù)整合 ]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38 --incremental=dir=/backup/2016-06-09_19-57-43/ //第一次增量備份，如果有多個(gè)，只把incremental后面的目錄改成第二次增量備份 ]# rm -rf /data/mysql/* ]# innobackupex --copy-back /backup/2016-06-09_19-55-38/ ]# service mariadb start MariaDB [hellodb]> select * from students; | 26 | Hello | 21 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 26 rows in set (0.01 sec)
每個(gè)備份目錄下都有些xtarbackup文件：
]# cd /backup/2016-06-09_19-55-38/ [root@localhost 2016-06-09_19-55-38]# ls xtrabackup_binlog_info ：mysql服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二 進(jìn)制日志事件的位置 xtrabackup_checkpoints ：備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息，增量備份就是通過查看LSN發(fā)生改變的內(nèi)容去備份修改的數(shù)據(jù) xtrabackup_logfile ：xtrabackup備份是日志 xtrabackup_binlog_pos_innodb ：二進(jìn)制日志文件及用于InnoDB或XtraDB表的二進(jìn)制日志文件的當(dāng)前position。xtrabackup_info ：xtrabackup備份數(shù)據(jù)庫(kù)的各種信息
對(duì)于以上關(guān)于mysql備份方式、策略、恢復(fù)等簡(jiǎn)析，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們創(chuàng)新互聯(lián)的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R(shí)更新。
