ITPub博客

首页 > 数据库 > MySQL > mysql localhost登录和tcp/ip登录

mysql localhost登录和tcp/ip登录

原创 MySQL 作者:myownstars 时间:2015-07-29 10:53:50 0 删除 编辑
小结:
1 mysql的账户名格式为 user@host,host为%时表示匹配任意主机或ip,但是localhost除外,也就是说必须为localhost显示创建账号;
2 登录mysql时若指定localhost选项则采用unix socket,而非tcp/ip协议;

以下为验证过程,
1 localhost VS % 的登录权限
先创建一个用户,其host为%,当前数据库没有localhost匿名用户

点击(此处)折叠或打开

  1. mysql> grant select on *.* to 'test1'@'%' identified by 'test1';
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> select host,user,password from mysql.user order by 1 desc ,2;
  4. +-----------------------------------------+--------------+-------------------------------------------+
  5. | host | user | password |
  6. +-----------------------------------------+--------------+-------------------------------------------+
  7. | localhost | root | |
  8. | ::1 | root | |
  9. | 127.0.0.1 | root | |
  10. | % | root | *96B86EC8D3F883B0C55D488A951E11B037E74816 |
  11. | % | test1 | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
  12. +-----------------------------------------+--------------+-------------------------------------------+
  13. 11 rows in set (0.00 sec)
尝试以unix socket方式登录,均失败

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql --socket=/data1-new/app/tmp/mysql55d.sock -utest1 -ptest1
  2. ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)
  3. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql -utest1 -ptest1 --socket=/data1-new/app/tmp/mysql55d.sock -hlocalhost
  4. ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)
另外,即便指定了socket选项,-h127.0.0.1也会强制采用tcp/ip连接登录

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql --socket=/data1-new/app/tmp/mysql55d.sock -utest1 -ptest1 -h127.0.0.1
  2. mysql> status
  3. --------------
  4. /data1-new/app/services/mysql55d/bin/mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1
  5. Connection id:1449940
  6. Current database:
  7. Current user:test1@127.0.0.1
  8. SSL:Not in use
  9. Current pager:stdout
  10. Using outfile:''
  11. Using delimiter:;
  12. Server version:5.5.31-log Source distribution
  13. Protocol version:10
  14. Connection:127.0.0.1 via TCP/IP
  15. Server characterset:utf8
  16. Db characterset:utf8
  17. Client characterset:utf8
  18. Conn. characterset:utf8
  19. TCP port:3306
  20. Uptime:79 days 12 hours 22 min 29 sec

  21. Threads: 1 Questions: 4746519297 Slow queries: 144823 Opens: 18675 Flush tables: 79 Open tables: 24 Queries per second avg: 690.890

  22. mysql> select user(),current_user();
  23. +-----------------+----------------+
  24. | user() | current_user() |
  25. +-----------------+----------------+
  26. | test1@127.0.0.1 | test1@% |
  27. +-----------------+----------------+
  28. 1 row in set (0.00 sec)
创建基于localhost的用户,

点击(此处)折叠或打开

  1. mysql> grant select on *.* to 'test1'@'localhost' identified by 'test1';
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> select host,user,password from mysql.user order by 1 desc ,2;
  4. +-----------------------------------------+--------------+-------------------------------------------+
  5. | host | user | password |
  6. +-----------------------------------------+--------------+-------------------------------------------+
  7. | localhost | root | |
  8. | localhost | test1 | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
  9. | ::1 | root | |
  10. | 127.0.0.1 | root | |
  11. | % | root | *96B86EC8D3F883B0C55D488A951E11B037E74816 |
  12. | % | test1 | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
  13. +-----------------------------------------+--------------+-------------------------------------------+
  14. 11 rows in set (0.00 sec)

再次以unix socket方式登录,成功

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql -utest1 -ptest1 --socket=/data1-new/app/tmp/mysql55d.sock
  2. mysql> status
  3. --------------
  4. /data1-new/app/services/mysql55d/bin/mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1
  5. Connection id:8
  6. Current database:
  7. Current user:test1@localhost
  8. SSL:Not in use
  9. Current pager:stdout
  10. Using outfile:''
  11. Using delimiter:;
  12. Server version:5.5.31-log Source distribution
  13. Protocol version:10
  14. Connection:Localhost via UNIX socket
  15. Server characterset:utf8
  16. Db characterset:utf8
  17. Client characterset:utf8
  18. Conn. characterset:utf8
  19. UNIX socket:/data1-new/app/tmp/mysql55d.sock
  20. Uptime:9 min 44 sec

  21. Threads: 1 Questions: 87 Slow queries: 4 Opens: 38 Flush tables: 2 Open tables: 2 Queries per second avg: 0.148

  22. mysql> select current_user(),user();
  23. +-----------------+-----------------+
  24. | current_user() | user() |
  25. +-----------------+-----------------+
  26. | test1@localhost | test1@localhost |
  27. +-----------------+-----------------+
  28. 1 row in set (0.00 sec)

