溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
大綱
一、MySQL備份類型
二、MySQL備份都備份什么？
三、MySQL備份工具
四、MySQL備份策略
五、備份準備工作
六、備份策略具體演示
注：系統版本 CentOS6.4 X86_64，MySQL版本 MySQL 5.5.32，相關軟件下載 http://yunpan.cn/QnymShsCMzGg9
一、MySQL備份類型
1.熱備份、溫備份、冷備份 （根據服務器狀態）
· 熱備份：讀、寫不受影響；
· 溫備份：僅可以執行讀操作；
· 冷備份：離線備份；讀、寫操作均中止；
2.物理備份與邏輯備份 （從對象來分）
· 物理備份：復制數據文件；
· 邏輯備份：將數據導出至文本文件中；
3.完全備份、增量備份、差異備份 （從數據收集來分）
· 完全備份：備份全部數據；
· 增量備份：僅備份上次完全備份或增量備份以后變化的數據；
· 差異備份：僅備份上次完全備份以來變化的數據；
4.邏輯備份的優點：
· 在備份速度上兩種備份要取決于不同的存儲引擎
· 物理備份的還原速度非常快。但是物理備份的最小力度只能做到表
· 邏輯備份保存的結構通常都是純ASCII的，所以我們可以使用文本處理工具來處理
· 邏輯備份有非常強的兼容性，而物理備份則對版本要求非常高
· 邏輯備份也對保持數據的安全性有保證
5.邏輯備份的缺點：
· 邏輯備份要對RDBMS產生額外的壓力，而裸備份無壓力
· 邏輯備份的結果可能要比源文件更大。所以很多人都對備份的內容進行壓縮
· 邏輯備份可能會丟失浮點數的精度信息
6.增量備份與差異備份區別
說明，差異備份要比增量備份占用的空間大，但恢復時比較方便！但我們一般都用增量備份！
二、MySQL備份都備份什么？
我們備份，一般備份以下幾個部分：
1.數據文件
2.日志文件（比如事務日志，二進制日志）
3.存儲過程，存儲函數，觸發器
4.配置文件（十分重要，各個配置文件都要備份）
5.用于實現數據庫備份的腳本，數據庫自身清理的Croutab等……
三、MySQL備份工具
如下圖，
上面的所有備份工具對比，下面我們就來說一下，常用的備份工具，
1.Mysql自帶的備份工具
· mysqldump 邏輯備份工具，支持所有引擎，MyISAM引擎是溫備，InnoDB引擎是熱備，備份速度中速，還原速度非常非常慢，但是在實現還原的時候，具有很大的操作余地。具有很好的彈性。
· mysqlhotcopy 物理備份工具，但只支持MyISAM引擎，基本上屬于冷備的范疇，物理備份，速度比較快。
2.文件系統備份工具
· cp 冷備份，支持所有引擎，復制命令，只能實現冷備，物理備份。使用歸檔工具，cp命令，對其進行備份的，備份速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平臺能力很差。
· lvm 幾乎是熱備份，支持所有引擎，基于快照(LVM，ZFS)的物理備份，速度非常快，幾乎是熱備。只影響數據幾秒鐘而已。但是創建快照的過程本身就影響到了數據庫在線的使用，所以備份速度比較快，恢復速度比較快，沒有什么彈性空間，而且LVM的限制：不能對多個邏輯卷同一時間進行備份，所以數據文件和事務日志等各種文件必須放在同一個LVM上。而ZFS則非常好的可以在多邏輯卷之間備份。
3.其它工具
· ibbackup 商業工具 MyISAM是溫備份，InnoDB是熱備份 ，備份和還原速度都很快，這個軟件它的每服務器授權版本是5000美元。
· xtrabackup 開源工具 MyISAM是溫備份，InnoDB是熱備份 ，是ibbackup商業工具的替代工具。
四、MySQL備份策略
1.策略一：直接拷貝數據庫文件（文件系統備份工具 cp）（適合小型數據庫，是最可靠的）
當你使用直接備份方法時，必須保證表不在被使用。如果服務器在你正在拷貝一個表時改變它，拷貝就失去意義。保證你的拷貝完整性的最好方法是關閉服務器，拷貝文件，然后重啟服務器。如果你不想關閉服務器，要在執行表檢查的同時鎖定服務器。如果服務器在運行，相同的制約也適用于拷貝文件，而且你應該使用相同的鎖定協議讓服務器“安靜下來”。當你完成了備份時，需要重啟服務器(如果關閉了它)或釋放加在表上的鎖定(如果你讓服務器運行)。要用直接拷貝文件把一個數據庫從一臺機器拷貝到另一臺機器上，只是將文件拷貝到另一臺服務器主機的適當數據目錄下即可。要確保文件是MyIASM格式或兩臺機器有相同的硬件結構，否則你的數據庫在另一臺主機上有奇怪的內容。你也應該保證在另一臺機器上的服務器在你正在安裝數據庫表時不訪問它們。
2.策略二：mysqldump備份數據庫（完全備份+增加備份，速度相對較慢，適合中小型數據庫）（MyISAM是溫備份，InnoDB是熱備份）
mysqldump 是采用SQL級別的備份機制，它將數據表導成 SQL 腳本文件，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。對于中等級別業務量的系統來說，備份策略可以這么定：第一次完全備份，每天一次增量備份，每周再做一次完全備份，如此一直重復。而對于重要的且繁忙的系統來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業務，實現在線備份，并且能增量備份，最好的辦法就是采用主從復制機制(replication)，在 slave 機器上做備份。
3.策略三：lvs快照從物理角度實現幾乎熱備的完全備份，配合二進制日志備份實現增量備份，速度快適合比較煩忙的數據庫
前提：
· 數據文件要在邏輯卷上；
· 此邏輯卷所在卷組必須有足夠空間使用快照卷；
· 數據文件和事務日志要在同一個邏輯卷上；
步驟：
(1).打開會話，施加讀鎖，鎖定所有表；
|
1 2 |
mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS; |
(2).通過另一個終端，保存二進制日志文件及相關位置信息；
|
1 |
mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info |
(3).創建快照卷
|
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).增量備份二進制日志；
4.策略四：xtrabackup 備份數據庫，實現完全熱備份與增量熱備份（MyISAM是溫備份，InnoDB是熱備份），由于有的數據在設計之初，數據目錄沒有存放在LVM上，所以不能用LVM作備份，則用xtrabackup代替來備份數據庫
說明：Xtrabackup是一個對InnoDB做數據備份的工具，支持在線熱備份(備份時不影響數據讀寫)，是商業備份工具InnoDB Hotbackup或ibbackup的一個很好的替代品。
Xtrabackup有兩個主要的工具：xtrabackup、innobackupex
· xtrabackup 只能備份InnoDB和XtraDB兩種數據表，而不能備份MyISAM數據表。
· innobackupex 是參考了InnoDB Hotbackup的innoback腳本修改而來的.innobackupex是一個perl腳本封裝，封裝了xtrabackup。主要是為了方便的 同時備份InnoDB和MyISAM引擎的表，但在處理myisam時需要加一個讀鎖。并且加入了一些使用的選項。如slave-info可以記錄備份恢復后作為slave需要的一些信息，根據這些信息，可以很方便的利用備份來重做slave。
特點：
· 備份過程快速、可靠；
· 備份過程不會打斷正在執行的事務；
· 能夠基于壓縮等功能節約磁盤空間和流量；
· 自動實現備份檢驗；
· 還原速度快；
5.策略五：主從復制（replication）實現數據庫實時備份（集群中常用）
6.總結
單機備份是完全備份（所有數據庫文件）+增量備份（備份二進制日志）相結合！
集群中備份是完全備份（所有數據庫文件）+增量備份（備份二進制日志）+主從復制（replication）相結合的方法！
五、備份準備工作
1.查看服務器狀態，
|
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.查看數據目錄存放位置
|
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.修改二進制日志的存放位置
(1).建立一目錄用于存放二進制日志
|
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 #二進制日志目錄及文件名前綴 innodb_file_per_table = 1 #啟用InnoDB表每表一文件，默認所有庫使用一個表空間 |
(3).重新啟動mysqld
|
1 |
[root@mysql ~]# service mysqld restart |
4.查看新生成的binlog日志
|
1 2 |
[root@mysql ~]# ls /mybinlog/ mysql-bin.000001 mysql-bin.index |
5.準備一個test庫，里面有兩張表，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 #查看兩張表的狀態 *************************** 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億多行數據，第二張t2表,使用的是INNODB引擎，其中有2千多萬行數據！有博友會問了，你是在做測試怎么會有這么多數據的，下面我就的大家說一下，快速插入1億條數據的方法！具體操作如下，
t1表，
|
1 2 3 4 5 6 7 8 |
mysql>use test; #使用 test數據庫 mysql>create table t1 ( #創建一個簡單的t1表，里面只有一個字段 id id int(10) default null )engine=myisam default charset=utf8; mysql> show create table t1; #查看創建的表 mysql>insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); #先插入十個數據 mysql>insert into t1 select * from t1; #重復多次便能插入1億條數據 mysql>select count(*) from t1; #查看插入數據的總數 |
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; |
好了，下面我們就來詳細說一說備份與還原！
六、備份策略具體演示
1.策略一：直接拷貝數據庫文件（文件系統備份工具 cp）（適合小型數據庫）
(1).標準流程：鎖表->刷新表到磁盤->拷貝文件->解鎖（注,若有有可能的話，可以先停止數據庫，再用cp命令準備，這樣備份的數據最可靠）
(2).具體步驟：
a.打開第一個終端，
|
1 2 3 |
[root@mysql data]# mysql mysql> FLUSH TABLES WITH READ LOCK; #刷新表到時磁盤中并讀鎖 Query OK, 0 rows affected (0.00 sec) |
b.打開第二個終端
|
1 2 |
[root@mysql data]# mkdir /root/alldb.`date +%F-%H-%M-%S`/ #創建備份目錄 [root@mysql data]# cp -rp /mydata/data/* /root/alldb.2013-07-22-13-46-22/ #復制所以的數據庫文件 |
c.在第一個終端解鎖
|
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/ #查看備份好的數據庫 總用量 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).模擬數據庫損壞
直接刪除數據目錄中的所有文件
|
1 2 3 4 |
[root@mysql data]# rm -rf * [root@mysql data]# ll 總用量 0 [root@mysql data]# |
(4).具體還原步驟
a.mysql這時是無法停止的
|
1 2 |
[root@mysql mydata]# service mysqld stop ERROR! MySQL server PID file could not be found! |
b.查找mysql所有進程
|
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.殺死mysql的所有進程
|
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.復制完全備份的數據文件到數據目錄中
|
1 2 |
[root@mysql test]# alias cp=cp #修改cp別名，不然復制時老是提醒是否覆蓋 [root@mysql test]# cp -pr /root/alldb.2013-07-22-13-46-22/* /mydata/data/ #復制完全備份的文件到數據目錄中 |
f.啟動mysql數據庫
|
1 2 |
[root@mysql test]# service mysqld start Starting MySQL SUCCESS! |
g.測試并查看數據
|
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) |
大家可以看到所有數據都恢復了，嘿嘿！
(5).總結
cp命令，對其進行的備份，速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平臺能力很差，適合小型數據庫備份！
2.策略二：mysqldump備份數據庫（完全備份+增加備份，速度相對較慢，適合中小型數據庫）（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 #備份所有庫 --lock-all-tables #為所有表加讀鎖 --routinge #存儲過程與函數 --triggers #觸發器 --events #記錄事件 --master-data=2 #在備份文件中記錄當前二進制日志的位置，并且為注釋的，1是不注釋掉在主從復制中才有意義 --flush-logs #日志滾動一次 |
(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.備份所有庫（完全備份）
|
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.插入幾條新的數據
|
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.備份二進制日志（增量備份）
|
1 |
[root@mysql mybackup]# cp /mybinlog/mysql-bin.000023 /root/mybackup/2013-07-22-16-20.binlog.full.000001 |
h.查看備份的二進制日志
|
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.模擬數據庫損壞
|
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 * #刪除所有數據 [root@mysql data]# ll 總用量 0 |
(3).具體還原過程如下，
a.查找mysql進程
|
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.殺死所有進程
|
1 |
[root@mysql data]# killall mysqld |
c.初始化mysql并啟動mysql
|
1 |
[root@mysql data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql |
d.因為我們不是全新初始化的，可能會有報錯的二進制日志，所有我們這里全部刪除
|
1 |
[root@mysql data]# rm -rf /mybinlog/* |
e.啟動mysql數據庫，啟動時會重新生成新的二進制日志的
|
1 |
[root@mysql ~]# service mysqld start |
f.恢復到備份狀態,備份前先關閉對恢復過程的二進制日志記錄，因為記錄恢復語句是毫無意義的
|
1 2 |
mysql> set global sql_log_bin=0; mysq> source /root/mybackup/2013-07-22-16-20.full.sql |
g.打開另一個終端查詢數據
|
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備份數據，還原myisam引擎時大概需要30s時間(共1億多條數據，速度不是挺快的) 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千多萬條數據) |
h.查看最后十條數據
|
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) |
大家可以看到，我們已經恢復到，完全備份時的狀態，但我們最后插入的三條數據沒有恢復，下面我們恢復，數據庫損壞前我們插入的三條數據！
|
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> |
大家可以看到，已經恢復我們最后增加的三條數據！
i.最后，打開二進制記錄并查看恢復狀況
|
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).總結：
基于mysqldump通常我們就是完整備份+二進制日志來進行恢復的！
3.策略三：lvs快照從物理角度實現幾乎熱備的完全備份，配合二進制日志備份實現增量備份，速度快適合比較煩忙的數據庫！
說明：要求你的MySQL的數據目錄必須在lvm卷上！
具體步驟如下，
(1).在MySQL中為所有表加讀鎖，不要關閉終端，否則鎖將失效,滾動日志
|
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).另開一終端速度建立快照,我的那個卷組是/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).就這樣一次完整備份就完成了，下面來測試能否正常使用
|
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 #如果能正常啟動代表沒有問題 |
(6).如果在完整備份后MySQL出現故障，與mysqldump一樣，先恢復上次的完整備份，再利用二進制日志恢復，找到完整備份時的二進制位置，把從那時到故障前的日志用mysqlbinlog導出來，然后再導入到MySQL中。這個同mysqldump中實驗一致就不重復了。
(7).總結
用lvm的快照來備份速度是非常快的，而且幾乎熱備，恢復也很快速，操作也簡單，完整恢復后再將相應二進制恢復即可。
4.策略四：xtrabackup 備份數據庫，實現完全熱備份與增量熱備份（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/目錄下。這個比變量方便，這樣就完成了部署安裝
|
1 |
[root@mysql bin]# ln -sv /usr/local/mysql/bin/* /usr/bin/ |
(5).測試
|
1 2 3 4 |
[root@mysql bin]# innobackupex innobackupex innobackupex-1.5.1 [root@mysql bin]# xtrabackup xtrabackup xtrabackup_55 xtrabackup_56 |
(6).查看innobackupex選項
|
1 |
[root@mysql ~]# innobackupex --help |
(7).設置mysql密碼
|
1 |
[root@mysql ~]# mysqladmin -uroot password 123456 |
(8).全庫備份
|
1 |
[root@mysql ~]# innobackupex --host=localhost --user=root --password=123456 /root/mybackup/xtrabackup/ |
報錯，
|
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 |
再次執行成功，
|
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 |
數據會完整備份到/root/mybackup/xtrabackup/中目錄名字為當前的日期，xtrabackup會備份所有的InnoDB表，MyISAM表只是復制表結構文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。除了保存數據外還生成了一些xtrabackup需要的數據文件，詳解如下：
· xtrabackup_checkpoints 備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日志序列號)范圍信息；每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
· xtrabackup_binlog_info mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。
· xtrabackup_binary 備份中用到的xtrabackup的可執行文件。
· backup-my.cnf 備份命令用到的配置選項信息。
· xtrabackup_logfile 記錄標準輸出信息xtrabackup_logfile
(10).測試恢復MySQL,用xtrabackup來完整恢復
|
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 的意義在于把備份時沒commit的事務撤銷，已經commit的但還在事務日志中的應用到數據庫 [root@mysql data]# innobackupex --copy-back /root/mybackup/xtrabackup/2013-07-23_05-48-03/ #--copy-back數據庫恢復，后面跟上備份目錄的位置 [root@mysql data]# chown -R mysql:mysql /mydata/data [root@mysql data]# service mysqld start #如果能啟動代表恢復正常 |
(11).在表中新增一些數據
|
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); #查看一下數據 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 指定基于哪個完整備份做增量備份，最后是增量備份保存的目錄 注：增量備份只能對InnoDB引擎做增量備份，對MyISAM的表是完全復制 |
(13).測試增量備份恢復
|
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 指的是把備份時commit的但還在事務日志中的應用到時數據，但是還沒提交的不撤消， 因為這個事務可能在增量備份中提交，假如的撤消了增量備份中就提交不，因為事務已經不完整 #/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/ 這個是完整備份的目錄 #--incremental-dir 后跟的是增量備份的目錄 |
注：這個會使增量備份中的的數據合并到完整備份中，如果還有增量備份，繼續合并，恢復時恢復完整備份即可
(15).恢復數據，并起動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).查看數據有沒丟失
|
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) |
所有數據全部恢復！
17.總結
如果在增量備份后數據庫出現故障，我們需要通過完整備份+到現在為止的所有增量備份+最后一次增量備份到現在的二進制日志來恢復。
18.附注
單獨備份：
|
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 |
帶時間戳：
|
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 #單獨對test數據庫做備份 ，若是不添加此參數那就那就是對全庫做 2>/root/mybackup/test.log #輸出信息寫入日志中 1>/root/mybackup/test.tar.gz #打包壓縮存儲到該文件中 解壓 tar -izxvf 要加-i參數，官方解釋 innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream. 在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。 此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。 |
5.策略五：主從復制（replication）實現數據庫實時備份（集群中常用）
說明，MySQL主從復制會單獨寫一篇博客講解，今天就說到這，嘿嘿！^_^……
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
