Mysql5.7 new feature _ MySQL

After being acquired by sun and oracle, Mysql has not been acquired by the oracle giant. On the contrary, it has been developing very fast in recent years. everyone knows that oracle is not doing well with mysql, the open-source market will be replaced by others, pg, MariaDB, and perconaserver.

After being acquired by sun and oracle, Mysql has not been acquired by the oracle giant. On the contrary, it has been developing very fast in recent years. everyone knows that oracle is not doing well with mysql, the open-source market will be replaced by others, pg, MariaDB, and percona server. if the open-source market does not make any money, I will have to wait. The selected version is the latest development version, 5.7.8. just to check the latest technology, of course, this version cannot be used in production, because it is not mature enough to stay in the lab for a long time, if mysql5.6 has already done enough, mysql5.7 will surely shine on your eyes. After being acquired by sun and oracle, Mysql has not been acquired by the oracle giant. On the contrary, it has been developing very fast in recent years. everyone knows that oracle is not doing well with mysql, the open-source market will be replaced by others, pg, MariaDB, and percona server. if open-source servers do not make any money, I will have to wait. (In this document, we made a word and pasted a lot of images into it. The csdn images are not very friendly to be loaded, so we will not post images .)

Before listing the new features, you must write the differences between MySQL 5.6 and MySQL.

It is simple and convenient to install binary packages. It is easy to understand.

The real installation process is also a wave of twists and turns.

Feature 1:

Security has been enhanced. the specific enhancements include the following:
1. After 5.6 is installed, the root user does not have a password. a temporary password is randomly generated. Or-skip-grant-table at startup to change the password.

2. you can set the automatic expiration time for the password,

5.6 user table structure

5.7 user table structure

5.7 added the Password_lifetime field to set the password's automatic expiration time.
This parameter can be set in the configuration file or dynamically modified.
[Mysqld]
Default_password_lifetime = 180 (180 invalid)

[Mysqld]
Default_password_lifetime = 0 (never expire)
Set global default_password_lifetime = 180; dynamic modification

Alter user 'jeffre' @ 'localhost' password expire interval 90 DAY; (set a USER to EXPIRE for 90 days)

Alter user 'jeffre' @ 'localhost' password expire never; (the user password is not invalid)

Alter user 'jeffre' @ 'localhost' password expire default; (enable this USER to follow global settings)

3. user account locking:
When creating a user, you can set the user to the lock status, for example:

,

Use the following method to unlock a logstore:

Log on again, OK

Here is a comment: 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 or views that have
A DEFINER clause naming the locked account. That is, the ability to use a proxied account or stored
Programs or views is not affected by locking the account.

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.

5. mysql installation and use (should be initialization) mysqld-initialize command,
Some changes during installation are as follows:
Only one root account with a password
No anonymous users will be created during initialization, that is, the previous empty users.
The Test Library has been removed by default.

II
The default value is the strict mode. for example, if you insert a data that exceeds the length in 5.6, the system reports a warning message. now, an error is returned.

3. you cannot change the index name in the previous version 5.6.

Thu
I don't know much about this feature.

We are concerned about the enhanced functions of innodb.

1. you can use the alter table function to change the length of a varchar field.
Add mode:

In this example, ALGORITHM = INPLACE can only increase the length of a field, but cannot decrease it. when it increases, it cannot exceed the value of 256, but it can be increased on both sides.

2. the performance of using DML to create an innodb temporary table has been improved. I do not know how much the performance has been improved ..
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 in the shared tablespace stored in innnodb. What are the specific advantages?
,
4. innodb adds a spatial data type DATA_GEOMETRY. However, I do not know what functions it has.
5. add an independent tablespace to the temporary table. the parameter is innodb_temp_data_file_path.

6. enhanced functions of checksum
The file size that can be checked. the file size can only be 2 GB before 5.7.2, and can be larger than 2 GB.

7. 5.7.2 introduced a new log for the first time, called non-redo undo log. it is stored in lbtmp1 like a temporary table, and the file is in the data directory.
8. new variables are added. The related parameters for innodb_buffer_pool addition are as follows:

First, two values are added: innodb_buffer_pool_dump_pct and innodb_buffer_pool_chunk_size.
The default value is different. MySQL is started when the database service is started, and dump data is pushed when it is stopped.
Innodb_buffer_pool_dump_pct is used to set the percentage of dump, which is the most popular data.
Innodb_buffer_pool_chunk_size
Other variables are added:

Innodb_page_cleaners, which defines the number of dirty pages clean threads

9, this
It seems that the online ddl operations are added, and the focus is to avoid copytable operations and provide table availability,

10. the double write technology fails on a disk that supports atomic write. Fusion-io Non-Volatile Memory (NVM) from 5.7.4

11,

This feature means that before 5.7.4, only the non-partition table can be copied to another server, and then the partition table is also supported. It is not clear why it was unavailable in the past.

12
Is the following function cool? haha.

13,
The number of triggers is not limited. Previously, as an after insert trigger, only one can be created for one table.
14,
Ctrl + C will not exit mysql client
15,
Replication database name redirection. this is what I call

16,
Change master,

Mysql can change the master to without stopping slave.
For more information, see mysql5.7refman.

17,
The default engine of the test suite is changed to innodb.
18,
Mysql supports multi-master replication.