auto_increment_increment resets after mysql restart? [closed]

I am setting the MySQL variable auto_increment_increment using the following command.

mysql -u root -p -e "SET GLOBAL auto_increment_increment = 10;"

And it all works, until I restart MySQL (using sudo service mysql restart), then the variables are back to default.

Before restarting:

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

After restarting:

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

How can I make this changes permanent?

2 Answers

Your command changes the behavior only temporary. Therefore add a new configuration in /etc/mysql/conf.d/. Avoid changes in /etc/mysql/my.cnf. Why? See at the end of my answer.

sudo nano /etc/mysql/conf.d/my.cnf

and add

[mysqld]
auto-increment-increment = 10

Reload the configuration or restart the server.

Taken from the standard my.cnf

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

As pointed out by ssta, you can use a configuration file. Probably the best place for it would be the my.cnf file, used at startup. Make the following changes:

...
[mysqld]
auto_increment_increment = 10
...

Save the file and restart the server.

sudo service mysql restart

That should work (I did not test it myself). By curisoity, why do you want such a behaviour?