MySQL单个大表增量备份+索引+结构变更简单方法

由于使用了低版本mysql，且是myisam引擎，在进行一个千万级数据表加索引时过于缓慢，安全起见采用增量模式备份新表，通过重命名方式迁移结构到新的备份表。

测试版本，目前针对的是MySQL低版本模式进行的测试，与此版本无关。

以下操作皆可以直接用于测试，是编写文章过程实际SQL

思路，依据原始表，创建三个insert，update，delete表，依赖主键ID，并创建三个触发器，然后全量备份原始表为一个新表，因为原始表数据的增删改都会记录在三个表，故单表SQL的操作记录可以直接拿到。这时选一个时间，或做好脚本，暂停原始表写入，导出触发器创建的insert，update，delete表记录，重新导入备份SQL到备份表中，原始表和备份表改名实现大表操作如加索引，字段等模式。

1.创建数据库

create database test_db;
use test_db;

2.创建原始表和insert，update，delete表

创建原始表，这个表就是我们要备份的表了

# 原始表
CREATE TABLE `back_test` ( 
  `id` int(11) NOT NULL auto_increment,   
  `user_id` int(11) NOT NULL default 0,   
  `friend_id` int(11) NOT NULL default 0,   
  `dir_id` int(11) NOT NULL default 0,   
  `created` int(11) NOT NULL default 0,   
  PRIMARY KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入记录表
create table back_test_insert like back_test;
#更新记录表
create table back_test_update like back_test;
#删除记录表
create table back_test_delete ( `id` int(11));

3.创建数据触发器

SHOW TRIGGERS;

delimiter //  
create trigger back_test_insert after insert on back_test for each row begin insert into back_test_insert set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//  

create trigger back_test_update after update on back_test for each row begin replace into back_test_update set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//  

create trigger back_test_delete after delete on back_test for each row begin insert into back_test_delete values(old.id); end//  

delimiter ; 

4.加入测试数据

insert into back_test values(null,1,10,1,1198464252);  
insert into back_test values(null,1,11,1,1198464252);  
insert into back_test values(null,1,2,1,1198464252);  
insert into back_test values(null,2,10,1,1198464252);  
insert into back_test values(null,2,12,1,1198464252);  
insert into back_test values(null,3,12,1,1198464252);  
update back_test set dir_id=5 where user_id=3;  
update back_test set dir_id=4 where user_id=3;  
delete from back_test where user_id=2 and friend_id=12; 

5.导出触发器记录备份SQL

退出mysql，使用linux命令行执行备份

mysql -uhomestead -psecret -e "use test_db;select concat('replace into back_test set id=',id,',user_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,';') from back_test_insert;">>backup_ii.sql  

mysql -uhomestead -psecret -e "use test_db;select concat('update back_test set user_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,' where id=',id,';') from back_test_update;">>backup_uu.sql  

mysql -uhomestead -psecret -e "use test_db;select concat('delete from back_test where id=',id,';') from back_test_delete">>backup_dd.sql  

6.迁移备份

查看备份文件结构，依据导出的备份文件，暂停数据库写入，导入insert，update，delete表的SQL到备份表，重命名原始表和备份表，实现数据结构或索引更改。

---

参考文献：MySQL大表备份的简单方法