Mysql的數據備份和恢復

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

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 <=2;
[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
登錄到數據庫，
就會發現有數據恢復正常

到此，關于“Mysql的數據備份和恢復”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！