MySQL 8.0.12 installation and configuration method graphic tutorial

Record the installation and configuration method of MySQL 8.0.12 and share it with everyone.

1. Installation

1. Download MySQL 8.0.12 from the Internet, download address

2. After downloading, unzip it

The path I unzipped is: D:\Java\mysql-8.0.12-winx64

3. Configuration Files

First, check whether there is a my.ini file in the decompressed path. If not, create a new one with the following content:

[mysqld]
# Set port 3306 port=3306
# Set the installation directory of mysql to basedir=D:\Java\mysql-8.0.12-winx64
# Set the storage directory of mysql database data datadir=D:\Java\mysql-8.0.12-winx64\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

4. Database Configuration

Enter cmd with administrator privileges (if you do not have administrator privileges, there will be problems) , and enter the bin directory of the MySQL installation directory

Then enter mysqld --initialize --console and the following screen will appear. The one marked in red is the initial password of the database root user. Be sure to remember it as you will need it for logging in later. Of course, if you accidentally turn it off or forget it, delete the initialized data directory and execute the initialization command again to regenerate it.

Then enter mysqld --install to install the MySQL service, and enter net start mysql to start the service. Note: mysqld --remove is to uninstall the MySQL service, and net stop mysql is to stop the service.

5. Enter the database configuration

After entering mysql -u root -p, you will be asked to enter the password. The password is the password you were asked to remember before. After entering it correctly, the following interface will appear, indicating that you have entered the MySQL command mode.

Then change the password and enter ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password'; the following interface appears, indicating that the change is successful.

At this point, the MySQL8.0.12 database has been installed.

2. Database related configuration

View the default database: show databases;

Select mysql database: use mysql

View the default MySQL user: select host, user, authentication_string, plugin from user;

Create a new user: CREATE USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

Authorize the new user: GRANT ALL PRIVILEGES ON *.* TO 'user name'@'host name';

Refresh permissions: FLUSH PRIVILEGES;

3. Detailed explanation of the problem

1. MySQL8.0.12 cannot connect to Navicat

Reason: The encryption rules used by MySQL 8.0 and MySQL 5.0 are different, so Navicat cannot be opened. You can view the password rules by selecting host, user, authentication_string, plugin from user;.

As shown above, the plugin column is the encryption rule for the corresponding user. You can see that the encryption rule for my root user is: mysql_native_password. This is because I have already set it. The default is: caching_sha2_password, so we only need to change the default caching_sha2_password to mysql_native_password.

Solution: Enter ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password'; to modify the encryption rules and password of the root user.

2. Authorization error, showing You are not allowed to create a user with GRANT

Cause: There are many tutorials online saying that when "The user specified as a definer ('root'@'%') does not exist" appears, it means that the root user does not have sufficient permissions and you only need to execute GRANT ALL ON *.* TO 'root'@'%';. However, the error message "You are not allowed to create a user with GRANT" often appears. This is because the percent sign in @ '%' in GRANT ALL ON *.* TO 'root'@ '%' is actually the name of the host corresponding to the root user. Many people do not notice that their root user actually corresponds to localhost, and directly execute the above statement, which is why an error is reported.

Solution: Just change the % in GRANT ALL ON *.* TO 'root'@'%'; to the corresponding host name, and finally refresh the permissions FLUSH PRIVILEGES ;.

Special note: It is said on the Internet that % means wildcard all hosts, but the operation was not successful. I don’t understand why. I guess it may be related to MySQL version 8.0.

Wonderful topic sharing:

MySQL different versions installation tutorial

MySQL 5.7 installation tutorials for various versions

MySQL 5.6 installation tutorials for various versions

mysql8.0 installation tutorials for various versions

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.