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>