- 一、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
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
网友评论