Mysql的數據備份和恢復

204次閱讀

共計 6558 個字符，預計需要花費 17 分鐘才能閱讀完成。

這篇文章主要介紹“Mysql 的數據備份和恢復”，在日常操作中，相信很多人在 Mysql 的數據備份和恢復問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql 的數據備份和恢復”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

Mysql 的備份分類：
mysql 的數據備份：
邏輯備份、物理備份
mysql 按照數據集分類：
完全備份
增量備份
差異備份
一般這幾種都是組合起來使用的，比如完全備份可以一周進行一次，增量備
份和差異備份可以一天備份一次，但是這要根據企業的業務邏輯而定，所以
合適的選擇，將會在數據恢復的時候將損失降到最低。
mysql 按照服務器是否停機分類：
冷備份：服務完全停止
溫備份：服務不關閉，但是在備份的時候，其他的事務只有查詢的操作
熱備份: 服務在線，不影響讀寫（但是非事務存儲引擎的很難實現熱備，但
是可以使用 LVM 實現幾乎熱備的功能）
需要備份的東西：
1. 數據文件
2. 日志文件（事務日志，二進制日志）
3. 存儲過程，存儲函數，觸發器
4. 配置文件
日志分類：
1. 事務日志：
隨機 IO 轉換成順序 IO
事件重放（撤銷）提供基礎
2. 事件事務:
記錄歷史時間
MySQL 的日志文件
1.the error log 錯誤日志
錯誤信息，服務器進程啟動或停止產生的信息，主從中添加復制的時
候，也會記錄
主配置文件啟動錯誤日志，
log_error=/var/log/mysqld.err（確保對 mysqld 有寫的權限）
2.the binary log 二進制日志
提供增量備份
基于時間點的恢復
復制架構提供基礎
log_bin=/data/logs/binary/changelog（默認在數據文件中）
log_bin_index=/data/logs/relay/binarylog 定義二進制文件的索引
位置
3.the general query log 平時建議不開
general_log=ON|OFF
general_log_file=/PATH
4.the slow query log 慢查詢日志
slow_query_log=ON
slow_query_log_file=/PATH
long_query_time=NUM 定義超時時間
5.relay log 中繼日志
默認 Mysql 不開啟任何日志，但在 mysqld 腳本中開啟了錯誤日志 (the
error log)

mysql 常用的備份工具：
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy (MyISAM 存儲引擎的幾乎冷備份，看著名字像是熱備)

