"Turn" MySQL version 5.7 new features serial

MySQL 5.7 version new feature serial (i)

This article will share the next 5.7 features with you, but let's start with the features that are about to be removed and the features that are no longer used. Based on these conditions, we should not use the new and future versions to avoid future compatibility issues.

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future.

1. Features that are about to be removed
1.1, InnoDB monitoring features, see: wl#7377 (Access address: http://dev.mysql.com/worklog/task/?id=7377, the following other WL, can be replaced by itself)
"suggest" can dynamically modify the values of Innodb_status_output, innodb_status_output_locks two parameters to print related information, or directly view the related table under INFORMATION_SCHEMA.

1.2, old-password,4.1 before the password Authentication mode has been disabled, old_passwords parameters are not available, wl#8006
"recommended" to upgrade the old password string as soon as possible, while upgrading the MySQL version, do not tell me that you are still in use 4.1 or earlier version.

1.3. Some SQL syntax is not available
1.3.1, ALTER TABLE ... IGNORE.
1.3.2, INSERT delay attribute, but keep this syntax. wl#6073
1.3.3, Error_for_division_by_zero, no_zero_date, no_zero_in_date SQL MODES and several other SQL mode merges into strict. However, it may cause replication to fail, so it is still under consideration. wl#7467
1.3.4 and no longer support year (2), it is recommended to upgrade to year (4) as soon as possible. wl#6263
It is recommended that you use standard SQL syntax whenever possible, and no longer use MySQL-specific or less stringent syntax to avoid further problems with version upgrades.

1.4, some parameters are not available
1.4.1, no longer support the short wording of some instructions, must be required to write the whole, for example, MYSQLDUMP–COMPR represents mysqldump–compress, after the entire parameter must be written intact. wl#6978
1.4.2, delete timed_mutexes. wl#7436
1.4.3, can no longer disable the InnoDB engine, because the system tables are also changed to InnoDB. wl#7976
1.4.4, Performance Improvement Limited, delete Innodb_use_sys_malloc, innodb_additional_mem_pool_size. wl#7628
1.4.5, meaning little, delete innodb_mirrored_log_groups. wl#6808
1.4.6, there are new system parameters replaced, delete innodb_file_io_threads. wl#7149
1.4.7, delete the system parameter Storage_engine, and use Default_storage_engine instead. wl#7148
1.4.8, delete the –basedir and –datadir system parameters in Mysql_upgrade. wl#7010

1.5. Some client Tools
Mysqlaccess, Mysql_convert_table_format, Mysql_fix_extensions, mysql_find_rows.sh, Mysql_setpermission, Msql2mysql, Mysqlbug, Mysql_zap and Mysql_waitpid, Mysqlhotcopy will no longer be used.
"Advice" nothing to say, conform to the new version of the trend, should give up, do not outworn, these tools are basically not used.

In the next issue, we will talk about features that are no longer recommended in 5.7, which may be removed in the future.

MySQL 5.7 Release new features serial (ii)

This article will share the next 5.7 features with you, but let's start with the features that are about to be removed and the features that are no longer used. Based on these conditions, we should not use the new and future versions to avoid future compatibility issues.

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future.

2. Characteristics of the expected cancellation
2.1, the Client interaction protocol, the EOF protocol package is not recommended for re-use, it is recommended to change to a OK protocol package. wl#7766
2.2. It is not recommended to use the show Profile directive, or directly from INFORMATION_SCHEMA. Profiling, it is recommended to use several views in Performance_schema. wl#6802
2.2, the DES algorithm based on the encryption and decryption function is not recommended, instead of using AES-based encryption and decryption functions. wl#8126
2.4, ibid. encode ()/decode () function is not recommended. wl#6984
2.5, using ALTER user to modify the password for the user, do not recommend the use of Set password to modify the password. wl#6409
2.6.4 system parameters related to InnoDB innodb_large_prefix, Innodb_file_format, Innodb_file_format_check, Innodb_file_format_ Max is no longer recommended for use. wl#7703
2.7. It is no longer recommended to add extended/partitions keywords after explain. wl#7027
2.8, no longer recommend the use of Collation_database, character_set_database system parameters. wl#2.11
2.9. It is no longer recommended to use SYNC_FRM system parameters. wl#8216
2.10. It is no longer recommended to use @ @session. gtid_executed System variables. wl#7518

