MySQL的安装、改密及远程连接
一、下载MySQL压缩包后的安装步骤
- 将压缩包解压到指定的目录
- 编辑好配置文件
[mysql]
#设置MySQL客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
#设置MySQL的安装目录
basedir =D:\Program Files\MySQL\mysql-8.0.22-winx64
#设置MySQL数据库的数据的存放目录
datadir = D:\Program Files\MySQL\mysql-8.0.22-winx64\data
#允许最大连接数
max_connections=20
#服务端使用字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
- DOS窗口下输入安装命令
- ①进入到MySQL的bin目录下，或者配置好环境变量即可，任意路径可操作
输入命令：mysqld install
C:\WINDOWS\system32>mysqld install
The service already exists!
The current server installed:
- 注释：提示该服务已存在，先卸载，命令：mysqld remove
C:\WINDOWS\system32>mysqld remove
Service successfully removed.
- ②继续安装
C:\WINDOWS\system32>mysqld install
Service successfully installed.
- 注释：提示安装成功
- ③根据配置文件进行初始化命令：mysqld --initialize-insecure
C:\WINDOWS\system32>mysqld --initialize-insecure
- ④启动MySQL服务，命令：net start mysql
C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。
- ⑤进入MySQL，root账号免密进入，命令：mysql -u root
C:\WINDOWS\system32>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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有几个数据库,起始有4个才正常，查看命令;mysql> show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
二、DOS命令更改MySQL的root用户的密码
- 进入mysql库命令：use mysql; 查看表命令：show tables;
mysql> use mysql;
Database changed
mysql> show tables;
+----------------------------------------------+
| Tables_in_mysql |
+----------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+----------------------------------------------+
34 rows in set (0.00 sec)
2.user表中管理用户名与密码因此我们在这张表里面去修改，命令：
alter user ‘root’ @‘localhost’ identified by ‘123456’;
mysql> alter user 'root' @'localhost' identified by '123456';
Query OK, 0 rows affected (0.15 sec)
3.改完记得重新加载权限表，命令：flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
4.退出mysql重进，命令：mysql -u root
C:\WINDOWS\system32>mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- 注释：此刻没有密码是进不去的，证明我们改密成功了
5.输入命令：mysql -u root -p
C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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>
- 注释：输入密码成功进入
三、如何使用工具端Navicat连接数据库
- 打开客户端工具，输入相关的信息，点击测试连接
- 注释：提示上述错误，是一个关于加密规则的错误：
很多用户在使用Navicat Premium 12连接MySQL数据库时会出现Authentication plugin ‘caching_sha2_password’ cannot be loaded的错误。出现这个原因是mysql 8 之前的版本中加密规则是mysql_native_password,而在mysql 8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password.
- 进入mysql库，在users表里面查看加密规则
mysql> use mysql;
Database changed
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
- host：允许用户登录的 ip，此处如果为 % 表示可以远程；
- user: 当前数据库的用户名；
- plugin： 密码加密方式；
- 在mysql 5.7.9以后废弃了password字段和password()函数
- 修改加密规则
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
- 修改加密规则后再次查看
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
-
然后重复一步骤，出现如下错误：
-
在步骤2中就发现不能远程，设置如下：
mysql> update user set Host='%' where User='root';
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
- 再次查看，发现root账号可以远程连接。
mysql> select host,plugin,user from user;
+-----------+-----------------------+------------------+
| host | plugin | user |
+-----------+-----------------------+------------------+
| % | mysql_native_password | root |
| localhost | caching_sha2_password | mysql.infoschema |
| localhost | caching_sha2_password | mysql.session |
| localhost | caching_sha2_password | mysql.sys |
+-----------+-----------------------+------------------+
4 rows in set (0.01 sec)
