Jojivijay

Permanently setting auto_increment_offset and auto_increment_increment in MySQL.

ALTER TABLE tablename AUTO_INCREMENT = 1;

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| auto_increment_increment   |   1     |
| auto_increment_offset          |   1     |
+------------------------------------+-------+
2 rows in set (0.00 sec)


auto_increment_increment controls the interval between successive column values. Default value 1.

SET GLOBAL auto_increment_increment  = 2;
SET SESSION auto_increment_increment = 2;

(or)

SET @@global.auto_increment_increment = 2;

SET @@session.auto_increment_increment = 2;

auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.

SET GLOBAL auto_increment_offset  = 2;
SET SESSION auto_increment_offset  = 2;

(or)

SET @@global.auto_increment_offset = 2;

SET @@session.global.auto_increment_offset = 2;

restart mysql server (if Required)

sudo service mysql restart  

If the global value of either variable is set, its effects persist until
the global value is changed or overridden by setting the session value,
or until mysqld is restarted. If the local value is set, the new value 
affects AUTO_INCREMENT columns for all tables into which new rows are 
inserted by the current user for the duration of the session, unless the
values are changed during that session.