最近使用ubuntu 18.04LST 安装mysql，设置密码，搞得我怀疑人生，搞半天是设置密码的方式在mysql8中改变；记录下问题的过程及设置密码

密码咋修改不了

之前在使用MySQL的时候，都是使用grant/alter/set 去修改root的密码的，今天安装了5.7.29，使用同样的方法修改，一直修改失败，系统账户root登录mysql不需要密码，普通用户无法登录

使用grant修改密码

mysql> select User,Host,authentication_string from user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *000BFFBA444B9D1E98861B0537ABAA4664A2CAA1 |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *87BD4C29046C1B2C43D1FB7342F5F1BA286253BC |
+------------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>   grant all on *.* to 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

执行没有返回错误，退出后重启MySQL，发现密码并没有修改；

于是觉得自己是不是没有操作成功，重新执行了两边，未果；

于是乎，换了一种

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

同样，操作了两边，还是未果；

执行后看返回

后来在某次执行发现，有一个warning，两个执行都有返回，就看了这两个warnings

mysql>   grant all on *.* to 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Using GRANT statement to modify existing user’s properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

就是说，grant已经要废弃了，要用alter User 修改

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings
    -> ;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

‘SET PASSWORD FOR = PASSWORD(‘‘)’ is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = ‘‘ instead

这种设置密码的方式也已经被废弃了，要使用

SET PASSWORD FOR = ‘‘

不过warnings只是警告，执行是成功的，只是说下一版本要去掉PASSWORD，继续查找问题。

好，那就用明文的方式再试一次

mysql> SET PASSWORD FOR 'root'@'localhost'='123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

又出现一个warnings，那就再看下有什么warnings的

mysql> SET PASSWORD FOR 'root'@'localhost'='123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Note  | 1699 | SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it. |
+-------+------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SET PASSWORD has no significance for user ‘root’@’localhost’ as authentication plugin does not support it.

plugin不支持set password

那就看下当前用户的plugin，刚好user里面有一列plugin

mysql> select Host,User,plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| localhost | root             | auth_socket           |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | debian-sys-maint | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

目测是这个plugin的问题，那就修改下plugin

user mysql;
update user set plugin="mysql_native_password" where User='root';
flush privileges;

修改完后重启mysql

终于成功修改密码

修改plugin成功后，这些密码修改的命令都可以使用了，终于，密码修改成功了

方法1，使用set password for , 使用明文

mysql> set password for 'root'@'localhost'='123456';
Query OK, 0 rows affected (0.00 sec)

方法2 ，alter user

mysql> alter user 'root'@'localhost' identified by 'P@55word';
Query OK, 0 rows affected (0.00 sec)

索引操作

1. 查看索引

show index from [table name];

2. 创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

或

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

3. 删除索引

DROP INDEX 索引名 ON 表名

或

ALTER TABLE 表名 DROP INDEX 索引名

4. 查询语句是否命中索引

explain [sql]

mysql> explain select * from sentence_daily where date_str='2019-10-25';（命中索引date_str_type）
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sentence_daily | NULL       | ref  | date_str_type | date_str_type | 50      | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from sentence_daily where type=1;(未命中)
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sentence_daily | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  101 |    10.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)