We proudly announce General Availability of MySQL 8.0. Download now! MySQL 8.0 is an extremely exciting new version of the world’s most popular open source database with improvements across the board. Some key enhancements include:

1. SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.
   
2. JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.
3. GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes,  spatial indexes,  and spatial functions.
   
4. Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary. Powered by InnoDB!
   
5. Observability Significant enhancements to Performance Schema, Information Schema, Configuration Variables, and Error Logging.
6. Manageability Remote management, Undo tablespace management, and new instant DDL.
7. Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, and more.
8. Performance InnoDB is significantly better at Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. Added Resource Group feature to give users an option optimize for specific workloads on specific hardware by mapping user threads to CPUs.

The above represents some of the highlights and I encourage you to further drill into the complete series of Milestone blog posts—8.0.0, 8.0.1, 8.0.2, 8.0.3, and 8.0.4 —and even further down in to the individual worklogs with their specifications and implementation details. Or perhaps you prefer to just look at the source code at github.com/mysql.

Developer features

MySQL Developers want new features and MySQL 8.0 delivers many new and much requested features in areas such as SQL, JSON, Regular Expressions, and GIS. Developers also want to be able to store Emojis, thus UTF8MB4 is now the default character set in 8.0. Finally there are improvements in Datatypes, with bit-wise operations on BINARY datatypes and improved IPv6 and UUID functions.

SQL

Window Functions

MySQL 8.0 delivers SQL window functions.   Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g. COUNT or SUM. But where a grouped aggregate collapses this set of rows into a single row, a window function will perform the aggregation for each row in the result set.

Window functions come in two flavors: SQL aggregate functions used as window functions and specialized window functions. This is the set of aggregate functions in MySQL that support windowing: COUNT, SUM, AVG, MIN, MAX, BIT_OR, BIT_AND, BIT_XOR, STDDEV_POP (and its synonyms STD, STDDEV), STDDEV_SAMP, VAR_POP (and its synonym VARIANCE) and VAR_SAMP. The set of specialized window functions are: RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD and LAG

Support for window functions (a.k.a. analytic functions) is a frequent user request. Window functions have long been part of standard SQL (SQL 2003). See blog post by Dag Wanvik here as well as blog post by Guilhem Bichot here.

Common Table Expression

MySQL 8.0 delivers [Recursive] Common Table Expressions (CTEs).  Non-recursive CTEs can be explained as “improved derived tables” as it allow the derived table to be referenced more than once. A recursive CTE is a set of rows which is built iteratively: from an initial set of rows, a process derives new rows, which grow the set, and those new rows are fed into the process again, producing more rows, and so on, until the process produces no more rows. CTE is a commonly requested SQL feature, see for example feature request 16244 and 32174 . See blog posts by Guilhem Bichot here, here, here, and here.

NOWAIT and SKIP LOCKED

MySQL 8.0 delivers NOWAIT and SKIP LOCKED alternatives in the SQL locking clause. Normally, when a row is locked due to an UPDATE or a SELECT ... FOR UPDATE, any other transaction will have to wait to access that locked row. In some use cases there is a need to either return immediately if a row is locked or ignore locked rows. A locking clause using NOWAIT will never wait to acquire a row lock. Instead, the query will fail with an error. A locking clause using SKIP LOCKED will never wait to acquire a row lock on the listed tables. Instead, the locked rows are skipped and not read at all. NOWAIT and SKIP LOCKED are frequently requested SQL features. See for example feature request 49763 . We also want to say thank you to Kyle Oppenheim for his code contribution! See blog post by Martin Hansson here.

Descending Indexes

MySQL 8.0 delivers support for indexes in descending order. Values in such an index are arranged in descending order, and we scan it forward. Before 8.0, when a user create a descending index, we created an ascending index and scanned it backwards. One benefit is that forward index scans are faster than backward index scans. Another benefit of a real descending index is that it enables us to use indexes instead of filesort for an ORDER BY clause with mixed ASC/DESC sort key parts. Descending Indexes is a frequently requested SQL feature. See for example feature request 13375 . See blog post by Chaithra Gopalareddy  here.

