Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is it row, statement or mixed based replication? –  ypercube Mar 26 at 20:44

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

ALTER TABLE some_table AUTO_INCREMENT=10000

This will set AUTO_INCREMENT to a specific value.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.