手把手教你在 Windows Server 2019 上安装 MySQL8.0.34_windows server2019部署mysql

手把手教你在 Windows Server 2019 上安装 MySQL8.0.34

1、下载Windows版的MySQL8.0.34安装包

下载地址是：https://downloads.mysql.com/archives/community/
在【Product Version】栏选择版本【8.0.34】
在【Operating System】栏选择【Microsoft Windows】
在下面选择【Windows (x86, 64-bit), ZIP Archive】(mysql-8.0.34-winx64.zip)，点击右边的【Download】下载即可。

2、安装Windows版的MySQL8.0.34

2.1、在服务器创建文件夹：

MySQL软件安装目录：D:\app\mysql\8.0.34
MySQL数据文件目录：D:\data\mysql8.0.34

2.2、将mysql-8.0.34-winx64.zip安装包上传到服务器并解压缩到目录D:\app\mysql\8.0.34

D:\app\mysql\8.0.34>dir
 驱动器 D 中的卷是 新加卷
 卷的序列号是 98C5-DAB9

 D:\app\mysql\8.0.34 的目录

2024/02/23  10:35    <DIR>          .
2024/02/23  10:35    <DIR>          ..
2024/02/23  10:33    <DIR>          bin
2024/02/23  10:33    <DIR>          docs
2024/02/23  10:33    <DIR>          include
2024/02/23  10:33    <DIR>          lib
2023/06/22  11:07           279,815 LICENSE
2024/02/23  10:59               887 my.ini
2023/06/22  11:07               666 README
2024/02/23  10:34    <DIR>          share
               3 个文件        281,368 字节
               7 个目录 740,473,819,136 可用字节
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3、创建my.ini初始化参数文件，文件内容如下：

注意：以下内容仅供参考，详细参数请以实际为准进行调整。

[mysqld]
innodb_dedicated_server=on
# 设置3307端口
port=3307
# 设置mysql的安装目录
basedir=D:\app\mysql\8.0.34
# 设置mysql数据库的数据的存放目录
datadir=D:\data\mysql8.0.34
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
innodb_buffer_pool_size=1G
log_timestamps=system
#innodb_force_recovery=6
secure_file_priv=''
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3、初始化MySQL数据库

运行如下命令初始化MySQL数据库：

D:\app\mysql\8.0.34\bin>mysqld.exe --defaults-file=D:\app\mysql\8.0.34\my.ini --initialize-insecure

--defaults-file 参数指定MySQL数据库的默认参数文件
--initialize-insecure 参数以安全模式初始化MySQL数据库，用此参数初始化的数据库默认密码为空
1
2
3
4

4、添加MySQL服务到Windows服务列表中

D:\app\mysql\8.0.34\bin>mysqld.exe install mysql8 --defaults-file=D:\app\mysql\8.0.34\my.ini
Service successfully installed.
注意语法顺序install是安装服务命令，mysql8是服务名，--defaults-file是指定启动参数文件。服务添加后，在Windows的服务列表中会出现mysql8的服务，启动参数为：
D:\app\mysql\8.0.34\bin\mysqld.exe --defaults-file=D:\app\mysql\8.0.34\my.ini mysql8

补充：删除MySQL服务的命令如下:
D:\app\mysql\8.0.34\bin>mysqld.exe remove mysql8
Service successfully removed.
1
2
3
4
5
6
7
8

5、启动MySQL数据库

右键mysql8服务，选择【启动】，即可启动MySQL数据库。
也可以使用命令启动/关闭MySQL服务：

启动MySQL服务：
D:\app\mysql\8.0.34\bin>net start mysql8
mysql8 服务正在启动 .........
mysql8 服务已经启动成功。

--作用：启动MySQL服务，注意此时的【mysql8】表示服务名

1
2
3
4
5
6
7

关闭MySQL服务：
D:\app\mysql\8.0.34\bin>net stop mysql8
mysql8 服务正在停止.
mysql8 服务已成功停止。
1
2
3
4

5.1、启动过程中可以查看MySQL数据库的错误日志。成功启动的日志如下:

