16.1.6.2 Replication Source Options and Variables (original) (raw)

System Variables Used on Replication Source Servers
mysql> SHOW VARIABLES LIKE 'auto_inc%';  
+--------------------------+-------+  
| Variable_name            | Value |  
+--------------------------+-------+  
| auto_increment_increment | 10    |  
| auto_increment_offset    | 5     |  
+--------------------------+-------+  
2 rows in set (0.00 sec)  
mysql> SELECT col FROM autoinc1;  
+-----+  
| col |  
+-----+  
|   1 |  
|  11 |  
|  21 |  
|  31 |  
+-----+  
4 rows in set (0.00 sec)  
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);  
Query OK, 4 rows affected (0.00 sec)  
Records: 4  Duplicates: 0  Warnings: 0  
mysql> SELECT col FROM autoinc1;  
+-----+  
| col |  
+-----+  
|   1 |  
|  11 |  
|  21 |  
|  31 |  
|  35 |  
|  45 |  
|  55 |  
|  65 |  
+-----+  
8 rows in set (0.00 sec)

The values shown forauto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to theINSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values forcol begin at that point and the results are as shown for the SELECT query.
It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in_all_ tables on the MySQL server. 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 affectsAUTO_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.
The default value ofauto_increment_increment is 1. SeeSection 16.4.1.1, “Replication and AUTO_INCREMENT”.