一、事件说明

事件的起因是这样的，我们的数据库自动化运维平台需要建一个统一的账号用来管理数据库。这看起来很简单，我用ansible在每个master上进行了如下操作

  1. mysql -e "grant all privileges on *.* to admin_test@'10.%' identified by 'abc$hthR+aaa';"

OK，事情看起来很顺利，但当我用这个管理员账号依次去连接master时，出现了如下连接错误：

>>> from app.models.agentconf import Db_info, db
>>> 
>>> 
>>> dbinfo = db.session.query(Db_info).filter(Db_info.db_role=='master')
>>> for db in dbinfo:
...     execute_sql(db.ip, int(db.port),"""sql""")
... 
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
  File "<stdin>", line 3, in execute_sql
  File "/opt/venv_py27/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/opt/venv_py27/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'admin_test'@'10.xxxx' (using password: YES)")

纳尼，我十分确信我没有搞错密码啊，但是为什么出现密码错误呢？

二、情景复现

为了一探究竟，我在找了一个相同版本的测试环境，并且我打开了general_log

mysql3306 -e "grant all privileges on *.* to admin_test@'10.%' identified by 'abc$hthR+aaa';"

查看general_log如下：

803 Query     GRANT ALL PRIVILEGES ON *.* TO 'admin_test'@'%' IDENTIFIED BY PASSWORD '*314F43C6517782AB849AC666BBCDCCABDF059DC3'

可以看到加密串为*314F43C6517782AB849AC666BBCDCCABDF059DC3
这与user表的password字段对应

MySQL [mysql]> select user,host,password from user where user='admin_test' and host='%';
+------------+------+-------------------------------------------+
| user       | host | password                                  |
+------------+------+-------------------------------------------+
| admin_test | %    | *314F43C6517782AB849AC666BBCDCCABDF059DC3 |
+------------+------+-------------------------------------------+
1 row in set (0.00 sec)

我们使用password函数对密码加密测试一下

MySQL [mysql]> select password('abc$hthR+aaa');
+-------------------------------------------+
| password('abc$hthR+aaa')                  |
+-------------------------------------------+
| *52895896DD4CFE067D0D2311C05491918917CAD4 |
+-------------------------------------------+
1 row in set (0.01 sec)

尼玛，这也对不上啊？是不是这个$字符的原因，我们加个转义试试

MySQL [mysql]> select password('abc\\$hthR+aaa');
+-------------------------------------------+
| password('abc\\$hthR+aaa')                 |
+-------------------------------------------+
| *52895896DD4CFE067D0D2311C05491918917CAD4 |
+-------------------------------------------+
1 row in set (0.00 sec)

这看起来也没啥变化，经过多次实验测试以后，发现问题确实出在了$字符上。如果我用下面的方式来授权也没有任何问题

mysql3306 -e "grant all privileges on *.* to admin_test1@'%' identified by 'abc\\$hthR+aaa';"
MySQL [mysql]> select user,host,password from user where user='admin_test1';
+-------------+------+-------------------------------------------+
| user        | host | password                                  |
+-------------+------+-------------------------------------------+
| admin_test1 | %    | *52895896DD4CFE067D0D2311C05491918917CAD4 |
+-------------+------+-------------------------------------------+
1 row in set (0.00 sec)
MySQL [mysql]> select password('abc$hthR+aaa');
+-------------------------------------------+
| password('abc$hthR+aaa')                  |
+-------------------------------------------+
| *52895896DD4CFE067D0D2311C05491918917CAD4 |
+-------------------------------------------+
1 row in set (0.00 sec)

三、老司机解答

经过360度的持续懵逼以后，请教了老司机叶老师，果然老司机就是不一样，一眼就看出了问题所在。
原来当我们使用mysql -e " 'abc$hthR+aaa' "的时候，单引号里面的$hthR被识别为了变量，所以，实际上，admin_test的密码被设置为了abc+aaa

[root@VM_0_9_centos data]# echo "abc$hthR+aaa"
abc+aaa
MySQL [mysql]> select password('abc+aaa');
+-------------------------------------------+
| password('abc+aaa')                       |
+-------------------------------------------+
| *314F43C6517782AB849AC666BBCDCCABDF059DC3 |
+-------------------------------------------+
1 row in set (0.00 sec)

*314F43C6517782AB849AC666BBCDCCABDF059DC3跟我们之前admin_test@’%’用户的加密串对上了

四、老司机避坑指南

在创建用户时，如有特殊字符$时，可以采用如下办法

  • -e 选项时使用\来转义$字符

  • -e 选项时，单引号包在外层，双引号在内层，比如

    mysql3306 -e 'grant all privileges on *.* to admin_test2@"%" identified by "abc$hthR+aaa";'

  • 不使用-e选项，直接登录mysql内部创建用户