0

We migrated our web server to a newer server. We have the old and new both running while we do testing on the new one. The old one is still being used in production by our clients and internal staff so is more up-to-date than the new one which was current as of a week ago when took a snap shot and did the migration. So I need to migrate the new data on the old server to the new server. What is the best way to accomplish this? If I do a mysqldump and import into the new server, will it overwrite the data already in there? That is fine if so, just concerned about it duplicating records. Basically just want to take everything that is on the old server and dump it into the new one when no one is on the system.

1

When dumping, use the option to not write the CREATE DATABASE statement.

When reloading, specify a new, empty, database on the mysql command.

That will put the imported data in a different database. You can then compare them, or do whatever. However, if you plan to move them into the old db name, you would have to do DROP TABLE + RENAME TABLE one table at a time to move them into place.

Alternatively, you could move the old data out of the way (drop + rename), then load into place.

1

So, you want to import the new data without overwriting and no duplicate keys, right? I'm no expert, but as I see it, you can either:

  • Remove the current data and import the whole old DB,
  • Use --ignore or --replace on mysqlimport - either won't duplicate rows (they'll either --replace existing rows or --ignore duplicates),
  • mysqldump without creating the database, the table and using the Insert Ignore option (--insert-ignore), so it'll insert the data and skip the duplicate keys,
  • Use --force when importing, to skip errors while inserting, this way if there's already one row with the key being inserted, it won't generate an error, just skip it and go to the next insert.

You can look up another already answered questions and try to find the best solution: here or here.

I tried with the --insert-ignore option on mysqldump and when importing I had no errors, the new data was inserted with no duplicates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.