MySQL登陆验证

  • 一、MySQL 客户端连接的两种方式
  • 二、MySQL 登陆验证
  • 1、Host、User
  • 2、Password
  • 三、MySQL 5.7 -- 待续
  • 四、SSL -- 待续

一、MySQL 客户端连接的两种方式

参考：B.5.2.2 Can't connect to [local] MySQL server

连接 MySQL 两种方式，一种是通过 Unix socket (default /emp/mysql.sock) ，另外一种是 TCP/IP 。

 socket 比 TCP/IP 更快的方式，但仅能在同一台机器才可以访问
 socket 在没有指定 hostname 或指定 localhost 时使用

所以 -h localhost 是通过 socket 方式访问 MySQL，-h 127.0.0.1 是通过 TCP/IP 访问 MySQL。

MySQL 用户列表

mysql@localhost.(none)>select user,host,password,plugin from mysql.user;
+-------+--------------+-------------------------------------------+-----------------------+
| user  | host         | password                                  | plugin                |
+-------+--------------+-------------------------------------------+-----------------------+
| root  | localhost    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | dbaone       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | 127.0.0.1    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | ::1          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| test1 | %            | 773359240eb9a1d9                          | mysql_old_password    |
| root  | %            | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | 192.168.56.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| test2 | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
+-------+--------------+-------------------------------------------+-----------------------+
8 rows in set (0.00 sec)

1>、-hlocalhost，默认是找 socket 文件，不能连接因为sock不是默认位置。

# mysql -uroot -proot -hlocalhost -P 3308 -e "select user(); status ;"
**ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)**

2>、socket 方式连接，status 显示通过 UNIX socket 方式连接

# mysql -uroot -proot -S /tmp/mysql-3308.sock  -e "select user(); status ;"
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
--------------
mysql  Ver 14.14 Distrib 5.5.15, for Linux (x86_64) using  EditLine wrapper

Connection id:        14
Current database:   
**Current user:        root@localhost**
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.35-log Source distribution
Protocol version:    10
**Connection:        Localhost via UNIX socket**
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql-3308.sock
Uptime:            2 hours 44 min 38 sec

Threads: 2  Questions: 68  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.006
--------------

3>、-h127.0.0.1 方式连接，status 显示通过 TCP/IP 连接，但为什么不是 root@127.0.0.1 而是 root@localhost ，这个就是第二部分，MySQL 登陆验证的部分。

# mysql -uroot -proot -h127.0.0.1 -P 3308 -e "select user(); status ;"
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
--------------
mysql  Ver 14.14 Distrib 5.5.15, for Linux (x86_64) using  EditLine wrapper

Connection id:        13
Current database:   
**Current user:        root@localhost**
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.35-log Source distribution
Protocol version:    10
**Connection:        127.0.0.1 via TCP/IP**
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3308
Uptime:            2 hours 42 min 42 sec

Threads: 2  Questions: 62  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.006
--------------

二、MySQL 登陆验证

参考：

6.2.4 Access Control, Stage 1: Connection Verification

mysql 空用户（user 列为空）带来的影响

1、Host、User

MySQL 用户验证一般是（Host、User、Password），但先验证 Host，再验证 User，最后验证 Password

Your identity is based on two pieces of information:

- The client host from which you connect

- Your MySQL user name

 如果 Host 是空的，则代表任何主机可以登陆。5.5、5.6 初始化 Host 默认不会为空
 如果 User 是空的，则代表可以匹配任何用户。5.5、5.6 初始化 localhost 和当前机器 hostname 的 User 为空
 如果 Password 是空的，则代表不需要密码就可以登陆。

当 MySQL 启动时，会把所有权限相当的表数据读到内存中(包括 mysql.user )，有一定排序规则。当用户登陆时，匹配到第一个记录就是要检验的记录。

 - 首先按 Host 排序，第一位是特别的主机名或IP，% 意味任何不特定的主机，任何主机都可以访问，'' 也表示任何主机都可以访问，但排在 % 之后。（相同意义的 hostname、IP，默认使用hostname，添加参数skip-name-resolve后，使用IP）
 - IP 不受子网掩网影响，因此 192.168.1.13 和 192.168.1.0/255.255.255.0 都是特别指定的，排序是没有办法确认先后。
 - 相同 Host，再按 User 排序。