MySQL 5.7 Release new features serial (III)

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future. This section begins with the new features in version 5.7.

1. Security
A. The plugin field of the user table Mysql.user is not allowed to be empty, the default value is Mysql_native_password, not Mysql_old_password, and the old password format is no longer supported;
B. Increase password expiration mechanism, need to change password after expiration, otherwise it may be disabled or go into sandbox mode;
C. When initializing with mysql_install_db, random passwords are automatically generated by default, and no accounts other than [email protected] are created, and the test library is not created;

"New Feature Practice"

Execute mysql_install_db to initialize the new instance:

 [[email protected]]#./bin/mysql_install_db--user=mysql--datadir=/data/mysql/2015-06-24 13:55:29 [ WARNING] mysql_install_db is deprecated. Consider switching to mysqld--initialize2015-06-24 13:55:38 [error]   child process:/opt/17173_ Install/mysql-5.7.7-rc-linux-glibc2.5-x86_64/bin/mysqld terminated prematurely with errno= 322015-06-24 13:55:38 [ error]   Failed to Execute/opt/17173_install/mysql-5.7.7-rc-linux-glibc2.5-x86_64/bin/mysqld--bootstrap --datadir=/data/mysql--lc-messages-dir=/usr/share/mysql--lc-messages=en_us--server log begin--MYSQLD: [Warning]-- Bootstrap is deprecated. Please consider using--initialize instead--server log End--

You can see the hint mysql_install_db is no longer recommended, it is recommended to change to mysqld–initialize to complete the initialization of the instance.

After changing to Mysqld–initialize, if the datadir point to the target directory already has data files, then there will be a similar prompt:

[Email protected]]#./bin/mysqld--user=mysql--basedir=/opt/17173_install/mysql-5.7.7-rc-linux-glibc2.5-x86_64/- -datadir=/data/mysql--initial--initialize-insecure2015-06-24t06:05:05.533588z 0 [ERROR]--initialize specified but The data directory has files in it. aborting.2015-06-24t06:05:05.533627z 0 [ERROR] aborting

Therefore, it is necessary to make sure that the DataDir target directory is empty, and avoid misoperation to destroy the existing data.

In addition, if you add –initial-insecurewhen initializing, you will create an [email protected] account with a blank password, or you will create an [email protected] account with a password, which is written directly in Log-error log file (in version 5.6 is placed in the ~/.mysql_secret file, more covert, unfamiliar words may be confused)

[Email protected]]#./bin/mysqld--user=mysql--basedir=/opt/17173_install/mysql-5.7.7-rc-linux-glibc2.5-x86_64/- -datadir=/data/mysql--initial2015-06-24t06:14:31.458905z 0 [Warning] Insecure configuration for--secure-file-priv: Current value does isn't restrict location of generated files. Consider setting it to a valid, Non-empty path.

After initialization, if you do not log in with the new version of the client, an error similar to the following is reported:

Mysql-uroot-penter Password:error 1862 (HY000): Your password has expired. To log the must change it using a client that supports expired passwords.

The above error means that you need to log in with the current version of the client, because the new user login needs to change the password immediately, or can not continue to work:

[(email protected])]>use mysqlerror 1820 (HY000): You must SET PASSWORD before executing this statement[([email Protec Ted])]>set password = password (' ABCD '); Query OK, 0 rows affected, 1 Warning (0.00 sec)

Once you have modified your password, you can continue to use the old version of the client tool.

MySQL 5.7 Release new features serial (iv)

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future.

1. SQL Mode change
A. strict_trans_tables mode is enabled by default;
B. More complex feature support is implemented for Only_full_group_by mode and is also enabled by default;
C. Other SQL mode that is enabled by default is also No_engine_substitution;

"Imysql Recommendations"
For the majority of MySQL users, the past is not so strict mode is very convenient, in the 5.7 version may feel slightly uncomfortable, slowly get used to it. For example, to a 20-character varchar column to write 30 characters, in the past will automatically truncate and give a warning alarm, and in the 5.7 version, the error is thrown directly. Personally think this is a good way to avoid all kinds of wonderful writing.

"New Feature Practice"

