Improved scalability on multi-core CPUs. 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.5.1, “Scalability Improvements”.

InnoDB I/O subsystem changes enable more effective use of available I/O capacity. See Section 1.5.2, “InnoDB I/O Subsystem Changes”.

Several modifications improve operation of MySQL Server on Solaris. See Section 1.5.3, “Enhanced Solaris Support”.

There is better access to execution and performance information. Diagnostic improvements include DTrace probes, expanded SHOW ENGINE INNODB STATUS output, and a new status variable. See Section 1.5.4, “Diagnostic and Monitoring Capabilities”.

The default storage engine for new tables is InnoDB rather than MyISAM. See Section 13.6.1, “InnoDB as the Default MySQL Storage Engine”.

Support for an interface for 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 the SQL standard SIGNAL and RESIGNAL statements. See Section 12.7.8, “SIGNAL and RESIGNAL”.

Support for Performance Schema, a feature for monitoring MySQL Server execution at a low level. See Chapter 21, MySQL Performance Schema.

Support for additional Unicode character sets: utf16, utf32, and utf8mb4. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP). See Section 9.1.10, “Unicode Support”.

Enhancements to XML functionality, including a new LOAD XML statement.

Two new types of user-defined partitioning are supported: RANGE COLUMNS partitioning is an extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:

For more information, see Section 18.2.1, “RANGE Partitioning”, and Section 18.2.2, “LIST Partitioning”.

It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE ... TRUNCATE PARTITION statement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the TRUNCATE PARTITION clause do not have to be contiguous.

Key caches are now supported for indexes on partitioned MyISAM tables, using the CACHE INDEX and LOAD INDEX 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 contiguous.

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.user table. 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. For information about these capabilities, see Section 5.5.6, “Pluggable Authentication”, and Section 5.5.7, “Proxy Users”.

The TO_SECONDS() function is added. This function converts a date or datetime expression to a number of seconds since the year 0. You may use this function in partitioning expressions, and partition pruning is supported for tables defined using such expressions.

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.11, “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 log_bin_trust_routine_creators system variable (use log_bin_trust_function_creators).

The myisam_max_extra_sort_file_size system variable.

The record_buffer system variable (use read_buffer_size).

The sql_log_update system variable.

The table_type system variable (use storage_engine).

The FRAC_SECOND modifier for the TIMESTAMPADD() function.

The TYPE table option to specify the storage engine for CREATE TABLE or ALTER TABLE (use ENGINE).

The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).

The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements (use SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX).

The SHOW PLUGIN SQL statement (use SHOW PLUGINS).

The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

The BACKUP TABLE and RESTORE TABLE SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

TIMESTAMP(N) data type: The ability to specify a display width of N (use without N).

The --default-character-set and --default-collation server options (use --character-set-server and --collation-server).

The --delay-key-write-for-all-tables server option (use --delay-key-write=ALL).

The --enable-locking and --skip-locking server options (use --external-locking and --skip-external-locking).

The --log-bin-trust-routine-creators server option (use --log-bin-trust-function-creators).

The --log-long-format server option.

The --log-update server option.

The --master-xxx server options to set replication parameters (use the CHANGE MASTER TO statement instead): --master-host, --master-user, --master-password, --master-port, --master-connect-retry, --master-ssl, --master-ssl-ca, --master-ssl-capath, --master-ssl-cert, --master-ssl-cipher, --master-ssl-key.

The --safe-show-database server option.

The --skip-symlink and --use-symbolic-links server options (use --skip-symbolic-links and --symbolic-links).

The --sql-bin-update-same server option.

The --warnings server option (use --log-warnings).

The --no-named-commands option for mysql (use --skip-named-commands

The --no-pager option for mysql (use --skip-pager).

The --no-tee option for mysql (use --skip-tee).

The --position option for mysqlbinlog (use --start-position).

The --all option for mysqldump (use --create-options).

The --first-slave option for mysqldump (use --lock-all-tables).

The --config-file option for mysqld_multi (use --defaults-extra-file).

The --set-variable=var_name=value and -O var_name=value general-purpose options for setting program variables (use --var_name=value).

The --with-pstack option for configure and the --enable-pstack option for mysqld.