To create a copy of an existing MySQL database, you need to make use of MySQL internal tools called mysqldump.
The mysqldump tool is usually installed when you install the MySQL server. It can be accessed from your computer’s terminal or command line using the mysqldump command.
You can use the following command to check if mysqldump exists on your computer:
$ which mysqldump
/usr/local/mysql/bin/mysqldump
The result above shows that mysqldump is installed on the bin/ folder of the MySQL installation folder.
Just like its name, mysqldump is used to “dump” one or more MySQL databases as a .sql file. The dump result can then be imported into MySQL server to create a copy or a backup of the original database.
For example, suppose you have a source database named school_db and you want to create a copy named school_db_copy as a backup.
First, you need to create an SQL dump file of the existing database using the mysqldump command as follows:
mysqldump --user=[username] --password=[password] school_db > dump.sql
Replace [username] and [password] above with your actual MySQL username and password.
Now that the dump.sql file has been created, you need to create the target database where the dump.sql file content will be imported.
You can log in to your MySQL server and create a new database using CREATE DATABASE statement as follows:
CREATE DATABASE school_db_copy;
Alternatively, you can use the mysqladmin command line tool which is very similar to mysqldump tool.
While mysqldump tool allows you to create a dump file of MySQL databases, the mysqladmin tool allows you to perform administrative tasks like creating and dropping databases from the command line.
Use the create command to create your database as shown below:
mysqladmin --user=[username] --password=[password] create school_db_copy
Now that you have a new database, all you need to do is import the dump.sql file with the mysql command as follows:
mysql --user=[username] --password=[password] school_db_copy < dump.sql
Now the school_db_copy database should have the exact same tables and data as school_db.
Copy MySQL database into another server
The MySQL dump file is essentially a bunch of statements to create tables and insert values derived from an existing database.
You can also use the file to copy a database from one MySQL server into another server by adding the --host option to the commands.
First, dump the database from your machine with mysqldump command:
mysqldump --user=[username] --password=[password] school_db > dump.sql
Then, use mysqladmin command with the --host option to create a database on the destination server.
For example, suppose you want to create a database on host 192.168.2.2, this is how you issue the command:
mysqladmin --host=192.168.2.2 \
--user=[username] --password=[password] create school_db_copy
Now that you have the school_db_copy database on the destination server, you can use mysql command to run the dump.sql file on that database by adding the --host option as well:
mysql --host=192.168.2.2 \
--user=[username] --password=[password] school_db_copy < dump.sql
And that’s how you copy a MySQL database into another server.
Copy MySQL database using client applications
If you’re using a MySQL client application to connect to a MySQL database server, then your client application probably has the feature to duplicate database(s) for your convenience.
For example, MySQL Workbench has Data Export and Data Import/Restore options from the Administration tab to help you create a MySQL dump file that you can use to copy your database over:
In the Data Export window, you can select which databases and the tables you wish to export from the server as a dump file. MySQL Workbench allows you to export each database table as its own .sql file so that you can selectively restore them later.
You can select whether to export the entire database tables or just specific tables.
Once you set the options, click the Start Export button to start the export process. Next, you can use the Data Import/Restore option from the left panel to restore your databases.
Other MySQL client application like Sequel Ace has the Duplicate Database command inside the Database tab.
You should be able to find similar feature in other MySQL client applications like PHPMyAdmin and SQLyog as well.