<h3>Copying tables</h3>


<pre class="brush: sql">
CREATE TABLE new_table LIKE old_table
</pre>



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. 



<pre class="brush: sql">
INSERT INTO new_table 
 (SELECT * FROM old_table WHERE old_table.field_name = something)
</pre>



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.



<h3>Another way to copy a table</h3>


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.



<pre class="brush: sql">
CREATE TABLE new_table AS (SELECT * FROM old_table);
</pre>
 
The command shown above copies the entire table structure and data from old_table to new_table. No keys copied.


<pre class="brush: sql">
CREATE TABLE new_table AS (SELECT field1, field2 FROM old_table);
</pre>
 
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.



<h3>Copying entire database</h3>



Duplicating a database is accomplished from the operating system command line, that is, DOS prompt in Windows or terminal prompt in Linux. 



<pre class="brush: bash">
mysql -e 'create database new_database'
</pre>


<pre class="brush: bash">
mysqldump -d old_database | mysql new_database
</pre>



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.

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