Comprehensive explanation of the installation method of MySQL8.0 For Windows

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 theWindowssystem, the configuration file defaults to themy.inifile (ormy-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 themy.inifile. 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 ##basediris my local installation directory,datadiris the location where my database data files are stored. Each configuration needs to be configured according to your own environment.

1.3, initialize the databaseExecute the command in the
bindirectory of theMySQL installation directory:

mysqld --initialize --console

rootuser 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>

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,Ewhereroot@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.

datadirdirectory, 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.

in the
MySQLinstallation directory## Execute the command in the #bindirectory (open thecmdcommand line as an administrator, or right-click on the installation directoryShift and "Open a command line window here"):

does not need to be written. The default name is

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

service through the commandnet 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

directory of theMySQLinstallation directory:

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

, execute

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

, 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

:

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

plug-in authentication by default, you can configure thedefault_authentication_plugin itemin the configuration file.

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

is twice as fast as5.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创建用户与授权。