Restoring a backup but ignore duplicate entries

[Bucky101]

Gday.
I know very little about mysql so i am hoping someone can help. I have a nortel reporting server that streams call record data off a BCM phone system onto a mysql database. Anyway we have the data spread accross 2 servers and we need to merge them into one.

I have taken a backup of the old sql database by going mysqldump --port=3309 --add-drop-table CCRDB > c:\MySQL_BKP\CCRDB_Backup.sql

I now need to restore this backup to the new maching by going mysql –-port=3309 CCRDB < c:\MySQL_BKP\CCRDB_Backup.sql


My question is how can merge/restore this database onto the new machine without getting any duplicate entries

 

[ingresman]

Do both servers get the same data ever ?
I'm intereted to know where you would get duplicates e.g. on an identity column or the actual table data? What would you do with the duplicate data?, throw it sway or do something to it?

 

[Bucky101]

Both servers have a period of about 3 weeks with the same data because both servers synchronised with the phone system at the same time. not sure which would contain the duplicatates i suppose all data within the tables. The duplicate data needs to be deleted

 

[ingresman]

I suppose you have two options:
Read through the backup and check if the record exists in the target database, if so ignore it, else write it out to another file. Use this file as the target of the backup, which should be quite small I suspect.
Second you could run the restore and just ignore the fact duplcates exst.
Are you familiar with the format of the mysql dump file?

 

[Kozusnik]

Do all of the tables have unique keys? If they're auto-incrementing, that could cause you headaches. I'd have to write some programs to pull that data together. Moving into the future, you may want to consider MySQL Replication as an alternative to using two database engines.

Best suggestion is to set up another server with MySQL (even a pc will do), restore the new database to it, then test restoring the old database to be sure you get the intended results. I'd hate to see you corrupt your data.

Mark