如何理解MySQL用户中的百分号%是否包含localhost
这篇文章主要介绍“如何理解MySQL用户中的百分号%是否包含localhost”，在日常操作中，相信很多人在如何理解MySQL用户中的百分号%是否包含localhost问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”如何理解MySQL用户中的百分号%是否包含localhost”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
1 前言操作MySQL的时候发现，有时只建了%的账号，可以通过localhost连接，有时候却不可以，网上搜索也找不到满意的答案，干脆手动测试一波
这里说的两种连接方法指是执行mysql命令时，-h参数填的是localhost还是IP, 两种连接方式的区别如下
当-h参数为localhost的时候，实际上是使用socket连接的（默认连接方式）, 实例如下
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-hlocalhostEnterpassword:=========省略===========mysql>status/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapperConnectionid:9Currentdatabase:Currentuser:test_user@localhostSSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.7.21-logMySQLCommunityServer(GPL)Protocolversion:10Connection:LocalhostviaUNIXsocket
从Current user可以看到用户是xx@localhost, 连接方式为Localhost via UNIX socket
当-h参数为IP的时候，实际上是使用TCP连接的, 实例如下
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h227.0.0.1Enterpassword:=========省略===========mysql>status--------------/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapperConnectionid:11Currentdatabase:Currentuser:test_user@127.0.0.1SSL:CipherinuseisDHE-RSA-AES256-SHACurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.7.21-logMySQLCommunityServer(GPL)Protocolversion:10Connection:127.0.0.1viaTCP/IPServercharacterset:utf8
从Current user可以看到用户是xx@127.0.0.1, 连接方式为TCP/IP
测试方法就是看能不能连接，如果不想看测试过程可以拉到最后看结论
3.1 MySQL 8.0创建用户mysql>selectversion();+-----------+|version()|+-----------+|8.0.11|+-----------+1rowinset(0.00sec)mysql>createusertest_user@'%'identifiedby'test_user';QueryOK,0rowsaffected(0.07sec)使用 localhost 登录
[root@mysql-test-72~]#/usr/local/mysql80/bin/mysql-utest_user-p-hlocalhostEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis9Serverversion:8.0.11MySQLCommunityServer-GPL=========省略===========mysql>status--------------/usr/local/mysql80/bin/mysqlVer8.0.11forlinux-glibc2.12onx86_64(MySQLCommunityServer-GPL)Connectionid:9Currentdatabase:Currentuser:test_user@localhostSSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:8.0.11MySQLCommunityServer-GPLProtocolversion:10Connection:LocalhostviaUNIXsocket...使用 IP 登录
[root@mysql-test-72~]#/usr/local/mysql80/bin/mysql-utest_user-p-h227.0.0.1Enterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis8Serverversion:8.0.11MySQLCommunityServer-GPL=========省略===========mysql>status--------------/usr/local/mysql80/bin/mysqlVer8.0.11forlinux-glibc2.12onx86_64(MySQLCommunityServer-GPL)Connectionid:8Currentdatabase:Currentuser:test_user@127.0.0.1SSL:CipherinuseisDHE-RSA-AES128-GCM-SHA256Currentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:8.0.11MySQLCommunityServer-GPLProtocolversion:10Connection:127.0.0.1viaTCP/IP
结果显示8.0版本的MySQL, % 包括localhost
db83-3306>>createusertest_user@'%'identifiedby'test_user';QueryOK,0rowsaffected(0.00sec)使用 localhost 登录
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-hlocalhost=========省略===========mysql>status/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapperConnectionid:9Currentdatabase:Currentuser:test_user@localhostSSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.7.21-logMySQLCommunityServer(GPL)Protocolversion:10Connection:LocalhostviaUNIXsocket....使用 IP 登录
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h227.0.0.1Enterpassword:=========省略===========mysql>status--------------/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapperConnectionid:11Currentdatabase:Currentuser:test_user@127.0.0.1SSL:CipherinuseisDHE-RSA-AES256-SHACurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.7.21-logMySQLCommunityServer(GPL)Protocolversion:10Connection:127.0.0.1viaTCP/IPServercharacterset:utf8...
结果显示5.7版本的MySQL, % 包括localhost
db83-3306>>selectversion();+------------+|version()|+------------+|5.6.10-log|+------------+1rowinset(0.00sec)db83-3306>>createusertest_user@'%'identifiedby'test_user';QueryOK,0rowsaffected(0.00sec)使用 localhost 登录
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-hlocalhostEnterpassword:ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)使用 IP 登录
[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h227.0.0.1Enterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis3Serverversion:5.6.10-logMySQLCommunityServer(GPL)=========省略===========mysql>status--------------/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapperConnectionid:3Currentdatabase:Currentuser:test_user@127.0.0.1SSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.6.10-logMySQLCommunityServer(GPL)Protocolversion:10Connection:127.0.0.1viaTCP/IP......--------------
结果显示MySQL 5.6的%不包括localhost
mysql>selectversion();+-----------+|version()|+-----------+|5.1.73|+-----------+1rowinset(0.00sec)mysql>createusertest_user@'%'identifiedby'test_user';QueryOK,0rowsaffected(0.00sec)使用 localhost 登录
[root@chengqm~]#mysql-utest_user-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)使用 IP 登录
[root@chengqm~]#mysql-utest_user-p-h227.0.0.1Enterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis4901339Serverversion:5.1.73Sourcedistribution=========省略===========mysql>status--------------mysqlVer14.14Distrib5.1.73,forredhat-linux-gnu(x86_64)usingreadline5.1Connectionid:4901339Currentdatabase:Currentuser:test_user@127.0.0.1SSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.1.73SourcedistributionProtocolversion:10Connection:127.0.0.1viaTCP/IP
结果显示 5.1 版本的%不包括localhost
db83-3306>>selectversion();+---------------------+|version()|+---------------------+|10.3.11-MariaDB-log|+---------------------+1rowinset(0.000sec)db83-3306>>createusertest_user@'%'identifiedby'test_user';QueryOK,0rowsaffected(0.001sec)使用 localhost 登录
[mysql@mysql-test-83~]$/usr/local/mariadb/bin/mysql-utest_user-p-hlocalhostEnterpassword:ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)使用 IP 登录
[mysql@mysql-test-83~]$/usr/local/mariadb/bin/mysql-utest_user-p-h227.0.0.1Enterpassword:WelcometotheMariaDBmonitor.Commandsendwith;or\g.YourMariaDBconnectionidis12Serverversion:10.3.11-MariaDB-logMariaDBServer=========省略===========MariaDB[(none)]>status--------------/usr/local/mariadb/bin/mysqlVer15.1Distrib10.3.11-MariaDB,forLinux(x86_64)usingreadline5.1Connectionid:12Currentdatabase:Currentuser:test_user@127.0.0.1SSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Server:MariaDBServerversion:10.3.11-MariaDB-logMariaDBServerProtocolversion:10Connection:127.0.0.1viaTCP/IP
结果显示MariaDB 10.3的%不包括localhost
%是否包括localhost到此，关于“如何理解MySQL用户中的百分号%是否包含localhost”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
