How to backup a table in MySQL database and copy to another database

Sometime you want to backup a table and all its contents in MySQL Database and then you might want to restore or copy the table to different MySQL database.
To do this you need to dump the table first and then restore to the target database.
Assume you have two database, db01 and db02. Inside the db01, there are table01 and table02 whereas inside the db02 there are table03 and table04.
In this example you want to copy table01 in db01 to the db02. Here are the steps:

1. Dump table01 using mysqldump command and save as table01.sql

$ mysqldump -u root -p db01 table01 > table01.sql
Enter password:

You will get a file table01.sql generated by the command above.

2. Restore or copy the table01.sql to db02 database using command below:

$ mysql -u root -p db02 < table01.sql
Enter password:

Verify that the table01 has been copied to the db02.

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 207886
Server version: 5.0.67 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> use db02;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_db02        |
+-----------------------+
| table01               |
| table03               |
| table04               |
+-----------------------+
3 rows in set (0.00 sec)
 
mysql>

Incoming search terms for this post:

Share
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Related Posts

By: Fuad NAHDI Date: December 10, 2009
5:44 am
Categories: MySQL
Post tags: ,
Our Sponsors Internet Faxing Reviews HP Pavilion Notebooks Free Collaboration Tools
Feedback
Related posts:

This website uses IntenseDebate comments, but they are not currently loaded because either your browser doesn't support JavaScript, or they didn't load fast enough.

Leave a Comment