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

Editor to share with you what are the new features of MySQL 5.7.I hope you can get something after reading this article. Let's discuss it together.

We are proud to announce that MySQL 5.7 stable Edition is available for download. MySQL 5.7.9 is an exciting new version of the world's most popular open source database, three times faster than MySQL 5.6, while improving usability, manageability and security. Some important enhancements are as follows:

Performance and scalability: improve the scalability of InnoDB and the performance of temporary tables to achieve faster network and big data loading and other operations.

JSON support: with MySQL's JSON function, you can combine the flexibility of NoSQL with the power of relational databases.

Improve replication to improve the performance of availability. Includes multi-source replication, multi-thread enhancement, online GTIDs, and enhanced semi-synchronous replication.

Performance mode provides a better perspective. We have added many new monitoring features to reduce space and overload, and use the new SYS mode to significantly improve ease of use.

Security: we implement the "security first" requirement, and many of the new MySQL 5.7features help users secure their databases.

Optimization: we rewrote most of the parsers, optimizers, and cost models. This improves maintainability, scalability and performance.

GIS: MySQL 5.7new features, including InnoDB spatial indexing, using Boost.Geometry, while improving integrity and standards compliance.

New features

Security improvements.

The plugin column is added to the mysql.user table, and if the value of this field is blank, the account cannot be used. Be aware of this when upgrading from an earlier version of MySQL to MySQL5.7, and recommend that DBA replace the account that uses the mysql_old_password plug-in with the mysql_native_password plug-in.

The database administrator can make an automatic expiration policy for the account password, and the password must be changed forcefully after it expires.