2024-02-23T11:10:06.351977+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-23T11:10:06.352026+08:00 0 [System] [MY-013169] [Server] D:\app\mysql\8.0.34\bin\mysqld.exe (mysqld 8.0.34) initializing of server in progress as process 6312
2024-02-23T11:10:06.355605+08:00 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2024-02-23T11:10:06.416046+08:00 0 [Warning] [MY-012358] [InnoDB] Option innodb_dedicated_server is ignored for innodb_buffer_pool_size because innodb_buffer_pool_size=1073741824 is specified explicitly.
2024-02-23T11:10:06.430584+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-23T11:10:10.890816+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-23T11:10:51.622527+08:00 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-02-23T11:10:52.098191+08:00 6 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-02-23T11:11:30.580173+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-23T11:11:30.580356+08:00 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-23T11:11:30.580421+08:00 0 [System] [MY-010116] [Server] D:\app\mysql\8.0.34\bin\mysqld.exe (mysqld 8.0.34) starting as process 164
2024-02-23T11:11:30.586285+08:00 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2024-02-23T11:11:30.617655+08:00 0 [Warning] [MY-012358] [InnoDB] Option innodb_dedicated_server is ignored for innodb_buffer_pool_size because innodb_buffer_pool_size=1073741824 is specified explicitly.
2024-02-23T11:11:30.625616+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-23T11:11:32.456521+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-23T11:12:13.602688+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-23T11:12:13.603674+08:00 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-23T11:12:14.039024+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2024-02-23T11:12:14.039632+08:00 0 [System] [MY-010931] [Server] D:\app\mysql\8.0.34\bin\mysqld.exe: ready for connections. Version: '8.0.34'  socket: ''  port: 3307  MySQL Community Server - GPL.


-- ready for connections 表示启动成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

6、查看MySQL端口

MySQL启动成功后，会出现my.ini参数中定义的端口3307，使用netstat命令查看端口：

D:\app\mysql\8.0.34\bin>netstat -ano |findstr :3307
  TCP    0.0.0.0:3307           0.0.0.0:0              LISTENING       164
  TCP    [::]:3307              [::]:0                 LISTENING       164
可以看到MySQL启动成功后的监听端口为3307
1
2
3
4

7、登录MySQL数据库

如果服务器只有一个MySQL实例，可以不用带端口，启动命令如下：

D:\app\mysql\8.0.34\bin>mysql.exe -uroot -p
Enter password:    --密码为空，直接回车即可登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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
3
4
5
6
7
8
9
10
11
12
13
14
15

8、修改MySQL数据库root用户密码

由于是以安全默认初始化的数据库，默认root用户密码为空，为了安全起见，要设置root用户的密码
查看数据库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

切换到mysql数据库：
mysql> use mysql;
Database changed

查看mysql默认用户信息：
由于前面我们是以安全模式初始化的数据库，所以root用户密码字段为空。
mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

修改mysql密码为【mysql@123】
mysql> alter user 'root'@'localhost' identified by 'mysql@123';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改成功后，重新登录测试
D:\app\mysql\8.0.34\bin>mysql.exe -uroot -p
Enter password: *********    --此处输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> use mysql;
Database changed
mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *4B4F5AF88255C46EB9281BD84ED0329C56937097                              |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
此处观察到authentication_string已经不为空了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

9、补充：

9.1、授权root用户远程访问

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'mysql@123';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| root             | %         | caching_sha2_password | *469194E3B01F14336D844E357DD8DE45FFEEEBF5                              |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | mysql_native_password | *469194E3B01F14336D844E357DD8DE45FFEEEBF5                              |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

9.2、修改MySQL密码认证插件

mysql> alter user 'root'@'%' identified with mysql_native_password by 'mysql@123';
mysql> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| root             | %         | mysql_native_password | *469194E3B01F14336D844E357DD8DE45FFEEEBF5                              |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | mysql_native_password | *469194E3B01F14336D844E357DD8DE45FFEEEBF5                              |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

至此，MySQL8.0.34在Windows上安装就完成了。后续根据需要创建相关的业务用户，导入数据，应用连接数据库等操作了。后续的操作，见后面的更新吧。