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

This article focuses on "what are the new features of MySQL 5.7". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the new features of MySQL 5.7"?

1. Background

MySQL 5.7 released GA version 5.7.9 in 2015-10-21, and the current minor version has reached 5.7.12. 5.7A lot of new feature and optimizations have been added, so let's try something new in the next series. First of all, this time is mainly to preview the changes in feature and compatibility issues. The following series will learn about important feature deployments.

2 Security-related features 2.1 authentication plug-in

The plugin in the mysql.user table is changed to not null,5.7 and the authentication plug-in of mysql_old_password is no longer supported. It is recommended that all mysql_native_password be used. When upgrading from an earlier version to 5.7, there are two compatibility issues that need to be addressed.

[compatibility]

You need to migrate the users of mysql_old_password first, and then upgrade the user table structure:

1. Migrate mysql_old_password users

In versions before MySQL 5.7.2, the type of authentication plug-in used is judged by password's hash value. In versions after 5.7.2, the plugin field is not null, which is judged directly by plugin. The new password is saved from the password field to the new field authentication_string, and the password field is discarded.

If user is an implicit mysql_native_password. Make changes directly using sql:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin =' AND (Password =''OR LENGTH (Password) = 41); FLUSH PRIVILEGES

If the user is an implicit or displayed mysql_old_password, first query it through the following sql:

SELECT User, Host, Password FROM mysql.user WHERE (plugin =''AND LENGTH (Password) = 16) OR plugin =' mysql_old_password'

If there is a record, it means that there is also a user using mysql_old_password, and the following sql is used for user migration:

ALTER USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY' DBA-chosen-password'

2. Upgrade the user table structure

Upgrade directly through mysql_upgrade. The steps are as follows:

Stop MySQL 5.6 instance

Replace the 5.7binary version of mysqld

Start the instance using 5.7

Run mysql_upgrade upgrade system table

Restart MySQL 5.7instance

2.2 password expires

Users can use statements such as ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE; to expire their passwords.

And a new default_password_lifetime is added to indicate the automatic expiration time of the user's password. Since 5.7.10, the default value has been changed from 0 to 360, that is, the default expires in one year.

Expiration can be prohibited in two ways:

1. SET GLOBAL default_password_lifetime = 0; 2. ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER

[compatibility]

You only need to upgrade the mysql.user system table through mysql_upgrade to use the new password expiration feature.

2.3 account lock

A user can lock an account with the following syntax to prevent the user from logging in:

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK; ALTER USER' jeffrey'@'localhost' ACCOUNT UNLOCK

[compatibility]

You only need to upgrade the mysql.user system table through mysql_upgrade to use the new password expiration feature.

2.4 SSL connection

If mysqld compilation uses openssl, SSL, RSA certificate, and key files are created by default at startup.

However, whether it is openssl or yassl, if the parameters related to ssl are not set, mysqld will look for the ssl authentication file in data directory to open the ssl feature as much as possible.

[compatibility]

There is no compatibility problem

2.5 install the database

At the beginning, users are advised to use mysqld-initialize to initialize the database, instead of the previous mysql_install_db method, the new method only creates a root@localhost user, the random password is stored in the ~ / .mysql_secret file, and the account is expired, the first time to use must reset password, and no longer create test db.

[compatibility]

There is no compatibility problem

3 sql mode Chan

5.7 the default value of sql_mode is changed to:

Mode_no_engine_substitution | mode_only_full_group_by | mode_strict_trans_tables | mode_no_zero_in_date | mode_no_zero_date | mode_error_for_division_by_zero | mode_no_auto_create_user

Prior to 5. 7, the default value for sql_mode was only mode_no_engine_substitution.

So by default, for example, a user whose grant does not exist will report an error:

ERROR 1133 (42000): Can't find any matching row in the user table

You must use create user before using grant user.

[compatibility]

A change to the default sql mode can cause sql to behave inconsistently.

4. Online alter table

Supports online rename index operations, in_place and does not require table copy.

[compatibility]

There is no compatibility problem

5. InnoDB enhancement 5.1 varchar length change supports inplace

Changing the length of a field of type varchar supports the inplace method, but there is a restriction that the number of bytes used to represent the length of the varchar field cannot be changed, that is, the number of bytes used to represent the length of the varchar field cannot be changed, for example, the length of the varchar should be changed from less than 255 to more than 255. because from less than 255 to greater than 255, the number of bytes of size needs to be increased from 1 to 2.

