Unable to create WordPress databse user

Question

Wordpress and MariaDB are on different hosts. I'm connected to MariaDB via it's CLI command interface. I am following the DigtalOcean guide.

Here are the DB and OS info:

• 10.3.32-MariaDB-0ubuntu0.20.04.1
• Ubuntu 20.04

Here is the command I'm using to create the user:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4' 
  IDENTIFIED WITH mysql_native_password BY 'PasswordAlphaNumeric';

And here is the error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY 'PasswordAlphaNumeric'' at line 1

Where:

• user_name_for_wordpress: Username with upper and lower case
• 1.2.3.4: WordPress server IP address
• PasswordAlphaNumeric: The password

What am I doing wrong?

Answer 1

Small syntax issue, but this requires MariaDB 10.4.0 or later:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4' 
  IDENTIFIED WITH mysql_native_password 
  USING PASSWORD('PasswordAlphaNumeric');

The solution for you is to simply not specify the authentication plugin as it will default to mysql_native_password anyway. So:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4'
  IDENTIFIED BY 'PasswordAlphaNumeric';

According to the MariaDB Knowledgebase article about authentication plugins:

By default, when you create a user account without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.