Creating a copy of an existing MySQL database, table structure, keys and records

Thursday October 20, 2011 ()

In this short blog we will demonstrate how to duplicate an entire MySQL database. We will also illustrate how to clone table structures, keys and records.

Copying tables

CREATE TABLE new_table LIKE old_table

The command above will create new_table with structure identical to existing old_table. Keys defined in old table will be created in the new table. No records will be copied.

INSERT INTO new_table 
    (SELECT * FROM old_table WHERE old_table.field_name = something)

The preceeding command will copy records from old_table that matches the where condition into new_table created in the earlier command. Leave out the where clause to copy all records. Please note that both commands above are entered from mysql SQL shell.

Another way to copy a table

The commands that follow will copy table structure including records from old_table in one command but will not copy any defined keys from old_table.

CREATE TABLE new_table AS (SELECT * FROM old_table);

The command shown above copies the entire table structure and data from old_table to new_table. No keys copied.

CREATE TABLE new_table AS (SELECT field1, field2 FROM old_table);

The resulting table created (new_table) by the command above will only have 2 columns (field1 and field2). Data from the specified columns will be copied to new_table. No keys copied.

Copying entire database

Duplicating a database is accomplished from the operating system command line, that is, DOS prompt in Windows or terminal prompt in Linux.

mysql -e 'create database new_database'

mysqldump -d old_database | mysql new_database

The first command creates our new database. Issue the second command to copy tables, keys and indexes from old_database to the new database we just created.

That's it. Good luck.


Comments (Creating a copy of an existing MySQL database, table structure, keys and records)