MySQL登陸驗(yàn)證

一坦敌、MySQL 客戶端連接的兩種方式

二侣诵、MySQL 登陸驗(yàn)證
1痢法、Host、User
2杜顺、Password
三财搁、MySQL 5.7 -- 待續(xù)
四、SSL -- 待續(xù)

一躬络、MySQL 客戶端連接的兩種方式

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

連接 MySQL 兩種方式尖奔，一種是通過 Unix socket (default /emp/mysql.sock) ，另外一種是 TCP/IP 穷当。

 socket 比 TCP/IP 更快的方式提茁，但僅能在同一臺機(jī)器才可以訪問
 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，默認(rèn)是找 socket 文件汪疮，不能連接因?yàn)閟ock不是默認(rèn)位置峭火。

# 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 登陸驗(yàn)證的部分。

# 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 登陸驗(yàn)證

參考：

6.2.4 Access Control, Stage 1: Connection Verification

mysql 空用戶（user 列為空）帶來的影響

1恐仑、Host、User

MySQL 用戶驗(yàn)證一般是（Host为鳄、User裳仆、Password），但先驗(yàn)證 Host孤钦，再驗(yàn)證 User歧斟，最后驗(yàn)證 Password

Your identity is based on two pieces of information:

- The client host from which you connect

- Your MySQL user name

 如果 Host 是空的，則代表任何主機(jī)可以登陸偏形。5.5静袖、5.6 初始化 Host 默認(rèn)不會為空
 如果 User 是空的，則代表可以匹配任何用戶俊扭。5.5队橙、5.6 初始化 localhost 和當(dāng)前機(jī)器 hostname 的 User 為空
 如果 Password 是空的，則代表不需要密碼就可以登陸萨惑。

當(dāng) MySQL 啟動時捐康，會把所有權(quán)限相當(dāng)?shù)谋頂?shù)據(jù)讀到內(nèi)存中(包括 mysql.user )，有一定排序規(guī)則庸蔼。當(dāng)用戶登陸時解总，匹配到第一個記錄就是要檢驗(yàn)的記錄。

 - 首先按 Host 排序姐仅，第一位是特別的主機(jī)名或IP花枫，% 意味任何不特定的主機(jī)，任何主機(jī)都可以訪問掏膏，'' 也表示任何主機(jī)都可以訪問劳翰，但排在 % 之后。（相同意義的 hostname馒疹、IP佳簸，默認(rèn)使用hostname，添加參數(shù)skip-name-resolve后行冰，使用IP）
 - IP 不受子網(wǎng)掩網(wǎng)影響溺蕉，因此 192.168.1.13 和 192.168.1.0/255.255.255.0 都是特別指定的伶丐，排序是沒有辦法確認(rèn)先后。
 - 相同 Host疯特，再按 User 排序哗魂。

參考官方文檔例子：

表中記錄是這樣

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

讀到內(nèi)存中的是這樣

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

如果從 localhost 登陸，用戶名為 jeffrey漓雅，第一感覺應(yīng)該是 jeffrey@% 的記錄录别，實(shí)際是 jeffrey@localhost，因?yàn)橄绕ヅ?Host邻吞，localhost 是匹配的组题，usr為空，代表任何用戶都可以抱冷，host崔列、user匹配結(jié)果后，就不會再往下匹配了旺遮。(當(dāng)然要忽略 password )

按規(guī)則指定 "主機(jī)名"或IP赵讯，都屬于第一順序，那localhost 和 127.0.01 哪個更優(yōu)先耿眉？

# 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

下面已經(jīng)演示的边翼，通過 127.0.0.1 連接，當(dāng)前用戶卻是 root@localhost鸣剪，不是 root@127.0.0.1组底，說明主機(jī)名排在IP前面，也可能默認(rèn)以主機(jī)名緩存驗(yàn)證(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

當(dāng)我修改 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)

當(dāng)然间雀，如果將 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
--------------

恢復(fù)環(huán)境 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() 相當(dāng)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

關(guān)于 password研底，涉及 mysql.user 表中2個字段乾胶。一個是 password(5.5喻频、5.6)(authentication_string 5.7)姻蚓、一個是 plugin

plugin 在 5.5 倦沧，可以為空，無默認(rèn)值
plugin 在 5.6 ，可以為空涧尿，默認(rèn)值為mysql_native_password
plugin 在 5.7 ，不可為空翁涤，默認(rèn)值為mysql_native_password
passowrd 在 5.5桥言、5.6 均是 NOT NULL char(41)
password 在 5.7 已經(jīng)不存在，存儲密碼字段是 authentication_string text類型（這個字段在5.5葵礼、5.6也存在）

所以驗(yàn)證的判斷方法

當(dāng) plugin = mysql_old_password 時号阿，登陸驗(yàn)證以 16 位短密碼驗(yàn)證
當(dāng) plugin = mysql_native_password 時，登陸驗(yàn)證以 41 位長密碼驗(yàn)證
當(dāng) plugin 為空時鸳粉，會根據(jù) password 存儲的密碼來決定以 mysql_old_password - 還是 mysql_native_password 來驗(yàn)證

