0

I am working with the same Database on two different computers, and need to keep them synchronized.

When I finish working on one computer I create a dump file of the Database at that point so I can import it in the other with the instruction:

mysqldump -u username -p DataBaseName TableName > Table.dump

My problem is that when I insert elements in some tables it may happen that their primary key is already assigned to an element of the same table of the Database on my other computer.

I would like to know how to insert the instructions I found in the question How can I merge two MySQL tables? in the dump files I create, in order to assign new primary key values to the duplicate entries, without loosing the data of the table.

I would like to avoid the behavior of the created dump file, that will entirely drop previous version of the interested table, as quoted below:

DROP TABLE IF EXISTS `TableName`;
CREATE TABLE `TableName` (
    ...
) ENGINE=MyISAM AUTO_INCREMENT=6512 DEFAULT CHARSET=utf8;
Community
  • 1
  • 1
Matteo
  • 7,924
  • 24
  • 84
  • 129

3 Answers3

1

As an alternative, you could use MySQL's increment and offset settings which are normally used for automatic master to master replication where data is synchronized without doing mysqldump. Or you could actually set up master to master replication if it applies to your situation.

On Server A, use the following settings:

auto-increment-offset=1
auto-increment-increment=2

On Server B, use the following settings:

auto-increment-offset=2
auto-increment-increment=2

Here is one guide that describes the master to master replication set up: Dual-Master MySQL 5 Replication Done Right.

dabest1
  • 2,347
  • 6
  • 25
  • 25
1

Have you tried using REPLACE?

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Ben English
  • 3,900
  • 2
  • 22
  • 32
0

You could consider using GUIDs as your primary keys (via the uuid function). That way, you won't get a clash.

Any scheme that involves remapping the primary keys that clash is likely to cause problems if the data is copied back in the opposite direction (since you will get two copies of the data in the original database, with the old and new primary keys respectively). Also, if the primary key is sufficiently meaningless that it could be remapped (e.g. if it's not referenced from other linked tables) then consider whether you need the primary key at all.

Matthew Strawbridge
  • 19,940
  • 10
  • 72
  • 93
  • I can't understand well what you mean, could you please explain me a little more?consider that i need to keep the PK. – Matteo Nov 28 '11 at 08:24
  • If you use a GUID as your primary key, every record you create will get a unique primary key regardless of which machine you create the record on. That way, you won't get a clash when you merge. – Matthew Strawbridge Nov 28 '11 at 09:50