--View the default Sql_mode[[email protected]]> select @ @sql_mode; +------------------------------------------- ----------------------------------------+| @ @sql_mode |+-----------------------------------------------------------------------------------+| Only_full_group_by,strict_trans_tables,no_auto_create_user,no_engine_substitution |+--------------------------- --------------------------------------------------------+--Insert a 50 character [[email protected]]> insert into T_char Select 0, repeat (' X ', 50); ERROR 1406 (22001): Data too long for column ' uname ' at row to modify sql_mode[[email protected]]> set SQL for this session _mode = ' only_full_group_by,no_auto_create_user,no_engine_substitution '; Query OK, 0 rows Affected (0.00 sec)-After removing strict_trans_tables mode [[Email protected]]> select @ @sql_mode; +-- -------------------------------------------------------------+| @ @sql_mode |+---------------------------------------------------------------+| Only_full_group_by,no_auto_create_useR,no_engine_substitution |+---------------------------------------------------------------+[[email  protected]]> INSERT INTO T_char Select 0, repeat (' X ', 50);  Query OK, 1 row affected, 1 Warning (0.00 sec)--Alert message records:1 duplicates:0 warnings:1[[email protected]]> Show warnings;+---------+------+--------------------------------------------+| Level | Code | Message |+---------+------+--------------------------------------------+| Warning | 1265 | Data truncated for column ' uname ' at row 1 |+---------+------+--------------------------------------------+

Because the uname field has a length of 40 characters.

2. Optimize online operations, such as modifying the buffer pool, modifying the index name (non-primary key), modifying the replication FILTER, modifying the master without shutting down the slave thread , and many other features.

The ability to modify the buffer pool online is very convenient for DBAs, which can be dynamically adjusted during the running of the instance to avoid unreasonable allocation of innodb_buffer_pool_instances, but cannot be modified and Innodb_buffer_pool_instances is greater than 1 o'clock, and you cannot adjust the buffer pool to 1GB or less, you need a little attention.

If the buffer pool is enlarged, the process is generally:

1. In Innodb_buffer_pool_chunk_size, allocate new memory pages;2, extend buffer pool's ahi (Adaptive Hash Index) list, and include the newly allocated pages; 3. Add the newly allocated pages to the free list;

If you are shrinking buffer pool, the process is roughly the following:

1, re-pages;2 buffer pool, ready to reclaim the Innodb_buffer_pool_chunk_size units, release delete these pages (this process will be a little time), 3, adjust the AHI linked list, using the new memory address.

In the actual test, it is found that the cost of modifying buffer poo online is not very good, the SQL command after the completion of the complete instantaneous, the second process is not too long time. In an environment where a concurrent 128 thread runs TPCC, the buffer pool is scaled from 32G to 48G, the background thread takes 3 seconds, and a reduction from 48G back to 32G takes 18 seconds, and no lock waits occur during the transaction that is being measured.

