This section summarizes what has been added to, deprecated in, and removed from MySQL 5.5.
The following features have been added to MySQL 5.5:
MySQL Enterprise Thread Pool. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. As of MySQL 5.5.16, MySQL Enterprise Edition distributions include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. For more information, see Section 5.5.4, “MySQL Enterprise Thread Pool”.
MySQL Enterprise Audit. MySQL Enterprise Edition now includes MySQL Enterprise Audit, implemented using a server plugin named
audit_log. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines. When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access. For more information, see Section 6.5.2, “MySQL Enterprise Audit”.
Pluggable authentication. MySQL authentication supports two new capabilities, pluggable authentication and proxy users. With pluggable authentication, the server can use plugins to authenticate incoming client connections, and clients can load an authentication plugin that interacts properly with the corresponding server plugin. This capability enables clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than the built-in MySQL authentication based on native MySQL passwords stored in the
mysql.usertable. For example, plugins can be created to use external authentication methods such as LDAP, Kerberos, PAM, or Windows login IDs. Proxy user capability enables a client who connects and authenticates as one user to be treated, for purposes of access control while connected, as having the privileges of a different user. In effect, one user impersonates another. Proxy capability depends on pluggable authentication because it is based on having an authentication plugin return to the server the user name that the connecting user impersonates. See Section 6.3.6, “Pluggable Authentication”, and Section 6.3.7, “Proxy Users”.
As of MySQL 5.5.16, MySQL Enterprise Edition includes two plugins that enable MySQL Server to use external authentication methods to authenticate MySQL users:
PAM (Pluggable Authentication Modules) enables a system to access various kinds of authentication methods through a standard interface. A PAM authentication plugin enables MySQL Server to use PAM to authenticate MySQL users.
Distributions of MySQL for Windows include an authentication plugin that enables MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.
These authentication plugins enable MySQL Server to accept connections from users defined outside the MySQL grant tables. They also support the MySQL proxy-user capability. Each plugin can return to MySQL a user name different from the login user, which means that the plugin can return the MySQL user that defines the privileges the externally authenticated user should have.
For more information, see Section 184.108.40.206, “The PAM Authentication Plugin”, and Section 220.127.116.11, “The Windows Native Authentication Plugin”.
Multi-core scalability. Scalability on multi-core CPUs is improved. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. MySQL 5.5 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores. The focus has been on
InnoDB, especially locking and memory management. See Section 1.4.1, “Scalability Improvements”.
Diagnostic improvements. There is better access to execution and performance information. Diagnostic improvements include Performance Schema (a feature for monitoring MySQL Server execution at a low level), DTrace probes, expanded
SHOW ENGINE INNODB STATUSoutput, Debug Sync, and a new status variable. See Section 1.4.3, “Diagnostic and Monitoring Capabilities”.
Solaris. Several modifications improve operation of MySQL Server on Solaris. See Section 1.4.4, “Enhanced Solaris Support”.
Default storage engine. The default storage engine for new tables is
MyISAM. See Section 14.1.2, “InnoDB as the Default MySQL Storage Engine”.
MySQL Cluster. MySQL Cluster is released as a separate product, with new development for version 7.2 of the
NDBstorage engine being based on MySQL 5.5. Clustering support is not available in mainline MySQL Server 5.5 releases. For more information about MySQL Cluster NDB 7.2, see Chapter 18, MySQL Cluster NDB 7.2.
MySQL Cluster releases are identified by a 3-part NDB version number. Currently, MySQL Cluster NDB 7.1 is the most recent GA release series. MySQL Cluster NDB 6.3 and MySQL Cluster NDB 7.0 are also still available. These versions of MySQL Cluster are based on MySQL Server 5.1 and documented in the MySQL 5.1 Reference Manual.
Semisynchronous replication. A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 17.3.8, “Semisynchronous Replication”
Unicode. Support for supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP). These new Unicode character sets include supplementary characters:
utf8mb4. See Section 10.1.11, “Unicode Support”.
Partitioning. Enhancements to table partitioning:
Two new types of user-defined partitioning are supported:
RANGE COLUMNSpartitioning is an extension to
LIST COLUMNSpartitioning is an extension to
LISTpartitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:
You can also define ranges or lists based on multiple column values when partitioning tables by
LIST COLUMNS, respectively. Such a range or list may refer to up to 16 columns.
For tables defined using these partitioning types, partition pruning can now optimize queries with
WHEREconditions that use multiple comparisons between (different) column values and constants, such as
a = 10 AND b > 5or
a < "2005-11-25" AND b = 10 AND c = 50.
It is now possible to delete all rows from one or more partitions of a partitioned table using the
ALTER TABLE ... TRUNCATE PARTITIONstatement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the
TRUNCATE PARTITIONclause do not have to be contiguous.
Key caches are now supported for indexes on partitioned
MyISAMtables, using the
LOAD INDEX INTO CACHEstatements. In addition, a key cache can be defined for and loaded with indexes from an entire partitioned table, or for one or more partitions. In the latter case, the partitions are not required to be contiguous.
TO_SECONDS()function converts a date or datetime expression to a number of seconds since the year 0. This is a general-purpose function, but is useful for partitioning. You may use it in partitioning expressions, and partition pruning is supported for tables defined using such expressions.
Metadata locking. The server now prevents DDL statements from compromising transaction serializibility by using a new class of locks called metadata locks. See Section 8.11.4, “Metadata Locking”.
IPv6 support. MySQL Server can accept TCP/IP connections from clients connecting over IPv6. See Section 5.1.8, “IPv6 Support”.
Build configuration. MySQL releases are now built using CMake rather than the GNU autotools. Accordingly, the instructions for installing MySQL from source have been updated to discuss how to build MySQL using CMake. See Section 2.9, “Installing MySQL from Source”.
The build process is now similar enough on all platforms, including Windows, that there are no longer sections dedicated to notes for specific platforms.
The following features are deprecated in MySQL 5.5 and may be or will be removed in a future series. Where alternatives are shown, applications should be updated to use them.
Relying on implicit
GROUP BYsorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit
GROUP BYsorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.
YEAR(2)columns in existing tables are treated as before, but
YEAR(2)in new or altered tables are converted to
YEAR(4). For more information, see Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.
--ignore-builtin-innodbserver option. It does nothing and has no effect.
Use of unambigious option prefixes. If an unambiguous prefix is given, a warning occurs to provide feedback. Option prefixes are no longer supported in MySQL 5.7; only full options are accepted.
timed_mutexessystem variable. It does nothing and has no effect.
Use of the data directory as the location for
The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.
log_bin_trust_routine_creatorssystem variable (use
record_buffersystem variable (use
table_typesystem variable (use
FRAC_SECONDmodifier for the
SHOW TABLE TYPESSQL statement (use
SHOW PLUGINSQL statement (use
TIMESTAMP(data type: The ability to specify a display width of
--default-table-typeserver option (use
--delay-key-write-for-all-tablesserver option (use
--log-bin-trust-routine-creatorsserver option (use
--master-server options to set replication parameters (use the
CHANGE MASTER TOstatement instead):
--warningsserver option (use
--no-named-commandsoption for mysql (use
--no-pageroption for mysql (use
--no-teeoption for mysql (use
--positionoption for mysqlbinlog (use
--alloption for mysqldump (use
--first-slaveoption for mysqldump (use
--config-fileoption for mysqld_multi (use
-Ogeneral-purpose options for setting program variables (use
--with-pstackoption for configure and the
--enable-pstackoption for mysqld.