What’s New in MySQL 5.7? 16 Essential Features Every DBA Should Know

MySQL 5.7, released in late 2015, introduces a broad set of security, performance, and usability improvements. The following sections summarize the most relevant changes for database administrators, with configuration snippets and practical notes.

1. Secure authentication changes

The mysql.user table now requires a non‑null plugin column; the default is mysql_native_password and the legacy mysql_old_password method is removed.

2. Password expiration

DBAs can define password lifetimes per user. The system variable default_password_lifetime controls the default, and setting it to 0 disables expiration.

default_password_lifetime=0

3. Account lock/unlock

MySQL 5.7 adds explicit ACCOUNT LOCK and ACCOUNT UNLOCK clauses, allowing DBAs to temporarily disable a user without dropping the account.

4. Simplified SSL configuration

SSL/TLS is now enabled by default for client connections. The installation process includes an extra step to generate server certificates, making encrypted connections easier to set up.

5. Safer initialization with --initialize

The old mysql_install_db script is deprecated. Use mysqld --initialize (or --initialize-insecure) to create a data directory. This method:

Creates a single root account with a temporary expired password.

Does not create any additional accounts.

Does not create the test database.

Using --initialize-insecure results in a root account with no password.

6. Updated default SQL_MODE

The default mode is now: <code>ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION</code> Strict mode forces errors when data would be truncated, improving data integrity. 7. Online DDL rename‑index support MySQL 5.7 allows RENAME INDEX as an online DDL operation, though the feature is often of limited practical use because index renaming typically coincides with structural changes. 8. Built‑in full‑text parser for CJK languages The new ngram full‑text parser enables proper tokenization of Chinese, Korean, and Japanese text, replacing the previous space‑based tokenization. 9. In‑place VARCHAR length changes Using ALTER TABLE … MODIFY COLUMN … with ALGORITHM=INPLACE can change a VARCHAR size without copying the table, provided the byte‑length representation (1‑byte vs 2‑byte) does not change. Reducing the length always requires ALGORITHM=COPY . 10. Temporary‑table performance improvements MySQL 5.7 optimizes the execution paths of CREATE , DROP , TRUNCATE , and ALTER for temporary tables. Metadata for InnoDB temporary tables is stored in INNODB_TEMP_TABLE_INFO instead of the system tablespace, reducing overhead. 11. Separate temporary tablespace A dedicated tablespace file ( ibtmp1 ) is created for non‑compressed InnoDB temporary tables. Its location and size can be controlled with the innodb_temp_data_file_path option. 12. Non‑redo undo logs for temporary tables From 5.7.2 onward, temporary tables use a "non‑redo" undo log because they do not need crash‑recovery, which reduces I/O. 13. New DATA_GEOMETRY spatial type InnoDB now stores spatial data using the native DATA_GEOMETRY type instead of generic BLOBs, improving support for GIS functions. 14. Enhanced innochecksum utility The offline InnoDB checksum tool gains several options: Choose a specific checksum algorithm. Rewrite checksum values without verification. Specify an acceptable mismatch tolerance. Report page counts, export page‑type info, and read from stdin. Validate files larger than 2 GB (available from 5.7.2). 15. Online DDL for ordinary and partitioned tables Operations such as OPTIMIZE TABLE , ALTER TABLE … FORCE , and ALTER TABLE … ENGINE=INNODB now use the inplace algorithm, reducing rebuild time and impact on running applications. 16. Fusion‑io NVM optimizations When running on Fusion‑io non‑volatile memory devices that support atomic writes, MySQL automatically disables the InnoDB doublewrite buffer from 5.7.4 onward, cutting unnecessary I/O and improving throughput. These enhancements collectively make MySQL 5.7 the most secure and performance‑oriented release to date, but DBAs should test each feature against their workloads before enabling them in production.