mysql user creation, password , grant permissions user management guide

syntax works with mysql 5.7 and 8 above

Create mysql user with password

CREATE USER ‘user1’@’localhost’ IDENTIFIED WITH mysql_native_password by ‘<UNIQUEPASSWORD>’;

from linuxcommad line

sudo mysql -e “CREATE USER ‘newrelic’@’localhost’ IDENTIFIED BY ‘YOUR_SELECTED_PASSWORD’;”

create user with ssha method

CREATE USER ‘user1’@’localhost’ IDENTIFIED WITH caching_sha2_password by ‘<UNIQUEPASSWORD>’;

change user password

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘new-Root-Password’;

ALTER USER ‘newrelic’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘new-Root-Password’;

ALTER USER ‘newrelic’@’127.0.0.1’ IDENTIFIED WITH caching_sha2_password BY ‘Raazk23-234’;

note: client & server mysql authentication should be same.

grant all permissions

Gant selectec permissions

view user permissions

Set user limits

ALTER USER ‘datadog’@’localhost’ WITH MAX_USER_CONNECTIONS 5;

flush privileges

to reflect changes

FLUSH PRIVILEGES;

view mysql users and password strings

SELECT User, Host, Password, FROM mysql.user;

mysql> SELECT User, Host, Password, FROM mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax;

password column removed in mysql 8.

mysql list users

mysql user authentication_string,plugin

view mysql user table columns mysql

SHOW COLUMNS FROM my_table;

SHOW COLUMNS FROM mysql.user;

SHOW COLUMNS FROM mysql;

| plugin | char(64) | NO | | ca ching_sha2_password | |
| authentication_string | text | YES | | NU LL | |
| password_expired | enum(‘N’,’Y’) | NO | | N

mysql delete remove user

mysql> DROP USER newrelic;

related Access denied for user root

mysql password reset

warning Plugin sha256_password reported

default_authentication_plugin = mysql nATIVE,

monitoring tool was indeed the culprit !

INSTALL PLUGIN CONNECTION_CONTROL

helps to identify the user, mycase grafana.

sudo systemctl stop grafana-agent.service