MySql - User

About

user managemet in mysql

Properties

host

  • % will allow a connection from all machines
  • ip will allow a connection only for this IP
  • name will allow a connection only for this name

You can use a name if the global variable skip_name_resolve is equal to false (otherwise it will not be resolved to an IP)

SHOW GLOBAL VARIABLES LIKE 'skip_name_resolve';

Management

Create

  • 8.0
CREATE USER 'userName'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';
  • 5.*
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

List

SELECT user, host FROM mysql.user;
user            |host     |
----------------|---------|
root            |%        |
crawler         |localhost|
healthchecker   |localhost|
mysql.infoschema|localhost|
mysql.session   |localhost|
mysql.sys       |localhost|
root            |localhost|

Update

UPDATE mysql.user SET host = '192.168.1.42' WHERE host = 'master';
FLUSH PRIVILEGES;

Reset password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Powered by ComboStrap