每月将db1数据追加至db2：mysqldump导入主键重复报错求助

Hey there! Let's break down what's going on and get that data archiving workflow working smoothly.

Why You're Seeing the Duplicate Entry Error #

The ERROR 1062 pops up because your archive database db2 already has a row with primary key 809, and the dump file from db1 is trying to insert that same row again. By default, mysqldump generates basic INSERT INTO statements that don't handle existing duplicate keys—they just throw an error when a conflict is hit.

Your existing setup handles new tables correctly with CREATE TABLE IF NOT EXISTS, but we need to add logic to handle duplicate rows in existing tables.

Solution 1: Ignore Duplicate Rows (Quickest Fix) #

If you don't need to update existing rows in db2 and just want to skip duplicates, use the --insert-ignore flag with mysqldump. This tells MySQL to ignore any INSERT that would cause a duplicate key conflict instead of throwing an error.

Here's the full workflow:

1. Export the data from db1 with the ignore flag:

   mysqldump -u user -p --skip-add-drop-table --insert-ignore db1 > db1_partial_dump.sql
   

2. Modify the dump to create tables only if they don't exist:

   sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' db1_partial_dump.sql
   

3. Import the modified dump into db2:

   mysql -u user -p db2 < db1_partial_dump.sql
   

Solution 2: Update Duplicate Rows (If You Need Fresh Data) #

If you want to update existing rows in db2 when a duplicate key is found (instead of skipping them), you have two options:

Option A: Use REPLACE INTO #

REPLACE INTO will delete the existing row and insert the new one from your dump. This is simple but replaces the entire row, so use it if you want the db1 version to overwrite db2 entirely:

1. Export without --insert-ignore:

   mysqldump -u user -p --skip-add-drop-table db1 > db1_partial_dump.sql
   

2. Fix the CREATE TABLE line as before:

   sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' db1_partial_dump.sql
   

3. Replace all INSERT INTO statements with REPLACE INTO:

   sed -i 's/INSERT INTO/REPLACE INTO/g' db1_partial_dump.sql
   

4. Import into db2 as usual.

Option B: Use INSERT ... ON DUPLICATE KEY UPDATE #

This lets you update specific fields instead of replacing the entire row (more granular control). For example, you might only update a last_updated timestamp or specific data fields. You'll need to tweak the dump file to add this clause:

1. Export the data normally:

   mysqldump -u user -p --skip-add-drop-table db1 > db1_partial_dump.sql
   

2. Fix the CREATE TABLE line:

   sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' db1_partial_dump.sql
   

3. Use sed to append the update clause to your INSERT statements. For example, if you want to update the updated_at field when a duplicate is found:

   sed -i 's/INSERT INTO \(.*\) VALUES/INSERT INTO \1 VALUES ON DUPLICATE KEY UPDATE updated_at = NOW()/g' db1_partial_dump.sql
   

   Adjust the update clause to match your table's needs (e.g., update specific columns from the new data).

Pro Tip: Export Only Incremental Data #

To avoid duplicate rows entirely, export only the new/updated rows from db1 each month instead of the full table. Use the --where flag with mysqldump to filter rows by a timestamp (like created_at or updated_at):

mysqldump -u user -p --skip-add-drop-table --insert-ignore --where="created_at >= '2024-05-01 00:00:00'" db1 > db1_partial_dump.sql

This ensures you're only adding rows that haven't been archived yet, which is more efficient and eliminates duplicate key issues from the start.

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