Mysql的備份和恢復

Mysql的備份分類：
mysql的資料備份：
 邏輯備份、實體備份
mysql依照資料集分類：
 完全備份
 增量備份
 差異備份
一般這幾種都是組合起來使用的，例如完全備份可以一週進行一次，增量備
份和差異備份可以一天備份一次，但是這要根據企業的業務邏輯而定，所以
合適的選擇，將會在資料恢復的時候將損失降到最低。
mysql依照伺服器是否停機分類：
 冷備份：服務完全停止
 溫備份：服務不關閉，但是在備份的時候，其他的事務只有查詢的操作
 熱備份:服務在線，不影響讀取和寫入（但是非交易儲存引擎的很難實現熱備，但
是可以使用LVM實現幾乎熱備的功能）
需要備份的東西：
 1.資料檔案
 2.日誌檔案（交易日誌，二進位日誌）
 3.存儲流程，儲存函數，觸發器
 4.設定檔
   事件重播（撤銷）提供基礎
 2.事件事務:
  事件重播（撤銷）提供基礎
 2.事件事務:
 的日誌檔案
  1.the error log 錯誤日誌
     錯誤訊息，伺服器程序啟動或停止產生的訊息，主從中新增複製的時
候，也會記錄
     主設定檔啟動錯誤，
候，也會記錄
    log/mysqld.err（確保對mysqld有寫入的權限）
  2.the binary log 二進位日誌
     提供增量備份
     基礎預設在資料檔案中）
     log_bin_index=/data/logs/relay/binarylog 定義二進位檔案的索引
位置
  3.the general query log PATH
  4.the slow query log 慢速查詢日誌
      slow_query_log=ON
      slow_query_log_file=/PATH
    日誌，但在mysqld腳本中開啟了錯誤日誌(the
error log)

mysql常用的備份工具：
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy (MyISAM儲存引擎幾乎是冷備備份，看著我名字的熱備簡單的資料庫進行示範資料的備份和還原的過程：
1.使用mysqldump來備份
 mysqldump的備份是一個邏輯備份；
 備份的格式如下;
 #mysqldump db_name [table_name] > /PATHmy
 #mysqldump db_name [table_name] > /PATHmy
 還有很多選項：
  --all-databases 備份所有的資料庫
  --events 備份事件
  --routines 備份儲存函數
  --triggers 備份觸發器
  --extended-insert  擴充擴充功能的插入
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         | test               |
+-------------------- +
mysql> USE luowei;
mysql> CREATE TABLE st (
    -> ID INT UNIQUE AUTO_INCREMENT,
    -> Name VARCHAR(30) );
值> ------------+
| 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 `luowtable for
-- ？ `st`
--
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;set_client;
SET character_set_client = utf8;
`Name` varchar(30) default NULL,
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_clicliclient = sved. 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 -p //查看刪除後
的資料庫
Enter password:
+--------------------+
| Database           |
+------- -------------+
| information_schema |
| mysql              |
| ndodb        | test               |
+--------------------+
我刪除了luowei的資料庫給刪除了，現在將進行資料庫的復原
[root@nfs ~]# mysql -uroot -p Enter password:
[root@nfs ~]# mysql -e "SHOW DATABASES;"
+------------------ --+
| Database           |
+--------------------+
| formation_schema |
| hellodb                      |
| 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
>FLUSH3c>FLUS READP. 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>sql> INSERT INTO st (Name) VALUE ('E');
mysql>sql> 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;
然後恢復該資料庫
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 .
# my
#. root/mysql.incr.sql
# service mysqld start

登入資料庫，

就會發現有資料復原正常

🎜🎜