MySQL 5.7 key features

ssplyh

　　The other day I was discussing new features of MySQL 5.7 with a Percona Supportcustomer. After that conversation, I thought it would be a good>　　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 multi-source replication you will find useful.
  
• 　　SHOW SLAVE STATUSis 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 LOCK FREE SHOW SLAVE STATUS feature is first implemented in Percona Server 5.5.
  
• 　　Now you can have all the information about SHOW SLAVE STATUS from performance schema database tables. More details here from the manual.
  
• 　　With the new CHANGE REPLICATION FILTER command now you can modify replication filters rules without bouncing MySQL servers.
  
• 　　Since MySQL 5.7 you can perform CHANGE MASTER TO without stopping the slave via the STOP SLAVE command. For further details check the manual.
  
• 　　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 slave_parallel_type for details.
  
• 
  　　Global Transaction>
  
• 
  

　　InnoDB Enhancements:

• 　　Now you can resize InnoDB buffer pool online. Since MySQL 5.7 innodb_buffer_pool_size is a dynamic variable which provides the ability to resize buffer pool without restarting MySQL server.
  
• 
  　　From MySQL 5.7, online>
  
• 　　InnoDB supports Transportable Tablespace feature for partitioned InnoDB tables. I wrote a blog post on Transportable Tablespace that you will find useful.
  
• 　　Innochecksum utility is enhanced with new options. I also wrote a recent blog post on this same topic.
  
• 
  　　As of MySQL 5.7, InnoDB supports “spatial indexes” and it also supports online DDL operation to add spatial indexes i.e.>
  
• 　　Improved InnoDB buffer pool dump/reload operations. A new system variable, innodb_buffer_pool_dump_pct 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.
  
• 
  

　　PerformanceImprovements:

• 　　Bulk data load is improved on InnoDB in MySQL 5.7. InnoDB performs abulk 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 two-threaded flushing implemented in Percona Server 5.6.
  
• 
  

　　Optimizer Improvements:

• 　　EXPLAIN FOR CONNECTIONwill 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 UNION ALL queries and this will help to reduce disk I/O and disk space when UNION yields large result set. I found Morgan Tocker post informative on same.
  
• 　　JSON format for EXPLAIN first introduced in MySQL 5.6 which producesextended 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 blogpost
  
• 
  

　　
　　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 here.
  
• 　　Database administrators can nowo lock/unlock user accounts. Check details here.
  
• 　　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 data directory initialization 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 freeto 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 manual.
　　https://www.percona.com/blog/2015/05/27/mysql-5-7-key-features/