昨天同事碰到一个问题，在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就可以登录成功，注意，这时候就需要创建时的密码了。
终于找到原因了，往往细微的地方，我们往往拿捏不住，不断的发觉这些细微现象的真相，才能有所提高。
真相只有一个:-)