Here’s an interesting scenario I happened across the other day. What if you had a distributed system that used twenty MySQL databases, each one located in one of 20 US cities. Same database on each server, but each server has a different set of data. Now, what if you wanted to merge all of the data from all of the servers into one database? You’d have a problem with primary key clashes. Here’s how you might solve that problem.

Here’s a step-by-step guide to setting up unique auto-incremented IDs in a distributed system using MySQL’s AUTO_INCREMENT increment and offset settings.

  1. Create Databases and Tables on Multiple Servers:

    • Assume we have two MySQL servers: Server 1 and Server 2.

  2. Configure Auto-Increment Settings:

    • On Server 1, we set the auto-increment increment to 100 and the offset to 1.
    • On Server 2, we set the auto-increment increment to 100 and the offset to 2.

  3. Create a Table on Both Servers:

    • The table structure should be identical on both servers.

Let’s see how to implement this with SQL commands:

Server 1 Configuration

-- Set auto-increment increment and offset for Server 1
SET @@auto_increment_increment = 100;
SET @@auto_increment_offset = 1;

-- Create a sample table on Server 1
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

CREATE TABLE IF NOT EXISTS test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255) NOT NULL
);

-- Insert some sample data
INSERT INTO test_table (data) VALUES ('Data from Server 1');
INSERT INTO test_table (data) VALUES ('This is a test entry from Server 1');
INSERT INTO test_table (data) VALUES ('This is an extra message');
INSERT INTO test_table (data) VALUES ('Last message');

Server 2 Configuration

-- Set auto-increment increment and offset for Server 2
SET @@auto_increment_increment = 100;
SET @@auto_increment_offset = 2;

-- Create a sample table on Server 2
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

CREATE TABLE IF NOT EXISTS test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255) NOT NULL
);

-- Insert some sample data
INSERT INTO test_table (data) VALUES ('Data from Server 2');
INSERT INTO test_table (data) VALUES ('This is a test entry from Server 2');
INSERT INTO test_table (data) VALUES ('Another message');
INSERT INTO test_table (data) VALUES ('Last message for Server 2');

Verify the Auto-Increment Values

Now, let’s verify the IDs generated on both servers.

On Server 1

SELECT * FROM test_table;

This should return something like:

+-----+------------------------------------+
| id  | data                               |
+-----+------------------------------------+
|   1 | Data from Server 1                 |
| 101 | This is a test entry from Server 1 |
| 201 | This is an extra message           |
| 301 | Last message                       |
+-----+------------------------------------+

On Server 2

SELECT * FROM test_table;

This should return something like:

+-----+------------------------------------+
| id  | data                               |
+-----+------------------------------------+
|   2 | Data from Server 2                 |
| 102 | This is a test entry from Server 2 |
| 202 | Another message                    |
| 302 | Last message for Server 2          |
+-----+------------------------------------+

Explanation

  • Server 1: The IDs start at 1 and increase by 100 for each new entry (1, 101, 201, …).
  • Server 2: The IDs start at 2 and increase by 100 for each new entry (2, 102, 202, …).

Benefits

  • This configuration ensures that there are no ID collisions when rows are inserted into both servers.
  • Rows can be freely migrated between servers without risk of ID collision.
  • The system remains simple and effective, leveraging MySQL’s built-in auto-increment functionality.

This setup is useful for distributed systems where different servers need to generate unique IDs for rows independently so that tables can be merged later without conflict.

Caveats

  • Naturally, the increment of 100 is arbitrary and can be adjusted based on the expected number of competing tables. In this case, we are expecting no more than 100 different servers to be merged.