Comprehensive explanation of the installation method of MySQL8.0 For Windows-Mysql Tutorial-php.cn

Download the zip installation package:

　MySQL8.0 For Windows zipPackage download address: https://dev.mysql.com/downloa..., you don’t need to log in after entering the page. Then click "No thanks, just start my download." at the bottom to start downloading.

Or download directly: https://dev.mysql.com/get/Dow...

Environment: Windows 10

1, Installation

　1.1. Unzip the zip package to the installation directory
For example, my installation directory is: C:\Program Files\MySQL

　1.2. Configuration file
In the Windows system, the configuration file defaults to the my.ini file (or my-default.ini) in the installation directory. Some configurations need to be initialized Configured during installation, most can also be changed after installation is complete. Of course, in extreme cases, everything can be changed.

We found that the decompressed directory does not have the my.ini file. It doesn’t matter, you can create it yourself. Add my.ini in the installation root directory, for example, here is: C:\Program Files\MySQL\my.ini, write the basic configuration:

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:\Program Files\MySQL
# 设置mysql数据库的数据的存放目录
datadir=E:\database\MySQL\Data
# 允许最大连接数
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
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

Note, the # inside ##basedir is my local installation directory, datadir is the location where my database data files are stored. Each configuration needs to be configured according to your own environment.

To view all configuration items, please refer to: https://dev.mysql.com/doc/ref...

1.3, initialize the database Execute the command in the
bin directory of the MySQL installation directory:

mysqld --initialize --console

Execute After completion, the initial default password of the

root user will be printed, such as:

C:\Users\Administrator>cd C:\Program Files\MySQL\bin

C:\Program Files\MySQL\bin>mysqld --initialize --console
2018-04-28T15:57:17.087519Z 0 [System] [MY-013169] [Server] C:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.11) initializing of server in progress as process 4984
2018-04-28T15:57:24.859249Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E
2018-04-28T15:57:27.106660Z 0 [System] [MY-013170] [Server] C:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.11) initializing of server has completed

C:\Program Files\MySQL\bin>

Attention! There is a paragraph in the execution output result:

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E where root@localhost: follows "rI5rvf5x5G,E" is the initial password (excluding the first space). Before changing the password, you need to remember this password and use it for subsequent logins.

If you are stupid, shut down too quickly, or don’t remember it, it’s okay. Delete the initialized

datadir directory, execute the initialization command again, and it will be regenerated. Of course, you can also use security tools to force a password change. It’s up to you which method you use.

Reference: https://dev.mysql.com/doc/ref...

1.4, install the service

in the
MySQL installation directory## Execute the command in the #bin directory (open the cmd command line as an administrator, or right-click on the installation directory Shift and "Open a command line window here" ):

mysqld --install [Service name] The service name following

does not need to be written. The default name is

mysql

. Of course, if you need to install multiple MySQL services on your computer, you can use different names to distinguish them, such as mysql5 and mysql8. After the installation is completed, you can start the

MySQL

service through the command net start mysql. Example:

C:\Program Files\MySQL\bin>mysqld --install
Service successfully installed.

C:\Program Files\MySQL\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。


C:\Program Files\MySQL\bin>

Reference: https://dev.mysql.com/doc/ref...

Second, change password and password authentication plug-in

Execute the command in the

bin

directory of the MySQL installation directory:

mysql -u root -p

At this time, it will You will be prompted to enter a password. Remember the password used during installation in step 1.3 above. Fill it in and you will be able to log in successfully and enter the MySQL command mode.
Before

MySQL8.0.4

, execute

SET PASSWORD=PASSWORD('[modified password]');

Change the password, but starting from MySQL8.0.4
, this will not work by default. Because before, the password authentication plug-in for MySQL was "mysql_native_password", but now it is "caching_sha2_password". Because many database tools and link packages currently do not support

"caching_sha2_password"

, for the sake of convenience, I temporarily changed it back to the "mysql_native_password" authentication plug-in. Change the user password and execute the command in

MySQL

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password';

Modify the password verification plug-in and change the password at the same time.
If you want to use

"mysql_native_password"

plug-in authentication by default, you can configure the default_authentication_plugin item in the configuration file.

[mysqld]

default_authentication_plugin=mysql_native_password
Example:

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11

Copyright (c) 2000, 2018, 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> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
Query OK, 0 rows affected (0.06 sec)

mysql>

Copy code

Reference: https://dev.mysql.com/doc/ref...

At this point, the installation and deployment is complete. Officially, the test speed

MySQL8

is twice as fast as 5. You can use the command to check the default installed database:

show databases;

use mysql;

show tables;

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

mysql>

看到默认初始化了mysql数据库，其中user表里面存储MySQL用户信息。我们可以看一下默认MySQL用户：

select user,host,authentication_string from mysql.user;

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root             | localhost | *27C237A977F4F44D3F551F1A673BE14DFD232961 |
+------------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>

管理员root的host是localhost，代表仅限localhost登录访问。如果要允许开放其他ip登录，则需要添加新的host。如果要允许所有ip访问，可以直接修改成“%”

创建用户：

CREATE USER 'xxh'@'%' IDENTIFIED WITH mysql_native_password BY 'xxh123!@#';

(需要注意：`mysql8.0`加密方式修改了)

检查用户

select user, host, plugin, authentication_string from user\G;

授权远程数据库

授权所有权限

GRANT ALL PRIVILEGES ON *.* TO 'xxh'@'%'；

授权基本的查询修改权限，按需求设置

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'xxh'@'%';

查看用户权限
show grants for 'xxh'@'%';

示例：

mysql> use mysql;
Database changed
mysql> CREATE USER 'xxh'@'%' IDENTIFIED WITH mysql_native_password BY 'xxh123!@#'; #创建用户(注意：mysql8.0加密方式修改了)
Query OK, 0 rows affected (0.07 sec)
mysql>

查看密码加密方式：

mysql> select user, host, plugin, authentication_string from user;
+------------------+-----------+-----------------------+-------------------------------------------+
| user             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| xxh              | %         | mysql_native_password | *70FD6FB4F675E08FF785A754755B5EBA6DA62851 |
| mysql.infoschema | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root             | localhost | mysql_native_password | *27C237A977F4F44D3F551F1A673BE14DFD232961 |
+------------------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql>

另外，如果需要新增账户，或者本机以外的其他人访问MySQL则还需要设置内置账户的host，具体可以参考：MySQL创建用户与授权。

The above is the detailed content of Comprehensive explanation of the installation method of MySQL8.0 For Windows. For more information, please follow other related articles on the PHP Chinese website!