Detailed installation tutorial of mysql-8.0.11-winx64.zip

Download the zip installation package:

Download address of MySQL8.0 For Windows zip package: https://dev.mysql.com/downloads/file/?id=476233. 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/Downloads/MySQL-8.0/mysql-8.0.11-winx64.zip

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 Windows systems, the default configuration file is the my.ini file (or my-default.ini) in the installation directory. Some configurations need to be configured during the initial installation, and most can be changed after the installation is complete. Of course, in extreme cases, everything can be changed.

We found that there is no my.ini file in the unzipped directory. It doesn’t matter, you can create it yourself. Add my.ini to the installation root directory, for example, mine is: C:\Program Files\MySQL\my.ini, write the basic configuration:

[mysqld]
# Set port 3306 port=3306
# Set the installation directory of MySQL basedir=C:\Program Files\MySQL
# Set the storage directory of MySQL database data datadir=E:\database\MySQL\Data
# Maximum number of connections allowed max_connections=200
# The number of connection failures allowed. This is to prevent someone from trying to attack the database system from this host max_connect_errors = 10
# The default character set used by the server is UTF8
character-set-server=utf8
# The default storage engine that will be used when creating a new table default-storage-engine=INNODB
# By default, the "mysql_native_password" plug-in is used for authentication. default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set of the mysql client to default-character-set=utf8
[client]
# Set the default port used by the mysql client to connect to the server port = 3306
default-character-set=utf8

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

To view all configuration items, refer to: https://dev.mysql.com/doc/refman/8.0/en/mysqld-option-tables.html

1.3, Initialize the database

Execute the command in the bin directory of the MySQL installation directory:

mysqld --initialize --console

After the execution is complete, the initial default password of the root user will be printed, for example:

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>

Notice! There is a paragraph in the execution output result: [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E The "rI5rvf5x5G,E" after root@localhost: is the initial password (excluding the first space). Before changing the password, you need to remember this password as it will be needed for subsequent logins.

If you accidentally close the directory too quickly or forget to remember, it's OK. Just delete the initialized datadir directory and execute the initialization command again, and it will be regenerated. Of course, you can also use security tools to force a password change, using any method you like.

Reference: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization-mysqld.html

1.4, Installation Service

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

mysqld --install [服務名]

The service name behind it can be omitted, the default name is mysql. Of course, if you need to install multiple MySQL services on your computer, you can distinguish them with different names, such as mysql5 and mysql8.

After the installation is complete, 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 service is starting..

The MySQL service has been started successfully.

C:\Program Files\MySQL\bin>

Reference: https://dev.mysql.com/doc/refman/8.0/en/windows-start-service.html

Second, change the 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, you will be prompted to enter a password. Remember the password you installed in step 1.3 above. Fill it in to log in successfully and enter the MySQL command mode.

Before MySQL 8.0.4, execute

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

You can change the password, but starting with MySQL 8.0.4, this is not possible by default. Because before, the password authentication plugin for MySQL was "mysql_native_password", and now it uses "caching_sha2_password".

Because many database tools and link packages currently do not support "caching_sha2_password", for convenience, I temporarily changed back to the "mysql_native_password" authentication plug-in.

To change the user password, execute the command in MySQL:

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

Modify the password verification plug-in and modify the password at the same time.

If you want to use the " mysql_native_password " plug-in authentication by default, you can configure 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 'new password';
Query OK, 0 rows affected (0.06 sec)
mysql>

Reference: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

At this point, the installation and deployment is complete. The official test shows that MySQL 8 is twice as fast as 5.

You can use the command to view 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>

You can see that the MySQL database is initialized by default, and the user table stores MySQL user information. We can take a look at the default MySQL user:

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>

The host of the administrator root is localhost, which means that only localhost login access is allowed. If you want to allow other IP addresses to log in, you need to add a new host. If you want to allow all IP access, you can directly change it to "%"

Create a user:

CREATE USER 'xxh'@'%' IDENTIFIED WITH mysql_native_password BY 'xxh123!@#';
#(Note: the encryption method of mysql8.0 has been modified)
#Check userselect user, host, plugin, authentication_string from user\G;
Authorize remote database #Authorize all permissions GRANT ALL PRIVILEGES ON *.* TO 'xxh'@'%';
#Authorize basic query and modification permissions, set GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO 'xxh'@'%' as required;

View User Permissions

show grants for 'xxh'@'%';

Example:

mysql> use mysql;
Database changed
mysql> CREATE USER 'xxh'@'%' IDENTIFIED WITH mysql_native_password BY 'xxh123!@#'; #Create user (Note: the encryption method has been modified in mysql8.0)
Query OK, 0 rows affected (0.07 sec)
mysql>

View password encryption method:

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>

In addition, if you need to add a new account or if someone other than the local machine needs to access MySQL, you also need to set the host of the built-in account.

Summarize

The above is the detailed installation tutorial of mysql-8.0.11-winx64.zip introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time!