Hello everyone, today I’m going to share some major changes released in MySQL 5.7, this includes only the important features and options that are available now for all the changes please visit the MySQL documentation site.
New MySQL 5.7 features:
1. Security improvements:
- Every row in mysql.user table must have a non-empty plugin column , otherwise the users account will be disabled. Also the support for mysql_old_password plugin is removed so mysql_native_password plugin should be used.
- Automatic password expiry is now enabled so that the users must change the password after a certain period of time
- To support secure servers, MySQL servers compiled with OPENSSL can now automatically generate SSL and RSA certificate at startup.
- Installation using mysqld –initialize are secure by default now, only one root user (root@localhost) is created, no anonymous users and no test databases are created.
2. SQL Modes:
Now Strict SQL mode for Transactional storage engine (STRICT_TRANS_TABLES) like InnoDB are enabled by default.
The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are now deprecated but enabled by default.
3. Change in ALTER Command:
Now ALTER TABLE supports RENAME INDEX clause to rename an index without making a physical copy of table.
4. Improvements in InnoDB Storage Engine:
- VARCHAR size may be increased using an in-place ALTER TABLE, for example:
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
Limitation: in-place ALTER TABLE only supports increasing VARCHAR size from 0 to 255 bytes or increasing VARCHAR size from a value equal to or greater than 256 bytes.
- DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.
- A new type of non-redo undo log for both normal and compressed temporary tables and related objects now resides in the temporary tablespace
- Online DDL is now supported for operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, ALTER TABLE … ENGINE=INNODB. Also it supports support reduces table rebuild time and permits concurrent DML.
- innodb_buffer_pool_size is now a dynamic variable, we can change it’s value without restarting the MySQL server.
5. JSON Support:
Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements.
6. System and Status variables:
System and status variable information is now available in Performance Schema tables, in preference to use of INFORMATION_SCHEMA tables to obtain these variable.
This also affects the operation of the SHOW VARIABLES and SHOW STATUS statements.
EXPLAIN can now also be user for a connection which executes explainable query. For example:
EXPLAIN [options] FOR CONNECTION connection_id;
Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER).
This limitation has been lifted and multiple triggers are permitted.
9. MySQL Client:
Using Control + C earlier interrupts the executing statement or if no statement it exists MySQL client but now it will only interrupt the statement and will not take you out of MySQL client.
10. Rename database using binary logs:
Now we can rename database while reading mysqlbinglogs if written using row-based format using –rewrite-db option.
11. Change Replication Master without STOP SLAVE:
Following MySQL 5.7.4 onwards, we can use CHANGE MASTER TO … statement without executing STOP SLAVE
12. Multi-Source Replication is available:
Now we can replicate data from multiple masters to a single slave.