MySQL 5.7

Currently, MySQL is the most trusted open-source database platform. 9 out of the top 10 most popular and highly-trafficked web, E-commerce, SaaS and Online Transaction Processing (OLTP) websites in the world rely on MySQL primarily for its proven performance, reliability, and ease of use.

MySQL 5.7 is one of the best releases of the world’s most popular open-source database. Version 5.7 provides a new, advanced feature set, designed to help users that are building the next-gen web-based and embedded applications and services. The new version delivers higher performance, scalability, and manageability, in addition to enhanced NoSQL capabilities with JSON support and MySQL Router. All those make it easy to connect applications to multiple MySQL databases.

MySQL 5.7 is Now Available at Flexible Net

MySQL 5.7 was one of the most-requested feature updates from our clients. That’s because of the significant speed and performance improvements in version 5.7. As you have come to know, we at FastComet are obsessed with all the different ways you can speed up your website. That is why we always strive to offer the latest supported versions that would ensure top performance of your project. The integration is aimed at all customers who want to leverage the power of MySQL 5.7 for their PHP apps. All of the major PHP frameworks, including Symfony, Laravel, Codeigniter, and Yii, are also fully compatible with MySQL 5.7.

What’s New in MySQL 5.7

MySQL version 5.7 includes more than 150 new features.

Learning from community feedback, performance and scalability are a top priority. MySQL takes into account trends and developments in computer hardware and its architecture. MySQL 5.7 delivers significant performance improvements on all workloads and sets new benchmark records. Version 5.7 promises improvements in speed as much as 3 times faster than what we have with version 5.6. The InnoDB storage engine also got optimized in this release. There is increased performance and concurrency, enhanced online operations, spatial indexes, and native partitioning.

Performance Enhancements

  • Improved bulk data load on InnoDB in MySQL 5.7. InnoDB performs a bulk load whenever it creates or rebuilds indexes. This method is known as a sorted index build and enhances the create index operation. Additionally, it has an impact on 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.

Optimizer Improvements

  • Explain for connection will let users run explain statements for queries that are already running. Those queries may prove to provide valuable information when it comes to query optimization;
  • In MySQL 5.7, the optimizer avoids creating a temporary table, resulting in Union all queries. That helps reducing disk I/O and disk space;
  • The JSON format for explaining (first introduced in MySQL 5.6) is enhanced in version 5.7 by printing the total query cost. That makes it easier to see the difference between the good and the bad execution plans;
  • With 5.7, MySQL supports generated columns, also known as virtual columns.

InnoDB improvements

  • Now you can resize the InnoDB buffer pool online. Since MySQL 5.7, innodb_buffer_pool_size is a dynamic variable. It provides the ability to resize the buffer pool without having to restart the MySQL server.
  • From MySQL 5.7, online alter table supports rename index clause to rename indexes. This change will be made without the table copy operation.
  • InnoDB supports the Transportable Tablespace feature for partitioned InnoDB tables.Innochecksum utility is enhanced with new options.
  • InnoDB now supports spatial indexes, as well as online DDL operations to add spatial indexes, i.e., ALTER TABLE .. ALGORITHM=INPLACE.
  • Improved InnoDB buffer pool dump/reload operations. A new system variable, innodb_buffer_pool_dump_pct allows users to specify the percentage of the most recently used pages in every buffer pool to read out and dump.

Security Improvements

MySQL 5.7 comes with improvements which make the deployment secure by default. These features try to minimize the attack surface as much as possible without hampering usability aspect. They are useful for novice users because help them to close common security loopholes that can be leveraged by attackers.

Secure-by-default features concentrate on four major areas:

  • Creation of installer packages in a way that resultant installations are secure;
  • Preparing secure pre-installation environment;
  • Secure configuration for a newly installed database;
  • Secure communication links between server-client;

Additional Security Changes

  • Database administrators can establish a policy for automatic password expiration;
  • Administrators can lock or unlock accounts for improved control over who can log in;
  • MySQL servers compiled using OpenSSL can automatically generate missing SSL and RSA certificate and key files at startup;
  • MySQL now sets the access control granted to clients on the named pipe to the minimum necessary for successful communication on Windows;
  • MySQL Enterprise Edition now provides data masking and de-identification capabilities.

Security-related Tips

When we talk about security within a MySQL installation, one should consider an extensive range of topics and the way they all affect the safety of their MySQL server and all related applications:

  • General factors, affecting security – choosing strong passwords, granting only the most necessary privileges to users, preventing SQL injections to ensure the security of applications, etc.
  • Security of the installation – all data, log, and application files of installation need to be protected. That is to ensure such files aren’t readable or writable by users that don’t have the required authorization.
  • Security and access control within the database system, including users that have granted access to the views, databases, and stored programs which are currently in use within the database.
  • Features provided by security-related plugins.
  • Network security of MySQL and your system. Security is related to grants for individual users. However, MySQL can also be restricted so that it’s available only on the server host, or a limited set of other hosts.
  • Make sure that you have the needed adequate backups of your database, configuration, and log files. Additionally, be sure to have a recovery solution and test if you can successfully recover all the information from your backups.

Conclusion

This is just a short list of the new features in MySQL 5.7. Along with them, there are several deprecated/removed ones in MySQL 5.7. You can get a full list from the manual.

Database optimization an element of necessity, especially when customers have lots of queries. MySQL 5.7 improves on the performance of databases for all PHP applications. We hope that our clients will be capable of utilizing the new MySQL version and add more power to their apps. We encourage you to try out MySQL 5.7 for multiple PHP versions and share your experience in the comment section below.



Saturday, November 9, 2019







« Nazad

Powered by WHMCompleteSolution