copy table from one database to another, maintaining table's structure and renaming it too

I want to copy a table (say tbl_1) from one database (say source_db) to another database (say target_db), with following things in consideration:

• Structure of table should be preserved, including primary key and auto-increment key

• While creating a copy of tbl_1, I need to rename i开发者_开发问答t to cpy_tbl_1

How it is possible using query?

P.S. I know there will be many similar questions like mine, but I have those special considerations too.

CREATE TABLE db_target.cloned_table 
SELECT * 
FROM db_source.source_table;

With the previous sentence the table will be created with just the fields and it's types, but no keys, constraints, engine will be set. You can specify them manually in the same sentence like this:

CREATE TABLE db_target.cloned_table (
  a INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (a), KEY(b)
) ENGINE=MyISAM 
SELECT b,c FROM db_source.source_table;

mysql create table doc

you can attain this as follows;

First create the target_db

mysql > create database target_db;

then use it

mysql > use target_db;

then create the structure of the tb1 with name cpy_tbl_1

which is done as

mysql> create table cpy_tbl_1 like source_db.tb1;

then just copy the data.

mysql > insert into cpy_tbl_1 select * from source_db.tb1;

and check results

if you are able to use shell scripting then mysqldump is a powerful and flexible option... especially if you can use a little sed to do search and replaces.

simple version:

mysqldump $SOURCE $TABLE | mysql $TARGET