Move table from one database to another in MySQL

Move table from one database to another in MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Moving Tables Between MySQL Databases Without phpMyAdmin

Moving data between schemas in a production environment requires precision. Using graphical tools like phpMyAdmin is often convenient for ad-hoc tasks, but it introduces unnecessary overhead when integrating database migrations into an application workflow or automating infrastructure changes. A robust solution involves leveraging native SQL commands or scripting languages like PHP to execute the migration directly from your application layer or via command-line interfaces.

The most direct approach utilizes the RENAME TABLE statement combined with database switching logic, though this requires careful attention to how MySQL handles cross-database references. In a standard single-connection context, you cannot simply rename a table from source_db.table_name to dest_db.table_name in one atomic step if the target database does not already exist or if you are strictly moving ownership rather than creating an alias.

To move a table effectively without graphical tools, you generally need to handle two distinct scenarios: moving within the same server instance and ensuring data integrity during the transition. The standard SQL strategy involves selecting all data from the source, inserting it into the destination, and then dropping the original. While RENAME TABLE is tempting, it operates within a specific database context. If you attempt to rename dbA.users to dbB.users, MySQL expects both to reside in the current selected database unless you qualify the names fully, and even then, moving ownership across databases often requires an explicit intermediate step.

Here is a practical implementation pattern using raw SQL commands that can be executed directly or embedded into a PHP script:

-- Ensure the target database exists first
CREATE DATABASE IF NOT EXISTS destination_db;
USE destination_db;

-- Create the structure in the new database without data
CREATE TABLE users LIKE source_db.users;

-- Move the data. This assumes no triggers or complex foreign keys that would block the insert.
INSERT INTO destination_db.users
SELECT * FROM source_db.users;

-- Once data is verified, drop the original table to complete the move
DROP TABLE source_db.users;

This sequence ensures structural fidelity before data transfer. The CREATE TABLE ... LIKE command copies column definitions, indexes, and character sets, which is critical for application compatibility. If you skip this step and rely solely on an insert into a non-existent table, you risk runtime errors or silent data loss if the target schema differs in subtle ways like engine type or collation.

When integrating this into PHP, you must be mindful of transaction boundaries. MySQL does not support cross-database transactions by default; therefore, operations involving multiple databases (like checking source existence and inserting to destination) cannot be wrapped in a single START TRANSACTION block if they span different schemas with foreign key constraints. You should execute the creation and insertion steps individually, verifying success before dropping the source table.

<?php
$sourceDb = 'source_db';
$destDb = 'destination_db';
$tableName = 'users';

$pdo = new PDO("mysql:host=localhost;dbname=$sourceDb", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 1. Create structure in destination (requires a separate connection or qualified statement)
// Using multi-statement execution to handle cross-db logic safely
try {
    $pdo->exec("CREATE DATABASE IF NOT EXISTS $destDb");
    
    // Connect to destination to create table structure
    $stmt = $pdo->query("USE $destDb; CREATE TABLE $tableName LIKE $sourceDb.$tableName");
} catch (PDOException $e) {
    die("Structure creation failed: " . $e->getMessage());
}

// 2. Move data
try {
    $pdo->exec("INSERT INTO $destDb.$tableName SELECT * FROM $sourceDb.$tableName");
    
    // 3. Drop source table to finalize the move
    $pdo->exec("DROP TABLE $sourceDb.$tableName");
} catch (PDOException $e) {
    // Rollback logic might not be atomic across DBs, but you can log and alert here
    echo "Data migration failed. Source table remains intact.";
    throw $e;
}
?>

The trade-off with this approach is the lack of atomicity across the two databases. If the data insertion fails partway through a large table, the destination will contain partial data unless you implement manual cleanup logic or rely on the source database to handle the failure state. For high-volume tables, INSERT INTO ... SELECT is generally efficient, but monitoring row counts before and after execution is a prudent safety measure.

If your application stack utilizes Laravel, you can leverage its query builders or raw SQL capabilities within migrations or artisan commands to automate this process, ensuring consistent deployment practices. The principle remains the same: define the target structure first, transfer data, then remove the source. This method avoids the overhead of graphical interfaces while providing full control over the migration lifecycle.

Our network