规避奇葩现象 —— MySQL 5.6 安装后必做事项

安装完MySQL 5.6之后，有个操作必须要做，否则可能会出现不可思议的奇葩现象。

目录

1 查看数据库

1.1 登陆

1.2 查看数据库

2 查看用户信息

2.1 切换到mysql数据库

2.2 查看user表

3 本地与远程登陆

3.1 本地登陆

3.2 远程登陆

4 为root用户设置密码

5 奇葩现象

5.1 展现奇葩现象

5.2 解决奇葩现象

6 总结避坑

---

咱们先来看看正常的操作。

1 查看数据库

1.1 登陆

由于还没有设置密码，登陆简直超轻松。

[root@dba mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

1.2 查看数据库

这里只需要一条命令就可以搞定，show  databases;就可以查看当前有哪些数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

这几个数据库就是在安装时，通过命令“scripts/mysql_install_db --user=mysql”初始化后生成的。执行完这条命令之后，也会在数据目录，datadir中看到对应的文件。比如小编的环境中数据目录在/data/mysql下，所以看到如下内容：

[root@dba mysql]# cd /data/mysql/
[root@dba mysql]# ls
auto.cnf              error.log  ib_logfile0  mysql       performance_schema
dba.enmotech.com.pid  ibdata1    ib_logfile1  mysql.sock  test

2 查看用户信息

2.1 切换到mysql数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

2.2 查看user表

如果字段记不清，可以desc user;查看一下表结构。

mysql> select user,host,password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | dba.com   |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | dba.com   |          |
+------+-----------+----------+
6 rows in set (0.00 sec)

3 本地与远程登陆

3.1 本地登陆

[root@dba mysql]# mysql -uroot

[root@dba mysql]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

3.2 远程登陆

[root@dba mysql]# mysql -uroot -h192.168.6.220

这里注意哟，只要加了-h参数，就表示要远程登陆。

[root@dba mysql]# mysql -uroot -h192.168.6.220
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

4 为root用户设置密码

MySQL 5.6中还是要使用password对密码进行加密的。5.7 版本就不需要了。

[root@dba mysql]# mysql -uroot -h192.168.6.220

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

再来看一下用户的信息，就会发现有密码了。这个可是加密之后的。

dba.com | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

记得先切换到mysql数据库。

mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost |                                           |
| root | dba.com   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
|      | localhost |                                           |
|      | dba.com   |                                           |
+------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

5 奇葩现象

5.1 展现奇葩现象

接下来再看看本地登陆和远程登陆，会不会有惊喜。。

本地登陆：

加了密码居然报错？？？？

[root@dba mysql]# mysql -uroot  -p123456

[root@dba mysql]# mysql -uroot  -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

来试试不加密码的

没密码居然成功？？？

[root@dba mysql]# mysql -uroot 

[root@dba mysql]# mysql -uroot  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

再来试远程不带密码登陆

报错了，报错了？？？

[root@dba mysql]# mysql -uroot -h192.168.6.220

[root@dba ~]# mysql -uroot -h192.168.6.220 
ERROR 1045 (28000): Access denied for user 'root'@'dba.com' (using password: NO)

再来试试远程带密码的

这个可以登陆。。。。

[root@dba mysql]# mysql -uroot -h192.168.6.220 -p123456

[root@dba mysql]# mysql -uroot -h192.168.6.220 -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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>

是不是感觉很奇葩。

5.2 解决奇葩现象

查看用户信息表：

会发现里面有两个密码为空的记录：host为localhost和dba.com

导致以上奇葩问题的就是这两条记录，果断把这两条记录删除！

mysql> delete from user where user='' and host in ('localhost','dba.com');
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost |                                           |
| root | dba.com   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

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

再试一下之前出现的奇葩问题，本地登陆其实用户对应的是root@localhost,在用户信息表中能够看到是没有密码的，所以有密码是不能登陆，没有密码才能登陆。

[root@dba ~]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 
mysql> quit
Bye
[root@dba ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@dba ~]# 

另外远程登陆时，mysql -uroot -h192.168.6.220 对应的用户名为：root@dba.com，从用户信息表中可以看出，是有密码的，所以必须加密码才能登陆。

[root@dba ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@dba ~]# mysql -uroot -h192.168.6.220 -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> quit
Bye
[root@dba ~]# mysql -uroot -h192.168.6.220 
ERROR 1045 (28000): Access denied for user 'root'@'dba.com' (using password: NO)
[root@dba ~]# 

6 总结避坑

MySQL 5.6安装完之后，先要把mysql.user表中的host为localhost和主机名的，并且user和密码为空的，这两行删除，然后flush privileges。

往期关联内容：

MySQL 5.6安装