Many developers encounter scenarios where they need to export only recent records rather than full tables. This is particularly common when:

• Creating test datasets from production
• Backing up recent transactions
• Migrating recent data between environments
• Analyzing time-based data subsets

The most efficient approach combines mysqldump with a WHERE clause to limit records. Here's the basic syntax:

mysqldump -u username -p database_name table_name \
--where="1=1 ORDER BY id DESC LIMIT 1000" > last_1000_records.sql

For Tables with Auto-Increment IDs

# Get the highest ID first
MAX_ID=$(mysql -u username -p database_name -e "SELECT MAX(id) FROM table_name" -s)

# Calculate the threshold
THRESHOLD=$((MAX_ID - 1000))

# Perform the dump
mysqldump -u username -p database_name table_name \
--where="id > $THRESHOLD" > recent_1000.sql

For Time-Based Records

mysqldump -u username -p database_name table_name \
--where="created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" \
--no-create-info > last_week.sql

Using Stored Procedures

For complex filtering requirements:

DELIMITER //
CREATE PROCEDURE dump_recent()
BEGIN
  SET @max_id = (SELECT MAX(id) FROM large_table);
  SET @sql = CONCAT('mysqldump -u root -p mydb large_table --where="id > ', @max_id-1000, '" > /tmp/recent.sql');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

• Performance Issues: Add proper indexes on columns used in WHERE clauses
• Locking Problems: Use --single-transaction for InnoDB tables
• Metadata Inclusion: Use --no-create-info if you only want data

For extremely large tables, consider:

# Using SELECT INTO OUTFILE
mysql -u username -p -e "SELECT * FROM table_name \
ORDER BY id DESC LIMIT 1000 INTO OUTFILE '/tmp/recent.csv' \
FIELDS TERMINATED BY ','"

When working with large datasets, we often need to export only recent records rather than entire tables. The standard mysqldump command exports complete tables, which can be inefficient for large databases where we only need the newest entries.

The most straightforward approach combines ORDER BY with LIMIT in a WHERE clause:

mysqldump -u username -p database_name table_name \
--where="1 ORDER BY id DESC LIMIT 1000" > latest_1000.sql

Key components:
- ORDER BY id DESC sorts records by primary key in descending order
- LIMIT 1000 restricts output to 1000 most recent records

For more complex scenarios or better performance on very large tables:

mysql -u username -p -e "CREATE TEMPORARY TABLE temp_latest \
SELECT * FROM big_table ORDER BY created_at DESC LIMIT 1000;"
  
mysqldump -u username -p database_name temp_latest > latest_records.sql

When dealing with tables containing millions of records:

• Ensure your ORDER BY column is properly indexed
• For InnoDB tables, consider using --single-transaction flag
• Add --skip-lock-tables if you can't lock the production database

Here's a full production-ready example with proper authentication and error handling:

#!/bin/bash
  
DB_USER="app_user"
DB_PASS="secure_password"
DB_NAME="production_db"
TABLE="user_activities"
OUTPUT_FILE="/backups/latest_activities_$(date +%Y%m%d).sql"

mysqldump --single-transaction --skip-lock-tables \
-u "$DB_USER" -p"$DB_PASS" "$DB_NAME" "$TABLE" \
--where="1 ORDER BY timestamp DESC LIMIT 1000" > "$OUTPUT_FILE"

For daily backups of recent records, add this to crontab:

0 3 * * * /usr/bin/mysqldump -u backup_user -p'password' app_db transactions \
--where="created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)" > /backups/daily_transactions.sql