Copying tables in MySQL

There are multiple ways this can be done. I will mention two here.

Method 1

In MySQL, run these statements:

/*This will create table2 with the schema of table1*/
CREATE TABLE `table2` LIKE `table1`

/*This will copy the data from table1 to table2*/
INSERT INTO `table2` SELECT * FROM `table1`

Method 2

On the command line, run these commands:

# Create a dump from MySQL.
# The -d flag will only copy the structure/schema of the tables. If you want
# to copy the data along with the schema then dont use -d flag
# In here, two MySQL commands are piped so the output from mysqldump is sent 
# to mysql command
mysqldump -d -u username -pPassword  db_name | mysql -u username -pPassword db_new_name 

By Moazzam

Moazzam is a software developer working in the big apple. He has over a decade of experience in development. While most of his work involves developing web applications, he does development for Android and Windows Mobile in his spare time.

Leave a Reply

Your email address will not be published. Required fields are marked *