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 8.11.6, “The Thread Pool Plugin”.
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.3.11, “MySQL Enterprise Audit Log Plugin”.
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
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.8, “Proxy Users”.
As of MySQL 5.5.16, commercial distributions of MySQL include 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 18.104.22.168, “The PAM Authentication Plugin”, and Section 22.214.171.124, “The Windows Native Authentication Plugin”.
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
InnoDB, especially locking
and memory management. See
Section 1.4.1, “Scalability 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
INNODB STATUS output, 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
InnoDB rather than
Section 14.1.2, “InnoDB as the Default MySQL Storage Engine”.
MySQL Cluster is released as a separate product, with new
development for version 7.2 of the
NDB storage 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 Manual. See MySQL Cluster NDB 6.1 - 7.1.
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”
Support for supplementary Unicode characters; that is,
characters outside the Basic Multilingual Plane (BMP). These
new Unicode character sets include supplementary characters:
Section 10.1.10, “Unicode Support”.
Partitioning. Enhancements to table partitioning:
Two new types of user-defined partitioning are supported:
RANGE COLUMNS partitioning is an
LIST COLUMNS partitioning is an
LIST partitioning. Each of
these extensions provides two enhancements to MySQL
You can also define ranges or lists based on multiple
column values when partitioning tables by
RANGE COLUMNS or
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
WHERE conditions that use multiple
comparisons between (different) column values and
constants, such as
a = 10 AND b >
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
... TRUNCATE PARTITION statement. Executing the
statement deletes rows without affecting the structure of
the table. The partitions named in the
PARTITION clause do not have to be contiguous.
Key caches are now supported for indexes on partitioned
MyISAM tables, using the
CACHE INDEX and
INTO CACHE statements. 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
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
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.10.4, “Metadata Locking”.
IPv6 support. MySQL Server can accept TCP/IP connections from clients connecting over IPv6. See Section 5.1.9, “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 BY sorting in
MySQL 5.5 is deprecated. To achieve a specific
sort order of grouped results, it is preferable to use an
ORDER BY clause.
BY sorting 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) data type.
YEAR(2) columns in existing tables are
treated as before, but
YEAR(2) in new or
altered tables are converted to
For more information, see
Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.
SHOW AUTHORS and
--ignore-builtin-innodb server 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.
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.
record_buffer system variable (use
sql_log_update system variable.
table_type system variable (use
FRAC_SECOND modifier for the
TIMESTAMPADD() function (use
SHOW TABLE TYPES SQL statement (use
SHOW PLUGIN SQL statement (use
data type: The ability to specify a display width of
N (use without
--default-table-type server option (use
--log-long-format server option.
--log-update server option.
server options to set replication parameters (use the
CHANGE MASTER TO statement
--safe-show-database server option.
--sql-bin-update-same server option.
--warnings server option (use
--no-named-commands option for
--no-pager option for
--no-tee option for
--position option for
--all option for
--first-slave option for
--config-file option for
general-purpose options for setting program variables (use
--with-pstack option for
configure and the
--enable-pstack option for