接下來我將借助我的一個簡單的進行演示數據的備份和還原的過程：
1. 使用 dump 來備份
mysqldump 的備份是一個邏輯備份；
備份的格式如下;
#mysqldump db_name [table_name] /PATH
當然 mysqldump 的備份還有很多選項：
–all-databases 備份所有的數據庫
–events 備份事件
–routines 備份存儲函數
–triggers 備份觸發器
–extended-insert 備份擴展的插入
–skip-extended-insert 關閉備份擴展的插入
–lock-all-tables 加鎖，這個是必須的
–flush-logs 刷新日志文件到磁盤中去，也是必須的
–master-data=n{0|1|2} 做復制的時候用到（2）
–no-data 只備份數據庫中的表結構
–where WHERE CLAUSE 備份指定的數據
mysql CREATE DATABASE luowei;
mysql SHOW DATABASES;// 查看所有的數據庫
+——————–+
| Database |
+——————–+
| information_schema |
| luowei |
| mysql |
| ndodb |
| test |
+——————–+
mysql USE luowei;
mysql CREATE TABLE st (
– ID INT UNIQUE AUTO_INCREMENT,
– Name VARCHAR(30) );
mysql SHOW TABLES; // 查看表
+——————+
| Tables_in_luowei |
+——————+
| st |
+——————+
mysql INSERT INTO st (Name) VALUE (a),(b),(c
mysql SELECT * FROM st; // 顯示表中的數據
+—-+——+
| ID | Name |
+—-+——+
| 1 | a |
| 2 | b |
| 3 | c |
+—-+——+
這是我的數據庫中原始的信息，因為等會要刪掉數據庫和表，所以先看一下
里面的數據，方便等會驗證；
[root@nfs ~]# mysqldump -uroot -p luowei st /root/st.sql // 開始
備份表 st，這里沒有使用任何參數的
Enter password:
OK，這個時候備份完了，由于 mysqldump 使用的是邏輯備份，所以備份下來
的是一些 create、insert 語句
我們可以打開看一下
[root@nfs ~]# cat /root/luoweidb | grep -v ^/ | grep -v ^$
— MySQL dump 10.11
—
— Host: localhost Database: luowei
— ——————————————————
— Server version 5.0.77
—
— Current Database: `luowei`
—
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `luowei` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `luowei`;
—
— Table structure for table `st`
—
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `st` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(30) default NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
—
— Dumping data for table `st`
—
LOCK TABLES `st` WRITE;
INSERT INTO `st` VALUES (1, a),(2, b),(3, c
UNLOCK TABLES;
— Dump completed on 2011-09-05 8:00:08
看到了吧，這里面是整個我剛做過的創建數據庫，創建表，插入數據的語句
；
接下來，我們做一下數據庫恢復：
[root@nfs ~]# mysql -uroot -e DROP DATABASE luowei; -p // 刪除
數據庫
Enter password:
[root@nfs ~]# mysql -uroot -e SHOW DATABASES; -p // 查看刪除后
的數據庫
Enter password:
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| ndodb |
| test |
+——————–+
我把 luowei 的數據庫給刪除了，現在將進行數據庫的恢復
[root@nfs ~]# mysql -uroot -p /root/luoweidb
Enter password:
[root@nfs ~]# mysql -e SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| hellodb |
| luowei |
| mysql |
| ndodb |
| test |
+——————–+
OK，數據庫是恢復過來了，接下來就是查看表中的信息是否完整了
[root@nfs ~]# mysql -e SELECT * FROM luowei.st -uroot -p
Enter password:
+—-+——+
| ID | Name |
+—-+——+
| 1 | a |
| 2 | b |
| 3 | c |
+—-+——+
Ok, 一起安好，呵呵，當然其他的參數就不做詳細介紹了，需要用到的話，
可以使用 HELP 命令尋求幫助。
下面介紹一個本機不用輸入 Mysql 密碼的方法：
#vim /root/.my.cnf
[client]
user = root
password = redhat
保存起來，下次對數據庫的操作就直接使用 mysql 命令了，不用在輸入密碼
了；
接下來的實驗我也就不輸入密碼了；

2. 使用 SELECT INTO OUTFILE 的方法進行備份
還是用上面的表，
[root@nfs ~]# mysql -e SELECT * FROM luowei.st
+—-+——+
| ID | Name |
+—-+——+
| 1 | a |
| 2 | b |
| 3 | c |
+—-+——+
如果想把 ID 小于等于 2 的備份出來，就可以使用這種方法備份了
mysql SELECT * INTO OUTFILE /tmp/st.bak FROM st WHERE ID
[root@nfs ~]# cat /tmp/st.bak
1 a
2 b
我們可以看到一個文本文件，類似于表，但是這種方法只備份數據本身，不
備份表結構，所以恢復的時候，就需要按照結構重新建立一個新表，然后在
恢復
mysql CREATE TABLE mt LIKE st; // 我這里新創建一個基于 st 表格式的新
表 mt，然后把剛備份的數據恢復到新表 mt 中，這也類是與恢復到原表了，呵
呵，小偷懶一下?。?！
mysql LOAD DATA INFILE /tmp/st.bak INTO TABLE mt;
mysql SELECT * FROM mt;
+—-+——+
| ID | Name |
+—-+——+
| 1 | a |
| 2 | b |
+—-+——+
ok 了，我們現在來總結一下這兩種方法，首先使用 mysqldump 備份的數據使
用邏輯備份的方式，備份的是一些對數據庫的操作的語句，恢復簡單；而使
用 SELECT INTO OUTFILE 這種可以備份指定的數據，有選擇性的備份數據，
但是在恢復的時候就要麻煩一點，還要創建表。

3. 基于 LVM 的快照備份：
操作步驟：
a. 確保數據文件在 LV 上
b. 對數據庫中的表施加讀鎖
c. 對數據所在的 LV 做快照
d. 釋放數據庫的讀鎖
e. 掛載快照卷，備份數據文件（對于 InnoDB 存儲引擎，同時備份事務日志
文件）
f. 刪除快照
使用 LVM 做快照備份的條件是：
SHOW VARIABLES LIKE log_binlog 處于 ON
SHOW VARIABLES LIKE sync_binlog 改為 1
SHOW VARIABLES LIKE innodb_support_xa 處于 ON 的狀態
LV 快照備份
#mysql
FLUSH TABLES WITH READ LOCK; // 加上讀鎖
# lvcreate -L 50M -n mysnap -s /dev/myvg/mydata // 創建快照
UNLOCK TABLES; // 解鎖
#mount /dev/myvg/mysnap /mnt // 掛載快照卷
#cd /mnt/data
#tar -jcf /root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-
bin.* // 備份數據并壓縮
#umount /mnt // 卸載快照
#lvremove –force /dev/myvg/mysnap // 移除快照

LV 恢復
cd /usr/local/mysql
#chown -R mysql:mysql . // 修改權限
#scripts/mysql_install_db –user=mysql –datadir=/mydata/data/ //
重新生成
#chown -R root .
#killall mysqld // 殺掉所有關于 mysql 的進程
#cd /mydata/data
#tar xf /root/mysql.tar.bz2 -C ./ // 把備份恢復到 mysql 的數據文件中
然后我們在登錄到 Mysql，就會發現原來的數據仍然存在了

4. 使用 binary 日志文件實現時間點的恢復
顯示當前正在使用的事務文件
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 330 | | |
+——————+———-+————–+——————+
mysql FLUSH LOGS; // 滾動日志文件
再查看正在使用的事務文件，然后記錄下這個 Position 就行了
由于二進制日志文件只記錄數據修改的信息，所以每次的修改對應的
Position 都會改變
現在我向表中插入一行數據
mysql INSERT INTO st (Name) VALUE (d
mysql INSERT INTO st (Name) VALUE (E
mysql SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000007 | 553 | | |
+——————+———-+————–+——————+
由于我們前面已經做了一個完全備份，所有我們現在可以備份從 330 開始的
數據了
# mysqlbinlog –start-position 330 mysql-bin.000007
/root/mysql.incr.sql // 備份增加的二進制文件
然后我們這個時候登錄到 mysql
mysql DROP DATABASE luowei;
然后恢復該數據庫
# service mysqld stop
# scripts/mysql_install_db –user=mysql –datadir=/mydata/data/
# cd /mydata/data/
# tar xf /root/mysql.incr.sql -C .
# mysql /root/mysql.incr.sql
# service mysqld start
登錄到數據庫，
就會發現有數據恢復正常