Updated to MySQL 5.7.9 to talk about the recent
The MySQL 5.7.9 GA version has been released, and the VPS has been updated. Although it is not as easy as the version number is updated, it is easy to move the database directory directly, but it is not difficult.
This time, the sub-version number was updated. The process went smoothly, there were no pitfalls, and there was an alarm when starting. (I'm updated from MySQL 5.6.27)
Talk about the first one:
[Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
The secure-file-priv parameter mainly limits LOAD_FILE and LOAD DATA and SELECT … , INTO OUTFILE reports to the specified directory.
Generally speaking, it is rarely used. The default values include empty, dirname, and NULL. The correct meaning is: leave blank, specify the path, and NULL value. Never think it is empty, specified path, or NULL. . . empty will report an error. empty is actually left blank and will be converted to the default value, which is platform-specific. I don’t know what it is. Anyway, it will be as prompted above. Setting the path will require you to not read the path other system users cannot read it and cannot get your MySQL database directory. NULL means empty, and it also means disable.
So I set secure-file-priv to NULL in:
secure-file-priv = NULL
You can see this operation, and now the prompt is:
[Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
The literal translation is: [Tip]--secure-file-priv is set to NULL, and the data import and export operation is prohibited.
This will not affect the use of mysqldump, don't worry.
The second warning is:
[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
In 5.7, connecting mysql through ssl is used as the default and recommended. If you don't need it (such as the same server on the web and db ends, the db server is on the web intranet, etc.), you can do not need to use the SSL method.
It is very simple to kill the warning, just skip SSL in the statement.
skip-ssl
That's OK.
There is also a warning. . . yes:
[Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
[Warning] 'user' entry '@localhost' ignored in --skip-name-resolve mode.
[Warning] 'db' entry 'sys @localhost' ignored in --skip-name-resolve mode.
[Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
[Warning] 'tables_priv' entry 'sys_config @localhost' ignored in --skip-name-resolve mode.
If you have to use the skip-name-resolve parameter, this is really unsolvable and you will not report localhost's fault in 5.6.
If you don't like this prompt, just say, don't use the skip-name-resolve parameter.
Even if you are willing to modify localhost to 127.0.0.1, many applications, such as WordPress, which access localhost by default, will be unavailable. You need to change the host to 127.0.0.1.
If you only have a single database or application. That can be solved by modifying localhost to 127.0.0.1. I'm too lazy to change it if I have too many online databases and applications.
If you do not have remote access to the database and only use the database locally, it is recommended that you use skip-networking instead of skip-name-resolve. The skip-name-resolve parameter mainly disables DNS resolution, while the skip-networking parameter sets MySQL not to listen to the network, so it can only be accessed by local machine.
There is another one that no one can see. The current default value of SQL Mode is:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
My suggestion is to remove NO_ZERO_IN_DATE and NO_ZERO_DATE, because after all, the default value of many products (such as Wordpress) is 0000-00-00 00:00:00, and it is not allowed if it is not removed. Of course, this is harmless.
If it fails, MySQL will warn you in the query:
#1067 - Invalid default value for 'comment_date'
Finally, let’s talk about what everyone cares about most, performance. I did some tests and it felt that 5.7.9 was slower than 5.6.27. . . Right, you read it right, it's not as fast as 5.6.27. . . Very disappointed, I'm still looking forward to PHP7.
Maybe I should choose MariaDB or Percona Server. . . To the latter, although the former may perform better, the latter is more stable. I used the former in its early days. How to say it depends on the critical hit rate of character.
Let’s talk about what happened to you recently.
NetEase’s incident has been very popular recently. I have been using NetEase Email for more than 10 years and I checked the account without any problem (with security code).
After thinking about it, I changed the security code to the account under the complaint, and then the security code was gone. . .
Is there no problem with the database with security codes in the past? . . I regretted it and kept it secretly. . .
But if you change it, it will be changed, it will be safer.
I thought that my first email address in my life was Yahoo China, and then I immediately entered the embrace of 126 in the same year. Now Yahoo China Email has 88, and only 126 left.
The reason why I chose 126 back then was just because 126 is short, a number, and easy to remember, especially because it is much easier to write than writing. . .
Supplement: Administrator modify the root password method of the new version of MySQL 5.7.9 and sort out some new changes
Administrator: Modify the root password method of the new version of MySQL 5.7.9 and sort out some new changes
Starting from the 5.7 version of MySQL, the password verification mechanism is enhanced. It is said that when installing, the default password will be generated in the /root/.mysql_secret file. This has also been removed since the 5.7.6 version.
For the purpose of generating a default password, there is a version online, with the following content:
1. Modify the configuration file, generally add the skip-grant-tables field under the [mysqld] field to ignore permission verification;
2. Restart MySQL server, the CentOS 7.0 operation command is:
systemctl restart
3. Use mysql -u root -p to log in to the data and press Enter directly with the password;
4. Modify the mysql database (name), the authentication_string field of the user table, and the modification command is as follows:
mysql> update set authentication_string=password('new_password') where user='root' and Host ='localhost';
mysql> flush privileges;
mysql> quit;5. Modify the configuration file, remove skip-grant-tables, restore it to its original appearance, and then re-replace the MySQL server.
6. Use the password set in step 4 to log in to the mysql server.
7. Reset the password through the set password=password('new_password'); command;
The above method is still very useful in the early versions of MySQL 5.7, but in the last point of resetting the password will never take effect, and the following error will appear.
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
At the same time, other operations cannot be performed.
This is because since MySQL 5.7, there is a concept of validity period for passwords, and due to security level issues, other methods need to be set. Another point to note is that since 5.7.6, the password() function has been abandoned, and the old_password=1 cannot be set. This is really a tortuous exploration process.
So, let’s organize the password setting methods for MySQL version 5.7.9 or above.
Most of the content is the same as the ones sorted out above. The key is to record the value written in authentication_string after step 4 (this is because the password() function is abandoned)
By using the following command instead of step 7, set password=password('new_password') method.
mysql> ALTER USER 'jeffrey'@'localhost'
-> IDENTIFIED WITH mysql_native_password
-> AS 'authentication_string field content';
Query OK, 0 rows affected (0.01 sec)In fact, there is a simplified version of the above command, the command is as follows
mysql> ALTER USER USER() IDENTIFIED BY 'news_password'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
The above error message appears is related to the security level of the password. Check the security level command as follows
mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+
Through the SET GLOBAL validate_password_policy='LOW'; command, after lowering the security level, it can be used directly. The limit is that it must be more than 8 characters;
A more detailed introduction to the safety level is as follows
LOWPolicy only tests password length. The password must be at least 8 characters long.MEDIUMPolicy Conditions The password must contain at least 1 numeric character, 1 uppercase and lowercase characters, and 1 special (nonalphanumeric) character.STRONGPolicy case The codon string length of 4 or more cannot match the word in the dictionary file if a person is specified.
Finally, in order to solve the problem of password failure, finally, under the [mysqld] field of the configuration file, add the following fields
[mysqld] default_password_lifetime=0
You can also set it through the command line
ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER
For more information about the validity period of the password, please refer to/doc/refman/5.7/en/
This is the end of this article about the detailed tutorial on updating to MySQL 5.7.9. For more related updates to MySQL 5.7.9, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!