溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
大綱
一、MySQL備份類型
二、MySQL備份都備份什么？
三、MySQL備份工具
四、MySQL備份策略
五、備份準(zhǔn)備工作
六、備份策略具體演示
注：系統(tǒng)版本 CentOS6.4 X86_64，MySQL版本 MySQL 5.5.32，相關(guān)軟件下載 http://yunpan.cn/QnymShsCMzGg9
一、MySQL備份類型
1.熱備份、溫備份、冷備份 （根據(jù)服務(wù)器狀態(tài)）
· 熱備份：讀、寫不受影響；
· 溫備份：僅可以執(zhí)行讀操作；
· 冷備份：離線備份；讀、寫操作均中止；
2.物理備份與邏輯備份 （從對(duì)象來分）
· 物理備份：復(fù)制數(shù)據(jù)文件；
· 邏輯備份：將數(shù)據(jù)導(dǎo)出至文本文件中；
3.完全備份、增量備份、差異備份 （從數(shù)據(jù)收集來分）
· 完全備份：備份全部數(shù)據(jù)；
· 增量備份：僅備份上次完全備份或增量備份以后變化的數(shù)據(jù)；
· 差異備份：僅備份上次完全備份以來變化的數(shù)據(jù)；
4.邏輯備份的優(yōu)點(diǎn)：
· 在備份速度上兩種備份要取決于不同的存儲(chǔ)引擎
· 物理備份的還原速度非?？?。但是物理備份的最小力度只能做到表
· 邏輯備份保存的結(jié)構(gòu)通常都是純ASCII的，所以我們可以使用文本處理工具來處理
· 邏輯備份有非常強(qiáng)的兼容性，而物理備份則對(duì)版本要求非常高
· 邏輯備份也對(duì)保持?jǐn)?shù)據(jù)的安全性有保證
5.邏輯備份的缺點(diǎn)：
· 邏輯備份要對(duì)RDBMS產(chǎn)生額外的壓力，而裸備份無壓力
· 邏輯備份的結(jié)果可能要比源文件更大。所以很多人都對(duì)備份的內(nèi)容進(jìn)行壓縮
· 邏輯備份可能會(huì)丟失浮點(diǎn)數(shù)的精度信息
6.增量備份與差異備份區(qū)別
說明，差異備份要比增量備份占用的空間大，但恢復(fù)時(shí)比較方便！但我們一般都用增量備份！
二、MySQL備份都備份什么？
我們備份，一般備份以下幾個(gè)部分：
1.數(shù)據(jù)文件
2.日志文件（比如事務(wù)日志，二進(jìn)制日志）
3.存儲(chǔ)過程，存儲(chǔ)函數(shù)，觸發(fā)器
4.配置文件（十分重要，各個(gè)配置文件都要備份）
5.用于實(shí)現(xiàn)數(shù)據(jù)庫(kù)備份的腳本，數(shù)據(jù)庫(kù)自身清理的Croutab等……
三、MySQL備份工具
如下圖，
上面的所有備份工具對(duì)比，下面我們就來說一下，常用的備份工具，
1.Mysql自帶的備份工具
· mysqldump 邏輯備份工具，支持所有引擎，MyISAM引擎是溫備，InnoDB引擎是熱備，備份速度中速，還原速度非常非常慢，但是在實(shí)現(xiàn)還原的時(shí)候，具有很大的操作余地。具有很好的彈性。
· mysqlhotcopy 物理備份工具，但只支持MyISAM引擎，基本上屬于冷備的范疇，物理備份，速度比較快。
2.文件系統(tǒng)備份工具
· cp 冷備份，支持所有引擎，復(fù)制命令，只能實(shí)現(xiàn)冷備，物理備份。使用歸檔工具，cp命令，對(duì)其進(jìn)行備份的，備份速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統(tǒng)，但是跨平臺(tái)能力很差。
· lvm 幾乎是熱備份，支持所有引擎，基于快照(LVM，ZFS)的物理備份，速度非常快，幾乎是熱備。只影響數(shù)據(jù)幾秒鐘而已。但是創(chuàng)建快照的過程本身就影響到了數(shù)據(jù)庫(kù)在線的使用，所以備份速度比較快，恢復(fù)速度比較快，沒有什么彈性空間，而且LVM的限制：不能對(duì)多個(gè)邏輯卷同一時(shí)間進(jìn)行備份，所以數(shù)據(jù)文件和事務(wù)日志等各種文件必須放在同一個(gè)LVM上。而ZFS則非常好的可以在多邏輯卷之間備份。
3.其它工具
· ibbackup 商業(yè)工具 MyISAM是溫備份，InnoDB是熱備份 ，備份和還原速度都很快，這個(gè)軟件它的每服務(wù)器授權(quán)版本是5000美元。
· xtrabackup 開源工具 MyISAM是溫備份，InnoDB是熱備份 ，是ibbackup商業(yè)工具的替代工具。
四、MySQL備份策略
1.策略一：直接拷貝數(shù)據(jù)庫(kù)文件（文件系統(tǒng)備份工具 cp）（適合小型數(shù)據(jù)庫(kù)，是最可靠的）
當(dāng)你使用直接備份方法時(shí)，必須保證表不在被使用。如果服務(wù)器在你正在拷貝一個(gè)表時(shí)改變它，拷貝就失去意義。保證你的拷貝完整性的最好方法是關(guān)閉服務(wù)器，拷貝文件，然后重啟服務(wù)器。如果你不想關(guān)閉服務(wù)器，要在執(zhí)行表檢查的同時(shí)鎖定服務(wù)器。如果服務(wù)器在運(yùn)行，相同的制約也適用于拷貝文件，而且你應(yīng)該使用相同的鎖定協(xié)議讓服務(wù)器“安靜下來”。當(dāng)你完成了備份時(shí)，需要重啟服務(wù)器(如果關(guān)閉了它)或釋放加在表上的鎖定(如果你讓服務(wù)器運(yùn)行)。要用直接拷貝文件把一個(gè)數(shù)據(jù)庫(kù)從一臺(tái)機(jī)器拷貝到另一臺(tái)機(jī)器上，只是將文件拷貝到另一臺(tái)服務(wù)器主機(jī)的適當(dāng)數(shù)據(jù)目錄下即可。要確保文件是MyIASM格式或兩臺(tái)機(jī)器有相同的硬件結(jié)構(gòu)，否則你的數(shù)據(jù)庫(kù)在另一臺(tái)主機(jī)上有奇怪的內(nèi)容。你也應(yīng)該保證在另一臺(tái)機(jī)器上的服務(wù)器在你正在安裝數(shù)據(jù)庫(kù)表時(shí)不訪問它們。
2.策略二：mysqldump備份數(shù)據(jù)庫(kù)（完全備份+增加備份，速度相對(duì)較慢，適合中小型數(shù)據(jù)庫(kù)）（MyISAM是溫備份，InnoDB是熱備份）
mysqldump 是采用SQL級(jí)別的備份機(jī)制，它將數(shù)據(jù)表導(dǎo)成 SQL 腳本文件，在不同的 MySQL 版本之間升級(jí)時(shí)相對(duì)比較合適，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。對(duì)于中等級(jí)別業(yè)務(wù)量的系統(tǒng)來說，備份策略可以這么定：第一次完全備份，每天一次增量備份，每周再做一次完全備份，如此一直重復(fù)。而對(duì)于重要的且繁忙的系統(tǒng)來說，則可能需要每天一次全量備份，每小時(shí)一次增量備份，甚至更頻繁。為了不影響線上業(yè)務(wù)，實(shí)現(xiàn)在線備份，并且能增量備份，最好的辦法就是采用主從復(fù)制機(jī)制(replication)，在 slave 機(jī)器上做備份。
3.策略三：lvs快照從物理角度實(shí)現(xiàn)幾乎熱備的完全備份，配合二進(jìn)制日志備份實(shí)現(xiàn)增量備份，速度快適合比較煩忙的數(shù)據(jù)庫(kù)
前提：
· 數(shù)據(jù)文件要在邏輯卷上；
· 此邏輯卷所在卷組必須有足夠空間使用快照卷；
· 數(shù)據(jù)文件和事務(wù)日志要在同一個(gè)邏輯卷上；
步驟：
(1).打開會(huì)話，施加讀鎖，鎖定所有表；
|
1 2 |
mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS; |
(2).通過另一個(gè)終端，保存二進(jìn)制日志文件及相關(guān)位置信息；
|
1 |
mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info |
(3).創(chuàng)建快照卷
|
1 |
lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv |
(4).釋放鎖
|
1 |
mysql> UNLOCK TABLES; |
(5).掛載快照卷，備份
|
1 2 |
mount cp |
(6).刪除快照卷；
(7).增量備份二進(jìn)制日志；
4.策略四：xtrabackup 備份數(shù)據(jù)庫(kù)，實(shí)現(xiàn)完全熱備份與增量熱備份（MyISAM是溫備份，InnoDB是熱備份），由于有的數(shù)據(jù)在設(shè)計(jì)之初，數(shù)據(jù)目錄沒有存放在LVM上，所以不能用LVM作備份，則用xtrabackup代替來備份數(shù)據(jù)庫(kù)
說明：Xtrabackup是一個(gè)對(duì)InnoDB做數(shù)據(jù)備份的工具，支持在線熱備份(備份時(shí)不影響數(shù)據(jù)讀寫)，是商業(yè)備份工具InnoDB Hotbackup或ibbackup的一個(gè)很好的替代品。
Xtrabackup有兩個(gè)主要的工具：xtrabackup、innobackupex
· xtrabackup 只能備份InnoDB和XtraDB兩種數(shù)據(jù)表，而不能備份MyISAM數(shù)據(jù)表。
· innobackupex 是參考了InnoDB Hotbackup的innoback腳本修改而來的.innobackupex是一個(gè)perl腳本封裝，封裝了xtrabackup。主要是為了方便的 同時(shí)備份InnoDB和MyISAM引擎的表，但在處理myisam時(shí)需要加一個(gè)讀鎖。并且加入了一些使用的選項(xiàng)。如slave-info可以記錄備份恢復(fù)后作為slave需要的一些信息，根據(jù)這些信息，可以很方便的利用備份來重做slave。
特點(diǎn)：
· 備份過程快速、可靠；
· 備份過程不會(huì)打斷正在執(zhí)行的事務(wù)；
· 能夠基于壓縮等功能節(jié)約磁盤空間和流量；
· 自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)；
· 還原速度快；
5.策略五：主從復(fù)制（replication）實(shí)現(xiàn)數(shù)據(jù)庫(kù)實(shí)時(shí)備份（集群中常用）
6.總結(jié)
單機(jī)備份是完全備份（所有數(shù)據(jù)庫(kù)文件）+增量備份（備份二進(jìn)制日志）相結(jié)合！
集群中備份是完全備份（所有數(shù)據(jù)庫(kù)文件）+增量備份（備份二進(jìn)制日志）+主從復(fù)制（replication）相結(jié)合的方法！
五、備份準(zhǔn)備工作
1.查看服務(wù)器狀態(tài)，
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> \s -------------- mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1 Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.32-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 0 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.033 |
2.查看數(shù)據(jù)目錄存放位置
|
1 2 3 4 5 6 7 |
mysql> show variables like '%datadir%'; +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | datadir | /mydata/data/ | +---------------+---------------+ 1 row in set (0.01 sec) |
3.修改二進(jìn)制日志的存放位置
(1).建立一目錄用于存放二進(jìn)制日志
|
1 2 3 4 |
[root@mysql ~]# mkdir /mybinlog [root@mysql ~]# chown mysql:mysql /mybinlog [root@mysql /]# ll | grep mybinlog drwxr-xr-x 2 mysql mysql 4096 7月 22 14:39 mybinlog |
(2).修改my.cnf
|
1 2 3 |
[root@mysql ~]# vim /etc/my.cnf log-bin=/mybinlog/mysql-bin #二進(jìn)制日志目錄及文件名前綴 innodb_file_per_table = 1 #啟用InnoDB表每表一文件，默認(rèn)所有庫(kù)使用一個(gè)表空間 |
(3).重新啟動(dòng)mysqld
|
1 |
[root@mysql ~]# service mysqld restart |
4.查看新生成的binlog日志
|
1 2 |
[root@mysql ~]# ls /mybinlog/ mysql-bin.000001 mysql-bin.index |
5.準(zhǔn)備一個(gè)test庫(kù)，里面有兩張表，t1表和t2表！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> show table status from test\G #查看兩張表的狀態(tài) *************************** 1. row *************************** Name: t1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 167772160 Avg_row_length: 7 Data_length: 1174405120 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-07-21 19:37:44 Update_time: 2013-07-21 19:52:48 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: *************************** 2. row *************************** Name: t2 Engine: InnoDB Version: 10 Row_format: Compact Rows: 20971797 Avg_row_length: 31 Data_length: 667942912 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2013-07-21 20:00:29 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.01 sec) |
第一張t1表，使用的是MyISAM引擎，其中有1億多行數(shù)據(jù)，第二張t2表,使用的是INNODB引擎，其中有2千多萬行數(shù)據(jù)！有博友會(huì)問了，你是在做測(cè)試怎么會(huì)有這么多數(shù)據(jù)的，下面我就的大家說一下，快速插入1億條數(shù)據(jù)的方法！具體操作如下，
t1表，
|
1 2 3 4 5 6 7 8 |
mysql>use test; #使用 test數(shù)據(jù)庫(kù) mysql>create table t1 ( #創(chuàng)建一個(gè)簡(jiǎn)單的t1表，里面只有一個(gè)字段 id id int(10) default null )engine=myisam default charset=utf8; mysql> show create table t1; #查看創(chuàng)建的表 mysql>insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); #先插入十個(gè)數(shù)據(jù) mysql>insert into t1 select * from t1; #重復(fù)多次便能插入1億條數(shù)據(jù) mysql>select count(*) from t1; #查看插入數(shù)據(jù)的總數(shù) |
t2表，
|
1 2 3 4 5 6 7 |
mysql>create table t2 ( id int(10) default null )engine=innodb default charset=utf8; mysql> show create table t2; mysql>insert into t2 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); mysql>insert into t2 select * from t2; mysql>select count(*) from t2; |
好了，下面我們就來詳細(xì)說一說備份與還原！
六、備份策略具體演示
1.策略一：直接拷貝數(shù)據(jù)庫(kù)文件（文件系統(tǒng)備份工具 cp）（適合小型數(shù)據(jù)庫(kù)）
(1).標(biāo)準(zhǔn)流程：鎖表->刷新表到磁盤->拷貝文件->解鎖（注,若有有可能的話，可以先停止數(shù)據(jù)庫(kù)，再用cp命令準(zhǔn)備，這樣備份的數(shù)據(jù)最可靠）
(2).具體步驟：
a.打開第一個(gè)終端，
|
1 2 3 |
[root@mysql data]# mysql mysql> FLUSH TABLES WITH READ LOCK; #刷新表到時(shí)磁盤中并讀鎖 Query OK, 0 rows affected (0.00 sec) |
b.打開第二個(gè)終端
|
1 2 |
[root@mysql data]# mkdir /root/alldb.`date +%F-%H-%M-%S`/ #創(chuàng)建備份目錄 [root@mysql data]# cp -rp /mydata/data/* /root/alldb.2013-07-22-13-46-22/ #復(fù)制所以的數(shù)據(jù)庫(kù)文件 |
c.在第一個(gè)終端解鎖
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> UNLOCK TABLES; #解鎖 Query OK, 0 rows affected (0.01 sec) [root@mysql ~]# ll alldb.2013-07-22-13-46-22/ #查看備份好的數(shù)據(jù)庫(kù) 總用量 267468 -rw-rw---- 1 mysql mysql 262221824 7月 21 20:17 ibdata1 -rw-rw---- 1 mysql mysql 5242880 7月 22 13:40 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 7月 22 13:40 ib_logfile1 drwx------ 2 mysql mysql 4096 7月 20 12:33 mysql -rw-rw---- 1 mysql mysql 27698 7月 20 12:33 mysql-bin.000001 -rw-rw---- 1 mysql mysql 190 7月 22 13:40 mysql-bin.index -rw-rw---- 1 mysql mysql 1925 7月 21 13:07 mysql-slow.log -rw-r----- 1 mysql mysql 21906 7月 22 13:40 mysql.test.com.err -rw-rw---- 1 mysql mysql 5 7月 22 13:40 mysql.test.com.pid drwx------ 2 mysql mysql 4096 7月 20 12:33 performance_schema drwx------ 2 mysql mysql 4096 7月 21 20:00 test |
(3).模擬數(shù)據(jù)庫(kù)損壞
直接刪除數(shù)據(jù)目錄中的所有文件
|
1 2 3 4 |
[root@mysql data]# rm -rf * [root@mysql data]# ll 總用量 0 [root@mysql data]# |
(4).具體還原步驟
a.mysql這時(shí)是無法停止的
|
1 2 |
[root@mysql mydata]# service mysqld stop ERROR! MySQL server PID file could not be found! |
b.查找mysql所有進(jìn)程
|
1 2 3 |
[root@mysql mydata]# ps aux | grep mysqld root 2728 0.0 0.1 11300 1520 pts/1 S 15:01 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/mysql.test.com.pid mysql 3029 0.1 9.1 773908 92312 pts/1 Sl 15:01 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mydata/data/mysql.test.com.err --pid-file=/mydata/data/mysql.test.com.pid --socket=/tmp/mysql.sock --port=3306 |
c.殺死m(xù)ysql的所有進(jìn)程
|
1 |
[root@mysql ~]# killall mysqld |
d.初始化mysql
|
1 |
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql |
e.復(fù)制完全備份的數(shù)據(jù)文件到數(shù)據(jù)目錄中
|
1 2 |
[root@mysql test]# alias cp=cp #修改cp別名，不然復(fù)制時(shí)老是提醒是否覆蓋 [root@mysql test]# cp -pr /root/alldb.2013-07-22-13-46-22/* /mydata/data/ #復(fù)制完全備份的文件到數(shù)據(jù)目錄中 |
f.啟動(dòng)mysql數(shù)據(jù)庫(kù)
|
1 2 |
[root@mysql test]# service mysqld start Starting MySQL SUCCESS! |
g.測(cè)試并查看數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[root@mysql test]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.01 sec) mysql> select count(*) from t1; +-----------+ | count(*) | +-----------+ | 167772160 | +-----------+ 1 row in set (0.01 sec) mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 20971520 | +----------+ 1 row in set (9.95 sec) |
大家可以看到所有數(shù)據(jù)都恢復(fù)了，嘿嘿！
(5).總結(jié)
cp命令，對(duì)其進(jìn)行的備份，速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統(tǒng)，但是跨平臺(tái)能力很差，適合小型數(shù)據(jù)庫(kù)備份！
2.策略二：mysqldump備份數(shù)據(jù)庫(kù)（完全備份+增加備份，速度相對(duì)較慢，適合中小型數(shù)據(jù)庫(kù)）（MyISAM是溫備份，InnoDB是熱備份）
(1).mysqldump命令詳解
|
1 2 3 4 5 6 7 8 |
mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql --all-tables #備份所有庫(kù) --lock-all-tables #為所有表加讀鎖 --routinge #存儲(chǔ)過程與函數(shù) --triggers #觸發(fā)器 --events #記錄事件 --master-data=2 #在備份文件中記錄當(dāng)前二進(jìn)制日志的位置，并且為注釋的，1是不注釋掉在主從復(fù)制中才有意義 --flush-logs #日志滾動(dòng)一次 |
(2).具體備份過程如下
a.查看備份前的binlog日志
|
1 2 3 4 5 6 7 |
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000022 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec) |
b.備份所有庫(kù)（完全備份）
|
1 |
[root@mysql mybackup]# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql |
c.查看備份是否成功
|
1 2 3 |
[root@mysql mybackup]# ll -h 總用量 739M -rw-r--r-- 1 root root 739M 7月 22 16:31 2013-07-22-16-20.full.sql |
d.查看新生成的binlog日志
|
1 2 3 4 5 6 7 |
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000023 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec) |
e.插入幾條新的數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) mysql> select count(*) from t1; +-----------+ | count(*) | +-----------+ | 167772160 | +-----------+ 1 row in set (0.01 sec) mysql> insert into t1 values(167772164),(167772165),(167772166); |
f.再次查看binlog日志
|
1 2 3 4 5 6 7 |
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000023 | 363 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec) |
g.備份二進(jìn)制日志（增量備份）
|
1 |
[root@mysql mybackup]# cp /mybinlog/mysql-bin.000023 /root/mybackup/2013-07-22-16-20.binlog.full.000001 |
h.查看備份的二進(jìn)制日志
|
1 2 3 4 |
[root@mysql mybackup]# ll 總用量 756264 -rw-r----- 1 root root 363 7月 22 16:34 2013-07-22-16-20.binlog.full.000001 -rw-r--r-- 1 root root 774402118 7月 22 16:31 2013-07-22-16-20.full.sql |
i.模擬數(shù)據(jù)庫(kù)損壞
|
1 2 3 4 5 6 7 8 9 |
[root@mysql mybackup]# cd /mydata/data/ [root@mysql data]# ls ibdata1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql.test.com.err ib_logfile0 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql.test.com.pid ib_logfile1 mysql-bin.000003 mysql-bin.000007 mysql-bin.index performance_schema mysql mysql-bin.000004 mysql-bin.000008 mysql-slow.log test [root@mysql data]# rm -rf * #刪除所有數(shù)據(jù) [root@mysql data]# ll 總用量 0 |
(3).具體還原過程如下，
a.查找mysql進(jìn)程
|
1 2 3 4 5 |
[root@mysql data]# ps -aux | grep mysqld Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ root 3599 0.0 0.1 11304 1340 pts/1 S 15:18 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/mysql.test.com.pid mysql 3901 4.7 21.5 1167384 218684 pts/1 Sl 15:18 3:49 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mydata/data/mysql.test.com.err --pid-file=/mydata/data/mysql.test.com.pid --socket=/tmp/mysql.sock --port=3306 root 4469 0.0 0.0 103244 876 pts/1 S+ 16:38 0:00 grep mysqld |
b.殺死所有進(jìn)程
|
1 |
[root@mysql data]# killall mysqld |
c.初始化mysql并啟動(dòng)mysql
|
1 |
[root@mysql data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql |
d.因?yàn)槲覀儾皇侨鲁跏蓟?，可能?huì)有報(bào)錯(cuò)的二進(jìn)制日志，所有我們這里全部刪除
|
1 |
[root@mysql data]# rm -rf /mybinlog/* |
e.啟動(dòng)mysql數(shù)據(jù)庫(kù)，啟動(dòng)時(shí)會(huì)重新生成新的二進(jìn)制日志的
|
1 |
[root@mysql ~]# service mysqld start |
f.恢復(fù)到備份狀態(tài),備份前先關(guān)閉對(duì)恢復(fù)過程的二進(jìn)制日志記錄，因?yàn)橛涗浕謴?fù)語句是毫無意義的
|
1 2 |
mysql> set global sql_log_bin=0; mysq> source /root/mybackup/2013-07-22-16-20.full.sql |
g.打開另一個(gè)終端查詢數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> select count(*) from t1; +-----------+ | count(*) | +-----------+ | 167772163 | +-----------+ 1 row in set (1 min 29.63 sec) #可以看到用mysqldump備份數(shù)據(jù)，還原myisam引擎時(shí)大概需要30s時(shí)間(共1億多條數(shù)據(jù)，速度不是挺快的) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 20971520 | +----------+ 1 row in set (46.14 sec) #還原INNODB引擎，大概50s左右(共2千多萬條數(shù)據(jù)) |
h.查看最后十條數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> select * from t1 order by id desc limit 10; +-----------+ | id | +-----------+ | 167772163 | | 167772162 | | 167772161 | | 10 | | 10 | | 10 | | 10 | | 10 | | 10 | | 10 | +-----------+ 10 rows in set (0.00 sec) |
大家可以看到，我們已經(jīng)恢復(fù)到，完全備份時(shí)的狀態(tài)，但我們最后插入的三條數(shù)據(jù)沒有恢復(fù)，下面我們恢復(fù)，數(shù)據(jù)庫(kù)損壞前我們插入的三條數(shù)據(jù)！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@mysql ~]# mysqlbinlog /root/mybackup/2013-07-22-16-20.binlog.full.000001 | mysql test [root@mysql ~]# mysql test mysql> select * from t1 order by id desc limit 10; +-----------+ | id | +-----------+ | 167772166 | | 167772165 | | 167772164 | | 167772163 | | 167772162 | | 167772161 | | 10 | | 10 | | 10 | | 10 | +-----------+ 10 rows in set (47.01 sec) mysql> |
大家可以看到，已經(jīng)恢復(fù)我們最后增加的三條數(shù)據(jù)！
i.最后，打開二進(jìn)制記錄并查看恢復(fù)狀況
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> set global sql_log_bin=1; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) |
(4).總結(jié)：
基于mysqldump通常我們就是完整備份+二進(jìn)制日志來進(jìn)行恢復(fù)的！
3.策略三：lvs快照從物理角度實(shí)現(xiàn)幾乎熱備的完全備份，配合二進(jìn)制日志備份實(shí)現(xiàn)增量備份，速度快適合比較煩忙的數(shù)據(jù)庫(kù)！
說明：要求你的MySQL的數(shù)據(jù)目錄必須在lvm卷上！
具體步驟如下，
(1).在MySQL中為所有表加讀鎖，不要關(guān)閉終端，否則鎖將失效,滾動(dòng)日志
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> flush tables with read lock; Query OK, 0 rows affected (0.01 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
(2).另開一終端速度建立快照,我的那個(gè)卷組是/dev/myvg/mydata
|
1 |
[root@mysql ~]# lvcreate -L 2G -n mysql-snap -s -p r /dev/myvg/mydata |
(3).速度釋放讀鎖
|
1 2 |
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) |
(4).掛載快照，拷備出來，卸載快照，刪除快照
|
1 2 3 4 5 |
[root@mysql ~]# mount /dev/myvg/mysql-snap /mnt [root@mysql ~]# mkdir /root/mybackup/lvm [root@mysql ~]# cp -pR /mnt/* /root/mybackup/lvm/ [root@mysql ~]# umount /mnt [root@mysql ~]# lvremove /dev/myvg/mysql-snap |
(5).就這樣一次完整備份就完成了，下面來測(cè)試能否正常使用
|
1 2 3 4 |
[root@mysql ~]# service mysqld stop [root@mysql ~]# rm -rf /mydata/* [root@mysql ~]# cp -Rp /root/mybackup/lvm/* /mydata/ [root@mysql ~]# service mysqld start #如果能正常啟動(dòng)代表沒有問題 |
(6).如果在完整備份后MySQL出現(xiàn)故障，與mysqldump一樣，先恢復(fù)上次的完整備份，再利用二進(jìn)制日志恢復(fù)，找到完整備份時(shí)的二進(jìn)制位置，把從那時(shí)到故障前的日志用mysqlbinlog導(dǎo)出來，然后再導(dǎo)入到MySQL中。這個(gè)同mysqldump中實(shí)驗(yàn)一致就不重復(fù)了。
(7).總結(jié)
用lvm的快照來備份速度是非?？斓?，而且?guī)缀鯚醾?，恢?fù)也很快速，操作也簡(jiǎn)單，完整恢復(fù)后再將相應(yīng)二進(jìn)制恢復(fù)即可。
4.策略四：xtrabackup 備份數(shù)據(jù)庫(kù)，實(shí)現(xiàn)完全熱備份與增量熱備份（MyISAM是溫備份，InnoDB是熱備份）
(1).安裝percona-xtrabackup-2.1.3-608所需的依賴包
|
1 |
[root@mysql ~]# yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* -y |
(2).解壓軟件包鍵入命令文件目錄
|
1 2 3 4 5 6 7 8 9 10 11 |
[root@mysql ~]# tar xf percona-xtrabackup-2.1.3-608.tar.gz [root@mysql src]# cd percona-xtrabackup-2.1.3/bin/ [root@mysql bin]# ll 總用量 112284 -rwxr-xr-x 2 root root 110738 5月 23 02:50 innobackupex lrwxrwxrwx 2 root root 12 7月 23 04:48 innobackupex-1.5.1 -> innobackupex -rwxr-xr-x 2 root root 2211237 5月 23 02:50 xbcrypt -rwxr-xr-x 2 root root 2285672 5月 23 02:50 xbstream -rwxr-xr-x 2 root root 13033745 5月 23 02:50 xtrabackup -rwxr-xr-x 2 root root 16333506 5月 23 02:28 xtrabackup_55 -rwxr-xr-x 2 root root 80988093 5月 23 02:40 xtrabackup_56 |
(3).將innobackupex、xtrabackup等文件copy到mysql程序目錄下/bin、目錄
|
1 |
[root@mysql bin]# cp -pl * /usr/local/mysql/bin/ |
(4).將mysql安裝目錄下的文件做軟鏈接到/usr/bin/目錄下。這個(gè)比變量方便，這樣就完成了部署安裝
|
1 |
[root@mysql bin]# ln -sv /usr/local/mysql/bin/* /usr/bin/ |
(5).測(cè)試
|
1 2 3 4 |
[root@mysql bin]# innobackupex innobackupex innobackupex-1.5.1 [root@mysql bin]# xtrabackup xtrabackup xtrabackup_55 xtrabackup_56 |
(6).查看innobackupex選項(xiàng)
|
1 |
[root@mysql ~]# innobackupex --help |
(7).設(shè)置mysql密碼
|
1 |
[root@mysql ~]# mysqladmin -uroot password 123456 |
(8).全庫(kù)備份
|
1 |
[root@mysql ~]# innobackupex --host=localhost --user=root --password=123456 /root/mybackup/xtrabackup/ |
報(bào)錯(cuò)，
|
1 2 |
xtrabackup: Error: Please set parameter 'datadir' innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 389. |
解決方法，
|
1 2 3 |
[root@mysql data]# vim /etc/my.cnf #增加一行 datadir = /mydata/data |
再次執(zhí)行成功，
|
1 2 |
[root@mysql xtrabackup]# innobackupex --host=localhost --user=root --password=123456 --defaults-file=/etc/my.cnf /root/mybackup/xtrabackup/ 130723 05:29:13 innobackupex: completed OK! |
(9).查看備份文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@mysql xtrabackup]# ll 總用量 4 drwxr-xr-x 5 root root 4096 7月 23 05:33 2013-07-23_05-32-51 [root@mysql xtrabackup]# cd 2013-07-23_05-32-51/ [root@mysql 2013-07-23_05-32-51]# ll 總用量 190496 -rw-r--r-- 1 root root 260 7月 23 05:32 backup-my.cnf -rw-r----- 1 root root 195035136 7月 23 05:32 ibdata1 drwxr-xr-x 2 root root 4096 7月 23 05:33 mysql drwxr-xr-x 2 root root 4096 7月 23 05:33 performance_schema drwx------ 2 root root 4096 7月 23 05:33 test -rw-r--r-- 1 root root 13 7月 23 05:33 xtrabackup_binary -rw-r--r-- 1 root root 23 7月 23 05:33 xtrabackup_binlog_info -rw-r----- 1 root root 95 7月 23 05:33 xtrabackup_checkpoints -rw-r----- 1 root root 2560 7月 23 05:33 xtrabackup_logfile |
數(shù)據(jù)會(huì)完整備份到/root/mybackup/xtrabackup/中目錄名字為當(dāng)前的日期，xtrabackup會(huì)備份所有的InnoDB表，MyISAM表只是復(fù)制表結(jié)構(gòu)文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相關(guān)文件，同時(shí)還會(huì)備份觸發(fā)器和數(shù)據(jù)庫(kù)配置信息相關(guān)的文件。除了保存數(shù)據(jù)外還生成了一些xtrabackup需要的數(shù)據(jù)文件，詳解如下：
· xtrabackup_checkpoints 備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息；每個(gè)InnoDB頁(通常為16k大小)都會(huì)包含一個(gè)日志序列號(hào)，即LSN。LSN是整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的系統(tǒng)版本號(hào)，每個(gè)頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的。
· xtrabackup_binlog_info mysql服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。
· xtrabackup_binary 備份中用到的xtrabackup的可執(zhí)行文件。
· backup-my.cnf 備份命令用到的配置選項(xiàng)信息。
· xtrabackup_logfile 記錄標(biāo)準(zhǔn)輸出信息xtrabackup_logfile
(10).測(cè)試恢復(fù)MySQL,用xtrabackup來完整恢復(fù)
|
1 2 3 4 5 6 7 8 |
[root@mysql data]# service mysqld stop [root@mysql data]# rm -rf /mydata/data/* [root@mysql data]# innobackupex --apply-log /root/mybackup/xtrabackup/2013-07-23_05-48-03/ #--apply-log 的意義在于把備份時(shí)沒commit的事務(wù)撤銷，已經(jīng)commit的但還在事務(wù)日志中的應(yīng)用到數(shù)據(jù)庫(kù) [root@mysql data]# innobackupex --copy-back /root/mybackup/xtrabackup/2013-07-23_05-48-03/ #--copy-back數(shù)據(jù)庫(kù)恢復(fù)，后面跟上備份目錄的位置 [root@mysql data]# chown -R mysql:mysql /mydata/data [root@mysql data]# service mysqld start #如果能啟動(dòng)代表恢復(fù)正常 |
(11).在表中新增一些數(shù)據(jù)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> insert into t1 values (123),(456),(789); #查看一下數(shù)據(jù) mysql> use test; Database changed mysql> select * from t1 order by id desc limit 10; +------+ | id | +------+ | 789 | | 456 | | 333 | | 222 | | 123 | | 111 | | 33 | | 22 | | 11 | | 10 | +------+ 10 rows in set (9.47 sec) |
(12).增量備份
|
1 2 3 4 |
[root@mysql data]# innobackupex --user=root --password=123456 --incremental --incremental-basedir=/root/mybackup/xtrabackup/2013-07-23_05-48-03/ /root/mybackup/xtrabackup/ #--incremental 指定是增量備份 #--incremental-basedir 指定基于哪個(gè)完整備份做增量備份，最后是增量備份保存的目錄 注：增量備份只能對(duì)InnoDB引擎做增量備份，對(duì)MyISAM的表是完全復(fù)制 |
(13).測(cè)試增量備份恢復(fù)
|
1 2 3 4 5 6 |
[root@mysql data]# service mysqld stop [root@mysql data]# rm -rf /mydata/data/* [root@mysql data]# innobackupex --apply-log --redo-only #--redo-only 指的是把備份時(shí)commit的但還在事務(wù)日志中的應(yīng)用到時(shí)數(shù)據(jù)，但是還沒提交的不撤消， 因?yàn)檫@個(gè)事務(wù)可能在增量備份中提交，假如的撤消了增量備份中就提交不，因?yàn)槭聞?wù)已經(jīng)不完整 #/root/mybackup/xtrabackup/2013-07-23_05-48-03/ 是完全備份的目錄 |
(14).將增量備份全部并到完整備份中去
|
1 2 3 |
[root@mysql data]# innobackupex --apply-log /root/mybackup/xtrabackup/2013-07-23_05-48-03/ --incremental-dir=/root/mybackup/xtrabackup/2013-07-23_06-05-37/ #/root/mybackup/xtrabackup/2013-07-23_05-48-03/ 這個(gè)是完整備份的目錄 #--incremental-dir 后跟的是增量備份的目錄 |
注：這個(gè)會(huì)使增量備份中的的數(shù)據(jù)合并到完整備份中，如果還有增量備份，繼續(xù)合并，恢復(fù)時(shí)恢復(fù)完整備份即可
(15).恢復(fù)數(shù)據(jù)，并起動(dòng)MySQL
|
1 2 3 |
[root@mysql data]# innobackupex --copy-back /root/mybackup/xtrabackup/2013-07-23_05-48-03/ [root@mysql data]# chown -R mysql:mysql /mydata/data/ [root@mysql data]# service mysqld start |
(16).查看數(shù)據(jù)有沒丟失
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@mysql data]# mysql -uroot -p123456 test mysql> use test; Database changed mysql> select * from t1 order by id desc limit 10; +------+ | id | +------+ | 789 | | 456 | | 333 | | 222 | | 123 | | 111 | | 33 | | 22 | | 11 | | 10 | +------+ 10 rows in set (9.47 sec) |
所有數(shù)據(jù)全部恢復(fù)！
17.總結(jié)
如果在增量備份后數(shù)據(jù)庫(kù)出現(xiàn)故障，我們需要通過完整備份+到現(xiàn)在為止的所有增量備份+最后一次增量備份到現(xiàn)在的二進(jìn)制日志來恢復(fù)。
18.附注
單獨(dú)備份：
|
1 |
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test /root/mybackup |
備份并打包壓縮：
|
1 |
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/testdb.tar.gz |
帶時(shí)間戳：
|
1 |
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/`date +%F`_testdb.tar.gz |
備份信息輸出重定向到文件：
|
1 |
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ 2>/root/mybackup/test.log | gzip 1>/root/mybackup/test.tar.gz |
說明：
|
1 2 3 4 5 6 7 |
--stream #指定流的格式，目前只支持tar --database=test #單獨(dú)對(duì)test數(shù)據(jù)庫(kù)做備份 ，若是不添加此參數(shù)那就那就是對(duì)全庫(kù)做 2>/root/mybackup/test.log #輸出信息寫入日志中 1>/root/mybackup/test.tar.gz #打包壓縮存儲(chǔ)到該文件中 解壓 tar -izxvf 要加-i參數(shù)，官方解釋 innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream. 在備份完成后，數(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)。 |
5.策略五：主從復(fù)制（replication）實(shí)現(xiàn)數(shù)據(jù)庫(kù)實(shí)時(shí)備份（集群中常用）
說明，MySQL主從復(fù)制會(huì)單獨(dú)寫一篇博客講解，今天就說到這，嘿嘿！^_^……
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