GROUPING

MySQL 8.0  delivers GROUPING(), SQL_FEATURE T433. The GROUPING() function distinguishes super-aggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP produce super-aggregate rows where the set of all values is represented by null. Using the GROUPING() function, you can distinguish a null representing the set of all values in a super-aggregate row from a NULL in a regular row. GROUPING is a frequently requested SQL feature. See feature requests 3156 and 46053. Thank you to Zoe Dong and Shane Adams for code contributions in feature request 46053 ! See blog post by Chaithra Gopalareddy  here.

Optimizer Hints

In 5.7 we introduced a new hint syntax for optimizer hints. With the new syntax, hints can be specified directly after the SELECT | INSERT | REPLACE | UPDATE | DELETE keywords in an SQL statement, enclosed in /*+ */ style comments. (See 5.7 blog post by Sergey Glukhov here). In MySQL 8.0 we complete the picture by fully utilizing this new style:

• MySQL 8.0 adds hints for INDEX_MERGE and NO_INDEX_MERGE. This allows the user to control index merge behavior for an individual query without changing the optimizer switch.
• MySQL 8.0 adds hints for JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, and JOIN_SUFFIX. This allows the user to control table order for the join execution.
• MySQL 8.0 adds a hint called SET_VAR.  The SET_VAR hint will set the value for a given system variable for the next statement only. Thus the value will be reset to the previous value after the statement is over. See blog post by Sergey Glukhov here.

We prefer the new style of optimizer hints as preferred over the old-style hints and setting of optimizer_switch values. By not being inter-mingled with SQL, the new hints can be injected in many places in a query string. They also have clearer semantics in being a hint (vs directive).

JSON

MySQL 8.0 adds new JSON functions and improves performance for sorting and grouping JSON values.

Extended Syntax for Ranges in JSON path expressions

MySQL 8.0 extends the syntax for ranges in JSON path expressions. For example SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]'); results in [2, 3, 4]. The new syntax introduced is a subset of the SQL standard syntax, described in SQL:2016, 9.39 SQL/JSON path language: syntax and semantics. See also Bug#79052 reported by Roland Bouman.

JSON Table Functions

MySQL 8.0 adds JSON table functions which enables the use of the SQL machinery for JSON data. JSON_TABLE() creates a relational view of JSON  data. It maps the result of a JSON data evaluation into relational rows and columns. The user can query the result returned by the function as a regular relational table using SQL, e.g. join, project, and aggregate.

JSON Aggregation Functions

MySQL 8.0 adds the aggregation functions JSON_ARRAYAGG() to generate JSON arrays and JSON_OBJECTAGG() to generate JSON objects . This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object. See blog post by Catalin Besleaga here.

JSON Merge Functions

The JSON_MERGE_PATCH() function implements the semantics of JavaScript (and other scripting languages) specified by RFC7396, i.e. it removes duplicates by precedence of the second document. For example, JSON_MERGE('{"a":1,"b":2 }','{"a":3,"c":4 }'); # returns {"a":3,"b":2,"c":4}.

The JSON_MERGE_PRESERVE() function has the semantics of JSON_MERGE() implemented in MySQL 5.7 which preserves all values, for example  JSON_MERGE('{"a": 1,"b":2}','{"a":3,"c":4}'); # returns {"a":[1,3],"b":2,"c":4}.

The existing JSON_MERGE() function is deprecated in MySQL 8.0 to remove ambiguity for the merge operation. See also proposal in Bug#81283 and blog post by Morgan Tocker here.

JSON Pretty Function

MySQL 8.0 adds a JSON_PRETTY() function in MySQL. The function accepts either a JSON native data-type or string representation of JSON and returns a JSON formatted string in a human-readable way with new lines and indentation.

JSON Size Functions

MySQL 8.0 adds JSON functions related to space usage for a given JSON object. The JSON_STORAGE_SIZE() returns the actual size in bytes for a JSON datatype.  The JSON_STORAGE_FREE() returns the free space of a JSON binary type in bytes, including fragmentation and padding saved for inplace update.

JSON Improved Sorting

