Skip to main content

User - MySQL

<host>

localhost | <IP address>[/<net mask>] | %

<IP address>[/<net mask>]

10.0.0.1
10.0.0.0/255.255.255.0
10.0.%
  • % is wildcard

Create user​

create user '<user>'@'<host>' identified by '<password>';
create user alice@localhost identified by 'password';

Display user​

select user, host from mysql.user;
select * from mysql.user \G

Drop user​

drop user '<user>'@'<host>';
drop user alice@localhost;

Allow root login with password​

ALTER USER '<user>'@'<host>'
    IDENTIFIED WITH { mysql_native_password | caching_sha2_password }
    BY '<password>';
  • caching_sha2_password is added in MySQL 8
ALTER USER root@localhost
    IDENTIFIED WITH mysql_native_password
    BY 'password';