Re: copying a database in real time.

>> You might try database replication. See
>>    https://dev.mysql.com/doc/refman/5.7/en/replication.html
>> for details
 

> The problem with using replication in this situation is that the
> slave (the new system) will create new records with new primary
> keys at the same time that the old system is creating new records
> with new primary keys which are then copied to the slave system
> causing a duplicate record error.

Can't you use auto_increment_increment and auto_increment_offset to make each server use its own set of identifiers for primary keys?

This, of course, will freak out the "no gaps" advocates.

Example:

On the original server, calculate the maximum value an auto_increment primary key currently has (in any table). Add a reasonable number to account for values that may be added before you can change the setup on that server (say, one billion), then round up to the next power of 10. This is the new auto_increment value. For example, if the highest auto_increment value is 4,571,882, the new auto_increment value might be 10,000,000,000. Any record with a primary key less than this value was added pre-conversion by the original server.

Assume that 42 servers that can create new records is sufficient. Any record with a primary key >= the new auto_increment value was inserted by the server with server with auto_increment_offset = ((primary key-1) % 42) + 1.

Each server should have an id equal to a unique number between 1 and 42.

Take the (original) server offline.
Set auto_increment_increment = 42 in the startup my.cnf file. Set auto_increment_offset = the server id (1 - 42) in the startup my.cnf file. Set the auto_increment value in every table with an auto_increment primary key to the new auto_increment value computed above. This might be a good time to take a snapshot of the original server for pre-loading new servers.
Restart the server and put it back online.

When adding new servers to the replication, start them up with a unique id, and:

Set auto_increment_increment (allowing up to 42 servers). Set auto_increment_offset = the server id (1 - 42). Set the auto_increment value in every table with an auto_increment primary key before allowing production queries to insert records.

Or is there some gotcha that makes this approach not work?

Note: You still have to deal with unique indexes that might have originally been primary keys and have real-world visibility. For example, license plate numbers. A state still needs to assign unique plate numbers (within the state) and may not have the luxury of wasting plate numbers, as the plates may be manufactured well before the specific plate number is assigned to anyone.

(A typical license plate (ignoring vanity plates) in the USA has 6 or 7 characters taken from an alphabet of digits and the upper-case alphabet, with a few left out, such as I, O, and Q, which can be confused with other characters. A 6-character license plate with a 33-character alphabet has about 1.3 billion possible combinations.)

You might also have to deal with mundane practical problems, like invoice forms that do not have enough room to fit a 12-digit invoice number on them. Received on Sat Jan 20 2018 - 02:50:37 CET