MySQL大表备份的简单方法

数据库 MySQL
MySQL数据库的表备份应该说是我们最常用的操作之一了,但是面对超过4G以上的表,备份的策略就要我们仔细去思考了。

MySQL大表备份是一个我们常见的问题,下面就为您介绍一个MySQL大表备份的简单方法,希望对您学习MySQL大表备份方面能有所帮助。

这里所说的大表是超过4G以上的表,我目前见到过***为60多G的单表,对于这种表每天一个全备可以说是一件很痛苦的事。
那么有没有办法,可以实现一个全备加增量的备份呢。
答案当然是有的。

在常规环境直可以用全备加binlog一同保存。
这种环境大多可以用一个Slave上进行备份操作。

思路:
先停止Slave的同步,刷新buffer,对于Innodb 如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零,然后在执行一次flush tables;
就可以cp了。如果是Dump出来可以这这样做。

这个方案目前来看也是比较***的,但一个并发力度大的应用一天的Binlog有可能能达到50G-60G,这样的系统开Binlog可以说是对系统的IO性能及整体性能都有早影响。

另一种方案就是基于表的上数据的罗辑变化进行备份。
主体思想:全备加逻辑备份。
逻辑备份:当有数据插入时,利用触发器同时写入另一个表,当数据更新时,我们同时记录一下,更新后的数据情况到另一个表。
当有删除操作时,只需要记录一下,删除的主建ID就行。

例子:
要备份的表:

  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  

这样利用这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这里不演示。

这个操作***用一个程序完成,当取完罗辑备份后,做一个标记点去清楚备份完的数据,以保证,逻辑记录表里的数据量比较少是正确的。
 


 

【编辑推荐】

MySQL分表处理的实现方法

MySQL授权表使用示例

MySQL多表删除的实现

MySQL独立表空间的优缺点

MySQL MyISAM表结构的恢复

责任编辑:段燃 来源: 互联网
相关推荐

2011-04-06 09:09:17

MySQL数据库备份

2009-08-19 13:06:37

C#操作注册表

2010-05-17 10:24:44

MySQL数据库

2010-08-06 13:23:58

NFS配置

2010-07-20 14:07:31

更改TELNET端口

2010-06-08 17:46:31

OpenSUSE安装

2010-09-30 14:01:38

2010-03-15 14:10:34

ubuntu系统

2009-08-12 16:47:36

C#转换农历

2009-08-24 14:14:56

C#导入Excel表

2010-03-03 13:56:43

Linux ubant

2009-02-05 14:48:51

跳出循环多层循环编程

2023-03-16 08:14:57

2011-05-18 13:37:26

LINQ

2011-03-02 10:01:15

2011-03-01 09:53:18

卸载PureFTPd

2010-08-25 20:37:05

DHCP服务器

2023-08-26 21:06:25

2010-08-27 10:40:58

Linux DHCP服

2011-05-07 10:07:41

Windows 8激活
点赞
收藏

51CTO技术栈公众号