MySQL 8.0 gives better performance for sorting/grouping JSON values by using variable length sort keys. Preliminary benchmarks shows from 1.2 to 18 times improvement in sorting, depending on use case.

JSON Partial Update

MySQL 8.0 adds support for partial update for the JSON_REMOVE(), JSON_SET() and JSON_REPLACE() functions.  If only some parts of a JSON document are updated, we want to give information to the handler about what was changed, so that the storage engine and replication don’t need to write the full document. In a replicated environment, it cannot be guaranteed that the layout of a JSON document is exactly the same on the slave and the master, so the physical diffs cannot be used to reduce the network I/O for row-based replication. Thus, MySQL 8.0 provides logical diffs that row-based replication can send over the wire and reapply on the slave. See blog post by Knut Anders Hatlen here.

GIS

MySQL 8.0 delivers geography support. This includes meta-data support for Spatial Reference System (SRS), as well as SRS aware spatial datatypes,  spatial indexes,  and spatial functions. In short, MySQL 8.0 understands latitude and longitude coordinates on the earth’s surface and can, for example, correctly calculate the distances between two points on the earths surface in any of the about 5000 supported spatial reference systems.

Spatial Reference System (SRS)

The ST_SPATIAL_REFERENCE_SYSTEMS information schema view provides information about available spatial reference systems for spatial data. This view is based on the SQL/MM (ISO/IEC 13249-3) standard. Each spatial reference system is identified by an SRID number. MySQL 8.0 ships with about 5000 SRIDs from the EPSG Geodetic Parameter Dataset, covering georeferenced ellipsoids and 2d projections (i.e. all 2D spatial reference systems).

SRID aware spatial datatypes

Spatial datatypes can be attributed with the spatial reference system definition, for example with SRID 4326 like this: CREATE TABLE t1 (g GEOMETRY SRID 4326); The SRID is here a SQL type modifier for the GEOMETRY datatype. Values inserted into a column with an SRID property must be in that SRID. Attempts to insert values with other SRIDs results in an exception condition being raised. Unmodified types, i.e., types with no SRID specification, will continue to accept all SRIDs, as before.

MySQL 8.0 adds the INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS view as specified in SQL/MM Part 3, Sect. 19.2. This view will list all GEOMETRY columns in the MySQL instance and for each column it will list the standard SRS_NAME , SRS_ID , and GEOMETRY_TYPE_NAME.

SRID aware spatial indexes

Spatial indexes can be created on spatial datatypes. Columns in spatial indexes must be declared NOT NULL. For example like this: CREATE TABLE t1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX(g));

Columns with a spatial index should have an SRID type modifier to allow the optimizer to use the index. If a spatial index is created on a column that doesn’t have an SRID type modifier, a warning is issued.

SRID aware spatial functions

MySQL 8.0 extends spatial functions such as  ST_Distance() and ST_Length() to detect that its parameters are in a geographic (ellipsoidal) SRS and to compute the distance on the ellipsoid. So far, ST_Distance and spatial relations such as ST_Within, ST_Intersects, ST_Contains, ST_Crosses, etc.  support geographic computations. The behavior of each ST function is as defined in SQL/MM Part 3 Spatial.

Character Sets

MySQL 8.0 makes UTF8MB4 the default character set. SQL performance – such as sorting UTF8MB4 strings  – has been improved by a factor of 20 in 8.0 as compared to  5.7. UTF8MB4 is the dominating character encoding for the web, and this move will make life easier for the vast majority of MySQL users.

