How to copy a table from one MySQL database to another
The proposed solution of running CREATE TABLE db1.table1 SELECT * FROM db2.table1 works perfectly fine for structural replication, but it fails immediately when you need to move data between databases that lack shared credentials. You cannot reference db2.table1 directly in a query against db1 unless the MySQL server is configured with identical authentication contexts, which is rarely the case in production environments where security boundaries are enforced per database.
When dealing with distinct usernames and passwords for two different databases, you must treat this as a data migration task rather than a simple schema operation. The naive approach of fetching all rows into PHP using a WHILE loop is functionally possible but architecturally fragile for a cronjob. A row-by-row iteration in PHP introduces significant latency due to the overhead of persistent connection handling and network round-trips for every single record. Furthermore, holding open a database connection for an extended period while processing millions of rows risks hitting server timeout limits or exhausting memory buffers.
A shell script is generally the preferred method for this specific use case because it executes entirely on the application server without requiring a complex PHP runtime environment to be spawned solely for a data dump. However, simply chaining SQL commands in a shell script does not solve the authentication hurdle; you still need a valid user account with SELECT privileges on the source and INSERT privileges on the destination.
The most robust pattern involves using standard MySQL command-line tools orchestrated via cron. You can construct a pipeline that dumps the table structure and data from the source database into a temporary file or a pipe, then loads it directly into the target database. This approach leverages optimized bulk-loading mechanisms rather than row-by-row inserts.
To implement this, you can use mysqldump to extract the data from the first database and mysql to ingest it into the second. The critical step here is ensuring that the MySQL client used for the dump and the one used for the load operate under accounts that have permission to access their respective databases.
Here is a practical implementation using a shell script:
#!/bin/bash
# Source credentials
SOURCE_USER="user_source"
SOURCE_PASS="password_source"
SOURCE_DB="db1"
SOURCE_TABLE="table1"
# Destination credentials
DEST_USER="user_dest"
DEST_PASS="password_dest"
DEST_DB="db2"
DEST_TABLE="table1"
# Create the destination table if it doesn't exist to ensure schema compatibility
mysql -u"$DEST_USER" -p"$DEST_PASS" "$DEST_DB" -e "CREATE TABLE IF NOT EXISTS $DEST_TABLE LIKE db1.$SOURCE_TABLE;"
# Dump data from source and insert into destination
# Using single quotes protects special characters in passwords
mysql -u"$SOURCE_USER" -p"$SOURCE_PASS" "$SOURCE_DB" \
-N --skip-column-names \
-e "SELECT * FROM $SOURCE_TABLE;" | \
mysql -u"$DEST_USER" -p"$DEST_PASS" "$DEST_DB" "$DEST_TABLE"
While this method works, it can be slow for very large datasets because the destination mysql client processes rows as they arrive. A more performant alternative is to dump the entire table into an SQL file and then load that file using LOAD DATA INFILE, which bypasses many of the safety checks that slow down standard inserts.
# Dump specific table data to a temporary file (ignoring structure)
mysql -u"$SOURCE_USER" -p"$SOURCE_PASS" "$SOURCE_DB" \
--no-create-info --skip-add-drop-table --quick \
$SOURCE_TABLE > /tmp/table1_data.sql
# Load the data into the destination database
mysql -u"$DEST_USER" -p"$DEST_PASS" "$DEST_DB" < /tmp/table1_data.sql
This approach significantly reduces execution time by utilizing optimized buffer pools during the load process. However, it requires that the destination table already exists with the correct column definitions and data types, which is why you must pre-create the schema or ensure the dump includes --create-info. If you include structure in the initial dump but only want to update existing rows, you might prefer using SOURCE statements instead of raw INSERT INTO SELECT syntax within a single file.
One common pitfall in these scripts is handling passwords that contain special characters like @, #, or $. These characters have specific meanings in shell scripts and can break the command parsing if not properly escaped or quoted. Using variables for credentials, as shown above, combined with double quotes around the password argument, usually mitigates this issue, but testing with complex passwords is always recommended before deploying to production cronjobs.
If your application stack relies on Laravel, you might consider using the DB facade within a scheduled task instead of a raw shell script. While the prompt explicitly prefers a shell script, it is worth noting that Laravel's migration system or queue workers offer safer ways to handle database interactions if the environment supports it. For instance, you could define a command in your artisan CLI that executes the necessary SQL logic, ensuring that credentials are managed securely via your .env file rather than hardcoded in a bash script.
Ultimately, the choice between mysqldump, direct piping, or intermediate files depends on dataset size and latency requirements. For incremental updates, you might need to calculate row counts before and after to avoid duplicating records, but for a full table copy, the dump-and-load strategy remains the industry standard for reliability and speed.