The other day I was discussing new features of MySQL 5.7 with a customer. After that conversation, I thought it would be a good idea to compile list of important features of MySQL 5.7. The latest MySQL 5.7.6 release candidate (RC) is out and is packed with nice features. Here’s a list of some MySQL 5.7 key features.

Replication Enhancements:

• One of the top features in MySQL 5.7 is multi-source replication. With multi-source replication you can point multiple master server’s to slave so limitation of slave having only one master is lift off. There is nice blog post written by my colleague on you will find useful.

• is non-blocking since MySQL 5.7. SHOW SLAVE STATUS returns immediately without waiting for STOP SLAVE to finish which can be blocked by long running SQL query from replication SQL_THREAD. As a side note, the feature is first implemented in .

• Now you can have all the information about SHOW SLAVE STATUS from performance schema database tables. More details here from the

• With the new command now you can modify replication filters rules without bouncing MySQL servers.

• Since MySQL 5.7 you can perform without stopping the slave via the STOP SLAVE command. For further details check the .

• There is now a different method for parallel replication. With new implementation the slave can apply transaction in parallel with single database/schema too. Check for details.

• Global Transaction Identifiers () is a feature that automatically tracks the replication position in replication stream, and since MySQL 5.7 is dynamic variables, which means you can enable/disable GTID in replication topology without synchronizing and restarting entire set of MySQL servers. As a side note, feature is added in Percona Server 5.6. With this feature you can deploy GTID on existing replication setups without marking master read_only and stopping all slaves in replication chain. My colleague Stephane had written nice to perform online migration without master downtime.

InnoDB Enhancements:

• Now you can resize InnoDB buffer pool online. Since MySQL 5.7 is a dynamic variable which provides the ability to resize buffer pool without restarting MySQL server.

• From MySQL 5.7, online ALTER TABLE also supports RENAME INDEX clause to rename an index. This change will take in place without table copy operation.

• InnoDB supports feature for partitioned InnoDB tables. I wrote a that you will find useful.

• Innochecksum utility is enhanced with new options. I also wrote a recent on this same topic.

• As of MySQL 5.7, InnoDB supports “spatial indexes” and it also supports to add spatial indexes i.e. ALTER TABLE .. ALGORITHM=INPLACE.

• Improved InnoDB buffer pool dump/reload operations. A new system variable, allows you to specify percentage of most recently used pages in each buffer pool to read out and dump.

Triggers:

• As per SQL standard, MySQL 5.7 now supports multiple triggers per table for trigger event (DML) and timing (BEFORE,AFTER) i.e. multiple triggers are permitted now for each event e.g. multiple triggers on INSERT action.

Performance Improvements:

• Bulk data load is improved on InnoDB in MySQL 5.7. InnoDB performs a bulk load when creating or rebuilding indexes. This method known as sorted index build and enhance create index operation and it also impacts FULLTEXT indexes.

• Currently there is a single page cleaner thread responsible for flushing dirty pages from the buffer pool(s). In MySQL 5.7 InnoDB parallel flushing was implemented to improve flushing where separate background thread for each buffer pool instance for flush list, LRU list. It’s worth to mention a implemented in Percona Server 5.6.

Optimizer Improvements:

• will let you run explain statements for already running queries. This may yield important information towards query optimization.

• In MySQL 5.7 the optimizer avoids the creatation temporary table for result of queries and this will help to reduce disk I/O and disk space when UNION yields large result set. I found Morgan Tocker informative on same.

• JSON format for EXPLAIN first introduced in MySQL 5.6 which produces extended information. JSON format for EXPLAIN is enhanced in version 5.7 by printing total query cost which makes it easier to see the difference between the good and bad execution plans.

• MySQL 5.7 now supports generated columns also known as virtual columns as new feature. My colleague Alexander explained this really well in this

MySQL Test Suite Enhancements:

• The MySQL test suite now uses InnoDB as its default storage engine. Along with that many new tests added and existing tests enhanced including test suite for replication with GTID.

Security Enhancements:

• Since MySQL 5.7 there is a password expiration policy in place. Any user that connects to a MySQL server goes through a password expiration life cycle and must change the password. More from the manual

• Database administrators can nowo lock/unlock user accounts. Check details .

• As of MySQL 5.7, installation only creates only one ‘root@localhost’ user account with random password and marks the password expiration cycle. So, installation no longer creates anonymous-user accounts and along with that there is no test database. For root user account password, MySQL generates it during and marks it as expired and will write a message to stdout displaying the password.

Conclusion:

This is only a short list of new features in MySQL 5.7. Please feel free to add your favorite features in the comments section. Along with new features, there are quite a few deprecated/removed features in MySQL 5.7. You can get full list from the