Overview

When you are working on a Development system it is often desirable to have a copy of a Production system database table. mySQL provides a handy method of making a copy of a single table and an easy path to extract the data from the production table and insert it into the development copy of the table.

In the example below, we will copy the "customers" table form the Production DataBase to our Develpment DataBase without using mysqldump to dump the entire database to a file.

These procedures delete and modify your database. Ensure you've double checked the commands before running them.

Instructions

  1. Login to the MySQL shell: 
    mysql --user=<username> --password=<password>
  2. Select the database you wise to replace: 
    use mydevdatabase;
  3. Drop the old table: 
    DROP TABLE mydevdatabase.customers;
  4. Create the table based on the database you wish to copy: 
    CREATE TABLE mydevdatabase.customers LIKE myproductiondatabase.customers;
  5. Then, copy all of the data: 
    INSERT INTO mydevdatabase.customers SELECT * FROM myproductiondatabase.customers;
  6. You should then see a confirmation of how many records were inserted.

If you need to copy a whole database instead of just one table, please see this article: How to copy a MySQL database.

Was this article helpful?
Yes No

Related Articles