Clone MySQL Table via SSH

—

## Step-by-Step Guide

### 1. SSH into your server

ssh user@your-server-address

### 2. Access MySQL command-line:

mysql -u your_mysql_user -p

Enter your MySQL password when prompted.

### 3. Select your database:

USE your_database_name;

### 4. Clone your table:
To duplicate the table structure **and** data:

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

To duplicate **only the structure**, no data:

CREATE TABLE new_table LIKE original_table;

To duplicate structure, data, and indexes in a single query (alternative way):

CREATE TABLE new_table AS SELECT * FROM original_table;

*(This will clone structure and data but **will not copy indexes and keys**.)*

—

### 5. Verify the new table:

DESCRIBE new_table;
SELECT COUNT(*) FROM new_table;

—

### 6. Exit MySQL:

exit;

—

### One-liner (optional):
You can also execute this as a one-liner directly in SSH:

mysql -u your_mysql_user -p -e "CREATE TABLE your_database.new_table LIKE your_database.original_table; INSERT INTO your_database.new_table SELECT * FROM your_database.original_table;"

—

This completes the table cloning process.

—

## ✅ Recommended Approach (Two-Step)

**Step 1:** Create the new table structure, including all keys and indexes:

CREATE TABLE new_table LIKE original_table;

This copies the table schema, including:

– Primary keys
– Indexes
– Constraints
– Column definitions
– Auto-increment properties

—

**Step 2:** Copy data into the new table:

INSERT INTO new_table SELECT * FROM original_table;

—

## 🚫 Avoid using:

**Do NOT use this method** if you want to preserve indexes and keys, because it **doesn't copy indexes or keys**:

CREATE TABLE new_table AS SELECT * FROM original_table;

—

## 🖥️ SSH One-liner Example

Execute directly from your SSH terminal:

mysql -u your_mysql_user -p -e "
  CREATE TABLE your_database.new_table LIKE your_database.original_table;
  INSERT INTO your_database.new_table SELECT * FROM your_database.original_table;
"

Enter your MySQL password when prompted.

—

## 📌 **Verification**

To verify the indexes and structure:

SHOW CREATE TABLE new_table;

To verify the data:

SELECT COUNT(*) FROM new_table;

—

**This ensures your table is cloned completely—including indexes, keys, and constraints.**