Mysql5.7 new Features

Mysql在被sun，继而被oracle收购之后，并没有被oracle这个巨人雪藏，反而近几年的发展速度极为之快，谁都知道，你oracle做不好mysql，开源的市场就会被别人取代，pg，MariaDB，percona server，如果说开源又不挣钱，我就只能呵呵了。这里我选的版本是最新的开发版，5.7.8.只为了看一下最新最近的技术，当然，这个版本是不可能被用到生产中去的，因为还不够成熟，还需要在实验室呆上很久，如果说mysql5.6已经做的够好了，那么mysql5.7一定会让你眼前一亮。Mysql在被sun，继而被oracle收购之后，并没有被oracle这个巨人雪藏，反而近几年的发展速度极为之快，谁都知道，你oracle做不好mysql，开源的市场就会被别人取代，pg，MariaDB，percona server如果说开源又不挣钱，我就只能呵呵了。（本来这个文档做了个word，粘了很多图片进去，csdn的图片加载不太友好，就不贴图片了。）

Before listing the new features, you'll still have to write out the mysql5.7 version that differs from 5.6 on the installation.

Generally like the binary package to install, simple and convenient. It's easy to understand.

The real installation process is also twists.

Feature One:

Security enhancements, specific enhancements, have the following points:
1,5.6 after installation, Root does not have a password, and now randomly generates a temporary password. Or –skip-grant-table at startup, change the password.

2, the password can be set to automatically expire the time,

5.6 User Table Structure

5.7 User Table structure

5.7 Added the Password_lifetime field to set the time for the password to expire automatically.
This parameter can be set in the configuration file, or it can be changed dynamically.
[Mysqld]
DEFAULT_PASSWORD_LIFETIME=180 (180 expiration)

[Mysqld]
Default_password_lifetime=0 (never expire)
SET GLOBAL default_password_lifetime = 180; Dynamic modification

ALTER user ' jeffrey ' @ ' localhost ' PASSWORD EXPIRE INTERVAL; (set a user to expire for 90 days)

ALTER user ' jeffrey ' @ ' localhost ' PASSWORD EXPIRE never; (user password not invalidated)

ALTER user ' jeffrey ' @ ' localhost ' PASSWORD EXPIRE default; (Enable this user to set by global)

3, user account lockout:
When you create a user, you can set the user as a lock state, such as:

，

Use the following method to unlock:

Log in again, OK

Here is a note: Locking An account does not affect being able to connect using a proxy user that assumes the identity
of the locked account. It also does not affect the ability to execute stored programs
A definer clause naming the locked account. That's, the ability to use a proxied account or stored
Programs or views isn't affected by locking.

4, to make it easier to support secure connections, MySQL servers compiled using OpenSSL
can automatically generate missing SSL and RSA certificate and key files at startup.

The 5,mysql installation uses (should be initialized) the Mysqld–initialize command,
Some changes to the installation are as follows:
There is only one root account, and it has a password.
No more anonymous users are created at initialization, that is, previous empty users
The test library has been removed by default

Two
The default is the strict mode, that is, for example, you inserted a longer than the length of the data in 5.6, will be reported warning, now will report an error.

Three online change index name; In the previous 5.6 version is not done.

Four
For this feature, I don't know much about it.

The following are more concerned with the InnoDB enhancements.

1, you can change the field length of the varchar type by using the ALTER TABLE feature.
The way to increase is:

The algorithm=inplace here, can only increase the length of the field, can not be reduced, the increase can not span the value of 256, but the two sides may be increased.

2, the performance of creating InnoDB temporary tables using DML has been improved, and this has improved to what extent, I do not know.
DDL performance for InnoDB temporary tables is improved through optimization of CREATE
Table, DROP table, TRUNCATE table, and ALTER table statements.

3, this means that the temporary table is not stored in the innnodb of the shared table space. What are the benefits?
，
4,innodb adds a spatial data type data_geometry, but I don't know what the eggs are.
5, a separate tablespace is added to the temp table with the parameter Innodb_temp_data_file_path

The enhancement of the 6,checksum function
Can check the size of the file, before 5.7.2 only support to 2GB, now can be greater than 2GB

7,5.7.2 first introduced a new log, called Non-redo Undo Log, which, like a temporary table, is stored in the LBTMP1, which is located in the data directory.
8, the newly added variable, the relevant parameters about Innodb_buffer_pool increase are as follows:

First increased by two, innodb_buffer_pool_dump_pct,innodb_buffer_pool_chunk_size
Second, the default values are different, mysql5.7 starts by default when the database service starts, stops, warms up with dump data.
Innodb_buffer_pool_dump_pct is the percentage that sets dump, which is the hottest data
Innodb_buffer_pool_chunk_size
Additional variables are added:

Innodb_page_cleaners, defines the number of dirty pages clean threads

9, this
It seems to say that the addition of an online DDL operation is focused on avoiding copytable operations and providing table availability.

The 10,double write technique fails on a disk that supports atomic writes. Fusion-io non-volatile Memory (NVM) from 5.7.4

11,

This feature means that before 5.7.4, you can only copy a non-partitioned table to another server, and then the partitioned table is supported. Why not in the past why not, now why can be unclear.

12
Below this feature is not very cool, haha.

13,
The number of triggers is not limited, and previously, as a trigger for after insert, only one can be created for a table and is not currently restricted.
14,
CTRL + C does not quit MySQL client
15,
Copy Library name redirection, that's what I'm calling it.

16,
Changes master to,

In the main case, MySQL can not do the operation of the Stop slave, the change of master to.
For more information, please refer to Mysql5.7refman

17,
The default engine of the test suite becomes INNODB
18,
MySQL starts to support multi-master replication.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Mysql5.7 new Features