What Is New in MySQL 5.7
MySQL 5.7 introduced a significant number of enhancements focused on performance, security, and SQL compliance. Here's a high-level summary of the key changes.
| Category | Key Changes |
|---|---|
| New Features | Native JSON support, Generated Columns, Multi-source replication, SQL modes for stricter data handling. |
| Performance & Scalability | InnoDB optimizations, better optimizer cost model, online buffer pool resizing, improved partitioning. |
| Security | Password validation plugin, password expiration policy, stricter default SQL mode, SSL improvements. |
| Replication | Multi-threaded slaves with parallel replication, enhanced GTIDs, loss-less semi-sync replication. |
| Deprecated & Removed | Deprecated: PASSWORD() function, several system variables. Removed: mysql_install_db. |
What are the major SQL and data type enhancements?
The most impactful changes are the introduction of a native JSON data type and generated columns. This moves MySQL beyond pure relational storage.
The native JSON implementation provides automatic validation of JSON documents and a set of functions for efficient manipulation and querying, like JSON_EXTRACT() and JSON_SEARCH(). Generated columns let you store values computed from an expression, either virtually (not stored) or stored (materialized), which is great for simplifying queries or building indexes on complex expressions.
In practice, this means you can build more flexible schemas without sacrificing the ability to index and quickly query semi-structured data. The SQL mode is also stricter by default (ONLY_FULL_GROUP_BY is enabled), which helps catch ambiguous queries early.
How did performance and scalability improve?
InnoDB received numerous optimizations that directly boost throughput and manageability for high-load environments.
You can now resize the InnoDB buffer pool online without restarting the server, which is a huge operational win. The optimizer uses a better cost model that considers factors like data read time, making its query execution plans more accurate. Partitioning performance is also significantly better, especially for queries that involve bulk data removal with TRUNCATE PARTITION.
For replication, multi-threaded slaves (slave_parallel_workers) allow applier threads to process transactions in parallel on a per-database basis, drastically reducing replication lag on busy slaves.
What security features were added?
Security was hardened with new password policies and stricter default settings. The goal was to make a secure configuration the default out of the box.
The validate_password plugin is now included to enforce strength policies for passwords. You can also set passwords to expire automatically, forcing periodic user updates. The default SQL mode includes more strict settings to prevent less secure SQL queries from running.
These changes matter because they help prevent weak authentication practices and common SQL injection patterns from succeeding, moving away from the older, more permissive defaults.
What changed with replication and high availability?
Replication became more robust, flexible, and faster. Multi-source replication is a standout feature for data consolidation workloads.
You can now configure a single replica to collect transactions from multiple master servers. This is ideal for bringing data from different sources into a central reporting server. Global Transaction Identifiers (GTIDs) are enhanced for better crash safety and ease of failover.
Semi-synchronous replication also became loss-less, ensuring that a transaction isn't considered committed on the master until at least one replica has received and acknowledged it, guaranteeing no data loss if the master fails.
FAQ
How do I start using the native JSON type?
You define a column with the JSON data type. You can then insert valid JSON documents and use functions like JSON_EXTRACT() or the -> operator to query specific values within the document.
What is the default SQL mode in MySQL 5.7 and why does my old query break?
The default mode includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, and others. Queries that use GROUP BY incorrectly (selecting non-aggregated columns not in the group by clause) will now generate an error instead of a warning.
Can I upgrade from 5.6 to 5.7 without downtime?
An in-place upgrade is possible but requires a restart. For minimal downtime, use a replication setup where you upgrade a slave first, fail over to it, and then upgrade the old master.
What happened to the mysql_install_db script?
It was removed. You now initialize the data directory using the mysqld --initialize or mysqld --initialize-insecure commands, which provide more control and security.
Is the PASSWORD() function still safe to use?
No, it was deprecated. The PASSWORD() function is intended for internal server use. Applications should use more secure hashing algorithms or the ALTER USER statement to manage passwords.