你真的了解如何登录MySQL么？

 　　昨天同事碰到一个问题，在MySQL上创建了一个用户，host设置为%，本地竟然无法登录。创建一个host为localhost的同名用户后，本地可以登录。感脚很怪异，下面我们重新分析产生这个问题的原因。

1. 现场重现

*root本地登录

Shell>./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

*切换到mysql库下

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

*创建用户

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

mysql> select user,host from user;
+------+-----------+
| user | host      |
+------+-----------+
| repl | %         |
| u1   | %         |
| root | 127.0.0.1 |
| root | ::1       |
|      | Ubuntu    |
| root | Ubuntu    |
|      | localhost |
| repl | localhost |
| root | localhost |
+------+-----------+
9 rows in set (0.00 sec)

*本地登录——使用新用户+密码

Shell>./mysql -uu1 --protocol=tcp --port=13000 -p1111111
ERROR 1045 (28000): Access denied for user 'u1'@'localhost' (using password: YES)

*本地登录——使用新用户+空密码登录

Shell>./mysql -uu1 --protocol=tcp --port=13000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| @localhost   |
+--------------+
1 row in set (0.00 sec)

mysql> select user();
+--------------+
| user()       |
+--------------+
| u1@localhost |
+--------------+
1 row in set (0.00 sec)

*远程登录——使用新用户+密码

AAA@-ThinkPad:~/mysql-bin/bin$ ./mysql -uu1 --port=13000 -h192.168.1.103 -p1111111
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| u1@%           |
+----------------+
1 row in set (0.01 sec)

*远程登录——使用新用户+空密码

AAA@-ThinkPad:~/mysql-bin/bin$ ./mysql -uu1 --port=13000 -h192.168.1.103
ERROR 1045 (28000): Access denied for user 'u1'@'-ThinkPad' (using password: NO)

tips：

user与current_user的区别：
user：client提供给server的用户名和密码
current_user：连接到server上的真正的用户名和密码

2. 现场分析

　　上面进行了本地登录和远程登录的实验，远程登录与预期一致，本地登录出现了问题。

　　本地登录使用密码登录时竟然失败，不使用时竟然成功，即使成功但是current_user竟然是‘@localhost’，也就是说根本不是u1用户登录成功，而是‘@localhost’登录成功。

　　我们在上面的user表的信息可以看到确实存在一个user为空，host为localhost的用户。这是个新库创建时自带的用户。

　　下面就有两个疑问了：

        为嘛本地登录使用密码不能成功？为嘛不用密码登录成功但登录用户却是‘@localhost’呢？结合代码来看看真正的认证过程吧。

 

3. 代码分析

　　关于用户认证的代码基本都在sql/sql_acl.cc文件中，acl即access control list（访问控制列表），MySQL在系统启动时会调用acl_load，将mysql.user表中的信息全部加载到系统中，这里不涉及加载，只为寻找真相。为了方便用户自行跟踪，给一个身份验证的堆栈。

(gdb) bt
#0  compare_hostname (host=0x1d80580, hostname=0xb45e52 "localhost", ip=0x1dc8430 "127.0.0.1")
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:2041
#1  0x00000000005990dc in find_mpvio_user (mpvio=0x7fffe8165530) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:8215
#2  0x000000000059a651 in parse_client_handshake_packet (mpvio=0x7fffe8165530, buff=0x7fffe8165418, pkt_len=58)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:8746
#3  0x000000000059adba in server_mpvio_read_packet (param=0x7fffe8165530, buf=0x7fffe8165418)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:8970
#4  0x000000000059c338 in native_password_authenticate (vio=0x7fffe8165530, info=0x7fffe8165548)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:9547
#5  0x000000000059b350 in do_auth_once (thd=0x1d5e470, auth_plugin_name=0xfd1280, mpvio=0x7fffe8165530)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:9133
#6  0x000000000059b7fe in acl_authenticate (thd=0x1d5e470, connect_errors=0, com_change_user_pkt_len=0)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:9269
#7  0x00000000006d4ed9 in check_connection (thd=0x1d5e470) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_connect.cc:524
#8  0x00000000006d5034 in login_connection (thd=0x1d5e470) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_connect.cc:582
#9  0x00000000006d5500 in thd_prepare_connection (thd=0x1d5e470) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_connect.cc:716
#10 0x00000000006d599a in do_handle_one_connection (thd_arg=0x1d5e470)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_connect.cc:782
#11 0x00000000006d54c9 in handle_one_connection (arg=0x1d5e470) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_connect.cc:708
#12 0x00007ffff6ee3efc in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#13 0x00007ffff6c1e59d in clone () from /lib/x86_64-linux-gnu/libc.so.6
#14 0x0000000000000000 in ?? ()

　　堆栈上的#4 native_password_authenticate便是真正的身份认证函数，感兴趣的同学可以自己仔细看，我们具体看下find_mpvio_user函数：