参考官方文档例子：

表中记录是这样

+-----------+----------+-
| Host      | User    | ...
+-----------+----------+-
| %        | root    | ...
| %        | jeffrey  | ...
| localhost | root    | ...
| localhost |          | ...
+-----------+----------+-

读到内存中的是这样

+-----------+----------+-
| Host      | User    | ...
+-----------+----------+-
| localhost | root    | ...
| localhost |          | ...
| %        | jeffrey  | ...
| %        | root    | ...
+-----------+----------+-

如果从 localhost 登陆，用户名为 jeffrey，第一感觉应该是 jeffrey@% 的记录，实际是 jeffrey@localhost，因为先匹配 Host，localhost 是匹配的，usr为空，代表任何用户都可以，host、user匹配结果后，就不会再往下匹配了。(当然要忽略 password )

按规则指定 "主机名"或IP，都属于第一顺序 ，那localhost 和 127.0.01 哪个更优先？

# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.101 dbaone
192.168.56.102 dbatwo

下面已经演示的，通过 127.0.0.1 连接，当前用户却是 root@localhost，不是 root@127.0.0.1，说明主机名排在IP前面，也可能默认以主机名缓存验证(host cache)。

# mysql -uroot -proot -h127.0.0.1 -P 3308 -e "select user(); status ;"
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
--------------
mysql  Ver 14.14 Distrib 5.5.15, for Linux (x86_64) using  EditLine wrapper

Connection id:        13
Current database:   
**Current user:        root@localhost**
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.35-log Source distribution
Protocol version:    10
**Connection:        127.0.0.1 via TCP/IP**
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3308
Uptime:            2 hours 42 min 42 sec

Threads: 2  Questions: 62  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.006

当我修改 root@localhost 的密码，但不修改 root@127.0.0.1 的密码，说明匹配 mysql.user 中 host=localhost，user=root 这行，直接报密码错误，即使 hos=127.0.0.1，user=root 是正确的。

mysql@localhost.(none)>update mysql.user set password=password('rootroot') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.18 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='root';
+------+--------------+-------------------------------------------+-----------------------+
| user | host         | password                                  | plugin                |
+------+--------------+-------------------------------------------+-----------------------+
**| root | localhost    | *6C362347EBEAA7DF44F6D34884615A35095E80EB | mysql_native_password |**
| root | dbaone       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root | 127.0.0.1    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root | ::1          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root | %            | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root | 192.168.56.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
+------+--------------+-------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

# mysql -uroot -proot -h127.0.0.1 -P 3308 -e "select user(); status ;"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

当然，如果将 skip-name-resolve 加到 my.cnf，重启MySQL，直接匹配 root@127.0.0.1，就可以登陆了。

mysql@localhost.(none)>show variables like '%skip_name_resolve%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
**| skip_name_resolve      | ON    |**
+------------------------+-------+
1 rows in set (0.00 sec)

# mysql -uroot -proot -h127.0.0.1 -P 3308 -e "select user(); status ;"
+----------------+
| user()         |
+----------------+
| root@127.0.0.1 |
+----------------+
--------------
mysql  Ver 14.14 Distrib 5.5.15, for Linux (x86_64) using  EditLine wrapper

Connection id:        2
Current database:   
**Current user:        root@127.0.0.1**
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.35-log Source distribution
Protocol version:    10
**Connection:        127.0.0.1 via TCP/IP**
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3308
Uptime:            27 sec

Threads: 2  Questions: 9  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.333
--------------

恢复环境 root@localhost 密码

mysql@localhost.(none)>update mysql.user set password=password('root') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

2、Password

参考：

MySQL · 特性分析 · MySQL 5.7 新特性系列一

Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin

密码在 MySQL 版本不同

在 MySQL 4.1 之前，password() 生成 16 位的密码
在 MySQL 4.1 开始，password() 生成 41 位的密码，old_password() 兼容之前版本的 password()，但在 5.7.5被移除、
old_passwords = 1，使password() 相当old_password()，在 MySQL 5.7.5 以后，old_password 只有0和2，没有1.
Value Password Hashing Method Associated Authentication Plugin
0 MySQL 4.1 native hashing mysql_native_password
1 Pre-4.1 (“old”) hashing mysql_old_password
2 SHA-256 hashing sha256_password

