Method of modifying root password in MySQL
As the first barrier to database security, the root account and its password.
Learning to manage the root password is undoubtedly a basic skill for the gatekeeper and the unlocking man.
Know the password
When the password is known, you can log in to the MySQL database and modify it through alter user (MySQL version 5.7.6 or above) or SET PASSWORD command.
1. Use the ALTER USER command to change the password
In MySQL 5.7.6 or MariaDB 10.1.20 or above, you can use the ALTER USER command to modify the user password
Example:
--Sign in mysql -bash-4.1$ mysql -u root -p mysql> alter user root identified by 'MyNewPass4!'; Query OK, 0 rows affected (0.02 sec)
2. Use the SET PASSWORD command to modify the password
Use the SET PASSWORD command to modify the user password of Mysql.
MySQL version after 5.7:
Example:
SET PASSWORD FOR <user name>@<host name> = 'my_new_password'; --Current user mysql> SET PASSWORD = 'my_new_password'; Query OK, 0 rows affected (0.33 sec)
MySQL before 5.7 requires the PASSWORD() function.
Example:
mysql> SET PASSWORD = PASSWORD('new_password');
reference resources:
https://dev.mysql.com/doc/refman/8.0/en/alter-user.html
13.7.1.1 ALTER USER Statement
https://dev.mysql.com/doc/refman/8.0/en/set-password.html
13.7.1.10 SET PASSWORD Statement
Forget the password
If you don't know the root password (the password is lost), you can log in to mysql to modify the password in the following way.
Use – init file
You can start MySQL by creating a local file containing password statements and then using the – init file option.
Example:
Create a local file
-bash-4.1$ vi /refresh/home/init-file.txt
The contents of the document are as follows:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass2!';
Content of confirmation document:
-bash-4.1$ cat /refresh/home/init-file.txt ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass2!';
- Close MySQL
-bash-4.1$ sudo service mysqld status mysqld (pid 2031) is running... -bash-4.1$ sudo service mysqld stop Stopping mysqld: [ OK ] -bash-4.1$ sudo service mysqld status mysqld is stopped
After Linux 7, you can use the following systemctl command.
systemctl stop mysqld.service perhaps /etc/init.d/mysqld stop
- Start MySQL with the following command
-bash-4.1$ sudo mysqld --user=mysql --init-file=/refresh/home/init-file.txt & [1] 2255
-
Test the connection with the new password.
mysql -u root -p
Use – skip grant tables
You can use the – skip grant tables option to start Mysql to skip permission verification and change the root password after logging in to MySQL.
- Close MySQL
-bash-4.1$ sudo service mysqld status mysqld (pid 2031) is running... -bash-4.1$ sudo service mysqld stop Stopping mysqld: [ OK ] -bash-4.1$ sudo service mysqld status mysqld is stopped
After Linux 7, you can use the following systemctl command.
systemctl stop mysqld.service perhaps /etc/init.d/mysqld stop
- Start MySQL with – skip grant tables
-bash-4.1$ sudo mysqld --skip-grant-tables --user=mysql & [1] 29840 -bash-4.1$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
- Reload the authorization table so that the account management statement works.
mysql> flush privileges; Query OK, 0 rows affected (0.05 sec)
- Change the root password
mysql> ALTER USER root identified by 'MyNewPass1!'; Query OK, 0 rows affected (0.02 sec)
- Test login MySQL with new password
-bash-4.1$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
- Restart MySQL normally
-bash-4.1$ sudo service mysqld status mysqld (pid 29841) is running... -bash-4.1$ sudo service mysqld stop Stopping mysqld: [ OK ] [1]+ Done sudo mysqld --skip-grant-tables --user=mysql -bash-4.1$ sudo service mysqld status mysqld is stopped -bash-4.1$ sudo service mysqld start Starting mysqld: [ OK ] perhaps -bash-4.1$ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Test connection:
-bash-4.1$ mysql -u root -p
reference resources:
https://dev.mysql.com/doc/refman/8.0/en/server-options.html
5.1.7 Server Command Options
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
B.3.3.2 How to Reset the Root Password
B.3.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems
B.3.3.2.3 Resetting the Root Password: Generic Instructions
Common problems and Solutions
mysql> set password for root@localhost = password('123');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('123')' at line 1
The password is not quoted
mysql> alter user root@localhost identified by pass;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pass' at line 1
ERROR 1819 (HY000)
ERROR 1819 (HY000) occurred while changing the password
mysql> alter user root@localhost identified by 'pass'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
Reason: the password does not meet the security requirements
The higher version of MySQL database (after 5.7) will have validate by default_ Password plug-in, which will enforce password authentication policy when enabled. If the new password does not meet the password verification policy, an ERROR 1819 (HY000) error will be reported.
| level | length | Character type | Word matching |
|---|---|---|---|
| LOW | At least 8 characters | N/A | N/A |
| MEDIUM | ditto | Must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character and 1 special (non alphanumeric) character | N/A |
| STRONG | ditto | ditto | Must not match words in dictionary file |
| Parameters (5.7) | validate_password_length | validate_password_number_count validate_password_mixed_case_count validate_password_special_char_count | validate_password_dictionary_file |
| Parameter (8.0) | validate_password.length | validate_password.number_count validate_password.mixed_case_count validate_password.special_char_count | validate_password.dictionary_file |
The default password policy is MEDIUM.
mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | 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.14 sec)
resolvent:
Reduce the level of policy
--5.7 mysql> SET GLOBAL validate_password_policy=LOW; OR mysql> SET GLOBAL validate_password_policy=0; --8.0 mysql> set global validate_password.policy=LOW; OR mysql> set global validate_password.policy=0;
Example:
mysql> set global validate_password.policy=LOW; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+-------+ | 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 | LOW | | validate_password.special_char_count | 1 | +--------------------------------------+-------+ 7 rows in set (0.00 sec)
Modify policy item separately
Example:
mysql> alter user root identified by 'pass'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET GLOBAL validate_password.length = 4; Query OK, 0 rows affected (0.00 sec) mysql> alter user root identified by 'pass'; Query OK, 0 rows affected (0.05 sec) mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 4 | ★★ | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | LOW | | validate_password.special_char_count | 1 | +--------------------------------------+-------+ 7 rows in set (0.00 sec)
reference resources:
https://dev.mysql.com/doc/refman/8.0/en/validate-password.html
6.4.3 The Password Validation Component
ERROR 1396 (HY000)
mysql> alter user root@localhost identified by 'my_new_pass'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
reason:
User's host is inconsistent.
mysql> alter user root@localhost identified by 'my_new_pass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
resolvent:
Modify or remove the host name.
Example:
mysql> alter user root@'%' identified by 'my_new_pass'; Query OK, 0 rows affected (0.02 sec) perhaps mysql> alter user root identified by 'my_new_pass'; Query OK, 0 rows affected (0.04 sec)
ERROR 1064 (42000)
mysql> SET PASSWORD = PASSWORD('new_password');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('new_password')' at line 1
Reason: the PASSWORD() function is not required after MySQL 5.7.
Example:
mysql> SET PASSWORD = 'my_new_password'; Query OK, 0 rows affected (0.02 sec)
Error starting with – skip grant tables -- user = MySQL option: Permission denied
Error starting with – skip grant tables -- user = MySQL option: Permission denied
-bash-4.1$ mysqld --skip-grant-tables --user=mysql & [1] 29806 -bash-4.1$ 2021-02-26T00:10:32.691961Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test 2021-02-26T00:10:32.692079Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 29806 2021-02-26T00:10:32.698349Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test 2021-02-26T00:10:32.698375Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive 2021-02-26T00:10:32.698711Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root 2021-02-26T00:10:32.699063Z 0 [ERROR] [MY-010187] [Server] Could not open file '/var/log/mysqld.log' for error logging: Permission denied 2021-02-26T00:10:32.699141Z 0 [ERROR] [MY-010119] [Server] Aborting 2021-02-26T00:10:32.699366Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL. [1]+ Exit 1 mysqld --skip-grant-tables --user=mysql -bash-4.1$ -bash-4.1$ -bash-4.1$ mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Solution: use sudo or root
Example:
-bash-4.1$ sudo mysqld --skip-grant-tables --user=mysql & [1] 29840 -bash-4.1$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>