MySQL中表的复制以及大型数据表的备份教程
表复制
mysql拷贝表操作我们会常常用到，下面就为您详细介绍几种mysql拷贝表的方式，希望对您学习mysql拷贝表方面能够有所帮助。
假如我们有以下这样一个表：
idusernamepassword ----------------------------------- 1admin************* 2sameer************* 3stewart*************
CREATETABLEIFNOTEXISTS`admin`( `id`int(6)unsignedNOTNULLauto_increment, `username`varchar(50)NOTNULLdefault'', `password`varchar(100)defaultNULL, PRIMARYKEY(`id`) )ENGINE=MyISAMDEFAULTCHARSET=latin1AUTO_INCREMENT=4;
1.下面这个语句会拷贝表结构到新表newadmin中。（不会拷贝表中的数据）
CREATETABLEnewadminLIKEadmin
2.下面这个语句会拷贝数据到新表中。注意：这个语句其实只是把select语句的结果建一个表。所以newadmin这个表不会有主键，索引。
CREATETABLEnewadminAS ( SELECT* FROMadmin )
3.如果你要真正的复制一个表。可以用下面的语句。
CREATETABLEnewadminLIKEadmin; INSERTINTOnewadminSELECT*FROMadmin;
4.我们可以操作不同的数据库。
CREATETABLEnewadminLIKEshop.admin; CREATETABLEnewshop.newadminLIKEshop.admin;
5.我们也可以拷贝一个表中其中的一些字段。
CREATETABLEnewadminAS ( SELECTusername,passwordFROMadmin )
6.我们也可以讲新建的表的字段改名。
CREATETABLEnewadminAS ( SELECTid,usernameASuname,passwordASpassFROMadmin )
7.我们也可以拷贝一部分数据。
CREATETABLEnewadminAS ( SELECT*FROMadminWHERELEFT(username,1)='s' )
8.我们也可以在创建表的同时定义表中的字段信息。
CREATETABLEnewadmin ( idINTEGERNOTNULLAUTO_INCREMENTPRIMARYKEY ) AS ( SELECT*FROMadmin )
MySQL大表备份
这里所说的大表是超过4G以上的表,我目前见到过最大为６０多Ｇ的单表，对于这种表每天一个全备可以说是一件很痛苦的事。
那么有没有办法，可以实现一个全备加增量的备份呢。
答案当然是有的。
在常规环境直可以用全备加binlog一同保存。
这种环境大多可以用一个Slave上进行备份操作。
思路：
先停止Slave的同步，刷新buffer，对于Innodb如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零，然后在执行一次flushtables;
就可以cp了。如果是Dump出来可以这这样做。
这个方案目前来看也是比较完美的，但一个并发力度大的应用一天的Binlog有可能能达到５０G－６０G，这样的系统开Binlog可以说是对系统的ＩＯ性能及整体性能都有早影响。
另一种方案就是基于表的上数据的罗辑变化进行备份。
主体思想：全备加逻辑备份。
逻辑备份：当有数据插入时，利用触发器同时写入另一个表，当数据更新时，我们同时记录一下，更新后的数据情况到另一个表。
当有删除操作时，只需要记录一下，删除的主建ＩＤ就行。
例子：
要备份的表：
CREATETABLE`wubx`(`id`int(11)NOTNULLauto_increment, `user_id`int(11)NOTNULLdefault'0', `friend_id`int(11)NOTNULLdefault'0', `dir_id`int(11)NOTNULLdefault'0', `created`int(11)NOTNULLdefault'0', UNIQUEKEY`id`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;
对于这个表我们需要建一个记录有新数据变化的表为：
mysql>createtablewubx_iilikewubx; QueryOK,0rowsaffected(0.00sec) mysql>createtablewubx_uulikewubx; QueryOK,0rowsaffected(0.00sec) mysql>createtablewubx_dd(idint(11)); QueryOK,0rowsaffected(0.00sec)
建立相应的触发程器
记录insert的操作：
delimiter// createtriggerwubx_iiafterinsertonwubxforeachrowbegininsertintowubx_iisetid=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created;end//
记录update的操作：
createtriggerwubx_uuafterupdateonwubxforeachrowbeginreplaceintowubx_uusetid=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created;end//
记录删除的操作：
createtriggerwubx_ddafterdeleteonwubxforeachrowbegininsertintowubx_ddvalues(old.id);end// delimiter;
操作：
先备份原始表wubx里的数据：
进行：
insertintowubxvalues(”,1,10,1,1198464252); insertintowubxvalues(”,1,11,1,1198464252); insertintowubxvalues(”,1,2,1,1198464252); insertintowubxvalues(”,2,10,1,1198464252); insertintowubxvalues(”,2,12,1,1198464252); insertintowubxvalues(”,3,12,1,1198464252); updatewubxsetdir_id=5whereuser_id=3; updatewubxsetdir_id=4whereuser_id=3; deletefromwubxwhereuser_id=2andfriend_id=12;
现在要实现增量备份：
取出insert的操作：
mysql-e”selectconcat(‘replaceintowubxsetid=',id,',user_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,';')fromwubx_ii;”>>backup_ii.sql
取出update的操作：
mysql-e”selectconcat(‘updatewubxsetuser_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,'whereid=',id,';')fromwubx_uu;”>>backup_uu.sql
取出delete的操作：
mysql-e“selectconcat(‘deletefromwubxwhereid=',id,';')fromwubx_dd”>>backup_dd.sql
这样利用这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这里不演示。
这个操作最好用一个程序完成，当取完罗辑备份后，做一个标记点去清楚备份完的数据，以保证，逻辑记录表里的数据量比较少是正确的。