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)
浙公網安備 33010602011771號