Copying a MySQL Database on the Same Instance without Dumping

Copying a database on the same MySQL instance can be done without having to create an intermediate SQL script. The following methods provide simpler alternatives to the traditional dump-and-import process.

Directly Piping Data

The MySQL manual outlines a method that allows piping the output of mysqldump directly into the mysql client:

mysqldump --routines --triggers db_name | mysql new_db_name

This command creates a copy of the db_name database with the name new_db_name. It includes both data and database objects like routines and triggers.

Copying MyISAM Files

For databases using the MyISAM storage engine, copying the data files directly is technically possible but not recommended. The files may need to be renamed and the database may require a manual repair afterward.

Using Connection Details

The mysqldump and mysql commands can accept various options for setting connection details, including the username and password:

mysqldump -u username --password=password original_db | mysql -u username -p new_db

This command copies the original_db database to a new database called new_db, using the specified credentials.

Creating a New Database

If the new database does not exist yet, it must be created before using the piping method. This can be done with the following command:

echo "create database new_db_name" | mysql -u username -p

By following these methods, you can efficiently create a copy of your MySQL database on the same instance without the need for an intermediate dump file.