关于 password，涉及 mysql.user 表中2个字段。一个是 password(5.5、5.6)(authentication_string 5.7)、一个是 plugin

  • plugin 在 5.5 ，可以为空，无默认值

  • plugin 在 5.6 ，可以为空，默认值为mysql_native_password

  • plugin 在 5.7 ，不可为空，默认值为mysql_native_password

  • passowrd 在 5.5、5.6 均是 NOT NULL char(41)

  • password 在 5.7 已经不存在，存储密码字段是 authentication_string text类型（这个字段在5.5、5.6也存在）

所以验证的判断方法

  • 当 plugin = mysql_old_password 时，登陆验证以 16 位短密码验证
  • 当 plugin = mysql_native_password 时，登陆验证以 41 位长密码验证
  • 当 plugin 为空时，会根据 password 存储的密码来决定以 mysql_old_password - 还是 mysql_native_password 来验证

在 MySQL 5.6.5 以后，如果 password 为16位短密码，且 plugin = mysql_old_password 依然不能登陆。

因为 secure-auth 在 >= 5.6.5 以后默认为 ON，不接受客户端 16位短密码 ，在 >= 5.7.5 以后只有ON，没有OFF。

mysql@localhost.(none)>select user,host,password,plugin from mysql.user;
+-------+--------------+-------------------------------------------+-----------------------+
| user  | host         | password                                  | plugin                |
+-------+--------------+-------------------------------------------+-----------------------+
| root  | localhost    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | dbaone       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | 127.0.0.1    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | ::1          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| test1 | %            | 773359240eb9a1d9                          | mysql_old_password    |
| root  | %            | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| root  | 192.168.56.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| test2 | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
+-------+--------------+-------------------------------------------+-----------------------+
8 rows in set (0.00 sec)

# mysql -utest1 -p123 -S /tmp/mysql-3308.sock
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'test1'@'localhost' has a password in the old format; please change the password to the new format

# mysql -utest1 -p123 -S /tmp/mysql-3308.sock --skip-secure-auth
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'test1'@'localhost' has a password in the old format; please change the password to the new format

解决：重启 MySQL

vim my.cnf

[mysqld]
secure-auth = OFF

[mysql]
secure_auth = 0

论证1： password 与 plugin 匹配才能正常登陆 （secure-auth = OFF）

mysql@localhost.(none)>select version();
+------------+
| version()  |
+------------+
| 5.6.35-log |
+------------+
1 row in set (0.00 sec)

mysql@localhost.(none)>set old_passwords=1;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>show  variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>create user test1@'%' identified by "123";
ERROR 1396 (HY000): Operation CREATE USER failed for 'test1'@'%'

mysql@localhost.(none)>grant all on test.* to test1@'%' identified by "123";
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

mysql@localhost.(none)>set old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>create user test1@'%' identified by "123";
ERROR 1396 (HY000): Operation CREATE USER failed for 'test1'@'%'

mysql@localhost.(none)>grant all on test.* to test1@'%' identified by "123";
Query OK, 0 rows affected (0.00 sec)

在 old_password = 1，5.6已经不允许生成 old_password 密码了。

mysql@localhost.(none)>set old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>show  variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
Empty set (0.00 sec)

mysql@localhost.(none)>grant all on test.* to test1@'%' identified by "123";
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
+-------+------+-------------------------------------------+-----------------------+
| user  | host | password                                  | plugin                |
+-------+------+-------------------------------------------+-----------------------+
| test1 | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
+-------+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

这个可以在客户端尝试登陆，是可以登陆的。

root@dbaone mysql_3308]# mysql -utest1 -p123 -S /tmp/mysql-3308.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2010, 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@localhost.(none)>select user(),sysdate();
+-----------------+---------------------+
| user()          | sysdate()           |
+-----------------+---------------------+
| test1@localhost | 2017-01-17 15:58:40 |
+-----------------+---------------------+
1 row in set (0.00 sec)

当修改为 old password

mysql@localhost.(none)>set old_passwords=1;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>update mysql.user set password=password('123'),plugin='mysql_old_password' where user='test1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
+-------+------+------------------+--------------------+
| user  | host | password         | plugin             |
+-------+------+------------------+--------------------+
| test1 | %    | 773359240eb9a1d9 | mysql_old_password |
+-------+------+------------------+--------------------+
1 row in set (0.00 sec)