static bool find_mpvio_user(MPVIO_EXT *mpvio)
{
  DBUG_ENTER("find_mpvio_user");
  DBUG_PRINT("info", ("entry: %s", mpvio->auth_info.user_name));
  DBUG_ASSERT(mpvio->acl_user == 0);
  mysql_mutex_lock(&acl_cache->lock);
  for (uint i=0; i < acl_users.elements; i++)
  {
    ACL_USER *acl_user_tmp= dynamic_element(&acl_users, i, ACL_USER*);
    if ((!acl_user_tmp->user || 
         !strcmp(mpvio->auth_info.user_name, acl_user_tmp->user)) &&
        compare_hostname(&acl_user_tmp->host, mpvio->host, mpvio->ip))
    {
      mpvio->acl_user= acl_user_tmp->copy(mpvio->mem_root);
      if (acl_user_tmp->plugin.str == native_password_plugin_name.str ||
          acl_user_tmp->plugin.str == old_password_plugin_name.str)
        mpvio->acl_user_plugin= acl_user_tmp->plugin;
      else
        make_lex_string_root(mpvio->mem_root, 
                             &mpvio->acl_user_plugin, 
                             acl_user_tmp->plugin.str, 
                             acl_user_tmp->plugin.length, 0);
      break;
    }
 
....
}

　　acl_users即为缓存mysql.user表中数据的动态数组。函数的基本逻辑是逐个遍历acl_users中的每个user，首先判断acl_user_tmp的用户名为空或者用户名和登录的用户名相同，然后比较host的值，由于‘@localhost’在列表中比‘u1@%’靠前，而且我们是本地登录，这就导致acl_user_tmp为空且host比较成功，就返回了‘@localhost’。这就是为什么current_user为‘@localhost’的原因。

　　那么为嘛加了密码就不行了呢？这是由于在获得了内存中的user后，会进行密码的验证。

static int native_password_authenticate(MYSQL_PLUGIN_VIO *vio,
                                        MYSQL_SERVER_AUTH_INFO *info)
{
 
....
  if (pkt_len == 0) /* no password */
    DBUG_RETURN(mpvio->acl_user->salt_len != 0 ? CR_ERROR : CR_OK);

  info->password_used= PASSWORD_USED_YES;
  if (pkt_len == SCRAMBLE_LENGTH)
  {
    if (!mpvio->acl_user->salt_len)
      DBUG_RETURN(CR_ERROR);

    DBUG_RETURN(check_scramble(pkt, mpvio->scramble, mpvio->acl_user->salt) ?
                CR_ERROR : CR_OK);
  }

....
}

　　如果输入了密码，那么就会进入pkt_len==SCRAMBLE_LEN的分支，而此时的用户为系统默认的用户'@localhost',所以mpvio->acl_user->salt_len必然为0，
故返回ERROR。

　　为嘛‘@localhost’在列表中比‘u1@%’靠前，这就涉及到acl_users的排序问题了，先给出一个堆栈：

#0  get_sort (count=1) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:1266
#1  0x0000000000580427 in acl_load (thd=0x1db0c50, tables=0x7fffffffc970)
    at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:872
#2  0x00000000005815bf in acl_reload (thd=0x1db0c50) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:1174
#3  0x000000000057f8b6 in acl_init (dont_read_acl_tables=false) at /home/loushuai/src/mysql-server/mysql-5.5/sql/sql_acl.cc:644
#4  0x000000000055973b in mysqld_main (argc=11, argv=0x12d6f38) at /home/loushuai/src/mysql-server/mysql-5.5/sql/mysqld.cc:4551
#5  0x0000000000552154 in main (argc=2, argv=0x7fffffffe0c8) at /home/loushuai/src/mysql-server/mysql-5.5/sql/main.cc:25

#1 现在执行到

acl_user.sort=get_sort(2,acl_user.host.hostname,acl_user.user);

用于计算当前acl_user的sort值，用于后面进行重新排序。
我们看下具体的排序函数：

static ulong get_sort(uint count,...)
{
  va_list args;
  va_start(args,count);
  ulong sort=0;

  /* Should not use this function with more than 4 arguments for compare. */
  DBUG_ASSERT(count <= 4);

  while (count--)
  {
    char *start, *str= va_arg(args,char*);
    uint chars= 0;
    uint wild_pos= 0;           /* first wildcard position */

    if ((start= str))
    {
      for (; *str ; str++)
      {
        if (*str == wild_prefix && str[1])
          str++;
    //如果碰到%或者_,则记录wild_pos
        else if (*str == wild_many || *str == wild_one) 
        {
          wild_pos= (uint) (str - start) + 1;
          break;
        }
        chars= 128;                             // Marker that chars existed
      }
    }
    sort= (sort << 8) + (wild_pos ? min(wild_pos, 127) : chars);
  }
  va_end(args);
  return sort;
}

从上面可以看出，根据host和user连个字段的值进行排序（host作为高位区分（sort<<8）），当存在%时，使用%的位置作为sort，否则就用128.
下面我们就具体看下一下三个用户的排序：

no    host             user
1.    localhost     u1
2.    localhost      
3.    %          u1

　　首先比较host，显然1，2 大于3，因为%用的是wild_pos，而1，2走chars=128.
然后比较1，2，显然1的优先级高，因为2为空。

　　故在acl_users中的顺序为1 2 3，也就是说，如果你创建了一个u1@localhost，那么使用u1就可以登录成功，注意，这时候就需要创建时的密码了。

　　终于找到原因了，往往细微的地方，我们往往拿捏不住，不断的发觉这些细微现象的真相，才能有所提高。

　　真相只有一个:-)