Analyze whether the percent sign % in MySQL user contains localhost?

What is the percentage of MySQL users that does not include localhost?

1 Preface

When operating MySQL, I found that sometimes only was created The account of % can be connected through localhost, but sometimes it cannot. I can’t find a satisfactory answer through online search. I just try it manually.

Recommended learning: "mysql video tutorial》

2 Two connection methods

The two connection methods mentioned here refer to when executing the mysql command,# The ##-h parameter is filled in with localhost or IP. The difference between the two connection methods is as follows

-h The parameter is localhost

When the

-h parameter is localhost, socket is actually used to connect (default connection method), the example is as follows

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket

From

Current user we can see that the user is xx@localhost, the connection method is Localhost via UNIX socket

-h and the parameter is IP

When the

-h parameter is IP, TCP is actually used to connect. The example is as follows

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8

From

Current userYou can see that the user is xx@127.0.0.1, and the connection method is TCP/IP

3 Differences between different versions

The test method is to see if it can connect. If you don’t want to see the test process, you can scroll to the end to see the conclusion

3.1 MySQL 8.0

Create user

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.07 sec)

Use localhost to log in

[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        Localhost via UNIX socket
...

Use IP to log in

[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        8
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP

The result shows the

8.0 version of MySQL, % including localhost

3.2 MySQL 5.7

Create % user

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)

Use localhost to log in

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
....

Use IP to log in

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
...

Result display

5.7 version MySQL, % including localhost

3.3 MySQL 5.6

Create user

db83-3306>>select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)

Use localhost to log in

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

Use IP to log in

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        3
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.10-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
......
--------------

The result shows

% of MySQL 5.6 does not include localhost

3.4 MySQL 5.1

Create user

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)

Use localhost to log in

[root@chengqm ~]# mysql -utest_user -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

Use IP to log in

[root@chengqm ~]# mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4901339
Server version: 5.1.73 Source distribution
========= 省略 ===========

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        4901339
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.1.73 Source distribution
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP

Result display

5.1 Version of % does not include localhost

3.5 MariaDB 10.3

Create user

db83-3306>>select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.11-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.001 sec)

Log in using localhost

[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

Use IP to log in

[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB-log MariaDB Server
========= 省略 ===========

MariaDB [(none)]> status
--------------
/usr/local/mariadb/bin/mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:        12
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.3.11-MariaDB-log MariaDB Server
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP

The result shows

MariaDB 10.3%excludinglocalhost

4 Conclusion

Version Whether the ##MySQL8.0MySQL5.7MySQL5.6Not includedMySQL5.1Not includedMariaDB 10.3Excludes

	% in the user includes localhost
includes
includes

The above is the detailed content of Analyze whether the percent sign % in MySQL user contains localhost?. For more information, please follow other related articles on the PHP Chinese website!