How to set "auto_increment_increment" and "auto_increment_offset" - MySQL

Referred web sites:

• http://dev.mysql.com

What is "auto_increment_increment" and "auto_increment_offset" ?

Both "auto_increment_increment" and "auto_increment_offset" are system variables which required in MySQL Master-Master replication. By changing above variables you will be able to manage auto increment columns and avoid from data conflict issues in your database.

What does it do ?

• "auto_increment_increment" controls the interval between successive column values
• auto_increment_offset determines the starting point for the AUTO_INCREMENT column value

How to access MySQL monitor (Ubuntu 12.04)

• Open Terminal window using "Dash Home".

• Log into MySQL Monitor (You may need a valid username and password)

mysql

or

mysql -uroot

or

mysql -uroot -p






• View existing(default) system variables ("auto_increment_increment" and "auto_increment_offset")

mysql> SHOW VARIABLES LIKE 'auto_inc%';
 






• Create a Database (You will be able to test this using your existing Database)

mysql> CREATE DATABASE TEST_DB;

• Select created Database to use

mysql> USE TEST_DB;

• Create a table

mysql> CREATE TABLE TestTable
    -> (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

• Insert sample records

mysql> INSERT INTO TestTable VALUES (NULL), (NULL), (NULL), (NULL);

• View inserted records

mysql> SELECT * FROM TestTable;






• Set "auto_increment_increment" to 5 and insert another sample records

mysql> SET @@auto_increment_increment=5;

mysql> INSERT INTO TestTable VALUES (NULL), (NULL), (NULL), (NULL);

mysql> SELECT * FROM TestTable;






• Now you know how it works. You can create another table to test "auto_increment_offset"
• You can drop your "TestTable" and follow above instructions. But make sure to keep the table without any data.
• Also do not forget to set "auto_increment_increment"
• Now you can set "auto_increment_offset"

mysql> SET @@auto_increment_offset=5;

• Then insert few sample records

mysql> INSERT INTO TestTable VALUES (NULL), (NULL), (NULL), (NULL);









• This is how "auto_increment_increment" and "auto_increment_offset" works.

Something to remember.

• You will be able to set "auto_increment_offset" greater then (>) "auto_increment_increment" and test again. But the value of "auto_increment_offset" will be ignore by the system.





• Because if the value of "auto_increment_offset" is greater than that of "auto_increment_increment", the value of auto_increment_offset is ignored.

Hardcode variables in my.cnf:

Also you can hardcode followings settings in your my.cnf file (/etc/my.cnf)

# The MySQL server
[mysqld]

auto-increment-increment = 2 # No of replication servers
auto-increment-offset = 1 # For first replication server