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?

3

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?