Referred web sites:
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
