Shulou(Shulou.com)05/31 Report--

This article mainly introduces whether MySQL new users include localhost, it has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

Normal interpretation

% means that any client can connect

Localhost representatives can only connect locally

In general, permissions are added to those who can access the local database, and other machines are generally prohibited from accessing the local mysql port. If allowed, it is necessary to add a specified ip to access the local database, so as to ensure that the database will not be accessed remotely.

1 preface

When operating MySQL, I found that sometimes only% of accounts have been set up, which can be connected through localhost, but sometimes not, and online search can not find a satisfactory answer, so just test it manually.

2 two connection methods

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

-h parameter is localhost

When the-h parameter is localhost, the socket connection is actually used (the default connection method). The example is as follows

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

Enter password:

= omitted =

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, you can see that the user is xx@localhost and the connection method is Localhost via UNIX socket.

-h parameter is IP

When the-h parameter is IP, it is actually connected using TCP. The example is as follows

[mysql@mysql-test-83] $/ usr/local/mysql57/bin/mysql-utest_user-p-h227.0.0.1

Enter password:

= omitted =

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 user, you 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 be connected. If you don't want to see the test process, you can pull it to the end and see the conclusion.

3.1 MySQL 8.0

Create a 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)

Log in using localhost

[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

= omitted =

Mysql > status

-

/ usr/local/mysql80/bin/mysql Ver 8.0.11 for linux-glibc2.12 on x8634 (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

...

Log in using IP

[root@mysql-test-72] # / usr/local/mysql80/bin/mysql-utest_user-p-h227.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

= omitted =

Mysql > status

-

/ usr/local/mysql80/bin/mysql Ver 8.0.11 for linux-glibc2.12 on x8634 (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 results show that version 8.0 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)

Log in using localhost

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

= omitted =

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

....

Log in using IP

[mysql@mysql-test-83] $/ usr/local/mysql57/bin/mysql-utest_user-p-h227.0.0.1

Enter password:

= omitted =

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

...

The results show that version 5.7 of MySQL, including localhost

3.3 MySQL 5.6

Create a 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)

Log in using localhost

[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)

Log in using IP

[mysql@mysql-test-83] $/ usr/local/mysql57/bin/mysql-utest_user-p-h227.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)

= omitted =

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 results show that% of MySQL 5.6 does not include localhost

3.4 MySQL 5.1

Create a 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)

Log in using localhost

[root@chengqm] # mysql-utest_user-p

Enter password:

ERROR 1045 (28000): Access denied for user' test_user'@'localhost' (using password: YES)

Log in using IP

[root@chengqm] # mysql-utest_user-p-h227.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

= omitted =

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

The results show that version 5.1% does not include localhost

3.5 MariaDB 10.3

Create a 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)

Log in using IP

[mysql@mysql-test-83] $/ usr/local/mariadb/bin/mysql-utest_user-p-h227.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

= omitted =

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 results show that% of MariaDB 10.3 does not include localhost

4 conclusion version of the user whether to include localhostMySQL8.0 including MySQL5.7, including MySQL5.6 does not include MySQL5.1 does not include MariaDB 10.3 does not include thank you for reading this article carefully, I hope the editor to share the "MySQL new users include localhost?" this article is helpful to you, but also hope that you support, pay attention to the industry information channel, more related knowledge waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.