• The default character set has changed from latin1 to utf8mb4 and the default collation has changed from latin1_swedish_ci to utf8mb4_800_ci_ai.
• The changes in defaults applies to libmysql and server command tools as well as the server itself.
• The changes are also reflected in MTR tests, running with new default charset.
• The collation weight and case mapping are based on Unicode 9.0.0 , announced by the Unicode committee on Jun 21, 2016.
• The 21 language specific case insensitive collations available for latin1 (MySQL legacy) have been implemented for  utf8mb4 collations, for example the Czech collation becomes utf8mb4_cs_800_ai_ci. See complete list in WL#9108 . See blog post by Xing Zhang here .
• Added support for case and accent sensitive collations. MySQL 8.0 supports all 3 levels of collation weight defined by DUCET (Default Unicode Collation Entry Table). See blog post by Xing Zhang here.
• Japanese utf8mb4_ja_0900_as_cs collation for utf8mb4 which sorts characters by using three levels’ weight. This gives the correct sorting order for Japanese. See blog post by Xing Zhang here.
• Japanese with additional kana sensitive feature, utf8mb4_ja_0900_as_cs_ks,  where ‘ks’ stands for ‘kana sensitive’. See blog post by Xing Zhang here.
• Changed all new collations, from Unicode 9.0.0 forward, to be NO PAD instead of PAD STRING, ie., treat spaces at the end of a string like any other character. This is done to improve consistency and performance. Older collations are left in place.

See also blog posts by Bernt Marius Johnsen here, here and here.

Datatypes

Bit-wise operations on binary data types

MySQL 8.0 extends the bit-wise operations (‘bit-wise AND’, etc) to also work with [VAR]BINARY/[TINY|MEDIUM|LONG]BLOB. Prior to 8.0 bit-wise operations were only supported for integers. If you used bit-wise operations on binaries the arguments were implicitly cast to BIGINT (64 bit) before the operation, thus possibly losing bits. From 8.0 and onward bit-wise operations work for all BINARY and BLOB data types, casting arguments such that bits are not lost.

IPV6 manipulation

MySQL 8.0 improves the usability of IPv6 manipulation by supporting bit-wise operations on BINARY data types. In MySQL 5.6 we introduced  the INET6_ATON() and INET6_NTOA() functions which convert IPv6 addresses between text form like 'fe80::226:b9ff:fe77:eb17' and VARBINARY(16). However, until now we could not combine these IPv6 functions with  bit-wise operations since such operations would – wrongly – convert output to BIGINT. For example, if we have an IPv6 address and want to test it against a network mask, we can now use  INET6_ATON(address)
& INET6_ATON(network) because INET6_ATON() correctly returns the VARBINARY(16) datatype (128 bits). See blog post by Catalin Besleaga here.

UUID manipulations

MySQL 8.0 improves the usability of UUID manipulations by implementing three new SQL functions: UUID_TO_BIN(), BIN_TO_UUID(), and IS_UUID(). The first one converts from UUID formatted text to VARBINARY(16), the second one from VARBINARY(16) to UUID formatted text, and the last one checks the validity of an UUID formatted text. The UUID stored as a VARBINARY(16) can be indexed using functional indexes. The functions UUID_TO_BIN() and UUID_TO_BIN() can also shuffle the time-related bits and move them at the beginning making it index friendly and avoiding the random inserts in the B-tree, this way reducing the insert time. The lack of such functionality has been mentioned as one of the drawbacks of using UUID’s. See blog post by Catalin Besleaga here.

Cost Model

Query Optimizer Takes Data Buffering into Account

MySQL 8.0 chooses query plans based on knowledge about whether data resides in-memory or on-disk. This happens automatically, as seen from the end user there is no configuration involved. Historically, the MySQL cost model has assumed data to reside on spinning disks. The cost constants associated with looking up data in-memory and on-disk are now different, thus, the optimizer will choose more optimal access methods for the two cases, based on knowledge of the location of data. See blog post by Øystein Grøvlen here.

Optimizer Histograms

MySQL 8.0 implements histogram statistics. With Histograms, the user can create statistics on the data distribution for a column in a table, typically done for non-indexed columns, which then will be used by the query optimizer in finding the optimal query plan. The primary use case for histogram statistics is for calculating the selectivity (filter effect) of predicates of the form “COLUMN operator CONSTANT”.

The user creates a histogram by means of the ANALYZE TABLE syntax which has been extended to accept two new clauses: UPDATE HISTOGRAM ON column [, column] [WITH n BUCKETS] and DROP HISTOGRAM ON column [, column]. The number of buckets is optional, the default is 100. The histogram statistics are stored in the dictionary table “column_statistics” and accessible through the view information_schema.COLUMN_STATISTICS. The histogram is  stored as a JSON object due to the flexibility of the JSON datatype. ANALYZE TABLE  will automatically decide whether to sample the base table or not, based on table size. It will also decide whether to build a singleton or a equi-height histogram based on the data distribution and the number of buckets specified. See blog post by Erik Frøseth here.

