How to set sql_mode in my.cnf in MySQL 8?

The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0, and it's no longer recognized.

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations says:

The following features related to account management are removed:

• Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

Change your sql_mode to "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION". I tested this on my sandbox instance of 8.0.11 and it worked.

Either spelling of sql-mode or sql_mode are both fine.

Using quotes or omitting quotes are both fine.

Assuming that "restart safe" just means permanent, the syntax is:

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

From Setting the SQL Mode:

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas.

If it doesn't work for your, perhaps you're placing it under the wrong section. For server settings that needs to be [mysqld], as in:

[mysqld]sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Step 1. Check sql mode:

mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

Step 2. Create a new configuration file under the /etc/mysql/conf.d/ directory:

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

Enter the text below on the editor:

[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Step 3. Restart MySQL:

sudo service mysql restart

Step 4. Confirm the change:

mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

NO_AUTO_CREATE_USER SQL mode has been removed in MySQL 8.0, please check the reference manual for the full list of SQL modes.