The safest MySQL to date? Number 5.7 The new features that amaze and chicken (above) "reprint"

Turn from:

Dbaplus Community

http://www.toutiao.com/m5762164771/

The safest MySQL to date? Number 5.7 The new features of stunning and chicken (top)-today's headline (toutiao.com)
Http://toutiao.com/a6300616158581604609/?tt_from=mobile_qq&utm_campaign=client_share&app=news_article &utm_source=mobile_qq&iid=4592472790&utm_medium=toutiao_ios

Write in front

MySQL 5.7 version around October 2015 GA, has been six months, but as MySQL DBA I have not time to follow its features, it is ashamed to take the time to study the features of the 5.7 version and to make stress tests for some of the optimization functions.

This series is based on the 5.7.12 version of the new features of MySQL, but also recommended that you follow the official blog and documents (http://dev.mysql.com/doc/refman/5.7/en/), to be aware of its new changes.

The goal of MySQL 5.7 is to become the most secure MySQL server since release, which has some important changes in SSL/TLS and overall security development.

The 1, 5.7 version of the user table Mysql.user requires the plugin field to be non-empty, and the default value is Mysql_native_password, and Mysql_old_password is no longer supported.

2. Add Password expiration function

The DBA can set the password expiration time for any user, as detailed in the official Document password expiration policy (http://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html).

Maybe we all have. What happens if the business system's account suddenly expires on one day and the business is affected? Don't worry, you can set the password to never expire.

Default_password_lifetime=0

3. DBAs can further control their access db by locking/unlocking the user

Example:

4. SSL Features

MySQL version 5.7 provides a simpler SSL secure access configuration, and the default connection uses SSL encryption. Careful friends when looking at the official documentation, you will notice that the installation step is a step to install SSL, specifically about SSL, please refer to "Graphical SSL and Encryption" (http://www.linuxidc.com/Linux/2016-04/130034.htm).

The following recommended two articles for extended reading, about SSL features I believe most of the databases are not open, and the test cases in the Kang article show that the performance cost of weak turn on SSL is around 25%. When you try new features, evaluate them according to your business.

"MySQL SSL encryption connection and performance overhead"

"SSL/TLS in MySQL 5.7"

5. Use a more secure initialization method

The phasing out of the mysql_install_db is replaced with initialize (mysql_install_db <5.7.6<= mysqld-initialize), Initializing a database with the Initialize parameter has the following characteristics:

1. Create only one root account and generate a temporary password that is marked as expired

2. Do not create other accounts

3. Do not create test database

In particular, unlike-initialize, parameters are used when initializing.

A root account with no password will be generated. For more details, please visit the official documentation (http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html).

6. Changes in Sql_mode

The official document stated that the 5.7 version of the default sql_mode= "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 ". In fact, I have added the Strict_trans_tables property by default in my own test environment.

Strict_trans_tables means that the length of the field to be stored is greater than the size of the field definition, and instead of truncating the data for storage with a direct error rather than a 5.6 version, a warning is thrown. Note the same session adjustment Sql_mode must be exited and will not take effect until you enter Sql_mode. For more information about Sql_mode, please visit the Sql_mode Official document (http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html). Note 5.7 For the effect of the default value of ' 00000000 00:00:00 '.

7. Online DDL support rename index name

Personal feeling this function is relatively chicken, usually change the index name, the structure of its own index needs to add or delete fields, the new index name to modify the appropriate name.

8. Add the built-in full text plugin to support Chinese, Korean, and Japanese fulltext indexing

The previous version, can only rely on the word between the words to participle, for relying on the semantic word segmentation and not space participle of other languages, the introduction of 5.7 support parsing Chinese, Korean, Japanese full-text index--ngram full-text parser solved the problem. Specific please visit "Ngram full-text Parser" (http://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html).

9, dynamic modification varchar length size

The size of the varchar can be modified in the place using the ALTER TABLE statement without table-copy. However, there is a limit: the number of bytes in the varchar length cannot be changed (if 1 bytes are used to denote the length before the change, and 1 bytes must be used after the change), that is, only support 0~255 or more than 255 of the range change (increase). If the length of the field is increased from 254 to 256, you cannot use the In-place algorithm, you must use the copy algorithm, or you will get an error. It is important to note that the size of the varchar (N) length must be reduced to use the copy type, such as:

10, temporary table performance optimization

MySQL 5.7 has made great changes to the temp table to improve performance.

By optimizing the execution logic of the CREATE TABLE, DROP table, TRUNCATE table, and ALTER TABLE statements, the performance of the temporary table is improved (this is translated from the official website and no other information except alter is found to illustrate the detailed optimization process).

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

11, new temporary table space

Provides a separate tablespace for all non-compressed innodb temporary tables, and the default temporary tablespace file is IBTMP1, which is located in the data directory. We can specify the path and size of the temporary tablespace through the Innodb_temp_data_file_path parameter.

Each time MySQL restarts, the temporary tablespace is recreated.

Note: Starting with 5.7.5, add a system option internal_tmp_disk_storage_engine the engine type that defines the disk staging table is InnoDB, and until then, only MyISAM can be used. The new parameter default_tmp_storage_engine after 5.6.3 is the storage engine that controls the temporary table created by the Create temporary table, which is memory before the default, and don't confuse the two.

12. Optimize temporary tables

The new "Non-redo" Undo log is introduced from MySQL 5.7.2 for normal and compressed temporary and related objects, because the staging table does not need to be restored after the database crashes, it does not require redo logs, and avoids writing relog-related IO, which improves performance. You must indicate that the action temp table requires the Undo log for the MySQL runtime rollback, MVCC, and so on. See "Innodb-temporary-table-undo-logs" (https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-table-undo-logs.html).

13. Support for new data_geometry spatial type data

InnoDB now supports mysql-supported spatial data types. The previous spatial data was stored in binary BLOB data, and now the spatial data type is mapped to a INNODB internal data type Data_geometry.

14, upgrade Innochecksum

innochecksum--Offline InnoDB file Verification tool, new options or extended features, such as:

1. Supports the use of the specified checksum algorithm;

2. Support for overriding checksum values without validation;

3. The allowable checksum mismatch amount can be specified;

4. Display the number of pages, export page type information, output to log, read data from standard input, etc.

5. From 5.7.2 can support the verification of more than 2G files.

The detailed usage is referenced in the official Innochecksum document (http://dev.mysql.com/doc/refman/5.7/en/innochecksum.html).

15. Online DDL statement Rebuilding normal and partitioned tables

OPTIMIZE table, ALTER table ... Force, ALTER TABLE ... DDL operations such as ENGINE=INNODB use the inplace algorithm to reduce rebuild time and impact on applications.

16. Optimized for Fusion-io NVM file system

The Fusion-io non-volatile Memory (NVM) file system in a Linux system provides the ability to write atomically, making InnoDB doublewrite redundant. Therefore, after MySQL5.7.4, if the Fusion-io device supports atomic writing, the MySQL system will automatically turn off doublewrite, reduce IO, and improve performance.

More new features resolved to be continued ... Please look forward to ~

The safest MySQL to date? Number 5.7 The new features that amaze and chicken (above) "reprint"