• 一、MySQL 客户端连接的两种方式
  • 1. -hlocalhost
  • 2. socket 方式连接
  • 3. -h127.0.0.1 方式连接
• 二、MySQL 登陆验证
  • 1、Host、User
  • 2、Password
• 3、权限变更
• 4、短密码升级方法
• 三、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

参考：

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

• mysql_old_password 验证插件被删除
• old_password() 函数被删除
• old_passwords 系统变量，不再允许值为1
• secure_auth 系统变量，不再允许值为0
• --skip-secure-auth 被弃用
• default_password_lifetime 5.7.4引入, <= 5.7.10 值为360,1年过期,5.7.11以后为0，没有密码过期限制

Pre-4.1 passwords 已经被弃用，old_passwords 不能设置为1了、secure_auth 不能设置为 OFF了。
一定要在升级 MySQL 5.7 前将短密码升级，否则 mysql_upgrade 会把password 列删除，迁移到 authentication_string 只迁移长密码的用户

四、 SSL