mysql localhost登入和tcp/ip登入

myownstars發表於2015-07-29
MySqllocalhostTCP
小結：
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

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

  24. mysql> select user(),current_user();
  25. +-----------------+----------------+
  26. | user() | current_user() |
  27. +-----------------+----------------+
  28. | test1@127.0.0.1 | test1@% |
  29. +-----------------+----------------+
  30. 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

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

  24. mysql> select current_user(),user();
  25. +-----------------+-----------------+
  26. | current_user() | user() |
  27. +-----------------+-----------------+
  28. | test1@localhost | test1@localhost |
  29. +-----------------+-----------------+
  30. 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, ", 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


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

3 Unix socket VS IP socket
 
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章