Regular Expressions

MySQL 8.0 supports regular expressions for UTF8MB4 as well as new functions like REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR().  The system variables regexp_stack_limit (default 8000000 bytes) and regexp_time_limit (default 32 steps) have been added to control the execution. The REGEXP_REPLACE()  function is one of the most requested features by the MySQL community, for example see feature request reported as BUG #27389 by Hans Ginzel. See also blog posts by Martin Hansson here and Bernt Marius Johnsen here.

Dev Ops features

Dev Ops care about operational aspects of the database, typically about reliability, availability, performance, security, observability, and manageability. High Availability comes with MySQL InnoDB Cluster and MySQL Group Replication which will be covered by a separate blog post. Here follows what 8.0 brings to the table in the other categories.

Reliability

MySQL 8.0 increases the overall reliability of MySQL because :

1. MySQL 8.0 stores its meta-data into InnoDB, a proven transactional storage engine.  System tables such as Users and Privileges  as well as Data Dictionary tables now reside in InnoDB.
2. MySQL 8.0 eliminates one source of potential inconsistency.  In 5.7 and earlier versions there are essentially two data dictionaries, one for the Server layer and one for the InnoDB layer, and these can get out of sync in some crashing scenarios.   In 8.0 there is only one data dictionary.
3. MySQL 8.0 ensures atomic, crash safe DDL. With this the user is guaranteed that any DDL statement will either be executed fully or not at all. This is particularly important in a replicated environment, otherwise there can be scenarios where masters and slaves (nodes) get out of sync, causing data-drift.

This work is done in the context of the new, transactional data dictionary. See blog posts by Staale Deraas here and here.

Observability

Information Schema (speed up)

MySQL 8.0 reimplements Information Schema. In the new implementation the Information Schema tables are simple views on data dictionary tables stored in InnoDB. This is by far more efficient than the old implementation with up to 100 times speedup. This makes Information Schema practically usable by external tooling. See blog posts by  Gopal Shankar here and here , and the  blog post by Ståle Deraas here.

Performance Schema (speed up)

MySQL 8.0 speeds up performance schema queries by adding more than 100 indexes on performance schema tables.  The indexes on performance schema tables are predefined. They cannot be deleted,added or altered. A performance schema index is implemented as a filtered scan across the existing table data, rather than a traversal through a separate data structure. There are no B-trees or hash tables to be constructed, updated or otherwise managed. Performance Schema tables indexes behave like hash indexes in that a) they quickly retrieve the desired rows, and b) do not provide row ordering, leaving the server to sort the result set if necessary. However, depending on the query, indexes obviate the need for a full table scan and will return a considerably smaller result set. Performance schema indexes are visible with SHOW INDEXES and are represented in the EXPLAIN output for queries that reference indexed columns. See comment from Simon Mudd. See blog post by Marc Alff here.

Configuration Variables

MySQL 8.0 adds useful information about configuration variables, such as the variable name, min/max values, where the current value came from,  who made the change and when it was made. This information is found in a new performance schema table called  variables_info. See blog post by  Satish Bharathy here.

Client Error Reporting – Message Counts

MySQL 8.0 makes it possible to look at aggregated counts of client  error messages reported by the server. The user can look at statistics from 5 different tables: Global count, summary per thread, summary per user, summary per host, or summary per account. For each error message the user can see the number of errors raised, the number of errors handled by the SQL exception handler, “first seen” timestamp, and “last seen” timestamp. Given the right privileges the user can either SELECT from these tables or TRUNCATE to reset statistics. See blog post by Mayank Prasad here.

Statement Latency  Histograms

MySQL 8.0 provides performance schema histograms of statements latency, for the purpose of better visibility of query response times. This work also computes “P95”, “P99” and “P999” percentiles from collected histograms. These percentiles can be used as indicators of quality of service. See blog post by Frédéric Descamps here.