--Demo 1: Extended from 1G to 16g[[email protected]]> SET GLOBAL innodb_buffer_pool_size = 51539607552; Query OK, 0 rows Affected (0.00 sec)--See logging 09:21:19.460543z 0 [Note] innodb:resizing buffer pool from 1073741824 to 171 79869184. (unit=134217728) 09:21:19.468069z 0 [note] innodb:disabled Adaptive hash index.09:21:20.760724z 0 [note] InnoDB  : Buffer pool 0:60 chunks (491511 blocks) were added.09:21:21.922869z 0 [Note] innodb:buffer pool 1:60 chunks (491520 Blocks) were added.09:21:21.935114z 0 [note] innodb:buffer pool 0:hash tables were resized.09:21:21.947264z 0 [note] I Nnodb:buffer pool 1:hash tables were resized.09:21:22.203031z 0 [Note] innodb:resized hash tables at Lock_sys, Adaptiv E Hash index, DICTIONARY.09:21:22.203062Z 0 [Note] innodb:completed to resize buffer pool from 1073741824 to 17179869184. 09:21:22.203075z 0 [Note] innodb:re-enabled Adaptive Hash index.--Demo 2: Reduced from 16G to 1g[[email protected]]> SET Globa L innodb_buffer_pool_size = 1073741824; Query OK,0 Rows Affected (0.00 sec)--look at the logging 09:22:55.591669z 0 [Note] innodb:resizing buffer pool from 17179869184 to 1073741824. (unit=134217728) 09:22:55.680836z 0 [note] innodb:disabled Adaptive hash index.09:22:55.680864z 0 [note] innodb:buffer PO OL 0:start to withdraw the last 491511 blocks.09:22:55.765778z 0 [Note] innodb:buffer pool 0:withdrew 489812 blocks F Rom free list.  Tried to relocate 1698 pages (491510/491511). 09:22:55.774492z 0 [Note] innodb:buffer pool 0:withdrew 0 blocks from free List. Tried to relocate 1 pages (491511/491511). 09:22:55.782745z 0 [Note] innodb:buffer pool 0:withdrawn target 491511 blocks .09:22:55.782786z 0 [note] Innodb:buffer pool 1:start to withdraw the last 491520 blocks.09:22:55.892068z 0 [note] Inno Db:buffer Pool 1:withdrew 489350 blocks from free list.  Tried to relocate 2166 pages (491517/491520). 09:22:55.900743z 0 [Note] innodb:buffer pool 1:withdrew 0 blocks from free List. Tried to relocate 2 pages (491519/491520). 09:22:55.908257z 0 [Note] innodb:buffer pool 1:withdrew 0 blocks from the free list. Tried to relocate 0 pages (491519/491520). 09:22:55.915778z 0 [Note] innodb:buffer pool 1:withdrew 0 blocks from free Li St. tried to relocate 1 pages (491520/491520). 09:22:55.923836z 0 [Note] innodb:buffer pool 1:withdrawn target 491520 bl OCKS.09:22:56.149172Z 0 [note] innodb:buffer pool 0:60 chunks (491511 blocks) were freed.09:22:56.308997z 0 [note] Inno Db:buffer Pool 1:60 chunks (491520 blocks) were freed.09:22:56.316258z 0 [Note] innodb:buffer pool 0:hash tables Wer e resized.09:22:56.324027z 0 [note] innodb:buffer pool 1:hash tables were resized.09:22:56.393589z 0 [note] Innodb:res  ized hash tables at Lock_sys, Adaptive Hash Index, dictionary.09:22:56.393616z 0 [Note] innodb:completed to resize buffer Pool from 17179869184 to 1073741824.09:22:56.393628z 0 [Note] innodb:re-enabled Adaptive Hash Index.

Then look at the online modification of the non-primary key index name , directly with ALTER TABLE RENAME index syntax.

"New Feature Practice"

For example, the following SQL syntax:

[Email protected]]> ALTER TABLE orders RENAME INDEX idx1 to idxxx1; Query OK, 0 rows affected (0.11 sec) records:0 duplicates:0 warnings:0

As you can see, almost instantaneously, although I'm running 64 concurrent TPCC in the execution of this SQL.

MySQL 5.7 Version new features serial (V)

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future.

1, support multi-source replication (multi-source replication), this is the use of Sub-Library sub-table is absolutely a super heavyweight gospel. Multiple master data can be merged onto one instance to help improve the utilization of the slave server. However, if it is the same table, there is a risk of a primary key and a unique index conflict, which requires planning in advance.

"New Feature Practice"
MySQL 5.7 's multi-source replication uses multi-channel mode, for example, you can create multiple replication channels and copy data from multiple master to the same slave node:

