咨询：因安全限制，跨库复制300GB数据库各表最新10万条数据的最优方案

Hey Eduardo, great question—dealing with large, restricted databases when you can’t connect directly is a common headache, but there are definitely more efficient approaches than your current plan. Let’s break down the best options tailored to your scenario:

1. Use Database-Native Export Tools with Filtering (Skip Intermediate Tables) #

Most databases have built-in utilities that let you filter and export records in one step, eliminating the need to create duplicate tables on the remote server. Here are examples for popular systems:

• MySQL: Use mysqldump with a targeted WHERE clause. Assuming your tables have an auto-increment ID or timestamp column (like created_at):

  mysqldump -h remote_host -u username -p your_database table_name --where="id >= (SELECT MAX(id) - 99999 FROM table_name)" > table_name_last_100k.sql
  

  If using timestamps, adjust the clause to something like created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) (tweak the interval to get ~100k rows) or use a subquery to fetch the exact cutoff value.
• PostgreSQL: Use pg_dump with a --where flag or the COPY command for direct CSV exports:

  COPY (SELECT * FROM table_name ORDER BY id DESC LIMIT 100000) TO '/local/path/table_name_last_100k.csv' WITH CSV HEADER;
  

• SQL Server: Use the bcp utility to export filtered results:

  bcp "SELECT TOP 100000 * FROM your_dbo.table_name ORDER BY id DESC" queryout "table_name_last_100k.csv" -S remote_server -U username -P password -c -t,
  

  This approach saves remote storage space and cuts out unnecessary steps.

2. Request a Read-Only Remote Connection (Direct Data Pull) #

If your security team allows it, ask for a read-only user account with secure access (SSL-enabled) to the remote database. This lets you pull data directly into your local tables without exporting/importing:

-- First, replicate the table schema locally (use `SHOW CREATE TABLE` or `pg_dump --schema-only` to generate this)
CREATE TABLE local_table LIKE remote_db.remote_table;

-- Insert the last 100k rows directly
INSERT INTO local_table
SELECT * FROM remote_db.remote_table
ORDER BY id DESC
LIMIT 100000;

This is the fastest method if permissions allow, as it skips file-based export/import entirely.

3. Automate with a Batch Script (For Multiple Tables) #

If you have dozens of tables, a script will save you hours of manual work. Here’s a bash example for MySQL:

# Fetch list of all tables in the remote database
TABLES=$(mysql -h remote_host -u username -p your_database -e "SHOW TABLES" | grep -v Tables_in_)

for TABLE in $TABLES; do
  echo "Exporting last 100k rows from $TABLE..."
  mysqldump -h remote_host -u username -p your_database $TABLE --where="id >= (SELECT MAX(id) - 99999 FROM $TABLE)" > "${TABLE}_last_100k.sql"
done

Adapt this logic for other databases using their command-line tools (like psql for PostgreSQL or sqlcmd for SQL Server).

Why Your Current Plan Isn’t Ideal #

Creating intermediate tables on the remote server works, but it has key downsides:

• Wastes remote storage: Adding duplicate tables to a 300GB database will consume extra space unnecessarily.
• Extra overhead: You’re adding steps (create tables → migrate data → export) that can be skipped with filtered exports.
• Inconsistency risk: If the remote database is active, your intermediate tables might become outdated before you export them.

Final Recommendations #

• If you can get read-only access, go with the direct INSERT...SELECT method—it’s the most efficient.
• If permissions are strict, use database-native export tools with filtering to avoid duplicate tables.
• Automate the process with a script to handle all tables quickly.

内容的提问来源于stack exchange，提问作者Eduardo