Data Locking Dependencies Graph

MySQL 8.0 instruments data locks in the performance schema. When transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. The added instrumentation exposes which data is locked (R), who owns the lock (A), and who is waiting for the data (B). See blog post by Frédéric Descamps here.

Digest Query Sample

MySQL 8.0 makes some changes to the events_statements_summary_by_digest performance schema table to capture a full example query and some key information about this query example. The column QUERY_SAMPLE_TEXT is added to capture a query sample so that users can run EXPLAIN on a real query and to get a query plan. The column QUERY_SAMPLE_SEEN is added  to capture the query sample timestamp. The column QUERY_SAMPLE_TIMER_WAIT is added to capture the query sample execution time. The columns FIRST_SEEN and LAST_SEEN  have been modified to use fractional seconds. See blog post by Frédéric Descamps here.

Meta-data about Instruments

MySQL 8.0  adds meta-data such as properties, volatility, and documentation to the performance schema table  setup_instruments. This read only meta-data act as online documentation for instruments, to be looked at by users or tools. See blog post by Frédéric Descamps here.

Error Logging

MySQL 8.0 delivers a major overhaul of the MySQL error log. From a software architecture perspective the error log is made a component in the new  service infrastructure. This means that advanced users can write their own error log implementation if desired. Most users will not want to write their own error log implementation but still want some flexibility in what to write and where to write it.  Hence, 8.0 offers users facilities to add sinks (where) and filters (what).  MySQL 8.0 implements a filtering service (API) and a default filtering service implementation (component). Filtering here means to suppress certain log messages (selection) and/or fields within a given log message (projection). MySQL 8.0 implements a log writer service (API) and a default log writer service implementation (component).  Log writers accept a log event and write it to a log. This log can be a classic file, syslog, EventLog and a new JSON log writer.

By default, without any configuration, MySQL 8.0 delivers many out-of-the-box error log improvements such as:

• Error numbering: The format is a number in the 10000 series preceded by “MY-“, for example “MY-10001”. Error numbers will be stable in a GA release, but the corresponding error texts are allowed to change (i.e. improve) in maintenance releases.
• System messages: System messages are written to the error log as [System] instead of [Error], [Warning], [Note]. [System] and [Error] messages are printed regardless of verbosity and cannot be suppressed.  [System] messages are only used in a few places, mainly associated with major state transitions such as starting or stopping the server.
• Reduced verbosity: The default of log_error_verbosity changes from 3 (Notes) to 2 (Warning). This makes MySQL 8.0 error log less verbose by default.
• Source Component: Each message is annotated with one of three values [Server], [InnoDB], [Replic] showing which sub-system the message is coming from.

This is what is written to the error log  in 8.0 GA after startup :

The introduction of error numbering in the error log allows MySQL to improve an error text in upcoming maintenance releases (if needed) while keeping the error number (ID) unchanged.   Error numbers also act as the basis for filtering/suppression  and internationalization/localization.

Manageability

INVISIBLE Indexes

MySQL 8.0 adds the capability of toggling the visibility of an index (visible/invisible). An invisible index is not considered by the optimizer when it makes the query execution plan. However, the index is still maintained in the background so it is cheap to make it visible again. The purpose of this is for a DBA / DevOp to determine whether an index can be dropped or not. If you suspect an index of not being used you first make it invisible, then monitor query performance, and finally remove the index if no query slow down is experienced. This feature has been asked for by many users, for example through Bug#70299. See blog post by Martin Hansson here.

Flexible Undo Tablespace Management

MySQL 8.0 gives the user full control over Undo tablespaces, i.e. how many tablespaces, where are they placed, and how many rollback segments in each.

1. No more Undo log in the System tablespace. Undo log is migrated out of the System tablespace and into Undo tablespaces during upgrade. This gives an upgrade path for existing 5.7 installation using the system tablespace for undo logs.
2. Undo tablespaces can be managed separately from the System tablespace. For example, Undo tablespaces can be put on fast storage.
3. Reclaim space taken by unusually large transactions (online). A minimum of two Undo tablespaces are created to allow for tablespace truncation. This allows InnoDB to shrink the undo tablespace because one Undo tablespace can be active while the other is truncated.
4. More rollback segments results in less contention. The  user might choose to have up to 127 Undo tablespaces, each one having up to 128 rollback segments. More rollback segments mean that concurrent transactions are more likely to use separate rollback segments for their undo logs which results in less contention for the same resources.

