&nbsp>&nbsp
&nbsp>&nbsp
&nbsp>&nbsp
MySQL中表的复制以及大型数据表的备份教程
摘要：表复制mysql拷贝表操作我们会常常用到,下面就为您详细介绍几种mysql拷贝表的方式,希望对您学习mysql拷贝表方面能够有所帮助。假如我们有以下这样一个表:idusernamepassword-----------------------------------1admin*************2sameer*************3stewart*************CREATETABLEIFNOTEXISTS`admin`(`id`int(6)unsigne
表复制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 INSERT INTO newadmin SELECT * FROM
4. 我们可以操作不同的数据库。
CREATE TABLE newadmin LIKE shop. CREATE TABLE newshop.newadmin LIKE shop.
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以上的表,我目前见到过最大为60多G的单表,对于这种表每天一个全备可以说是一件很痛苦的事。
那么有没有办法,可以实现一个全备加增量的备份呢。
答案当然是有的。
在常规环境直可以用全备加binlog一同保存。
这种环境大多可以用一个Slave上进行备份操作。
先停止Slave的同步,刷新buffer,对于Innodb 如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零,然后在执行一次
就可以cp了。如果是Dump出来可以这这样做。
这个方案目前来看也是比较完美的,但一个并发力度大的应用一天的Binlog有可能能达到50G-60G,这样的系统开Binlog可以说是对系统的IO性能及整体性能都有早影响。
另一种方案就是基于表的上数据的罗辑变化进行备份。
主体思想:全备加逻辑备份。
逻辑备份:当有数据插入时,利用触发器同时写入另一个表,当数据更新时,我们同时记录一下,更新后的数据情况到另一个表。
当有删除操作时,只需要记录一下,删除的主建ID就行。
要备份的表:
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_ Query OK, 0 rows affected (0.00 sec) mysql& create table 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,created=new. 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,created=new. end//
记录删除的操作:
create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values(old.id); end//
先备份原始表wubx里的数据:
insert into wubx values(”,1,10,1,); insert into wubx values(”,1,11,1,); insert into wubx values(”,1,2,1,); insert into wubx values(”,2,10,1,); insert into wubx values(”,2,12,1,); insert into wubx values(”,3,12,1,); 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_”&&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_”&&backup_uu.sql
取出delete的操作:
mysql -e “select concat(‘delete from wubx where id=',id,';') from wubx_dd”&&backup_dd.sql
这样利用这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这里不演示。
这个操作最好用一个程序完成,当取完罗辑备份后,做一个标记点去清楚备份完的数据,以保证,逻辑记录表里的数据量比较少是正确的。
以上是的内容，更多
的内容，请您使用右上方搜索功能获取相关信息。
若你要投稿、删除文章请联系邮箱：zixun-group@service.aliyun.com,工作人员会在五个工作日内给你回复。
云服务器 ECS
可弹性伸缩、安全稳定、简单易用
&40.8元/月起
预测未发生的攻击
&24元/月起
为您提供0门槛上云实践机会
你可能还喜欢
你可能感兴趣
阿里云教程中心为您免费提供
MySQL中表的复制以及大型数据表的备份教程相关信息，包括
的信息，所有MySQL中表的复制以及大型数据表的备份教程相关内容均不代表阿里云的意见！投稿删除文章请联系邮箱：zixun-group@service.aliyun.com，工作人员会在五个工作日内答复
售前咨询热线
服务与支持
资源和社区
关注阿里云
InternationalMySql树状结构的表数据平移复制到另一张表有什么方案？
[问题点数：100分]
MySql树状结构的表数据平移复制到另一张表有什么方案？
[问题点数：100分]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2016年12月 其他数据库开发大版内专家分月排行榜第二
2005年 总版技术专家分年内排行榜第一2004年 总版技术专家分年内排行榜第一
2006年 总版技术专家分年内排行榜第六2003年 总版技术专家分年内排行榜第八
2005年 总版技术专家分年内排行榜第一2004年 总版技术专家分年内排行榜第一
2006年 总版技术专家分年内排行榜第六2003年 总版技术专家分年内排行榜第八
2016年12月 其他数据库开发大版内专家分月排行榜第二
2016年10月优秀大版主2016年8月论坛优秀版主2015年4月优秀版主2014年11月论坛优秀版主
2016年4月 荣获微软MVP称号2015年4月 荣获微软MVP称号2014年4月 荣获微软MVP称号2013年4月 荣获微软MVP称号2009年1月 荣获微软MVP称号2012年4月 荣获微软MVP称号2011年4月 荣获微软MVP称号2010年4月 荣获微软MVP称号
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
匿名用户不能发表回复！|mysql&中如何在同一张表中复制记录
中如何在同一张表中复制记录
复制完全相同的记录(条件是表结构中没有auto_increment,和uniq字段）
mysql& select *
+------------+---------------+--------+---------------------+-------+------------+------------+
| last_name& |
first_name&&& |
city&&&&&&&&&&&&&&&
birth&&&&&
death&&&&&
+------------+---------------+--------+---------------------+-------+------------+------------+
| Washington |
George&&&&&&&
| NULL&& |
Wakefield&&&&&&&&&&
Adams&&&&&
John&&&&&&&&&
| NULL&& |
Braintree&&&&&&&&&&
| Jefferson& |
Thomas&&&&&&&
| NULL&& | Albemarle
County&&& |
| Madison&&& |
James&&&&&&&&
| NULL&& | Port
Conway&&&&&&&&
+------------+-------------+------+-----+---------+-------+
Field&&&&&
Type&&&&&&&
| Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| last_name& | varchar(15) |
NO&& | MUL |
| first_name | varchar(15) | NO&&
suffix&&&&
| varchar(5)& | YES&
city&&&&&&
| varchar(20) | NO&&
state&&&&&
| varchar(2)& |
birth&&&&&
date&&&&&&&
death&&&&&
date&&&&&&&
+------------+-------------+------+-----+---------+-------+
在这种情况下要在表中复制一条Washington& George
的记录，输入以下命令即可：
insert into president select * from president where
last_name='Washington';
例子2：新增部分字段相同的记录
+----+-------------+-------+-------------+----------+------------+---------------------+
| id | enable_flag | types |
account&&&&
| password | location&& |
reg_date&&&&&&&&&&&
+----+-------------+-------+-------------+----------+------------+---------------------+
|&&&&&&&&&&
abcd&&&&&&&
| 1234567&
|&&&&&&&&&&&
00:00:00 |
|&&&&&&&&&&
2 | anonymous&& |
654123&& |
17:29:04 |&
其表结构如下：
+-------------+-------------+------+-----+---------------------+----------------+
Field&&&&&&
Type&&&&&&&
| Null | Key |
Default&&&&&&&&&&&&
Extra&&&&&&&&&
+-------------+-------------+------+-----+---------------------+----------------+
id&&&&&&&&&
int(4)&&&&&
| NO&& | PRI |
NULL&&&&&&&&&&&&&&&
| auto_increment |
| enable_flag | smallint(2) |
0&&&&&&&&&&&&&&&&&&
|&&&&&&&&&&&&&&&
types&&&&&&
| smallint(2) | NO&&
0&&&&&&&&&&&&&&&&&&
|&&&&&&&&&&&&&&&
account&&&&
| varchar(50) | NO&& | UNI
|&&&&&&&&&&&&&&&&&&&&
|&&&&&&&&&&&&&&&
| password&&& |
varchar(50) | NO&&
|&&&&&&&&&&&&&&&&&&&&
|&&&&&&&&&&&&&&&
| location&&& |
varchar(10) | NO&&
|&&&&&&&&&&&&&&&&&&&&
|&&&&&&&&&&&&&&&
| reg_date&&& |
datetime&&& |
|&&&&&&&&&&&&&&&
&------------+-------------+------+-----+---------------------+----------------+
若想通过新增一条和manager
表中的anonymous这条记录，其它字段内容相同，但名字不同的记录(account为test)。则得用以下方法：
因为manager表的
id字段是auto_increment,而且account字段是具有唯一性的（UNI).所以我们得具体指定这两个字段的值。
insert into manager select '22',enable_flag,types,'test',password,
location,reg_date from manager where
account='anonymous';
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。MySQL拷贝表结构、表数据总结 _ PHP程序员，雷雪松的博客
在的过程中，经常需要拷贝表复制表。有时需要只需要复制拷贝表的结构，有时需要拷贝复制表的结构和数据。今天就详细的总结一下MySQL拷贝表结构、表数据的方式和SQL语句。
1、在MySQL数据库中创建一个用户表user作为测试表，包含三个最基础字段：主键id，姓名name和年龄age。
123456CREATE TABLE USER(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age tinyint UNSIGNED,
PRIMARY KEY (id)
)ENGINE=InnoDB CHARSET=utf8;
2、向user表中插入一条数据。
1INSERT INTO USER VALUES(&&，&雷雪松&,&20&);
3、只复制拷贝表结构，不复制拷贝表数据。
1CREATE TABLE newuser LIKE USER;
4、复制表结构和数据，但是新的表结构不会有主键和索引。
1CREATE TABLE newuser AS (SELECT * FROM USER);
5、真正的复制表结构和完整数据，先复制表结构，再插入所有数据。
12CREATE TABLE newuer LIKE USER;
INSERT INTO newuer (SELECT * FROM USER);