Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the new features of MySQL 8.0.20". In daily operation, I believe many people have doubts about what are the new features of MySQL 8.0.20. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to help you answer the doubts about "what are the new features of MySQL 8.0.20"! Next, please follow the small series to learn together!

(1) Authorization is expressed in InnoDB.

There will be no more partial success, partial failure, either total success or total rollback.

Incompatible Change: The grant tables in the mysql system database are now InnoDB (transactional) tables.

Previously, these were MyISAM (nontransactional) tables.

This change applies to these tables: user, db, tables_priv, columns_priv, procs_priv, proxies_priv

Previously, an account-management statement that named multiple users could succeed for some users and fail for others.

Now, each statement is transactional and either succeeds for all named users or rolls back and has no effect if any error occurs.

The statement is written to the binary log if it succeeds, but not if it fails; in that case, rollback occurs and no changes are made

MySQL supports role roles

MySQL now supports roles

(3) Changes in parameters

expire-logs-days #deprecated, binlog_expire_logs_seconds replaced, default to 30 days

innodb_undo_tablespaces #is obsolete, innodb always creates 2 undo tablespaces, need to add, use CREATE UNDO TABLESPACE

internal_tmp_disk_storage_engine #has been deprecated, since 8.0.16 internal disk temporary tables default to InnoDB storage engine.

character-set-server=utf8 #The parameter utf8 is now an alias for utf8mb3, which will be changed to utf8mb4 later

innodb_read_only #originally only works for InnoDB tables, unable to create dropping, now works for all tables.

--log-tc-size #The hardcoded memory page size of 8KB for the memory-mapped

# transaction coordinator was too small for platforms such as

# ARM64 and PowerPC where the page size is much larger.

#Now defaults to 6 x memory pages.

validate_password_check_user_name #is now on by default

validate_password #is turned on by default and rejects passwords that are the same as the current username.

innodb_deadlock_detect #can disable deadlock detection, prevent multi-thread waiting for the same lock to cause slow processing, you can set innodb_lock_wait_timeout

innodb_checksum_algorithm #Replaced parameter innodb_checksums in 5.6.3

innodb_flush_method #now defaults to fsync for Unix and unbuffered for Windows

innodb_support_xa #removed, default support for two-phase commit

(4) mysql setting parameters newly introduced SET PERIST

SET PERSIST # SET introduces dynamic global parameter settings and persists parameters to mysqld-auto.cnf file under data directory, which will be loaded when server starts.

performance_schema.variables_info #can display the most recently modified parameters as well as the source.

(5) Changes in physical files

INFORMATION_SCHEMA.TABLES VERSION: .frm .trg .trn file will not exist, replaced by a data dictionary, version field is written dead to 10, this is the last one

The version number of MySQL 5.7 that supports.frm.

mysql_install_db has been removed

mysql_install_db: removed, database initialization must use mysqld --initialize or --initialize-insecure

(7) Introduction of ibd2sdi new command line

ibd2sdi: newly introduced command-line tool, used to extract serialized dictionary information (SDI) from persistent InnoDB tablespaces

Support Hint MERGE NO_MERGE

Optimizer now supports table-level hint:MERGE and NO_MERGE

SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

(9) Support invisible index

Invisible index is now supported, the optimizer does not choose to use the index, but will maintain the index normally, you can test the impact of removing the index without breaking the existing structure.

(10) performance_schema enhancement

performance_schema now collects server errors: events_errors_summary_global_by_error,

events_errors_summary_by_account_by_error,

events_errors_summary_by_host_by_error,

events_errors_summary_by_thread_by_error,

events_errors_summary_by_user_by_error

performance_schema_error_size: Controls the number of collection errors.

(11) InnoDB engine

--Zoning changes

General purpose partition processor removed, MySQL 8.0 InnoDB is the only storage engine that supports partition processing,

Remove partitions with ALTER TABLE... REMOVE PARTITIONING

Before upgrading, you need to use ALTER TABLE for non-InnoDB storage engine partition tables. ENGINE=INNODB Convert to InnoDB

--InnoDB file changes

InnoDB: InnoDB does not create.isl InnoDB dynamically linked files when creating tablespaces outside the Data directory,

So when the server is no longer supported, modify.isl to move the remote table space operation, and use redo log to locate the remote log file.

InnoDB: Compression of temporary tables is no longer supported. When row_format=compressed, innodb_strict_mode is true(default), create temporary table will report an error

(12) Newly introduced data dictionary table

INFORMATION_SCHEMA.INNODB_CACHED_INDEXES: Displays the number of pages of the index cache in memory.

(13) AUTO_INCREMENT ENHANCED

Now write AUTO_INCREMENT value to Redo log, write to storage engine system table, persist autoincrement max value, set AUTO_INCREMENT=N does not take effect,

Manual update AUTO_INCREMENT column also takes effect.

At this point, the study of "What are the new features of MySQL 8.0.20" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.