The World of MySQL & MariaDB

MySQL Table Copy Guide: Replicate Structure, Data & Indexes

Table Management
目次

1. Introduction

Table copying in MySQL is one of the operations frequently used in database administration. By duplicating an existing table, you can easily back up data or set up a test environment. This article provides a detailed explanation of various methods for copying tables in MySQL, including steps, benefits, and cautions.

2. Basic Concepts and Uses of Table Copy

Table copy has three basic methods: Method to copy structure only, Method to copy structure and data, Full copy including indexes and constraints. Choose the best method based on your needs.

Main Uses of Table Copy

  • Data Backup: For data preservation, regularly backing up tables gives peace of mind.
  • Test Environment Setup: It’s useful when you need duplicate data for testing new data or features.
  • System Migration and Optimization: It is also used to prepare for migrations that involve changes to table structures.

3. How to Copy Only the Table Structure

If you want to copy only the table structure, CREATE TABLE ... LIKE statement is convenient. With this method, only the table schema (column definitions and data types) is duplicated, and no data is included.

How to Use CREATE TABLE … LIKE

  • Basic Syntax
  CREATE TABLE new_table_name LIKE original_table_name;
  • Example For example, to duplicate only the structure of the employees table and create a new employees_backup table, the SQL statement is as follows.
  CREATE TABLE employees_backup LIKE employees;
  • Advantages This method is very simple and allows you to copy the table’s column layout as is. Since indexes and constraints are also copied, it is suitable when you want to duplicate a basic table structure.
  • Cautions Data is not copied. Also, if there are custom foreign key constraints, note that dependencies with other tables will not be reestablished.

4. How to Copy Table Structure and Data Simultaneously

If you want to copy not only the table structure but also the data at the same time, use the CREATE TABLE ... AS SELECT statement. This is handy when you want to duplicate a table along with its data.

How to Use CREATE TABLE … AS SELECT

  • Basic Syntax
  CREATE TABLE new_table_name AS SELECT * FROM original_table_name;
    • Example For instance, to duplicate the structure and data of the employees table into a employees_full_backup table, use the following SQL statement.
  CREATE TABLE employees_full_backup AS SELECT * FROM employees;
  • Benefits Since the structure and data are copied in one go, it’s ideal for backing up an entire table.
  • Cautions Indexes and foreign key constraints are not copied with this method. If needed, you must manually recreate indexes and constraints after copying the data.

5. How to Fully Copy Including Indexes and Constraints

If you want to completely duplicate a table and include its indexes and constraints, combine the CREATE TABLE ... LIKE statement with the INSERT INTO ... SELECT statement.

Combining CREATE TABLE … LIKE with INSERT INTO … SELECT

  • Steps
  1. Copy the structure with CREATE TABLE ... LIKE.
  2. Insert the data with INSERT INTO ... SELECT.
  • Example
  CREATE TABLE employees_full_copy LIKE employees;
  INSERT INTO employees_full_copy SELECT * FROM employees;
  • Benefits Because you can create a complete copy that retains indexes and constraints, it is ideal for database backups and replication.
  • Cautions If the data volume is large, the INSERT operation can take time. Also, many indexes or foreign keys can temporarily increase server load.

6. Table Copy Between Different Databases

When copying a table to another database, specify the database name to distinguish the source and destination tables.

How to Copy Between Different Databases

  • Basic Syntax
  CREATE TABLE new_database.new_table AS SELECT * FROM original_database.original_table;
  • Example Copy the employees table from the test_db database to the backup_db database.
  CREATE TABLE backup_db.employees_copy AS SELECT * FROM test_db.employees;
  • Caution When copying a table to a different database, you may need to configure user permissions and settings related to connections between databases.

7. Copying Tables Using phpMyAdmin

By using phpMyAdmin, you can easily copy tables through a GUI. This method is also recommended for those who are not familiar with SQL.

Steps in phpMyAdmin

  1. Log in to phpMyAdmin and select the table you want to copy.
  2. From the “Operations” tab, choose “Copy table.”
  3. Select either “Structure only” or “Structure and data,” specify a new table name, and execute.

Advantages

The GUI makes the process simple, and because you don’t need to write SQL statements, even beginners can use it easily.

Cautions

A confirmation message may appear during phpMyAdmin operations, so be careful when, for example, changing permissions.

8. Table Copying: Considerations and Best Practices

When copying a table, consider data consistency and performance, and use transactions as needed.

Things to Watch When Copying

  • Data Consistency In environments where data is updated frequently, lock the table or use a transaction before copying to maintain data consistency.
  • Performance Copying large tables puts load on the server. Run the copy outside peak hours or leverage backup tools.
  • To guard against errors or data loss, it’s recommended to take a backup before copying.

9. Summary

There are various options for copying tables in MySQL. Choose the method that best fits your needs—whether copying only the table structure, copying with data, or a full copy that includes indexes and constraints. Use this article as a reference to manage your data efficiently.