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.使用mysqldump來備份
　　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
　　登入到資料庫，
　　就會發現有資料恢複正常

 

      來自：http://www.hackbase.com/tech/2011-09-07/65217_1.html