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

What is the parameter UP DOWN of MYSQL 8? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Mysql8 is a version that will be available sooner or later, and developers will be delighted in the morning, because many unsupported features are supported, especially the improvements in MGR and plug-ins for physical replication. It also makes sense at night, and the speed of the query is expected to make those who continue to use MYSQL.5.7 feel gratified.

The installation of mysql 8 is different from that of mysql,5.7 in the configuration of parameters, so it is better to figure out which parameters first. It is better to configure a better mysql 8. At present, the latest version of mysql is 8.019, the current slightly stable version is 8.015, and the creative function is 8.017.

The following versions are based on the configuration of percona mysql 8.017 vs. 5.7

1 SQL MODE mainly affects the SQL syntax supported by MySQL and the data validation checks it performs. SQL MODE is an important setting when MYSQL is initialized, while MYSQL 5.7and 8.0are different in some configurations.

Here is a SQL MODE that matches a version of mysql 8.011 or above

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION

The following is the configuration above mysql 5.78

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

We can see the difference between the two versions, only in the option no_auto_create_user, which means that unless authentication information is specified, the GRANT statement will be prevented from automatically creating a new user account. The statement must specify a non-empty password for authentication plug-ins that use identify BY or identify WITH. Since MYSQL 8 can no longer set up an account through grant, it must be done through create user, so this configuration is removed by MYSQL 8.

If you do not change the configuration that still uses MYSQL5.7, it will cause the startup server to report an error

The configuration of 2 innodb_file_format has also been removed in mysql 8. If you still have this configuration, MYSQL 8 will not be able to start and report an error. The main reason is that mysql 8 no longer supports Antelope format, only Barracuda format, so the existence of this option is not necessary.

The configuration of 3 innodb_undo_logs in 5. 7 UNDO LOGS has been replaced by innodb_rollback_segments in 8. 0. Here is a formula to calculate how many concurrent transactions your current setting can support. If you exceed this number, an error will occur and the transaction will not run.

(innodb_page_size / 16 / 2) * innodb_rollback_segments *

Number of undo tablespaces

4 query_cache_type this is also a chicken rib feature that is also removed in MYSQL 8.0 and rarely used in MYSQL 5.X.

MYSQL account security plug-ins, all invalid, need to install security plug-in products in accordance with 8.0. Therefore, the relevant configuration does not exist, and an error will be reported if it is configured.

6 expire_logs_days, this configuration has also been cancelled

The configuration of 7 innodb_undo_tablespaces has also been cancelled

8 master-info-file, relay_log_info_file, etc., are also cancelled, and related information is forced to be saved in the data table.

After cleaning up these configurations, other participants have been added to replace them.

1 Caching_sha2_password_rsa_public_key password plug-in configuration used by the new user security plug-in

2 Innodb_undo_tablespaces_active number of surviving undo tablespace

3 binlog_expire_logs_seconds to set the expiration cleanup time of BINLOG (in seconds)

4 innodb_deadlock_detect detect deadlock

5innodb_dedicated_server automatically adjusts INNODB BUFFER memory log file size, data refresh method.

6 innodb_temp_tablespaces_dir begins to adjust the storage space of the temporary table

Note: when innodb_dedicated_server is set to ON, if you do not clean up the previous information related to innodb_dedicated_server, MYSQL will set it according to your initial setting, and innodb_dedicated_server is equivalent to invalid.

Among them, the change of UNDO LOG is relatively great.

1 you can create UNDO LOG dynamically

2 No longer, only 128SEGMENT can be created.

3 more than a certain number of UNDO LOG SIZE will be controlled by innodb_undo_truncate detection through innodb_max_undo_log_size, beyond this setting will start automatic online contraction.

In addition, for the problem of user password authentication, I have written a MYSQL article on username password authentication, so I won't repeat it here.

INSTALL COMPONENT 'file://component_validate_password';

Finally, I will review some of the limitations of MYSQL 8.

1 A table has at most 1017 columns

2 A table has at most 64 secondary indexes

The maximum index of 3 rows can hold 3072 or 767 bytes bytes

4. The table index of 4 uft8mb4 can hold a maximum of 191characters, which exceeds that it cannot be indexed, including prefix indexes.

5 if you reduce innodb_page_size to 8KB to 4KB, the restrictions on some of the above contents will be halved or reduced by 75%.

6 set up a secondary index, and the maximum operation of a single index is listed as 16 columns

The answer to any question about the parameter UP DOWN of MYSQL 8 is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.