Shulou(Shulou.com)06/02 Report--

This article mainly explains "some problems and summaries often encountered in MYSQL8 installation". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "some problems and summaries often encountered in MYSQL8 installation"!

1 add at SQL_MODE

ONLY_FULL_GROUP_BY, which has always been criticized by other database DBER, in the past, the GROUP BY of MYSQL was often complained because it was not rigorous. After adding this parameter, the GROUP BY of MYSQL will be the same as the GROUP BY of other databases, and loose writing will be prohibited. Loose writing means that the select column of the GROUP BY statement is inconsistent with the column in group by.

2 the following configurations do not exist in MY.CNF, because the format of MYSQL 8 is unified as barracuda

Innodb_file_format = Barracuda

Innodb_file_format_max = Barracuda

3 expire_logs_day will be eliminated in future versions, replaced by binlog_expire_logs_seconds=432000. Here you can try to convert days into seconds.

4 the setting of Undo table space is not in the setting, and MYSQL will automatically set 2 undo spaces by default. If you still have any problems, you need to create your own undo table space.

5 the original password plug-in was changed into a password component by MYSQL8.0. First, the following settings were removed from the original my.cnf traditional Chinese medicine

# disconnect_on_expired_password=disable

# default_password_lifetime=0

# validate_password_policy=LOW # STRONG

# validate-password=FORCE_PLUS_PERMANENT

And the validate_password.so loaded at the start of startup

The official documentation explains that MYSQL installed through yum or rpm includes components of component_validata_password.

If it is by other means (here it is installed through the percona binary), type directly after entering the MYSQL

INSTALL COMPONENT 'file://component_validate_password';

Validate_password.check_user_name compares whether the user name is included in the password, which is turned on by default. If the password contains the user's name, the password generation will be incorrect.

Validate_password.mixed_case_count is used to determine whether there is a case setting for the user name in the password. 0 does not need to be mixed, and 1 requires case mixing by default.

Validate_password.policy= low this is the complexity of the password requirements, low is to require the length of the password, MEDIUM is to require case, numbers and passwords Chengdu, STRONG is in addition to the above requirements, but also think of the contents of the dictionary file.

Finally, MYSQL adds connection-control plugin to MYSQL8. The main function of this plug-in is to supplement the content that does not manage the connection to the server, and how to deal with these ongoing connections after the server's continuous attempted access fails.

You can add the following to the configuration file

Connection-control=FORCE_PLUS_PERMANENT

Connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

Connection_control_failed_connections_threshold=4

Connection_control_min_connection_delay=1500

And install the plug-in before that.

Plugin-load-add=connection_control.so

And execute the command in MYSQL

INSTALL PLUGIN CONNECTION_CONTROL

SONAME 'connection_control.so'

INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

SONAME 'connection_control.so'

After setting up, pass the

Connection_control_failed_connections_threshold to continue to determine how many failed connection attempts the client has made.

Start the connection timeout after exceeding the connection

Connection_control_min_connection_delay=1500 unit is millisecond

This effectively makes up for the shortcomings of the previous MYSQL in this aspect, and more ensures the security of the connection and how to make up for the aggressive connection.

In addition, there are some changes in the default value.

1 max_allow_packet changed from the original 4MB to 64MB

2 table_open_cache changed from 2000 to 4000

3 log_error_verbosity changed from the original notes to warnings

4 innodb_flush_neighbors changed from 1 to 0

5 innodb_max_dirty-pages_pct from 75% to 90%

6 transaction-write-set-extraction changed from the original off to XXHASH64

7 Slave_rows_search_algorithms from the original index_scan, table scan to index_scan hash_scan

8 event_scheduler changed from 0 to 1

At this point, I believe you have a deeper understanding of "some problems and summaries often encountered in MYSQL8 installation". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.