(http://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html)

Database administrators can lock / unlock accounts for better login control, and correspondingly, a new account_locked column is added to the mysql.user table to indicate the locked status. Pay attention to this issue during the version upgrade process. (http://dev.mysql.com/doc/refman/5.7/en/account-locking.html)

MySQL Server can automatically create SSL, RSA certificates, and Key files to support secure connections, provided that Server is compiled with OpenSSL (http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html)

The default deployment strategy for MySQL has changed to become more secure. Mysql_install_db is deprecated, and the emysqld plus-initialize or-initialize-insecure option is used to initialize the data directory instead. When using-initialize, the default is to create a 'root'@'localhost' account and generate a random password, set the password to expire and save it in error log. You need to use the password and change the password for the first login. Anonymous accounts and test databases are no longer created. (http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html, http://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html)

SQL mode changes.

STRICT_TRANS_TABLES SQL mode is enabled by default.

The implementation of ONLY_FULL_GROUP_BY SQL mode is more elaborate and is turned on by default. ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE are enabled by default (may include strict SQL mode in the future and remove these separate modes), that is, the current default 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 and NO_ENGINE_SUBSTITUTION. (http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes)

Online ALTER TABLE.

RENAME INDEX clause is supported and in place operation does not require table-copy, which is suitable for each engine. (http://dev.mysql.com/doc/refman/5.7/en/alter-table.html)

Ngram and MeCab full-text parser plugins.

Built-in ngram parser plugin that supports full-text parsing in China, Japan and South Korea and installable MeCab plugin that supports full-text parsing in Japanese.

InnoDB enhancements.

The VARCHAR size can be in place modified by the ALTER TABLE statement without the need for table-copy. For example, ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN C1 C1 VARCHAR (255), but there are limitations, that is to say, if the INPLACE algorithm cannot be used when increasing from 254to 256, COPY algorithm must be used, otherwise the error will be reported on the side. This is because VARCHAR values in 0,255 require an extra byte to encode, while VARCHAR values above 256 require two bytes to encode. In addition, it is not supported to use the INPLACE algorithm to reduce the ALTER TABLE of VARCHAR, so the COPY algorithm must be used.

Improved DDL performance for InnoDB temporary tables.

InnoDB temporary table metadata is no longer stored in InnoDB system tables but in INNODB_TEMP_TABLE_INFO, which contains temporary table information created by all users and systems. The table is created the first time you run select on it.

InnoDB now supports MySQL-supported spatial data types. That is, the previous spatial data was stored in binary BLOB data, but now the spatial data type is mapped to an InnoDB internal data type DATA_GEOMETRY.

There is a separate table space for the non-compressed InnoDB temporary table, which is rebuilt in the default DATADIR each time the server is restarted, and a different path can be specified through the innodb_temp_data_file_path option.

Innochecksum (offline InnoDB file verification tool), add new options or extended functions, such as, you can specify a specific check algorithm, you can only rewrite the check value without verification, you can specify the allowed checksum mismatch, display the number of pages of all types, export page type information, output to the log, read data from the standard input, etc. Currently, more than 2G files can be supported. (http://dev.mysql.com/doc/refman/5.7/en/innochecksum.html)

Introduce a new "non-redo" undo log for temporary tables and related objects and store them in temporary tablespaces. This type of undo log is non-redolog because the temporary table does not need crash recovery and therefore does not need redo logs, but it requires undo log for rollback, MVCC, and so on. The default temporary tablespace file is ibtmp1, which is located in the data directory and is recreated each time the server starts. Temporary tablespaces can be specified through innodb_temp_data_file_path. (http://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-table-undo-logs.html)

The percentage of recently used page reads and dump in buffer pool can be adjusted through innodb_buffer_pool_dump_pct. When there are other InnoDB background tasks, the frequency of buffer pool load operations can be limited by innodb_io_capacity.

InnoDB supports the full-text parser plug-in (http://dev.mysql.com/doc/refman/5.7/en/full-text-plugins.html)

Support multiple page cleaner threads to brush dirty pages from buffer pool, and configure the number of threads through innodb_page_cleaners. The default value is 1.

Support to use online DDL statements of INPLACE algorithm to reconstruct normal and partitioned tables: OPTIMIZE TABLE, ALTER TABLE... FORCE, ALTER TABLE... ENGINE=INNODB .

The Fusion-io Non-Volatile Memory (NVM) file system in the Linux system provides atomic write capability, which makes InnoDB double writes redundant. Therefore, after MySQL5.7.4, the system tablespace InnoDB doublewrite buffer on Fusion-io devices that support atomic writes is automatically closed.

For partitioned tables and independent InnoDB table partitions, "transportable" tablespaces are supported from MySQL5.7.4, which makes it easier to back up partitioned tables and makes it possible to copy partitioned tables and independent table scores between different MySQl instances. (http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html)

You can dynamically adjust the buffer pool size through the innodb_buffer_pool_size parameter. The resize is in chunk units, and the chunk size is configured through innodb_buffer_pool_chunk_size. In addition, you can observe the adjustment process through the Innodb_buffer_pool_resize_status state variable. (http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-online-resize.html)

MySQL5.7.5 start Multi-threaded page clean is also supported in the shutdown and recover phases.

Indexing on spatial data types is supported after MySQL5.7.5. And through ALTER TABLE... ADD SPATIAL INDEX ALGORITHM=INPLACE operates online.

Bulk load is supported when creating and rebuilding indexes, and this method is called "sorted index build", which improves the efficiency of index creation and supports full-text indexes but not spatial indexes. The fill factor for each page can be defined through innodb_fill_factor, and the remaining space is used for future index growth. (http://dev.mysql.com/doc/refman/5.7/en/sorted-index-builds.html)

Use the new logging type (MLOG_FILE_NAME) to identify tablespaces that have changed since the last checkpoint. This simplifies tablespace discovery during crash recovery and eliminates file system scanning prior to redo log application. (http://dev.mysql.com/doc/refman/5.7/en/innodb-recovery-tablespace-discovery.html). It is important to note that this behavior results in a change in the redo log format, so when upgrading to MySQL5.7.5 or downgrading from a modified version, you need to turn off MySQL completely.

You can configure innodb_undo_log_truncate to truncate undo the undo logs in the tablespace. Undo logs in the system tablespace will not be truncate. (by default, undo log is stored in the system tablespace and can be adjusted through innodb_undo_directory, innodb_undo_logs, and innodb_undo_tablespaces. Http://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html)

Support for localized partitions (native partitioning), which used to rely on ha_partition handler to create handler objects for each partition, now requires only one partition-aware handler object, saving memory. (through mysql_upgrade or ALTER TABLE... UPGRADE PARTITIONING upgrades the partition table built by the old method to a new one.

Supports the creation of common tablespaces using CREATE TABLESPACE statements. And through CREATE TABLE tbl_name... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name adds tables to the created universal tablespace. (http://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html)

The default row format of the InnoDB table can be specified through innodb_default_row_format, and the default value for the new version is replaced by DYNAMIC with COMPACT.

JSON support.

MySQL5.7.8 began to natively support JSON data types, which are no longer stored as strings but in binary format, allowing document elements to be read quickly. The JSON column is automatically validated when it is inserted or updated, and an error is reported if the document is not formatted correctly. In addition to the common comparison operators available for comparison, a series of functions are introduced to handle JSON types. (http://dev.mysql.com/doc/refman/5.7/en/json.html)

System and status variables. (http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html)

Priority is given to obtaining system and state variable information from tables under performance_schema (older versions are obtained from tables under information_schema, which will be removed in the future), which also affects show variables and show status statements, that is, the source of information comes from performance_schema. If you want to use the original table to get information, you can turn on the show_compatibility_56 parameter (not recommended, only as an auxiliary tool in the upgrade process, which will be removed in the future). (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html)

Sys schema.

Contains sys libraries that store related objects collected from performance_schema to assist DBA and developers, such as tables and triggers, views, stored procedures, stored functions, etc., for tuning and problem diagnosis. (http://dev.mysql.com/doc/refman/5.7/en/sys-schema.html)

Condition handling.

Optimizer.

Explain can be used to obtain the execution plan of running statements in other SESSION/CONNECTION (if the statement is running and the statement type supports EXPLAIN). The syntax is: EXPLAIN [options] FOR CONNECTION connection_id;, where connection_id is connected to id, which can be obtained by information_schema.processlist or CONNECTION_ID ().

Support for providing optimizer prompts in statements (previously in the optimizer_switch system variable) to provide more control over the execution plan of the statement. Similarly, you can use optimization hints in statements when using explain/desc to view execution plans so that you can see how optimization prompts affect the execution plan. (http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html)

Triggers.

In the previous version, there could only be one trigger per table for a combination of trigger event (insert\ update\ delete) and trigger opportunity (before\ after), but this restriction was removed in the new version. For example, in the version before the value, CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @ sum = @ sum + NEW.amount; cannot create another BEFORE INSERT ON account-based trigger after creating the following trigger. The new version can, as long as the trigger has a different name.

Logging.

In previous versions, MySQL on Unix or Unix-like systems supported sending error logs to syslog, specifically by capturing error output through mysqld_safe and passing it to syslog. The new version natively supports the output of error logs to syslog and is suitable for windows systems, which only needs to be configured through simple parameters (log_syslog, etc.). (http://dev.mysql.com/doc/refman/5.7/en/error-log.html)

Mysql supports the-syslog option to output interactive statements to the system's syslog (usually / var/log/message in Unix or Unix-like systems). Statements that match "ignore" filtering rules (which can be set through the-histignore option or the MYSQL_HISTIGNORE environment variable) are not recorded. For more information about the log usage of the mysql client, see: http://dev.mysql.com/doc/refman/5.7/en/mysql-logging.html

Generated Columns.

CREATE TABLE and ALTER TABLE statements support Generated Columns. That is, the value of the column is calculated from other columns, the expression for calculating the column value is given in the column definition, and the column value is calculated and stored when a row is inserted or updated. (http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns)

Mysql client.

Previously, when the mysql client was running, Control+C terminated the statement if there was a statement running, and exited the mysql client if no statement was running. The mysql client is not exited in the new version.

Database name rewriting with mysqlbinlog.

The mysqlbinlog tool adds a new option-rewrite-db, which allows database name rewriting when reading events from binary logs in line copy format-rewrite-db='dboldname- > dbnewname'. You can specify multiple rewriting rules by specifying this parameter multiple times.

HANDLER with partitioned tables.

You can use HANDLER (http://dev.mysql.com/doc/refman/5.6/en/handler.html)) on partitioned tables

Index condition pushdown support for partitioned tables.

Queries on partitioned tables using InnoDB or MyISAM support the use of ICP (http://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html)

WITHOUT VALIDATION support for ALTER TABLE... EXCHANGE PARTITION.

ALTER TABLE... The EXCHANGE PARTITION statement contains the {WITH | WITHOUT} VALIDATION clause, which defaults to WITH VALIDATION, and verifies line by line whether the exchanged values meet the partition boundary definition. If WITHOUT VALIDATION is specified, no validation is performed. (http://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html)

Master dump thread improvements.

Master dump thread has refactored to reduce lock contention and improve master throughput. In previous versions, locks were acquired only when dump thread was read for one time; in MySQL5.7.2 and subsequent versions, locks were acquired only when they were read from the last bit of an event that was successfully written. This means that multiple dump threads can read binary logs concurrently and can be read when clients write to binary logs.

Globalization improvements.

Start to include the gb18030 character set and support the China National Standard GB18030 character set.

Changing the replication master without STOP SLAVE.

The new version of MySQL master-slave replication does not have to execute STOP SLAVE before executing the CHANGE MASTER TO statement. At this point, the behavior of the CHANGE MASTER statement depends on the slave SQL thread and the IO thread; the start / stop of the two threads determines the options that can / cannot be used in the CHANGE MASTER TO statement at some point. The specific rules are as follows:

If the SQL thread stops, you can use RELAY_LOG_FILE in the CHANGE MASTER TO statement

RELAY_LOG_POS and MASTER_DELAY

Options, even if the IO thread is running. If the IO thread is still running at this time, you cannot run options other than the above options.

If the IO thread stops, you can use it in the CHANGE MASTER TO statement in addition to RELAY_LOG_FILE

Any combination of options other than RELAY_LOG_POS and MASTER_DELAY options, even if the SQL thread is running.

Running CHANGE MASTER TO... The SQL thread and IO thread must be stopped before MASTER_AUTO_POSITION=1.

You can check the running status of SQL and IO threads through the SHOW SLAVE STATUS command.

In previous versions, if statement-based replication was used and there were temporary tables, then executing CHANGE MASTER TO after STOP SLVAVE might leave temporary tables on the remote slave. A warning will be given in the new version if the Slave_open_temp_tables is still 0. 0 when executing CHANGE MASTER TO.

Test suite.

The test suite now uses InnoDB as the default storage engine

Multi-source replication is now possible.

Starting to support multi-source replication, that is, replicating from multiple master to a salve. Used to back up multiple server to a single server, merge table shard, merge data from multiple server to a single server, etc. (currently, no conflict detection and resolution is provided, which is handled by the application layer. Http://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html).

Another technology introduced with multi-source replication is the replication channel (replication channels), where Replication channels enables slave to open multiple connections, each channel connected to a different master for replication. (http://dev.mysql.com/doc/refman/5.7/en/replication-channels.html)

Group Replication Performance Schema tables.

A new batch of tables in performance_schema provides replication group related information (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html)

Group Replication SQL.

Introduce the following two group replication control statements: (http://dev.mysql.com/doc/refman/5.7/en/replication-group-sql.html)

START GROUP_REPLICATION

STOP GROUP_REPLICATION

Abandonment characteristic

The following features are not recommended in MySQL5.7 and may be removed in a future release:

Several sql mode of ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE are not approved (but currently enabled by default). The long-term plan is to include this mode in the strict mode and explicitly remove these individual mode.

With regard to account management statements, there are some features that do not approve of re-use:

It is not recommended to use the Grant statement to create users, but it is recommended to use the CREATE USER statement to create users. In this way, the sql mode of no _ AUTO_CREATE_USER makes little sense to the GRANT statement, so it will also be degraded.

It is not recommended to use the Grant statement to modify account properties, but only for account empowerment. Account attributes are assigned or changed when created or modified by CREATE USER or ALTER USER

IDENTIFIED BY PASSWORD 'hash_string' syntax is not recommended. IDENTIFIED WITH auth_plugin AS' hash_string' is recommended.

SET PASSWORD statement and PASSWORD () function are not recommended, but ALTER USER is recommended to change the account password.

The old_password system variable is not recommended.

The use of GROUP BY implicit sorting is not recommended, and the explicit use of ORDER BY clauses is recommended. (GROUP BY sorting is only an extended syntax for MySQL and may be removed in a future version)

It is not recommended to use the EXTENDED and PARTITIONS keywords in EXPLAIN statements (still recognized but no longer necessary in the new version. )

-skip-innodb and-innodb=OFF,-disable-innodb, etc., do not approve of use, because InnoDB cannot be banned in the new version.

The log_warnings system variable and the-log_warnings option are not recommended, but log_error_verbosity is recommended.

Binlog_max_flush_queue_time has expired in the new version.

Innodb_support_xa is not valid in the new version because two-phase commit of XA transactions is supported by default in MySQL5.7.

Metadata_locks_cache_size and the metadata_locks_hash_instances, sync_frm, character_set_database, collation_database system variables no longer work.

ENCRYPT (), ENCODE (), DECODE (), DES_ENCRYPT () and DES_DECRYPT () are not recommended, but AES_ENCRYPT () and AES_DECRYPT () are recommended.

Use MBREquals () instead of MBREqual ().

Please use Performance Schema instead of INFORMATION_SCHEMA.PROFILING.

Replace the syslog output supported by mysqld_safe with native syslog.

The-fix-db-names and-fix-table-names options of the mysqlcheck tool are not recommended, and the UPGRADE DATA DIRECTORY NAME clause of the ALTER DATABASE statement is not recommended.

Remove Properti

Support for the hash format of the pre-4.1 version of the password is removed, the associated old_passwords system variable, the OLD_PASSWORD () function is removed, the mysql_old_password authentication plug-in is removed, the-secure-auth option is invalid and will be removed in subsequent versions, and the value of the secure_auth variable is allowed to be removed.

YEAR (2) is no longer supported, please use YEAR (4).

Innodb_mirrored_log_groups has been removed.

Use default_storage_engine instead of storage_engine.

Thread_concurrency, timed_mutexes system variables.

The IGNORE clause of ALTER TABLE.

The DELAYED in INSERT DELAYED and REPLACE DELAYED is ignored, the-delayed-insert option in the associated mysqldump is removed, the related column in performance_schema.table_lock_waits_summary_by_table is removed, and mysqlbinlog is no longer annotated for INSERT DELAYED.

The Database symlinking that uses .sys files in the Windows system is removed and mylink that supports native symlink is used. (http://dev.mysql.com/doc/refman/5.7/en/windows-symbolic-links.html)

The-basedir,-datadir, and-tmpdir options in mysql_upgrade.

Option prefixes are no longer supported, only the full name of the option is supported, such as-key-buffer-size instead of-key-buffer,-skip-grant-tables and-skip-grant, etc.

The SHOW ENGINE INNODB MUTEX output is removed and the relevant information can be obtained by creating a view on the Performance Schema table.

InnoDB tablespace monitoring and InnoDB table monitoring are removed. Table monitoring information can be obtained through the INFORMATION_ schema table.

Specially named tables for enabling and disabling InnoDB Monitor and InnoDB Lock Monitor are removed and replaced by two dynamic system variables, innodb_status_output and innodb_status_output_locks. (http://dev.mysql.com/doc/refman/5.7/en/innodb-monitors.html)

The innodb_use_sys_malloc and innodb_additional_mem_pool_size system variables are removed.

Msql2mysql, mysql_convert_table_format, mysql_find_rows, mysql_fix_extensions, mysql_setpermission, mysql_waitpid, mysql_zap, mysqlaccess and mysqlbug are removed.

Mysqlhotcopy has been removed, using mysqldump and MySQL Enterprise Backup or some open source tools.

The binary-configure.sh script was removed.

The INNODB_PAGE_ATOMIC_REF_COUNT CMake option was removed

The innodb_create_intrinsic, innodb_optimize_point_storage, innodb_log_checksum_algorithm options have been removed.

After reading this article, I believe you have a certain understanding of "what are the new features of MySQL 5.7". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.