MySQL中表的复制以及⼤型数据表的备份教程
表复制
mysql拷贝表操作我们会常常⽤到，下⾯就为您详细介绍⼏种mysql拷贝表的⽅式，希望对您学习mysql拷贝表⽅⾯能够有所帮助。
假如我们有以下这样⼀个表：
id  username  password
-----------------------------------
1    admin    *************
2    sameer  *************
3    stewart  *************
CREATE TABLE IF NOT EXISTS `admin` (
`id` int(6) unsigned NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`password` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
1. 下⾯这个语句会拷贝表结构到新表newadmin中。（不会拷贝表中的数据）
CREATE TABLE newadmin LIKE admin
2. 下⾯这个语句会拷贝数据到新表中。注意：这个语句其实只是把select语句的结果建⼀个表。所以newadmin这个表不会有主键，索引。CREATE TABLE newadmin AS
(
SELECT *
FROM admin
)
3. 如果你要真正的复制⼀个表。可以⽤下⾯的语句。
CREATE TABLE newadmin LIKE admin;
INSERT INTO newadmin SELECT * FROM admin;
4. 我们可以操作不同的数据库。
CREATE TABLE newadmin LIKE shop.admin;
CREATE wadmin LIKE shop.admin;
5. 我们也可以拷贝⼀个表中其中的⼀些字段。
CREATE TABLE newadmin AS
(
SELECT username, password FROM admin
)
6. 我们也可以讲新建的表的字段改名。
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin
)
7. 我们也可以拷贝⼀部分数据。
CREATE TABLE newadmin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
8. 我们也可以在创建表的同时定义表中的字段信息。
CREATE TABLE newadmin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)
MySQL⼤表备份
这⾥所说的⼤表是超过4G以上的表，我⽬前见到过最⼤为６０多Ｇ的单表，对于这种表每天⼀个全备可以说是⼀件很痛苦的事。
那么有没有办法，可以实现⼀个全备加增量的备份呢。
答案当然是有的。
在常规环境直可以⽤全备加binlog⼀同保存。
这种环境⼤多可以⽤⼀个Slave上进⾏备份操作。
思路：
先停⽌Slave的同步，刷新buffer，对于Innodb 如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零，然后在执⾏⼀次flush tables;
就可以cp了。如果是Dump出来可以这这样做。
这个⽅案⽬前来看也是⽐较完美的，但⼀个并发⼒度⼤的应⽤⼀天的Binlog有可能能达到５０G－６０G，这样的系统开Binlog可以说是对系统的ＩＯ性能及整体性能都有早影响。
另⼀种⽅案就是基于表的上数据的罗辑变化进⾏备份。
主体思想：全备加逻辑备份。
逻辑备份：当有数据插⼊时，利⽤触发器同时写⼊另⼀个表，当数据更新时，我们同时记录⼀下，更新
后的数据情况到另⼀个表。
当有删除操作时，只需要记录⼀下，删除的主建ＩＤ就⾏。
例⼦：
要备份的表：
CREATE TABLE `wubx` ( `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',
UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
对于这个表我们需要建⼀个记录有新数据变化的表为：
mysql> create table wubx_ii like wubx;
Query OK, 0 rows affected (0.00 sec)
mysql> create table wubx_uu like wubx;
Query OK, 0 rows affected (0.00 sec)
mysql> create table wubx_dd ( id int(11));
Query OK, 0 rows affected (0.00 sec)
建⽴相应的触发程器
记录insert的操作：
delimiter //
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,ated; end//
记录update的操作：
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,ated; end// 记录删除的操作：
create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values(old.id); end//
delimiter ;
python安装教程非常详细操作：
先备份原始表wubx⾥的数据：
进⾏：
insert into wubx values(”,1,10,1,1198464252);
insert into wubx values(”,1,11,1,1198464252);
insert into wubx values(”,1,2,1,1198464252);
insert into wubx values(”,2,10,1,1198464252);
insert into wubx values(”,2,12,1,1198464252);
insert into wubx values(”,3,12,1,1198464252);
update wubx set dir_id=5 where user_id=3;
update wubx set dir_id=4 where user_id=3;
delete from wubx where user_id=2 and friend_id=12;
现在要实现增量备份：
取出insert的操作：
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的操作：
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的操作：
mysql -e “select concat(‘delete from wubx where id=',id,';') from wubx_dd”>>backup_dd.sql
这样利⽤这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这⾥不演⽰。
这个操作最好⽤⼀个程序完成，当取完罗辑备份后，做⼀个标记点去清楚备份完的数据，以保证，逻辑记录表⾥的数据量⽐较少是正确的。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。