0

Sorry for the wall of text, but I don't know what's relevant and what's not.

I have MySQL a MySQL database that it is being replicated from our production environment to our development/test environment so that they get real-time updates for the DEV side of the web application. The problem is that DEV wouldn't really be DEV if they couldn't change stuff and submit it. It's one-way so and changes to DEV don't replicate back to prod, but this creates an issue where if they create new entries in the DEV database, they stay local to DEV but have to potential to have their AUTO_INCREMENT id numbers clash should the numbers be re-used in PROD (which won't see the same value for the next available index number since it's blind to what's going on in DEV).

I thought I could resolve this clashing by setting auto_increment_offset at the GLOBAL level so that new database entries get ID's in the 30,000+ range whereas PROD stays below 30,000.

I set the variable on the slave, created the table on the master, filling it with values 1-4 on the master, which get replicated to the slave, but subsequent attempts to INSERT data on the slave, have it pick up where the master DB left off.

mysql> describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 30000 |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> delete from test where id=6;
Query OK, 1 row affected (0.00 sec)

I've searched online and can't really find anything. It seems auto_increment_offset was probably created for this sort of situation but it doesn't seem to be having the effect I would anticipate (that the slave generates ID's that are a lot higher than what's in the Master). Is this saved table-level somewhere?

Any level of help would be appreciated.

  • Is it row, statement or mixed based replication? – ypercubeᵀᴹ Mar 26 '14 at 20:44
  • when auto_increment_offset ignored , it should be set to default value 1 . so when you insert into test at that time , it should populate value '5' right, not '6' right after value '4'. – kasi Jan 31 '17 at 12:45
4

You find the explanation of your question in the docs: "If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored."

For your purpose it would be sufficient to leave auto_increment_increment and auto_increment_offset on their default values and simply run: ALTER TABLE test AUTO_INCREMENT=30000 on the slave.

This will result in values like 30001,30002,...,N on your slave.

  • when auto_increment_offset ignored , it should be set to default value 1 . so when you insert into test at that time , it should populate value '5' right, not '6' right after value '4'. – kasi Jan 31 '17 at 12:46
1

ALTER TABLE some_table AUTO_INCREMENT=10000

This will set AUTO_INCREMENT to a specific value.

0

You can use AUTO_INCREMENT variable at the end of your create statement like this:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=30000;

Another solution would be to insert a row and set the row ID manually, like if you insert a row with ID 29999 manually, mysql would increment from there on the next row.

0

This is old but sad to see the obvious not mentioned. Setting an AUTO_INCREMENT high on every table is a lot more work as it has to be done for every table, and only works for so long until the db grows big enough. None of these even explain what auto_increment_offset is really for!

The sensible way is to use auto_increment_increment Set it to 2 or 3 or any number > 1. Then set auto_increment_offset to 1 on production and 2 on development server.

In this way, the production will generate IDs like: 1,3,5,7,9,11...

Dev server will generate like: 2,4,6,8,10,12..

This is a feature used when using multiple masters but can be a good solution for this type of scenario as well.

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.