Just yesterday morning, I had Apache2.4.33 and PHP7.2.4 environments installed for the cloud server and was ready for a new MySQL5.7.22. RPM: mysql80xxx. RPM: mysql80xxx. RPM: mysql80xxx. RPM MySQL has released the 8.0.11 official Stable Release (GA). How do I feel in my heart

— Was overtaken just as he was about to start

Seeing the advertised 2x performance improvement and many new features, objk. Drop MySQL5.7 and go straight to 8.

MySQL8.0 uses the new password plug-in authentication method. 5.7 is mysql_native_password, 8.0 is caching_sha2_password, This encryption disables many interface tools (such as Navicat) or programming languages (such as PHP) that connect to MySQL using mysqli:

Error : The server requested authentication method unknown to the client [caching_sha2_password]

Error: The client does not know the server requires authentication rule [caching_sha2_password],

Goal: to update MySQL8.0.11 on both cloud Linux and local Windows, while allowing PHP7 to connect to the generic mysqli interface.

Tool: PuTTY Used to remotely connect to the cloud server. FileZilla Client monitors the server’s file directory to see what the command does.

The Windows version

1. Download the MySQL ZIP from the official website

https://dev.mysql.com/downloads/mysql/Copy the code

2, decompress and put in any directory you specify

For example: E: \ myServer \ mysql – 8.0.11 – winx64

The content of the default configuration file my.ini is as follows. Change the path based on the actual address

[mysqld] # port [mysqld] # port [mysqld] # port [mysqld] # port [mysqld] # port [mysqld] # port 3380 basedir = E: \ myServer \ mysql - 8.0.11 - winx64 \ datadir = E: \ myServer \ mysql - 8.0.11 - winx64 \ data# mysql8.0.11 The default value is caching_sha2_passwordDefault_authentication_plugin =mysql_native_password[client]# cmd.exe. The port used by the MySQL command line must be the same as the preceding port = 3380 # https://dev.mysql.com/doc/refman/8.0/en/connecting.htmlCopy the code

3. Log in to the cli as the administrator to install and initialize cmd.exe

Note: Execute cmd.exe as the administrator. After entering a line of instructions, press Enter to execute

The statement is explained as follows, E: CD E:\myServer\mysql-8.0.11-winx64\bin# 2 Mysql > install MySQL3380; mysql > install MySQL3380 [mysqld --initialize-insecure# 4 [starting the MySQL service]net start MySQL3380# 5 Log in to the MySQL console using the cli. The default password is empty, Return to mysql -u root - p# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- # / unloading service (not use) net stop MySQL3380sc delete MySQL3380Copy the code

[Top command GIF demo.gif]

The Linux version

The environment is cloud server system version CentOS7.3, installed using Yum Repository

[root@codenight ~]# cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core)Copy the code

My.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockassorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid! includedir /etc/my.cnf.dCopy the code

1. Download the. RPM file of MySQL8.0.11

wget  https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpmCopy the code

For this step, download the mysql80-community-release-el7-1.noarch. RPM file from the server /root directory

2. Install the RPM package

rpm -ivh mysql80-community-release-el7-1.noarch.rpm --force --nodepsCopy the code

This step generates two files in /etc/yum.repos.d/ :

mysql-community.repo

mysql-community-source.repo

3. Install mysql – community – server

yum install mysql-community-serverCopy the code

A dialog box will pop up to confirm y and finally Complete! This command installs a lot

This step generates a new MySQL configuration file, my.cnf, which contains the following contents and does not need to be modified

# # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html/mysqld innodb_buffer_pool_size = 128 m # log_bin# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M## Remove the # in front of default_authentication_plugin, # so it is compatible with older clients. Please look at the content: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin# default-authentication-plugin=mysql_native_passworddatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/ log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidCopy the code

4. Start mysqld service and add startup

[root@codenight ~]# systemctl start mysqld.service[root@codenight ~]# systemctl enable mysqld.serviceCopy the code

5. Obtain the temporary login password

[root@codenight ~]# grep 'temporary password' /var/log/mysqld.logCopy the code

Copy the password returned from it:

Tip: If the password is incorrect, copy it to notepad and check it carefully. If not, go to /var/log/, download mysqld.log, find the password in it, change the password, upload mysql.log, and restart the mysql service

 

6. Log in to the database and enter the password

[root@codenight ~]# mysql -u root -pCopy the code

An error occurs when any SQL statement is executed

mysql> SHOW DATABASES; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.Copy the code

[Error 1820] Warning that you must reset your password using ALTER USER

7. Reset the password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678'; ERROR 1819 (HY000): Your password does notsatisfy the current policy requirementsCopy the code

[Error 1819] Your password does not meet current policy requirements

The reason is that the default value of validate_password.policy is 1(intermediate strength), except that the 8-bit length of validate_password.policy must contain at least one of the following characters, such as 3ONbm<gdt1_j

https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html#sysvar_validate_password.policyCopy the code

If you still want a simple 8-bit password such as ABC54321, lower the policy value

mysql> SET GLOBAL validate_password.policy=0; Query OK, 0 rows affected (0.00 SEC)Copy the code

The password will now be 12345678

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678'; Query OK, 0 rows affected (0.04 SEC)Copy the code

8. Open remote connections

Insert a record in the user table that is applicable to any host with the login name root and password 12345678, refresh the permission to log in remotely, and confirm that the server security group or port 3306 has been granted.

USE mysql; INSERT INTO `user` VALUES ('%', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0', 'mysql_native_password', '*84AAC12F54AB666ECFC2A83C676908C8BBC381B1', 'N', '2018-04-21 11:25:16', null, 'N', 'Y', 'Y', null, null); FLUSH PRIVILEGES;Copy the code

Take a look at the user permissions table for users and passwords

mysql> SELECT host,user,plugin,authentication_string FROM mysql.user; +-----------+------------------+-----------------------+---------------------------------------------------------------- -----+| host | user | plugin | authentication_string |+-----------+------------------+-----------------------+--------------------------------------------------------------- ------+| % | root | mysql_native_password | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 || localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.session | mysql_native_password |  *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | root | caching_sha2_password | $A$005$0qyQJPnk>R5J>~}tGo2dCdahVKfz42pCUr/3tLWJYERvir2Mr5jUUkBR2B |+-----------+------------------+-----------------------+--------------------------------------------------------------- ------+5 rows in setCopy the code

Note: The same password 12345678 is the ciphertext string generated. The first line is the result of native encryption, AND the fifth line is the result of SHA2 encryption. The server command line uses host=localhost AND user=root, AND the remote connection uses host=% AND user=root

9. Downgrade gracefully

Navicat can now connect to Navicat remotely, PHP and other programming languages call mysqli interface is not available, because MySQL8.0.11 was released only before the official version, these interfaces are not written (the latest VERSION of PHP is on March 30), so the encryption plugin needs to be set to mysql_native

Modify /etc/my.cnf to save and restart the MySQL server

[root@codenight ~]# systemctl restart mysqld.serviceCopy the code

10. Other related error handling

For example, 12345678, the authentication_string field in the user table must store the ciphertext generated according to the rules specified in the plugin field. Otherwise, the password will be incorrect no matter how you input it

References:

[official] use Yum Repository MySQL:https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html installation

MySQL5.7:https://www.cnblogs.com/jorzy/p/8455519.html CentOS7 by YUM installation

https://blog.csdn.net/wisdom_c_1010/article/details/55506081