Use mysqldump to export the source database: generate SQL backup files containing structure and data through the mysqldump command. It is recommended to use parameters such as --single-transaction, --routines, --triggers, and --events to ensure consistency and include all objects; 2. Create a new database: If you need to change the database name, create the target database manually, such as CREATE DATABASE dev_db; 3. Import to the target database: If the dump file contains CREATE DATABASE statement, you can import it directly, otherwise you need to modify the dump file or specify the database import through the command; 4. Development environment data processing: sensitive information should be updated after cloning, such as anonymizing the user's mailbox and password through the UPDATE statement; 5. Automated scripting: shell scripts can be written to achieve periodic cloning, combining variables and secure password management to improve efficiency; in addition, high-performance alternatives such as MySQL replication or LVM snapshots can be considered for large databases. The entire process requires ensuring the security of the credentials and always desensitizing the development environment data.

Cloning a MySQL database for development or testing is a common task that allows you to work with a copy of production data without affecting the live environment. The most reliable and widely used method involves using mysqldump to create a backup and then importing it into a new database. Here's how to do it step by step.

1. Use mysqldump to Export the Source Database

The mysqldump command-line tool generates a SQL dump file containing all the necessary CREATE and INSERT statements to recreate the database.

 mysqldump -u [username] -p[password] --databases [source_db_name] > backup.sql

• Replace [username] with your MySQL user.
• [password] comes immediately after -p without a space (or omit it to be prompted securely).
• [source_db_name] is the name of the database you want to clone.
• --databases preserves the CREATE DATABASE statement in the dump, which helps during import.

Optional flags for better performance or accuracy:

• --single-transaction : Ensures a consistent state without locking tables (ideal for InnoDB).
• --routines : Includes stored procedures and functions.
• --triggers : Includes triggers (enabled by default).
• --events : Includes event scheduler events.

Example:

 mysqldump -u root -p --single-transaction --routines --triggers --events --databases production_db > production_clone.sql

2. Create a New Database (Optional if Using --databases)

If your dump includes --databases , it will attempt to recreate the original database name. To clone to a different name:

 CREATE DATABASE dev_db;

You can run this in MySQL:

 mysql -u [username] -p -e "CREATE DATABASE dev_db;"

Then, you'll need to edit the dump file or redirect the import to the new database.

3. Import the Dump into the Target Database

If your dump was created with --databases , it contains USE and CREATE DATABASE statements, so you can import directly:

 mysql -u [username] -p < backup.sql

But if you want to change the database name , you have two options:

Option A: Edit the dump file

Open backup.sql and:

• Change CREATE DATABASE [source_db_name]; to CREATE DATABASE [new_db_name];
• Or remove the CREATE DATABASE and USE lines and import into an existing DB.

Option B: Import into an existing database (skip CREATE DATABASE)

Remove or comment out the CREATE DATABASE and USE lines, then:

 mysql -u [username] -p dev_db < backup.sql

Or use a pipeline to filter:

 grep -v "^CREATE DATABASE" backup.sql | grep -v "^USE" | mysql -u root -p dev_db

4. (Optional) Modify Data for Development

After cloning, you may want to sanitize sensitive data (eg, user emails, passwords) for security in dev/test environments.

Example SQL to anonymize user data:

 UPDATE users SET email = CONCAT('user', id, '@example.com'), phone = NULL;
UPDATE users SET password_hash = '$2y$10$fakehash...' WHERE id > 0;

Run this on the cloned database to protect privacy.

5. Automate or Script for Reuse

For frequent cloning (eg, nightly test data refresh), wrap the process in a script:

 #!/bin/bash
DUMP_FILE="/tmp/clone_$(date %Y%m%d_%H%M%S).sql"
SOURCE_DB="production_db"
TARGET_DB="test_db"

mysqldump -u root -p$MYSQL_PWD --single-transaction --routines --triggers --events --databases $SOURCE_DB > $DUMP_FILE

mysql -u root -p$MYSQL_PWD -e "DROP DATABASE IF EXISTS $TARGET_DB; CREATE DATABASE $TARGET_DB;"

mysql -u root -p$MYSQL_PWD $TARGET_DB < $DUMP_FILE

mysql -u root -p$MYSQL_PWD $TARGET_DB -e "UPDATE users SET email = CONCAT(&#39;user&#39;, id, &#39;@test.com&#39;);"

Note: Store passwords securely (eg, via .my.cnf ) instead of in scripts.

Alternative: Use Replication or LVM Snapshots (Advanced)

For very large databases, mysqldump may be too slow. Alternatives include:

• MySQL Replication : Set up a replica server and promote it for testing.
• LVM or Filesystem Snapshots : If using InnoDB on a supported filesystem, take a binary snapshot for faster cloning.

These require more infrastructure but offer near-instant copies.

Cloning a MySQL database doesn't have to be complex. Using mysqldump with proper flags covers most use cases for development and testing. Just remember to handle credentials securely and sanitize sensitive data before sharing the clone.

The above is the detailed content of How to Clone a MySQL Database for Development or Testing?. For more information, please follow other related articles on the PHP Chinese website!