How to Create New MySQL User
Before you can create a new MySQL user, you need to open a terminal window and launch the MySQL shell as the root user. To do so, enter the following command:
sudo mysql –u root –p
or rather than typing sudo each time you can enter:
sudo su mysql -u root -p
In either scenario next type in the root password and press Enter.
The prompt will change to
mysql>
Database list
Can you view the list of databases on the server using the following command:
SHOW databases;
And you will see an output similar to
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
Select the mysql database
use mysql;
User list
SELECT user,authentication_string,plugin,host FROM user;
Or as a specific user such as root
SELECT user,authentication_string,plugin,host FROM user WHERE user='root';
If you get the following error
ERROR 1046 (3D000): No database selected
Then you are not currently in a database, so there are no tables or records to look up. Review the database list above to view the databases on your server.
Create a new user
Next, create a new MySQL user with:
CREATE USER 'username' IDENTIFIED BY 'password';
Replace username and password with a username and password of your choice.
Alternatively, you can set up a user by specifying the machine hosting the database.
If you are working on the machine with MySQL, use username@localhost to define the user.
If you are connecting remotely, use username@ip_address, and replace ip_address with the actual address of the remote system hosting MySQL.
Subsequently, the command will be:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
or
CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';
You can also create a user that can connect from any machine with the command:
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Errors whilst creating a new user
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
To establish what your password policy requirements are, run the command:
SHOW VARIABLES LIKE 'validate_password%';
Which will output something like:
+--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)
Now you cross check the validation of your password against the server policy requirements. Or you can set the password policy level lower, for example:
SET GLOBAL validate_password.length=6;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.policy=LOW;
For more details go to MySQL documentation.
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'localhost'
Your first step to resolving this error is the flush the privileges.
FLUSH PRIVILEGES;
This query produced the following output....
Query OK, 0 rows affected (0.01 sec)
Attempting to create the user again, produced the same error!
To confirm the user that I was attempting wasn't in the system - even though the user list stated it wasn't you can run
DROP USER username@localhost;
However, as the user I was attempting to add wasn't in the list, there was nothing to delete. So the result not surprising was
Query OK, 0 rows affected (0.01 sec)
hmmm, according to the MySQL documentation, commands like CREATE USER, GRANT, REVOKE, and DROP USER do not require a subsequent FLUSH PRIVILEGES command. If you reads the docs... it's clear how come this is the case. It's because altering the MySQL tables directly does not reload the info into memory.
Interestingly, if I run the command
DROP USER username
The error is
ERROR 1396 (HY000): Operation DROP USER failed for 'username'@'%'
Therefore, if I use username@localhost
Which is interpreted as DROP USER 'username@localhost'@'%';
Yep completely wrong. The only time I get this error is when I do DROP USER user; like the doc suggests, but MySQL does not treat the '%' as a wildcard in a way that would drop all users at all hosts. It's not so wild after all. Or, it may be that it sometimes works when it deletes the localhost user and then tries to delete the one at %.
Te correct way to create a user is to run the command
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Changing username and password to your credentials. This outputs the screen information of
Query OK, 0 rows affected (0.01 sec)
However, on running
SELECT user,authentication_string,plugin,host FROM user;
The new username was sitting in the list.
Test, test and test
Before you go any further, check that you can log in to your new user account.