See blog post by Kevin Lewis here.

SET PERSIST for global variables

MySQL 8.0 makes it possible to persist global, dynamic server variables. Many server variables are both GLOBAL and DYNAMIC and can be reconfigured while the server is running. For example: SET GLOBAL sql_mode='STRICT_TRANS_TABLES'; However, such settings are lost upon a server restart.

This work makes it possible to write SET PERSIST sql_mode='STRICT_TRANS_TABLES'; The effect is that the setting will survive a server restart. There are many usage scenarios for this functionality but most importantly it gives a way to manage server settings when editing the configuration files is inconvenient or not an option. For example in some hosted environments you don’t have file system access, all that you have is the ability to connect to one or more servers. As for SET GLOBAL you need the super privilege for SET PERSIST.

There is also the RESET PERSIST command. The RESET PERSIST command has the semantic of removing the configuration variable from the persist configuration, thus converting it to have similar behavior as SET GLOBAL.

MySQL 8.0 allows SET PERSIST to set most read-only variables as well, the new values will here take effect at the next server restart.  Note that a small subset of read-only variables are left intentionally not settable. See blog post by  Satish Bharathy here.

Remote Management

MySQL 8.0 implements an SQL RESTART command. The purpose is to enable remote management of a MySQL server over an SQL connection, for example to set a non-dynamic configuration variable by SET PERSIST followed by a RESTART.  See blog post MySQL 8.0: changing configuration easily and cloud friendly !  by Frédéric Descamps.

Rename Tablespace (SQL DDL)

MySQL 8.0 implements ALTER TABLESPACE s1 RENAME TO s2; A shared/general tablespace is a user-visible entity which users can CREATE, ALTER, and DROP. See also Bug#26949, Bug#32497, and Bug#58006.

Rename Column (SQL DDL)

MySQL 8.0 implements ALTER TABLE ... RENAME COLUMN old_name TO new_name;This is an improvement over existing syntax ALTER TABLE <table_name> CHANGE … which requires re-specification of all the attributes of the column. The old/existing syntax has the disadvantage that all the column information might not be available to the application trying to do the rename. There is also a risk of accidental data type change in the old/existing syntax which might result in data loss.

Security features

New Default Authentication Plugin

MySQL 8.0 changes the default authentication plugin from mysql_native_password to caching_sha2_password. Correspondingly, libmysqlclient will  use caching_sha2_password as the default authentication mechanism, too. The new  caching_sha2_password  combines better security (SHA2 algorithm) with high performance (caching). The general direction is that we recommend all users to use TLS/SSL for all  their network communication. See blog post by Harin Vadodaria here.

OpenSSL by Default in Community Edition

MySQL 8.0 is unifying on OpenSSL as the default TLS/SSL library for both MySQL Enterprise Edition and MySQL Community Edition.  Previously, MySQL Community Edition used YaSSL. Supporting OpenSSL in the MySQL Community Edition has been one of the most frequently requested features. See blog post by Frédéric Descamps here.

OpenSSL is Dynamically Linked

MySQL 8.0 is linked dynamically with OpenSSL. Seen from the MySQL Repository users perspective , the MySQL packages depends on the OpenSSL files provided by the Linux system at hand. By dynamically linking, OpenSSL updates can be applied upon availability without requiring a MySQL upgrade or patch. See blog post by Frédéric Descamps here.

Encryption of Undo and Redo log

MySQL 8.0 implements data-at-rest encryption of UNDO and REDO logs. In 5.7 we introduced Tablespace Encryption for InnoDB tables stored in file-per-table tablespaces. This feature provides at-rest encryption for physical tablespace data files. In 8.0 we extend this to include UNDO and REDO logs.  See documentation here.

SQL roles

