MySQL大表備份的簡單方法

標籤：http   for   資料   ar   art   問題   log   new   

MySQL大表備份是一個我們常見的問題，下面就為您介紹一個MySQL大表備份的簡單方法，希望對您學習MySQL大表備份方面能有所協助。

這裡所說的大表是超過4G以上的表,我目前見到過最大為６０多Ｇ的單表，對於這種表每天一個全備可以說是一件很痛苦的事。
那麼有沒有辦法，可以實現一個全備加增量的備份呢。
答案當然是有的。

在常規環境直可以用全備加binlog一同儲存。
這種環境大多可以用一個Slave上進行備份操作。

思路：
先停止Slave的同步，重新整理buffer，對於Innodb 如果想直接拷貝還需要把innodb_max_dirty_pages_pct這個值置為零，然後在執行一次flush tables;
就可以cp了。如果是Dump出來可以這這樣做。

這個方案目前來看也是比較完美的，但一個並發力度大的應用一天的Binlog有可能能達到５０G－６０G，這樣的系統開Binlog可以說是對系統的ＩＯ效能及整體效能都有早影響。

另一種方案就是基於表的上資料的羅輯變化進行備份。
主體思想：全備加邏輯備份。
邏輯備份：當有資料插入時，利用觸發器同時寫入另一個表，當資料更新時，我們同時記錄一下，更新後的資料情況到另一個表。
當有刪除操作時，只需要記錄一下，刪除的主建ＩＤ就行。

例子：
要備份的表：

1. CREATE TABLE `wubx` (  `id` int(11) NOT NULL auto_increment,   
2. `user_id` int(11) NOT NULL default ‘0‘,   
3. `friend_id` int(11) NOT NULL default ‘0‘,   
4. `dir_id` int(11) NOT NULL default ‘0‘,   
5. `created` int(11) NOT NULL default ‘0‘,   
6. UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

對於這個表我們需要建一個記錄有新資料變化的表為：

1. mysql> create table wubx_ii like wubx;  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> create table wubx_uu like wubx;  
5. Query OK, 0 rows affected (0.00 sec)  
6. mysql> create table wubx_dd ( id int(11));  
7. Query OK, 0 rows affected (0.00 sec)  
8.  

建立相應的觸發程器

1. 記錄insert的操作：  
2. delimiter //  
3. create trigger wubx_ii after insert on wubx for each row begin insert into wubx_ii set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//  
4. 記錄update的操作：  
5. create trigger wubx_uu after update on wubx for each row begin replace into wubx_uu set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//  
6. 記錄刪除的操作：  
7. create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values(old.id); end//  
8.  
9. delimiter ;  
10.  

操作：
先備份原始表wubx裡的資料：
進行：

1. insert into wubx values(”,1,10,1,1198464252);  
2. insert into wubx values(”,1,11,1,1198464252);  
3. insert into wubx values(”,1,2,1,1198464252);  
4. insert into wubx values(”,2,10,1,1198464252);  
5. insert into wubx values(”,2,12,1,1198464252);  
6. insert into wubx values(”,3,12,1,1198464252);  
7. update wubx set dir_id=5 where user_id=3;  
8. update wubx set dir_id=4 where user_id=3;  
9. delete from wubx where user_id=2 and friend_id=12;  

現在要實現增量備份：
取出insert的操作：

1. mysql -e ” select concat(‘replace into wubx set id=’,id,’,user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’;‘) from wubx_ii;”>>backup_ii.sql  

取出update的操作：

1. mysql -e ” select concat(‘update wubx set user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’ where id=’,id,’;‘) from wubx_uu;”>>backup_uu.sql  

取出delete的操作：

1. mysql -e “select concat(‘delete from wubx where id=’,id,’;‘) from wubx_dd”>>backup_dd.sql  

這樣利用這些邏輯的備份加是完畢備份恢複到當前復原點就很容易了。這裡不示範。

這個操作最好用一個程式完成，當取完羅輯備份後，做一個標記點去清楚備份完的資料，以保證，邏輯記錄表裡的資料量比較少是正確的。