How to Copy All of the Tables from One Database to Another

Under normal circumstances, one of the easiest ways to copy the contents of one MySQL database to another is to use phpMyAdmin to dump the database to a file. You can then import the contents into your destination database. This usually works, but I recently encountered a situation where phpMyAdmin could not handle a particularly large database. Therefore, I had to drop to the command line and do it manually.

Although manipulating MySQL from the command line can feel intimidating, it is not difficult to do, and you can actually complete many tasks faster by using it. To begin, you will need to make sure that you can either log in as a user who has privileges in both databases or log in as root. Then, follow these instructions:

1. Dump the source database to an sql file

mysqldump -u root -p[root-password] [database] > [sqlfile].sql

As an example, suppose you wanted to dump a database called “serverschool”.

mysqldump -u root -pfakepassword serverschool > serverschool.sql

2. Now that you have a backup of your database, all you have to do is import the contents to your destination database.

mysql -u root -p[root-password] [database] < [sqlfile].sql

(Notice that the arrow is a “less than” sign rather than a “greater than” sign)

We will use the same “serverschool” database as an example, with the destination database called “newserverschool”.

mysql -u root -pfakepassword newserverschool < serverschool.sql

Once the import is complete, you will have two databases that are essentially identical, except for their name.