MySQL 8.0 implements SQL Roles. A role is a named collection of privileges. The purpose is to simplify the user access right management. One can grant roles to users, grant privileges to roles, create roles, drop roles, and decide what roles are applicable during a session. See blog post by Frédéric Descamps here.

Allow grants and revokes for PUBLIC

MySQL 8.0 introduces the configuration variable mandatory-roles which can be used for automatic assignment and granting of default roles when new users are created. Example: role1@%,role2,role3,role4@localhost.  All the specified roles are always considered granted to every user and they can’t be revoked. These roles still require activation unless they are made into default roles. When the new server configuration variable activate-all-roles-on-login is set to “ON”, all granted roles are always activated after the user has authenticated.

Breaking up the super privileges

MySQL 8.0  defines a set of new granular privileges for various aspects of what SUPER is used for in previous releases. The purpose is to limit user access rights to what is needed for the job at hand and nothing more. For example BINLOG_ADMIN, CONNECTION_ADMIN, and ROLE_ADMIN.

Authorization model to manage XA-transactions

MySQL 8.0 introduces a new system privilege XA_RECOVER_ADMIN which controls the capability to execute the statement XA RECOVER. An attempt to do XA RECOVER by a user who wasn’t granted the new system privilege XA_RECOVER_ADMIN will cause an error.

Password rotation policy

MySQL 8.0 introduces restrictions on password reuse. Restrictions can be configured at global level as well as individual user level. Password history is kept secure because it may give clues about habits or patterns used by individual users when they change their password. The password rotation policy comes in addition to other, existing mechanisms such as the password expiration policy and allowed password policy. See Password Management.

Slow down brute force attacks on user passwords

MySQL 8.0 introduces a delay in the authentication process based on consecutive unsuccessful login attempts. The purpose is to slow down brute force attacks on user passwords. It is possible to configure the number of consecutive unsuccessful attempts before the delay is introduced and the maximum amount of delay introduced.

Retire skip-grant-tables

MySQL 8.0  disallows remote connections when the server is started with –skip-grant-tables.  See also Bug#79027 reported by Omar Bourja.

Add mysqld_safe-functionality to server

MySQL 8.0 implement parts of the logic currently found in the mysqld_safe script inside the server. The work improves server usability in some scenarios for example when using the --daemonize startup option. The work also make users less dependent upon the mysqld_safe script, which we hope to remove in the future. It also fixes Bug#75343 reported by Peter Laursen.

Performance

MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. In addition, the new Resource Group feature gives users an option to optimize for specific workloads on specific hardware by mapping user threads to CPUs.

Scaling Read/Write Workloads

Better Performance upon High Contention Loads (“hot rows”)

MySQL 8.0 significantly improves the performance for high contention workloads. A high contention workload occurs when multiple transactions are waiting for a lock on the same row in a table,  causing queues of waiting transactions. Many real world workloads are not smooth over for example a day but might have bursts at certain hours (Pareto distributed). MySQL 8.0 deals much better with such bursts both in terms of transactions per second, mean latency, and 95th percentile latency. The benefit to the end user is better hardware utilization (efficiency) because the system needs less spare capacity and can thus run with a higher average load. The original patch was contributed by Jiamin Huang (Bug#84266). Please study the Contention-Aware Transaction Scheduling (CATS) algorithm and read the MySQL blog post by Jiamin Huang and Sunny Bains here.

MySQL 8.0 introduces global Resource Groups to MySQL. With Resource Groups, DevOps/DBAs can manage the mapping between user/system threads and CPUs. This can be used to split workloads across CPUs to obtain better efficiency and/or performance in some use cases. Thus, Resource Groups adds a tool to the DBA toolbox,  a tool which can help the DBA to increase hardware utilization or to increase query stability. As an example, with a Sysbench RW workload running on a Intel(R) Xeon (R) CPU E7-4860 2.27 GHz 40 cores-HT box we doubled the overall throughput  by limiting the Write load to 10 cores. Resource Groups is a fairly advanced tool which requires skilled DevOps/DBA to be used effectively as effects will vary with type of load and with the hardware at hand.

Other Features