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...
![Auto_Increment_Increment Resets After Mysql Restart? [Closed]](https://www.alledaags.nl/uploads/auto-increment-increment-resets-after-mysql-restart_1200.jpg)
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?
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?
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
