MySQL Users

Generated random passwords have a length of 20 characters. This length is controlled by the generated_random_password_length system variable.This option not available in older versions of MySQL or derivatives.
Password corpus is hard coded as "1234567890abcdefghijklmnopqrstuvwxyz" \ "ABCDEFGHIJKLMNOPQRSTUVWXYZ"\ ",.-;:_+*!%&/(){}[]<>@"

If you need control over what the password will be, then use...

CREATE USER 'myuser'@'myhost.mydomain' IDENTIFIED BY 'myPassword'

GRANT

GRANT ALL PRIVILEGES ON database.* TO myuser@'%';

To limit login attempts (e.g. to reduce risks of a Denial Of Service style of attack)...

GRANT USAGE ON *.* TO 'myuser'@'myhost' WITH MAX_CONNECTIONS_PER_HOUR 50;

Limits number of connection attempts by 'myuser'@'myhost to any database to 50 per hour

See other pages for examples of specific GRANT options...

MySQL Stored Procedures

REVOKE

REVOKE ALL PRIVILEGES ON database.* FROM myuser@%;

ALTER

ALTER USER 'myuser'@'myhost.mydomain' IDENTIFIED BY RANDOM PASSWORD;

SET PASSWORD FOR 'myuser'@'myhost.mydomain' TO RANDOM;

NOTE: On older versions of MySQL (8.0.18 and earlier) you will need to change passwords using...SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('mypassword');

If you need control over what the password will be, you can use...

ALTER USER 'myuser'@'myhost.mydomain' IDENTIFIED BY 'myPassword'

DROP

DROP USER 'myuser'@'localhost';

Variables

Modification of variables requires the SYSTEM_VARIABLES_ADMIN privilege

generated_random_password_length

Default: 20

Duplicate

Percona Toolkit

If you have the Percona Toolkit installed you can dump the CREATE statements and GRANTs for all users...

pt-show-grants --ask-pass --user root

MySQL Backup & Recovery

Reset Root Password

If you have lost the root password, you can do this to reset it. Note that this DOES require an outage.

systemctl stop mysql

mysqld_safe --skip-grant-tables --skip-networking &

mysql -u root

USE mysql

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'New-Password';

exit

ps aux | grep mysqld

kill -9 9999999

systemctl start mysql

Bibliography

https://dev.mysql.com/doc/refman/5.7/en/create-user.htmlhttps://dev.mysql.com/doc/refman/5.7/en/show-create-user.html
https://dev.mysql.com/doc/refman/8.0/en/create-user.htmlhttps://dev.mysql.com/doc/refman/8.0/en/roles.htmlhttps://dev.mysql.com/doc/refman/8.0/en/grant.htmlhttps://dev.mysql.com/doc/refman/8.0/en/privileges-provided.htmlhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
https://mysqlserverteam.com/everything-about-mysql-logins/https://linuxize.com/post/how-to-create-mysql-user-accounts-and-grant-privileges/#display-mysql-user-account-privileges https://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database/ https://stackoverflow.com/questions/5376427/cant-connect-to-local-mysql-server-through-socket-var-mysql-mysql-sock-38
https://stackoverflow.com/questions/20698335/error-1396-hy000-operation-drop-user-failed-for-userlocalhosthttps://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw
Reset Root Passwordhttps://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.htmlhttps://www.techrepublic.com/article/how-to-set-change-and-recover-a-mysql-root-password/https://www.percona.com/blog/reset-mysql-root-password-without-restarting-mysql-no-downtime/
Password Managementhttps://dev.mysql.com/doc/refman/8.0/en/password-management.htmlhttps://dev.mysql.com/doc/refman/5.7/en/set-password.htmlhttps://dev.mysql.com/worklog/task/?id=11772 (WL#11772: CREATE USER IDENTIFIED BY RANDOM PASSWORD)https://www.percona.com/blog/mysql-8-random-password-generator/https://dba.stackexchange.com/questions/267558/generating-a-password-that-matches-the-password-validation-component
MAX_CONNECTIONS_PER_HOURhttps://serverfault.com/questions/65255/log-mysql-login-attempts

Page updated

Google Sites

Report abuse