[root@dbaone mysql_3308]# mysql -utest1 -p123 -S /tmp/mysql-3308.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2010, 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@localhost.(none)>show variables like '%secure_auth%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

论证2： plugin 为空，登陆依赖 password 存储格式选择方法验证登陆 （secure-auth = OFF）

mysql@localhost.(none)>set old_passwords=0;
Query OK, 0 rows affected (0.04 sec)

mysql@localhost.(none)>update mysql.user set password=password('123'),plugin='' where user='test1';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
+-------+------+-------------------------------------------+--------+
| user  | host | password                                  | plugin |
+-------+------+-------------------------------------------+--------+
| test1 | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |        |
+-------+------+-------------------------------------------+--------+
1 row in set (0.04 sec)

# mysql -utest1 -p -hlocalhost -P 3311 -S /tmp/mysql3311.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 104306286
Server version: 5.6.24-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user(),sysdate();
+-----------------+---------------------+
| user()          | sysdate()           |
+-----------------+---------------------+
| test1@localhost | 2017-01-17 15:50:54 |
+-----------------+---------------------+
1 row in set (0.00 sec)

是可以登陆的

如果 password、与 plugin 不匹配，则不能登陆

mysql@localhost.(none)>set old_passwords=0;
Query OK, 0 rows affected (0.04 sec)

mysql@localhost.(none)>update mysql.user set password=password('123'),plugin='mysql_old_password' where user='test1';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
+-------+------+-------------------------------------------+--------------------+
| user  | host | password                                  | plugin             |
+-------+------+-------------------------------------------+--------------------+
| test1 | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_old_password |
+-------+------+-------------------------------------------+--------------------+
1 row in set (0.04 sec)

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.06 sec)

[root@gaea227 ~]# mysql -utest1 -p -hlocalhost -P 3311 -S /tmp/mysql3311.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)

清除测试用户

mysql@localhost.(none)>delete from mysql.user where user='test1';
Query OK, 1 row affected (0.03 sec)

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test1';
Empty set (0.03 sec)

也可以使用不同 PHP 来测试，PHP 5.2与PHP5.6，下面是PHP脚本

<?php
/**
 * 运行示例：
 *
 * root@vagrant-ubuntu-trusty-64:/vagrant# php php5_mysql.php
 * Array
 * (
 *   [0] => 2017-01-17 15:15:39
 * )
 */

$host="192.168.1.100:3311";
$username="test2";
$passwd="123";

# 数据库连接
#$link = mysqli_connect($host, $username, $passwd) or die('Unale to connect');
$link = mysql_connect($host, $username, $passwd) or die('Unale to connect');

if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$sql = "select sysdate();";
$result = mysql_query($sql,$link);
$row = mysql_fetch_row($result);
print_r($row);

# 执行查询，获取结果
#$sql = "select sysdate();";
#$result = mysqli_query($link, $sql);
#$row = mysqli_fetch_row($result);
#print_r($row);

# 关闭数据库连接
#mysqli_close($link);
mysql_close($link);

3、权限变更

6.2.6 When Privilege Changes Take Effect

当使用 DDL 语法， GRANT, REVOKE, SET PASSWORD, or RENAME USER 时，立刻把 授权表 load 到内存存

 在下面测试中，如果 mysql.user 已经存在的权限，还是需要 flush privileges; 刷新权限的

当使用 DML 语法，INSERT, UPDATE, or DELETE 时，需要执行 flush privileges ，手工load。

 表、列的权限，影响下次访问
 DB的权限，影响下次执行 use DB

4、短密码升级方法

MySQL 版本 ?
update ?
grant ?

方法一、grant 覆盖 （单节点）

MySQL 5.5.15

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+------------------+--------+
| user | host | password         | plugin |
+------+------+------------------+--------+
| test | %    | 773359240eb9a1d9 |        |
+------+------+------------------+--------+
1 row in set (0.00 sec)

mysql@localhost.(none)>show variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>grant all on test.* to test@'%' identified by "123";
Query OK, 0 rows affected (0.00 sec)

# 已经存在的权限，一定要flush，才可以登陆

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+-------------------------------------------+--------+
| user | host | password                                  | plugin |
+------+------+-------------------------------------------+--------+
| test | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |        |
+------+------+-------------------------------------------+--------+
1 row in set (0.00 sec)