Note: table copy is still required to reduce the length of varchar.

5.2 optimize InnoDB temporary table

Because the data of the InnoDB temporary table is no longer protected by redo, while redo only protects the metadata of the temporary table, the performance of the temporary table is greatly improved.

And the metadata of the InnoDB temporary table is saved in a new system table, namely innodb_temp_table_info.

Temporary tables create a unified tablespace, which we call temporary tablespaces, whose directory address can be set by the parameter innodb_temp_data_file_path. When the system is started, the tablespace will be created, and the restart will delete the reconstruction.

For example:

Mysql > show global variables like'% temp_data_file_path%' +-+ | Variable_name | Value | +-- +- -+ | innodb_temp_data_file_path | ibtmp1:12M:autoextend | +-+-- +

And the storage engine is changed to InnoDB by default:

Mysql > show global variables like'% storage_engine%' +-- +-+ | Variable_name | Value | +-- +-+ | default_storage_engine | InnoDB | | Default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | internal_tmp_disk_storage_engine | InnoDB | +-+-+

Note: when gtid is enabled, the temporary table of create or drop is still the same as 5.6 in the case of non-auto commit or context showing begin:

ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.

In addition, insert into t select * from t also encounters errors and cannot reference temporary tables twice in the same sql statement.

Note: because the InnoDB temporary table has been changed a lot, we will give a detailed introduction.

5.3 InnoDB Native support for DATA_GEOMETRY types

And support the establishment of index on spatial data types to speed up the query.

5.4 buffer pool dump

Buffer pool dump and load support a new parameter innodb_buffer_pool_dump_pct, the proportion of dump, and use innodb_io_capacity to control the IO throughput during load.

5.5Multithreaded flush dirty

Starting from 5.7.4, the innodb_page_cleaners parameter can be set to support multithreaded flush dirty page to speed up the refresh of dirty blocks.

5.6 NVM file system

MySQL has been using double write buffer to solve a partial write problem for page writes, but Fusion-io Non-Volatile Memory (NVM) file system on linux systems supports atomic writes.

In this way, the use of double write buffer can be omitted. After 5.7.4, if Fusion-io devices supports atomic write, then MySQL automatically writes dirty block directly to the data file. This reduces one memory copy and IO operation.

5.7 InnoDB Partition Table

Prior to MySQL 5.7, InnoDB does not support partitioned tables. Partitioned tables are supported on the ha_partition engine. Since 5.7. InnoDB supports native partitioned tables and can use transport tablespaces.

[compatibility]

Mysql_upgrade scans the InnoDB tables supported by the ha_partition engine and upgrades them to InnoDB partition tables. After 5.7.9, you can use the command ALTER TABLE. UPGRADE PARTITIONING. To upgrade. If previous versions made heavy use of partitioned tables, be aware that using mysql_upgrade can take a very long time to upgrade partitioned tables.

5.8dynamically adjust buffer pool size

After MySQL 5.7.5, the buffer pool size can be adjusted dynamically by online, which can be adjusted by setting the dynamic parameter innodb_buffer_pool_size, and the progress of resize can be checked according to the status of Innodb_buffer_pool_resize_status, because the process of resize is to copy pages from one memory area to another according to the size of chunk.

5.9 accelerate recovery

Before MySQL 5.7.5, in the process of recovery, all ibd files need to be scanned for meta-information. After 5.7.5, a new redo log type, MLOG_FILE_NAME, has been added to record files that have changed since the last checkpoint, so that in the process of recovery, only these files need to be opened.

[compatibility]

Due to the addition of the new log record type, it is necessary to safely shut down the instances before 5.7and clean up the redo.

5.10 Tablespace management

Support the creation of tablespaces, such as

CREATE TABLESPACE `tablespace_ name` ADD DATAFILE 'file_name.ibd' [FILE_BLOCK_SIZE = n]

And when you create a table, you can specify which tablespace it belongs to

[compatibility]

Because you can specify any space directory, you should pay attention to the upgrade process, do not miss the directory.

5.11 InnoDB Tablespace Encryption

Support InnoDB data file encryption, which relies on keyring plugin to manage the secret key. Later, we introduce the method of InnoDB encryption separately, and RDS also implements a transparent encryption method of InnoDB data file, and manages the secret key through the KMS system. For example:

Create table t (id int) encryption='y'; here, I believe you have a deeper understanding of "what are the new features of MySQL 5.7". 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.