--You need to change master_info_repository and relay_log_info_repository to TABLE mode [[email protected]]> SET GLOBAL MASTER_ Info_repository = "TABLE"; Query OK, 0 rows Affected (0.00 sec) [[email protected]]> SET GLOBAL relay_log_info_repository = "TABLE"; Query OK, 0 rows Affected (0.00 sec)--Create the first copy channel [[email protected]]> change MASTER to master_host= ' 1.2.3.4 ', maste R_user= ' USER ', master_password= ' repl ' for CHANNEL ' MASTER-01 '; Query OK, 0 rows affected, 2 warnings (0.00 sec)--Create a second copy channel [[email protected]]> change MASTER to master_host= ' 2.3 .4.5 ', master_user= ' USER ', master_password= ' repl ' for CHANNEL ' MASTER-02 '; Query OK, 0 rows affected, 2 warnings (0.00 sec)--View the status of the second replication channel [[email protected]]> show SLAVE status for Channel ' MASTER-02 '; *************************** 1. Row *************************** slave_io_state:master_host:2.3.4.5 Mast Er_user:user master_port:3306 Connect_retry: Master_log_file:read_master_log_pos:4 relay_log_file: [email protected] Relay_log_pos:4 Relay_Master_Log_File:Slave_IO_Running:No slave_sql_running : No Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_T able:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 Las t_error:skip_counter:0 exec_master_log_pos:0 relay_log_space:154 U           Ntil_condition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no                Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:No Last_io_err no:0 Last_io_error:last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Mas ter_server_id:0 Master_UUID:Master_Info_File:mysql.slave_master_info S                  ql_delay:0 Sql_remaining_delay:null slave_sql_running_state:master_retry_count:86400            MASTER_BIND:LAST_IO_ERROR_TIMESTAMP:LAST_SQL_ERROR_TIMESTAMP:MASTER_SSL_CRL: master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:f0df162a-1a39-11e5-883a-782bcb65f419  : 1-11025782 auto_position:0 replicate_rewrite_db:channel_name:master-021 Row in Set (0.00 sec)

Other SQL directives related to replication are basically the same as in the past, just add the for CHANNEL ' channel-name ' clause.

In addition, it is supported to modify the replication filter rules online, but it is not recommended to use the filter rule, so it is not highlighted. Execute the following SQL command to complete the filter Rule modification:

[Email protected]]> change REPLICATION FILTER replicate_do_db = (d1), replicate_ignore_db = (D2);

2, support multi-threaded replication (multi-threaded slaves, referred to as MTS), Schema-level parallel replication was implemented in version 5.6, but it didn't make much sense, since most of our online reading and writing pressures were basically focused on a few data tables, which basically didn't help to mitigate replication latency issues. It is mariadb multi-threaded parallel replication shine, there are many people because of this feature select MARIADB (for example, I am also one, hehe).

MySQL 5.7 MTS supports two modes, one is the same as 5.6, the other is based on the Binlog group commit implementation of multi-threaded replication, that is, master on the Binlog at the same time on the slave side can also be apply, to achieve parallel replication. For more detailed information on MTS you can view Kang's shared MySQL 5.7 parallel replication implementation principle and tuning, and I won't say it again.

It is worth mentioning that, after the comparison test, 5.7 with the new parallel replication, there will still be a certain degree of delay, but compared to the 5.6 version reduced by 86%, compared to the MARIADB parallel replication latency is also much smaller.

MySQL 5.7 version new feature serial (VI)Post a reply

This article is based on the MYSQL-5.7.7-RC version, and more changes may occur in the future.

2. Automatically determines whether the underlying I/O device can support atomic io (AIO) and, if detected, automatically turns off double write buffer, further improving performance.
4. The innodb_page_cleaners option allows you to set multiple page cleaner threads to improve the efficiency of dirty page refreshes.
6. You can automatically delete unused undo log by setting options such as Innodb_undo_log_truncate.
8. Enhance the performance of InnoDB read-only mode.
10. Supports multiple triggers on a single table, so that the original trigger table can support Pt-osc .
12. The new log_syslog option allows you to print the MySQL log to the system log file.
14. The partition table for the INNODB and MyISAM engines also supports the ICP feature.
16. Supports direct view of execution plans for a connection online, such as EXPLAIN for CONNECTION 1024.
18. Support Online (INPLACE) to increase the length of VARCHAR columns. However, the 0-255 length is an interval, more than 256 is another interval, can not cross 255 this threshold, such as the length from 100 to 1000 (because the 255 length of the extra 1 bytes, more than 255 of the length of the additional 2 bytes required). It is also not supported to reduce the length of VARCHAR online.
20. And more on performance improvements, including increased client connection efficiency, improved bulk data load efficiency, non-sorted fields stored in sort buffer (increased memory utilization), UNION all no longer generating temporary tables, parsing refactoring, The query optimizer is further refined (such as adding a manageable CBO rule) and so on.

The above content from http://imysql.com, feel the teacher's share!

Related references: http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=205236417&idx=1&sn= 15281c834348911cea106478aa819175&3rd=mza3mdu4ntyzmw==&scene=6#rd

Http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-removals

"Turn" MySQL version 5.7 new features serial