MySQL 5.6.35

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+------------------+--------------------+
| user | host | password         | plugin             |
+------+------+------------------+--------------------+
| test | %    | 773359240eb9a1d9 | mysql_old_password |
+------+------+------------------+--------------------+
1 row in set (0.00 sec)

mysql@localhost.(none)>show variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>grant all on test.* to test@'%' identified by "123";
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

看样子走不通，plugin = mysql_old_password 不能通过 grant 方法覆盖。还是要直接 update。

方法二：update mysql.user（单节点）

MySQL 5.5.15

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+------------------+--------+
| user | host | password         | plugin |
+------+------+------------------+--------+
| test | %    | 773359240eb9a1d9 |        |
+------+------+------------------+--------+
1 row in set (0.00 sec)

mysql@localhost.(none)>show variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>update mysql.user set password=password('123') where user='test';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+-------------------------------------------+--------+
| user | host | password                                  | plugin |
+------+------+-------------------------------------------+--------+
| test | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |        |
+------+------+-------------------------------------------+--------+
1 row in set (0.00 sec)

# 测试可以登陆
# mysql -utest -p123 -e "select user();"
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+

# 若 update 更新 password 还更新 plugin ？

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+------------------+--------+
| user | host | password         | plugin |
+------+------+------------------+--------+
| test | %    | 773359240eb9a1d9 |        |
+------+------+------------------+--------+
1 row in set (0.00 sec)

mysql@localhost.(none)>show variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>update mysql.user set password=password('123'),plugin='mysql_native_password' where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+-------------------------------------------+-----------------------+
| user | host | password                                  | plugin                |
+------+------+-------------------------------------------+-----------------------+
| test | %    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

# mysql -utest -p123 -e "select user();"
+-----------------+
| user()          |
+-----------------+
| test1@localhost |
+-----------------+

MySQL 5.6.35

mysql@localhost.(none)>select user,host,password,plugin from mysql.user where user='test';
+------+------+------------------+--------------------+
| user | host | password         | plugin             |
+------+------+------------------+--------------------+
| test | %    | 773359240eb9a1d9 | mysql_old_password |
+------+------+------------------+--------------------+
1 row in set (0.00 sec)

mysql@localhost.(none)>show variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql@localhost.(none)>update mysql.user set password=password('123'),plugin='mysql_native_password' where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql@localhost.(none)>flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 测试可以登陆
# mysql -utest -p123 -S /tmp/mysql-3308.sock -e "select user();"
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+

grant 只适用于 5.5，5.6还是得通过直接 update 更新。

短密码升级流程：A -> B -> C -> D

检查 Master、Salve 的 old_passwords 、secure_auth 参数值
检查 Master、Slave MySQL 版本（特别注意 Master、Slave 版本不一致）

如果 old_passwords = 1

****
**A.**
****
**Master:**

set global old_passwords = 0;

show global variables like '%old_passwords%';

修改 my.cnf 配置文件

B.

**Slave:**

stop slave;

set global old_passwords = 0;

show global variables like '%old_passwords%';

start slave;

修改配置文件

如果 MySQL 版本是 5.5

C.

update mysql.user set password = password('XXX') where user = 'XXX';

select length(password) from mysql.user where user = 'XXX';

flush privileges;

如果 MySQL 版本是 5.6

C.

update mysql.user set password=password('XXX'),plugin='mysql_native_password' where user='XXX';

select length(password) from mysql.user where user = 'XXX';

flush privileges;

最后登陆验证：

D.

登陆验证

如果 Master 是5.5、Slave 是 5.6的异构，可以按 5.6 升级方法，或者单独升级 Master 和 Slave。

三、MySQL 5.7

四、 SSL

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 159,716评论 4赞 364
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,558评论 1赞 294
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,431评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,127评论 0赞 209
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,511评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,692评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,915评论 2赞 313
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,664评论 0赞 202
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,412评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,616评论 2赞 245
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,105评论 1赞 260
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,424评论 2赞 254
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,098评论 3赞 238
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,096评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,869评论 0赞 197
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,748评论 2赞 276
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,641评论 2赞 271

推荐阅读更多精彩内容