How to Clone a MySQL Database on the Same Instance Without Dumping to SQL?

Cloning a MySQL Database on the Same Instance without Dumping to SQL

Copying a database on the same MySQL instance can be achieved without creating an intermediate SQL dump. The following steps outline an efficient method to accomplish this task:

1. Establish a Connection: Log into the MySQL client using the root user or a user with sufficient privileges:

mysql -u root -p

2. Use the Pipe Method: Execute the following command to pipe the dump directly into the new database:

mysqldump --routines --triggers source_db | mysql target_db

This method transfers the structure and data from source_db to target_db.

3. Handling Connection Details: If necessary, specify connection details for both the source and target databases:

mysqldump -u source_user -p source_password -h source_host source_db | mysql -u target_user -p target_password -h target_host target_db

4. Create the Target Database: If the target database doesn't exist, create it first using the following command:

echo "create database target_db" | mysql -u user_name -p

5. Copy Non-MyISAM Tables: If your tables are not in MyISAM format, follow the same piping method but additionally include the --skip-data and --add-drop-table options:

mysqldump --routines --triggers --skip-data --add-drop-table source_db | mysql target_db
mysql source_db -e "select * from table_name" | mysql target_db

By utilizing the pipe method, you can quickly clone a MySQL database on the same instance without the need to create an intermediary SQL file.

The above is the detailed content of How to Clone a MySQL Database on the Same Instance Without Dumping to SQL?. For more information, please follow other related articles on the PHP Chinese website!