荆轲刺秦王

在使用虚拟机配置本地环境的时候，环境脚本里面有安装 mysql 但是在职的时候一直连接的是测试服务器上mysql 所以就一直没有关心虚拟机上的 mysql 离职之后发现不使用公司内网就无法连接数据库和redis了，所以今天特来记录下如果使用本机的 Navicat 12 for MySQL 连接 虚拟机上的 mysql 服务

环境：CentOS 7

1.修改数据库密码

由于长时间未使用导致我忘记了虚拟机上的mysql root 用户的密码，实际上我根本就不知道，因为是脚本安装 安装完给的密码是加密后的也用不上，直接看步骤：

编辑 mysql 的配置文件

[root@localhost ~]# vi etc/my.cnf 

打开后是这样的：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

在最后一行加上：

skip-grant-tables

这句话的意思是：登录数据库跳过登录 就是不用密码也能登录数据库

既然改了配置，那多半都要重启服务：

[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

[root@localhost ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service

[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

[root@localhost ~]# service mysqld status
Redirecting to /bin/systemctl status  mysqld.service

重启完之后就可以直接无密码登录 mysql 了：

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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
mysql> update user set password=password("root") where user="root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('root') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> quit
Bye

如果想修改 root 用户的登录密码 需要先进入 mysql 这个数据库 使用命令: use mysql 

然后可以看到我使用: update user set password=password("root") where user="root";

时报错了：ERROR 1054 (42S22): Unknown column 'password' in 'field list'

错误的原因是 5.7版本上的 mysql 已经没有 password 这个字段了，password 字段改成了authentication_string

所以我使用: update mysql.user set authentication_string=password('root') where user='root';

修改成功之后别忘了 flush privileges; 使其生效。

理论上来说这会已经成功了，但是需要注意把 mysql 配置文件里的 跳过密码 登录 最后一行 去掉

然后再重启 mysql 服务 再使用密码登录:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.16 Source distribution

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

以上