Original name: mysql-8.0.11-winx64.zip installation tutorial

Original Author: Fairy Tales

Original address: https://www.cnblogs.com/xc1234/p/9050149.html

 

 

 

Download the zip installation package:

MySQL8.0 For Windows zip package download address: https://dev.mysql.com/downloads/file/?id=476233 , you can enter the page without signing in. Then click "No thanks, just start my download." at the bottom to start the download.

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, unpack zip package to installation directory

For example, my installation directory is C:\Program Files\MySQL

1.2, Profile

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

We found that the unzipped directory does not have my.ini file, so you can create it yourself. Add my.ini to the installation root directory, such as here: C:\Program Files\MySQL\my.ini, write the basic configuration:

 

 

[mysqld]
# Set up port 3306
port=3306
# Set the installation directory for mysql
basedir=C:\Program Files\MySQL
# Set up the storage directory for the data in the mysql database
datadir=E:\database\MySQL\Data
# Maximum number of connections allowed
max_connections=200
# Number of failed connections allowed. This is to prevent someone from attempting to attack the database system from this host
max_connect_errors=10
# The character set used by the server defaults to UTF8
character-set-server=utf8
# Default storage engine to be used when creating new tables
default-storage-engine=INNODB
# Use "mysql_native_password" plug-in authentication by default
default_authentication_plugin=mysql_native_password
[mysql]
# Set mysql client default character set
default-character-set=utf8
[client]
# Set the default port used by mysql clients when connecting to the service side
port=3306
default-character-set=utf8

 

 

 

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

See all the configuration items for reference: https://dev.mysql.com/doc/refman/8.0/en/mysqld-option-tables.html

1.3, Initialize database

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

 

　　C:\Users\Administrator>mysqld --initialize --console

 

When the execution is complete, the initial default password for the root user is 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 section in the execution output:   [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E   among root@localhost : The following "rI5rvf5x5G,E" is the initial password (without the first space). You need to remember this password before you can change it and use it for subsequent logins.

If you're handy, close fast, or don't remember, that'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 password changes and use whatever method you like.

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

 

1.4, Installation Services

Execute commands in the bin directory of the MySQL installation directory (open the cmd command line as an administrator, or right-click Open Command Line Window Here) in the installation directory Shift+

 

C:\Users\Administrator>mysqld--install    [Service Name]

　　C:\Users\Administrator>mysqld --install mysql

 

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

Once the installation is complete, you can start the MySQL service with the command net start mysql.

 

　　C:\Users\Administrator>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 ..
MySQL The service has been started successfully.


C:\Program Files\MySQL\bin>

 

 

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

 

2. Change Password and Password Authentication Plugin

 

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

 

       C:\Users\Administrator>mysql -uroot -p

*   Enter password: (initial password)

You will be prompted to enter your password, remember the password you used when you installed step 1.3 above, and fill it in to log in successfully and enter MySQL command mode.

Before MySQL 8.0.4, executed

SET PASSWORD=PASSWORD('[modified password]');
You can change the password, but MySQL 8.0.4 starts, which is not possible by default. Because previously MySQL's password authentication plug-in was "mysql_native_password", now it's "caching_sha2_password".

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

Modify the user password to execute commands in MySQL:

 

        Mysql> alter user'root'@'localhost'identified by'New password';

Modify the password validation plug-in and also change the password.

If you want to use the "mysql_native_password" plug-in authentication by default, you can configure default_in the configuration file Authentication_ Plugin item.

[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 resources: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

 

This completes the installation and deployment. Officials say MySQL8 is twice as fast as 5.

 

You can use commands to view the default installed databases:

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 see that the MySQL database is initialized by default, where the user table stores MySQL user information. Let's 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>

 

Administrator root's host is a localhost, meaning only localhost login access. If you want to allow other ip logins to be open, you need to add a new host. If you want to allow all ip access, you can modify it directly to'%'

Create user:

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

# (Note: the mysql8.0 encryption has been modified)
#Check Users

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

Authorize Remote Database

#All authorization rights  
GRANT ALL PRIVILEGES ON *.* TO 'xxh'@'%'；
#Authorize basic query modification permissions, set as required
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'xxh'@'%';

view user permission
show grants for 'xxh'@'%';

 

Example:

 

 

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

mysql> 

 

View password encryption:

 

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>

 

 

Additionally, if you need to add a new account or access MySQL from someone else outside your local computer, you need to set up a host for the built-in account, which you can refer to: MySQL Create Users and Authorizations