在 MySQL 5.6.5 以后扔涧，如果 password 為16位短密碼，且 plugin = mysql_old_password 依然不能登陸届谈。

因?yàn)?secure-auth 在 >= 5.6.5 以后默認(rèn)為 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已經(jīng)不允許生成 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)

當(dāng)修改為 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 存儲格式選擇方法驗(yàn)證登陸（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
/**
 * 運(yùn)行示例：
 *
 * 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";

# 數(shù)據(jù)庫連接
#$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);

# 執(zhí)行查詢，獲取結(jié)果
#$sql = "select sysdate();";
#$result = mysqli_query($link, $sql);
#$row = mysqli_fetch_row($result);
#print_r($row);

# 關(guān)閉數(shù)據(jù)庫連接
#mysqli_close($link);
mysql_close($link);

3瀑踢、權(quán)限變更

6.2.6 When Privilege Changes Take Effect

當(dāng)使用 DDL 語法扳还， GRANT, REVOKE, SET PASSWORD, or RENAME USER 時，立刻把授權(quán)表 load 到內(nèi)存存

 在下面測試中橱夭，如果 mysql.user 已經(jīng)存在的權(quán)限氨距，還是需要 flush privileges; 刷新權(quán)限的

當(dāng)使用 DML 語法，INSERT, UPDATE, or DELETE 時棘劣，需要執(zhí)行 flush privileges 俏让，手工load。

 表茬暇、列的權(quán)限首昔，影響下次訪問
 DB的權(quán)限，影響下次執(zhí)行 use DB

4糙俗、短密碼升級方法

MySQL 版本 ?
update ?
grant ?

方法一勒奇、grant 覆蓋（單節(jié)點(diǎn)）

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)

# 已經(jīng)存在的權(quán)限，一定要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（單節(jié)點(diǎn)）

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 參數(shù)值
檢查 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;

最后登陸驗(yàn)證：

D.

登陸驗(yàn)證

如果 Master 是5.5、Slave 是 5.6的異構(gòu)屠凶，可以按 5.6 升級方法驰后，或者單獨(dú)升級 Master 和 Slave。

三矗愧、MySQL 5.7

四灶芝、 SSL

最后編輯于：2017.12.05 07:40:09

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子夜涕，更是在濱河造成了極大的恐慌犯犁，老刑警劉巖，帶你破解...
沈念sama閱讀 217,907評論 6贊 506
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件女器，死亡現(xiàn)場離奇詭異酸役，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)驾胆，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,987評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門涣澡，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人丧诺，你說我怎么就攤上這事暑塑。” “怎么了锅必？”我有些...
開封第一講書人閱讀 164,298評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵事格，是天一觀的道長。經(jīng)常有香客問我搞隐，道長驹愚，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,586評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任劣纲，我火速辦了婚禮逢捺，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘癞季。我一直安慰自己劫瞳，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 67,633評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布绷柒。她就那樣靜靜地躺著志于，像睡著了一般。火紅的嫁衣襯著肌膚如雪废睦。梳的紋絲不亂的頭發(fā)上伺绽，一...
開封第一講書人閱讀 51,488評論 1贊 302
城市分裂傳說
那天，我揣著相機(jī)與錄音嗜湃，去河邊找鬼奈应。笑死，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播粒没，決...
沈念sama閱讀 40,275評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼唆鸡！你這毒婦竟也來了跃巡？” 一聲冷哼從身側(cè)響起粉捻，我...
開封第一講書人閱讀 39,176評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤幌衣，失蹤者是張志新（化名）和其女友劉穎屿附，沒想到半個月后郎逃，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體哥童，經(jīng)...
沈念sama閱讀 45,619評論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡挺份，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,819評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了贮懈。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片匀泊。...
茶點(diǎn)故事閱讀 39,932評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖朵你，靈堂內(nèi)的尸體忽然破棺而出各聘，到底是詐尸還是另有隱情，我是刑警寧澤抡医，帶...
沈念sama閱讀 35,655評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布躲因，位于F島的核電站，受9級特大地震影響忌傻，放射性物質(zhì)發(fā)生泄漏大脉。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,265評論 3贊 329
男人毒藥：我在死后第九天來索命
文/蒙蒙一水孩、第九天我趴在偏房一處隱蔽的房頂上張望镰矿。院中可真熱鬧，春花似錦俘种、人聲如沸秤标。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,871評論 0贊 22
一樁弒父案宙刘，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽苍姜。三九已至，卻和暖如春悬包，著一層夾襖步出監(jiān)牢的瞬間怖现，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 32,994評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工玉罐，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留屈嗤，地道東北人。一個月前我還...
沈念sama閱讀 48,095評論 3贊 370
代替公主和親
正文我出身青樓吊输，卻偏偏與公主長得像饶号，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子季蚂，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,884評論 2贊 354

MySQL登陸驗(yàn)證

推薦閱讀更多精彩內(nèi)容