As a senior MySQL ticket pal, I've been blowing water for 8.0 recently. We know that several MySQL courses have been upgraded to version 8.0 since last year. My VPS has been trying to upgrade for a long time, but the PHP version is too low. I'm worried about problems after the upgrade, so I haven't been bothered. After two days, I started to upgrade mysql.
To unlock the latest features, I directly upgraded to the latest MySQL version 8.0.19:
[yejr@imysql.com]> \s .. Using delimiter: ; Server version: 8.0.19 MySQL Community Server - GPL) Protocol version: 10 ... Binary data as: Hexadecimal
Have you noticed the Binary data as: Hexadecimal above? I wrote a few days ago A small change of MySQL 8.0.19 client This new feature is introduced in.
Because my PHP is still a relatively low version, MySQL 8.0 has the greatest impact on PHP applications compared to version 5.7, which is the account authentication method. Therefore, the former mysqlnativepassword has become cacheingsha2password. In addition to the native authentication plug-in to be used for account authorization, there is also a default character set to be used that needs to be kept in utf8. Otherwise, the following two kinds of errors may be reported:
SQLSTATE[HY000] [2054] Server sent charset unknown to the client //Or maybe SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
Don't panic when you see the error report above. Just modify the following two MySQL parameters:
#Since MySQL 8.0, the default character set has been changed to utf8mb4, which causes PHP PDO connection error #You only need to specify the default character set as utf8, but this does not affect the creation of the utf8mb4 character set table in MySQL character-set-server = utf8 #Specifies that the account authentication plugin also uses the native mode, otherwise an error will be reported when the PHP PDO connection handshakes default_authentication_plugin = mysql_native_password
When creating a new account under MySQL 8.0, you need to first CREATE USER and specify password, and then GRANT authorization. As before, you can't directly use a GRANT statement to do three things: create account + specify password + authorization. For example:
[yejr@imysql.com]> CREATE USER yejr@localhost identified with mysql_native_password by 'c97721c63c9fc077'; [yejr@imysql.com]> GRANT ALL ON yejr.* to yejr@localhost;
The upgrading scheme of MySQL from 5.7 to 8.0 is recommended as follows:
- First upgrade the current MySQL 5.7 to the latest version, for example, 5.7.29.
- Check the error log to make sure that no tables report errors during upgrade.
- Set InnoDB? Fast? Shutdown = 0 to close the 5.7.29 instance.
- Modify my.cnf parameter to adapt to version 8.0, for example, remove the internal TMP disk storage engine parameter.
- Add the parameter upgrade = AUTO in my.cnf, so that MySQL 8.0 can automatically upgrade system tables and user tables after startup.
- Check to make sure that there are no other errors during MySQL 8.0 instance startup.
During the upgrade process, I also found that if the drupal system is used, there may be an error message:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of xx/includes/lock.inc).
That's because in drupal code, SQLMODE is set by the way when mysql is connected. The NOAUTOCREATEUSER mode no longer exists after 8.0. You only need to fine tune the following code:
[root@yejr.me]# vim includes/database/mysql/database.inc $connection_options['init_commands'] += array( #Comment out the line code or delete the no auto create user mode #'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'", 'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'", );
OK, now the whole site is upgraded to MySQL 8.0, yeah A kind of A kind of
enjoy MySQL 8.0.
End of the paper
The 17th issue of "MySQL optimization course" of zhishutang, which I'm talking about, has already started. Our course has been upgraded from the 15th issue to MySQL 8.0. Now it's just time to get on the bus. Let's start the practice tour of MySQL 8.0 together
In addition, I am in Tencent class Intensive course of MySQL performance optimization At the end of this course, we will read several core elements of MySQL Performance Optimization: rational use of index, reduction of lock impact, and improvement of transaction concurrency.