2 localhost的登录协议
  1. On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:
  2. shell> mysql --host=127.0.0.1
  3. shell> mysql --protocol=TCP
  4. http://dev.mysql.com/doc/refman/5.6/en/connecting.htm
当指定-h localhost选项时,采用unix socket方式连接,

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# strace /data1/app/services/mysql55a/bin/mysql --socket=/data1/app/tmp/mysql55a.sock -hlocalhost -utest1 -ptest1
  2. execve("/data1/app/services/mysql55a/bin/mysql", ["/data1/app/services/mysql55a/bin"..., "--socket=/data1/app/tmp/mysql55a"..., "-hlocalhost", "-utest1", "-ptest1", "-o", "/tmp/tmp12.log"], [/* 30 vars */]) = 0
  3. ......
  4. stat("/etc/my.cnf", 0x7ffeb7006980) = -1 ENOENT (No such file or directory)
  5. stat("/etc/mysql/my.cnf", 0x7ffeb7006980) = -1 ENOENT (No such file or directory)
  6. stat("/data1/app/services/mysql55/etc/my.cnf", 0x7ffeb7006980) = -1 ENOENT (No such file or directory)
  7. stat("/root/.my.cnf", 0x7ffeb7006980) = -1 ENOENT (No such file or directory)
  8. socket(PF_LOCAL, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
  9. connect(3, {sa_family=AF_LOCAL, sun_path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
  10. close(3) = 0
  11. socket(PF_LOCAL, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
  12. connect(3, {sa_family=AF_LOCAL, sun_path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
  13. close(3) = 0
  14. ......
  15. socket(PF_LOCAL, SOCK_STREAM, 0) = 3
  16. fcntl(3, F_SETFL, O_RDONLY) = 0
  17. fcntl(3, F_GETFL) = 0x2 (flags O_RDWR)
  18. connect(3, {sa_family=AF_LOCAL, sun_path="/data1/app/tmp/mysql55a.sock"}, 110) = 0
  19. setsockopt(3, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  20. setsockopt(3, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  21. setsockopt(3, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
  22. setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
  23. read(3, "N\0\0\0\n5.5.31-log\0\372f\25\0rvz&Gs04\0\377\367!"..., 16384) = 82
  24. open("/usr/lib/locale/locale-archive", O_RDONLY|O_CLOEXEC) = 4
  25. fstat(4, {st_mode=S_IFREG|0644, st_size=106065056, ...}) = 0
  26. mmap(NULL, 106065056, PROT_READ, MAP_PRIVATE, 4, 0) = 0x7f21b9ad9000
  27. close(4) = 0
  28. stat("/data1/app/services/mysql55/share/charsets/Index.xml", 0x7ffeb7006100) = -1 ENOENT (No such file or directory)
  29. futex(0x90d540, FUTEX_WAKE_PRIVATE, 2147483647) = 0
  30. write(3, "`\0\0\1\215\246\17\0\0\0\0\1!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 100) = 100
  31. read(3, "I\0\0\2\377\25\4#28000Access denied for u"..., 16384) = 77
  32. shutdown(3, SHUT_RDWR) = 0
  33. close(3) = 0
  34. fstat(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 2), ...}) = 0
  35. mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f21c8956000
  36. write(2, "ERROR 1045 (28000): ", 20ERROR 1045 (28000): ) = 20
  37. write(2, "Access denied for user 'test1'@'"..., 64Access denied for user 'test1'@'localhost' (using password: YES)) = 64
  38. write(2, "\n", 1
  39. ) = 1
  40. write(1, "\7", 1) = 1
  41. gettimeofday({1436522608, 908777}, NULL) = 0
  42. exit_group(1) = ?

采用TCP/IP协议连接

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# strace /data1/app/services/mysql55a/bin/mysql -h127.0.0.1 -utest1 -ptest1
  2. execve("/data1/app/services/mysql55a/bin/mysql", ["/data1/app/services/mysql55a/bin"..., "-h127.0.0.1", "-utest1", "-ptest1"], [/* 30 vars */]) = 0
  3. ......
  4. socket(PF_LOCAL, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
  5. connect(3, {sa_family=AF_LOCAL, sun_path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
  6. close(3) = 0
  7. socket(PF_LOCAL, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
  8. connect(3, {sa_family=AF_LOCAL, sun_path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
  9. close(3) = 0
  10. ......
  11. socket(PF_INET, SOCK_STREAM, IPPROTO_TCP) = 3
  12. connect(3, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("127.0.0.1")}, 16) = 0
  13. fcntl(3, F_SETFL, O_RDONLY) = 0
  14. fcntl(3, F_GETFL) = 0x2 (flags O_RDWR)
  15. setsockopt(3, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  16. setsockopt(3, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  17. setsockopt(3, SOL_IP, IP_TOS, [8], 4) = 0
  18. setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = 0
  19. setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
  20. read(3, "N\0\0\0\n5.5.31-log\0\7g\25\0r2>$6$PH\0\377\367!"..., 16384) = 82
  21. ......
  22. poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout)
  23. write(3, "!\0\0\0\3select @@version_comment li"..., 37) = 37
  24. read(3, "\1\0\0\1\1'\0\0\2\3def\0\0\0\21@@version_comme"..., 16384) = 90
  25. write(1, "Your MySQL connection id is 1402"..., 83Your MySQL connection id is 1402631
  26. Server version: 5.5.31-log Source distribution
  27. ) = 83
  28. ......
我们再看一下分别通过unix socket和TCP/IP连接数据库时,mysqld都做了什么事情

点击(此处)折叠或打开

  1. [root@mysqlslave3 ~]# strace -p 23465
  2. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql -uroot -p -h127.0.0.1 -P3309
  3. Process 23465 attached
  4. restart_syscall(<... resuming interrupted call ...>) = 1
  5. fcntl(14, F_GETFL) = 0x2 (flags O_RDWR)
  6. fcntl(14, F_SETFL, O_RDWR|O_NONBLOCK) = 0
  7. accept(14, {sa_family=AF_INET, sin_port=htons(6771), sin_addr=inet_addr("127.0.0.1")}, [16]) = 17
  8. fcntl(14, F_SETFL, O_RDWR) = 0
  9. getsockname(17, {sa_family=AF_INET, sin_port=htons(3309), sin_addr=inet_addr("127.0.0.1")}, [16]) = 0
  10. fcntl(17, F_SETFL, O_RDONLY) = 0
  11. fcntl(17, F_GETFL) = 0x2 (flags O_RDWR)
  12. setsockopt(17, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  13. setsockopt(17, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  14. fcntl(17, F_SETFL, O_RDWR|O_NONBLOCK) = 0
  15. setsockopt(17, SOL_IP, IP_TOS, [8], 4) = 0
  16. setsockopt(17, SOL_TCP, TCP_NODELAY, [1], 4) = 0
  17. futex(0xf444a4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xf444a0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
  18. futex(0xf438c0, FUTEX_WAKE_PRIVATE, 1) = 1
  19. poll([{fd=14, events=POLLIN}, {fd=16, events=POLLIN}], 2, 4294967295


  20. [root@mysqlslave3 ~]# strace -p 23465
  21. [root@mysqlslave3 ~]# /data1-new/app/services/mysql55d/bin/mysql --socket=/data1-new/app/tmp/mysql55d.sock -uroot -P3309
  22. Process 23465 attached
  23. restart_syscall(<... resuming interrupted call ...>) = 1
  24. fcntl(16, F_GETFL) = 0x2 (flags O_RDWR)
  25. fcntl(16, F_SETFL, O_RDWR|O_NONBLOCK) = 0
  26. accept(16, {sa_family=AF_LOCAL, NULL}, [2]) = 17
  27. fcntl(16, F_SETFL, O_RDWR) = 0
  28. getsockname(17, {sa_family=AF_LOCAL, sun_path="/data1-new/app/tmp/mysql55d.sock"}, [35]) = 0
  29. fcntl(17, F_SETFL, O_RDONLY) = 0
  30. fcntl(17, F_GETFL) = 0x2 (flags O_RDWR)
  31. setsockopt(17, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  32. setsockopt(17, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
  33. fcntl(17, F_SETFL, O_RDWR|O_NONBLOCK) = 0
  34. setsockopt(17, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
  35. futex(0xf444a4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xf444a0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
  36. futex(0xf438c0, FUTEX_WAKE_PRIVATE, 1) = 1
  37. poll([{fd=14, events=POLLIN}, {fd=16, events=POLLIN}], 2, 4294967295

3 Unix socket VS IP socket
https://lists.freebsd.org/pipermail/freebsd-performance/2005-February/001143.html 
1 UNIX domain sockets use the file system as the address name space.
2 UNIX domain sockets have explicit knowledge that they're executing on
  the same system.  They avoid the extra context switch through the
  netisr, and a sending thread will write the stream or datagrams directly
  into the receiving socket buffer.  No checksums are calculated, no
  headers are inserted, no routing is performed, etc.
3 In general, the argument for implementing over TCP is that it gives you
location independence and immediate portability -- you can move the client
or the daemon, update an address, and it will "just work".  The sockets
layer provides a reasonable abstraction of communications services, so
it's not hard to write an application so that the connection/binding
portion knows about TCP and UNIX domain sockets, and all the rest just
uses the socket it's given.  So if you're looking for performance locally,
I think UNIX domain sockets probably best meet your need.  Many people
will code to TCP anyway because performance is often less critical, and
the network portability benefit is substantial.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-1755100/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3091516