MySQL - Copy Database

MySQL - Cloning and Copying Databases

Why Copy a Database?

Copying or cloning a database is a common administrative task with several key purposes, distinct from creating backups for disaster recovery. You might copy a database to:

• Create a staging or testing environment that mirrors production.
• Provide a development database with realistic data for developers.
• Perform a major data migration or schema upgrade on a copy before applying it to production.
• Analyze data without impacting the performance of the live production database.

Unlike some other database systems, MySQL does not have a single COPY DATABASE command. The process involves exporting the source database and importing it into a new destination.

Method 1: The Standard Approach with mysqldump (Recommended)

mysqldump is a command-line utility that comes with MySQL. It is the most reliable and comprehensive way to create a logical copy of a database. It generates a .sql file containing all the CREATE statements for the schema (tables, views, triggers, stored procedures) and all the INSERT statements for the data.

Step 1: Create the Destination Database

First, connect to MySQL and create an empty database that will serve as the destination.

CREATE DATABASE source_db_copy;

Step 2: Export the Source Database

From your system’s command line (not the MySQL prompt), run mysqldump. This command exports the source_db into a file named dump.sql.

# Syntax: mysqldump -u [username] -p [source_database] > [dump_file.sql]
mysqldump -u root -p source_db > dump.sql

You will be prompted for your password. The > operator redirects the output to the specified file.

Step 3: Import into the Destination Database

Now, import the contents of the .sql file into your newly created destination database.

# Syntax: mysql -u [username] -p [destination_database] < [dump_file.sql]
mysql -u root -p source_db_copy < dump.sql

The < operator feeds the SQL commands from the file into the MySQL client. Once complete, source_db_copy will be an exact replica of source_db at the time of the dump.

Method 2: Table-by-Table Copy using SQL

For quick copies of a few specific tables without leaving the SQL client, you can use a two-step SQL process. Warning: This method has significant limitations.

Limitations of this Method

This approach only copies the table structure and its data. It DOES NOT copy crucial elements like indexes, triggers, foreign key constraints, or auto-increment values. It is not suitable for creating a complete database clone.

Example: Copying a products table

-- Assuming 'source_db_copy' already exists

-- Step 1: Create an empty table in the destination with the same structure
CREATE TABLE source_db_copy.products LIKE source_db.products;

-- Step 2: Copy all data from the source table to the new table
INSERT INTO source_db_copy.products SELECT * FROM source_db.products;

You would need to repeat this process for every table, and then manually recreate all indexes, triggers, and constraints, which is tedious and error-prone.

Method 3: Using GUI Tools

Modern database management tools provide graphical interfaces that simplify this process significantly. Tools like MySQL Workbench, DBeaver, and DataGrip have built-in features for data export and import.

Typically, the workflow involves:
1. Right-clicking the source database.
2. Selecting an option like 'Data Export/Dump' or 'Backup'.
3. Configuring the options (e.g., self-contained SQL file, include triggers).
4. Creating the dump file.
5. Creating a new, empty database.
6. Right-clicking the new database and choosing 'Data Import/Restore' to load the file.

Modern Cloud-Native Approaches

If you are using a managed database service in the cloud (e.g., Amazon RDS, Google Cloud SQL, Azure Database for MySQL), the process is often much simpler and faster. These platforms provide features to create a ‘snapshot’ of your database and then ‘restore’ that snapshot to a new database instance, handling the underlying infrastructure automatically.

Important Considerations

- **Consistency:** For active databases, you want a consistent snapshot. When using `mysqldump` with InnoDB tables, add the `--single-transaction` flag. This starts a transaction and dumps the data from a consistent point in time without locking the tables for reads.
  `mysqldump --single-transaction -u root -p source_db > dump.sql`
- **Size:** For very large databases, `mysqldump` can be slow and create massive files. In these cases, physical backup methods (like Percona XtraBackup) or cloud snapshots are often preferred.