MySQL 8.0 user management change password creation user login encounters fascinating problems
1, MySQL version 8.0 creates the user according to the official document and sets the password. Then test the login and change the password.
1.1, create a user and log in to test
MySQL
mysql> create user "aplan"@"%" identified by "Aplan123@";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on aplan.* to "aplan"@"%";
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL
[root@test zhiwei]-sharp /usr/local/mysql/bin/mysql -u aplan -pAplan123@
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user "aplan"@"localhost" (using password: YES)
[root@test zhiwei]-sharp
[root@test zhiwei]-sharp
[root@test zhiwei]-sharp
[root@test zhiwei]-sharp /usr/local/mysql/bin/mysql -u aplan -p
Enter password:
ERROR 1045 (28000): Access denied for user "aplan"@"localhost" (using password: YES)
1.2, change user password
mysql> ALTER USER "aplan"@"%" IDENTIFIED BY "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 "IDENTIFIED BY "password"" at line 1
?????
The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password.
CREATE USER 'aplan'@'%' IDENTIFIED WITH mysql_native_password BY 'Aplan123@';
pay attention to the formatting of the text, sometimes there are problems with direct copy and paste execution elsewhere, such as copy execution from word. In addition, if the password has a special symbol, it can be enclosed in single quotation marks. The following is the test, and no problems are found:
mysql > create user 'aplan'@'%' identified by' Aplan123@';
Query OK, 0 rows affected (0.02 sec)
< table >
< thead > < tr > < th > mysql > select user,host
from mysql.user; < / th > < / tr > < / thead >
< tbody >
< tr >
< td > user < / td >
< td > host < / td >
< / tr >
< tr >
< td > aplan < / td >
< td >% < / td >
< / tr >
< tr >
< td > mysql.infoschema < / td >
< td > localhost < / td >
< / tr >
< tr >
< td > mysql.session < / td >
< td > localhost < / td >
< / tr >
< tr >
< td > mysql.sys < / td >
< td > localhost < / td >
< / tr >
< tr >
< td > root < / td >
< td > localhost < / td >
< / tr >
< / tbody >
< / table >
5 rows in set (0.00 sec)
mysql > flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql > exit
Bye
[root@linux-base mysql]-sharp mysql-uaplan-paired Aplan 123 plans'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 10
Server version: 8.0.12 MySQL Community Server-GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
< table >
< thead > < tr > < th > mysql > select user (); < / th > < / tr > < / thead >
< tbody >
< tr > < td > user () < / td > < / tr >
< tr > < td > aplan@localhost < / td > < / tr >
< / tbody >
< / table >
1 row in set (0.00 sec)
mysql > alter user aplan@'%' identified by 'password';
Query OK, 0 rows affected (0.04 sec)
mysql > exit
Bye
[root@linux-base mysql]-sharp mysql-uaplan-paired password'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 11
Server version: 8.0.12 MySQL Community Server-GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
< table >
< thead > < tr > < th > mysql > select user (); < / th > < / tr > < / thead >
< tbody >
< tr > < td > user () < / td > < / tr >
< tr > < td > aplan@localhost < / td > < / tr >
< / tbody >
< / table >
1 row in set (0.00 sec)
Thank you for your help. I think I've solved it. Thank you ~
