What ' s New in MySQL 8.0

Since there are many c++11 features within 8.0. Requires gcc4.8 version above. The RHEL6 series default GCC is 4.7. Still check after installing gcc6.1.

The reason may be that the 6.1 version is not necessarily higher than 4.7 and is not discussed at this stage. Because it takes a long time to upgrade GCC, it does not match the test purpose. Take up the official RPM package installation. In order to achieve the rapid testing purposes.

The following new features are introduced, most of which are tested in relation to daily work. Limited time, not exhaustive, interested in self-testing.

Most of the following sources are from https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html. Part of this is derived from this. The beta version is MySQL8.0.11 GA version.

Level is limited, there are questions to advise. QQ 475982055

MySQL 8.0 Release Notes and features Ⅰ security and account management authentication encryption plug-in changes

In addition to the Sha256_password authentication plugin. A new Caching_sha2_password authentication plugin is available. The latter can use the cache to resolve latency issues when connecting.

It also supports more connection protocols and does not need to be linked to OpenSSL based on the RSA key pair's password Exchange functionality.

Comparison can be seen. The default new user in MySQL8.0 uses Caching_sha2_password instead of the native encryption policy. This can cause problems when the client connects using the original method.

Windows Nivicat Client Connectivity issues

Connect under Linux Client

This is because the default password plug-in mismatch on both sides causes the inability to decrypt. There are two ways to solve this. One is the server-side downgrade encryption method, one is the client upgrade encryption method.

The first approach is to make the security cost easier. The second is the opposite. I think the server should have global parameters to control what new users use to encrypt the plug-in. As follows:

This allows the client to connect in a regular manner. But the user who was created before will still report that error. The workaround is to modify the ALTER statement.

Note that a little bit of 5.6 of the set password has been completely eliminated. and the password function has been deleted.

The default configuration starts. After initialization starts, login with the generated password cannot do anything, but you can set the command.

Due to the strong password strength, the modification parameters are made in most cases validate_password.policy . (This has broken my heart, formerly underlined "_".) A bit of object-oriented flavor)

Summary: This means that the encryption changes cause the account connection change. You can also revert to the previous mode by default.

New role Features

As with Oracle, it is used to package permissions. Assigned to the owning user.

See: http://dev.mysql.com/doc/refman/8.0/en/create-role.html

User password aspects

MySQL previously had password strength policy, expiration time. Information about password history is now maintained, thereby restricting the reuse of previous passwords (social engineering).
exists in the Mysql.password_history table. That is, if you are upgrading from the old version to the 8.0.3 version. You need to upgrade these system tables (nonsense) at the same time.
There are several more columns in the Mysql.user table about password reuse. (The related table has been confirmed and the feature has not been tested.) )

User rights aspects

Added multiple administrative permissions.

Ⅱinnodb enhanced self-adding columns

The self-increment of the column. The self-increment counter now writes values to the redo log when each value is modified, and writes to the system table in the storage engine's private checkpoint.

This eliminates the problem of previous reboots of instance self-increment (which may also create a new competitive point (Gai the InnoDB developer)).

Btree Index aspects

The Btree index is corrupted. InnoDB writes a corruption flag to the redo log. It also checkpoint the data from the corrupted pages in memory to the system tables that are private to the storage engine.

This also contributed to the recovery. The same situation on both sides. The index is not available and does not cause the instance to be unavailable. This has largely reduced the need to use Innodb_force_recovery and Innodb_fast_shutdown before.

Improved consistency. (For the general DBA to be transparent, know that this is OK)

NoSQL operations

InnoDB memcached plug-in supports multiple get operations (get multiple key/value pairs in a single memcached query)

and scope queries. (I think this is a pretty good one, a bit like NoSQL, not just nosql.)

Need to install the daemon_memcached plug-in, which has a innodb_memcache schema, there are several tables in this schema, one of the containers used to map with the InnoDB table,

The InnoDB table is then accessed through the interface. Then there will be a 11211 port open for establishing the connection.

The benefit is that by reducing traffic between the client and the server, the ability to get multiple key/value pairs in a single memcached query can improve read performance.

For InnoDB, it also means fewer transactions and open table operations.

Deadlock detection

The new dynamic configuration option Innodb_deadlock_detect can be used to disable deadlock detection, which is turned on by default. On high-concurrency systems, deadlock detection can cause a decrease in speed when a large number of threads wait for the same lock. Sometimes, when a deadlock occurs,

Disabling deadlock detection and relying on innodb_lock_wait_timeout settings for transaction rollback can be more efficient. Remember that the previous version encountered a deadlock that automatically rolls back. The following comes from MySQL5.7, which is the same as 8.0 default.

(That is, even if the MySQL5.7 has deadlock detection, it automatically rolls back the smaller-weight transactions (except for the nesting).)

Try changing the Innodb_deadlock_detect parameter to OFF. The two worker threads will be blocked when a deadlock is encountered. The lock timeout until innodb_lock_wait_timeout is set.

The new table holds the number of pages that the INFORMATION_SCHEMA.INNODB_CACHED_INDEXES InnoDB index caches in the InnoDB buffer pool.

All InnoDB temporary tables are now created in the shared temporary tablespace ibtmp1.

Encryption features

Supports redo and undo table space Encryption.

Shared lock aspect

InnoDB in SELECT ... FOR SHARE and SELECT ... FOR UPDATE 锁定读语句上 support the option not to wait ( NOWAIT ) and Skip Lock (Skip LOCKED). This means that a shared lock has previously been added and must be released manually.

If there is no lock, return the result, if any, report the following error.

If it is skipped with a lock, there is no data.

Use according to the scene. It's all seconds back anyway. Reduces the possibility of troubleshooting database timeouts.

Different from the use of MySQL 5.7. First initialized log is concise (top three)

Restart the log as follows:

InnoDB uses its own data dictionary of the MySQL service layer and no longer retains its own data dictionary. Facilitates the atomicity of the transaction.

MySQL system tables and data dictionaries are represented in a single innodb tablespace file named Mysql.ibd in the MySQL data directory.

Previously, these tables were created in the various InnoDB tablespace files in the MySQL database directory.

Data dictionary

Data dictionaries such as Table object information are now present in the internal transaction table. Instead of the previous. frm file.

MySQL schema multi-out table: (compared to MySQL5.7)

Component

Default_roles

Global_grants

Password_history

Role_edges

The table that was removed

Proc

Ndb_binlog_index

Event

Because the storage engine layer no longer retains its own data dictionary. So the. frm file for the table does not exist either.

Atomicity of DDL

The data dictionary update that unites the above changes. Implements the atomicity of the DDL. Not available in previous versions.

Changes in the Ⅲundo table space

(There is basically no need for the DBA to configure parameters about the Undo table space)

Modifying the number of undo table spaces Online

The amount of undo tablespace can be modified online. Or, when the instance restarts, the parameters that were previously set before initialization can be used innodb_undo_tablespaces .

Default value change

innodb_undo_log_truncateis turned on by default.

innodb_undo_tablespacesThe number of tablespaces is changed from 0 to 2 by default. And set to 0 is not already allowed.

Undo default name has undoNNN becomes undo_NNN.

Parameter change

innodb_rollback_segmentsThe configuration option defines the number of rollback segments for each undo table space. Previously, this was a global setting for MySQL instances, with a single write.

The change this time is that the transaction writes the undo log when it can be written concurrently. innodb_undo_logswas removed. This parameter instead.

The innodb_available_undo_logs state variable is removed. To view available rollback segments available show VARIABLES like ' innodb_rollback_segments ';

Change parameter default value change of Ⅳbuffer pool

innodb_max_dirty_pages_pct_lwmchanged from 0 to 10

innodb_max_dirty_pages_pctfrom 75 to 90.

Control InnoDB BUFFER Pool pre-write, Brush disc action parameters change. I think it should be a hardware upgrade over the years.

InnoDB self-growing lock mode becomes 2. Statement-based is very sensitive to this, and it is clear that this is a comprehensive line-based symbol.

The use ALTER TABLESPACE ... RENAME TO of statements to rename a common table space is supported.

Automatic memory management

The new configuration option Innodb_dedicated_server can be controlled by the total server memory innodb_buffer_pool_size

innodb_log_file_size，

innodb_flush_method

Size and manner. See

Default.

After you open the parameter

INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEFLog table space information.

When the system is not in line, you can remove or dump the system table space to other machines. This also opens up a path to backup recovery. The downtime must be a bit unsatisfactory.

Extracting table Spaces

SDI (serialized dictionary information) exists in all InnoDB tablespace files except for the temporary tablespace and the undo tablespace file. The presence of SDI data provides meta-data redundancy.
If the data dictionary becomes unavailable, the Dictionary object metadata may be extracted from the tablespace file. Use the IBD2SDI tool to perform SDI extraction.

As follows: where SDI data is stored in JSON format. This tool can be used both online and offline. Look back at how to import this JSON data (MySQL high version already supports JSON).

Ⅴredo aspects of optimizing parallel writes

1. The user thread can now write concurrently to the log buffer, which was previously a serial write.

Attribute Additions

1. User threads can now add dirty pages to the flush list in the order in which they are relaxed.

2. Now the dedicated log thread is responsible for writing the log buffers to the system buffers, flushing the system buffers to the disk, writing and refreshing the redo notifications to the user thread, and maintaining the delay required to clear the list order,

and write check points. Previously, the Read,write process was responsible for background reading and writing. Now I'm kinda thinking about Oracle's DBWR process.

Some system variables have been added. Controls how the CPU waits for the refresh redo.

innodb_log_wait_for_flush_spin_hwm
innodb_log_spin_cpu_abs_lwm
innodb_log_spin_cpu_pct_hwm

Dynamically modifying log buffer

3. The innodb_log_buffer_size configuration option is now dynamic.

Ⅵ other key Features resource management

MySQL8.0 has the concept of a resource group (details click link). This means that you can create and manage a resource group.

Assign a worker thread to a specific group. For DBAs to limit and provision resources. Currently only for CPUs.

INFORMATION_SCHEMA.RESOURCE_GROUPSThe table displays information about the resource definition. PerformanceSchema  threads The table shows the resource group assignments for each thread.

There are SQL interfaces that support resource group management. These operations are not recorded in Binlog, which means they are not copied.

Character

The official suggests replacing UTF8 with utf8mb4 (UTF8 is now the alias of Utf8mb3). Default is UTF8MB4

JSON Enhancements

In view of not seeing useful, do not mention.

Optimizer enhancements

1. Support for invisible indexes. The optimizer does not use an unavailable index. The index is visible by default. This is useful when indexing is optimized. You do not need to delete an index to increase the system burden.

2. mysql now supports descending indexing: desc in the index definition is no longer ignored, causing the key values to be stored in descending order. Previously, indexes might be scanned in reverse order, but performance would be affected.

Descending indexes can be scanned sequentially, which is more efficient. Descending indexing also enables the optimizer to use multi-column indexes in the most efficient scan order to mix the ascending of some columns and the descending of other columns.

Common table expressions

This means that you can take different columns of many tables as new tables, and then do the associated unions and so on. Personally, this is great for writing SQL with a large number of subqueries, as well as row to column.

Window functions

A large number of window functions are supported.

Similar to the following data. Try the properties of the window function. Oracle has these features called analytic functions, and so on.

window functions for aggregate functions

AVG ()

Bit_and ()

Bit_or ()

Bit_xor ()

COUNT ()

MAX ()

MIN ()

Stddev_pop (), STDDEV (), STD ()

Stddev_samp ()

SUM ()

Var_pop (), VARIANCE ()

Var_samp ()

Example: If each column contains a total profit and contains the current line's country profit and. The following results

If you are using a traditional SQL implementation. Below, you have to write a bunch of table associations, aggregations.

SELECT T1. Year,t1.country,t1.product,t1.profit,t2.total_profit,t3.country_profit From ( SELECT Year,country,product,profit From Sales ) T1 Left JOIN ( SELECT SUM (profit) as Total_profit From Sales ) t2 on 1 = 1 Left JOIN ( SELECT Country,sum (profit) as Country_profit From Sales GROUP by Country ) t3 on t1.country = T3.country ORDER by Country,year,product,profit;

window function Implementation:

SELECT Year, country, product, profit, SUM (Profit) over () as Total_profit, SUM (Profit) over (PARTITION by country) as Country_profit From sales ORDER by country, year, product, profit;

window functions for non-aggregate functions

Cume_dist ()

Dense_rank ()

First_value ()

LAG ()

Last_value ()

Lead ()

Nth_value ()

NTILE ()

Percent_rank ()

RANK ()

Row_number ()

Example:

There is also a more common operation. In the above table, the profits of the same country are ranked.

As shown below:

The general implementation needs to sort the profit of each city in the union together. Too cumbersome.

window function implementation

SELECT Year,country,product,profit, Row_number () Over ( PARTITION by country ORDER by Profit DESC ) as Row_num2 From sales;

Regular expression support

Before MySQL used the Henry Spencer regular expression library to support the regular expression operator (Regexp,rlike), powerful regular expressions are now supported.

For example, the functions regexp_instr,regexp_replace and REGEXP_SUBSTR functions can be used to find matching locations and perform substring substitution and extraction, respectively.

Log records

In addition to the traditional error log. In addition, there is a JSON logger that can be loaded. To control which log components are enabled, use log_error_services system variables. See the error log for more information.

Backup lock

A new backup lock allows DML during online backup while preventing operations that may cause snapshot inconsistencies. The new backup lock is supported by Lockinstance for backup and unlock instance syntax.

Set Command Enhancements

Similar to Oracle, you can change the current set PERSIST for the next reboot, and Persist_only (change applies only the next reboot.) Unlike Oracle, it is not recorded in the SPFile parameter file. (This feature has been tested)

command line partial completion function

The more cool is that the command supports the table completion function. The object to be mended is not a table under the schema. It's the schema that corresponds

Two MySQL 8.0 deprecated

1. The Validate_password plugin has been re-implemented to use the server component infrastructure.

2, ALTERTABLESPACE and DROPTABLESPACE ENGINE clauses are deprecated.

3, the JSON_MERGE function is discarded in the 8.0.3, the use JSON_MERGE_PRESERVE() replaces.

Three MySQL 8.0 removed in the

1. The view in the following information schema is renamed

Old Name	New Name
Innodb_sys_columns	Innodb_columns
Innodb_sys_datafiles	Innodb_datafiles
Innodb_sys_fields	Innodb_fields
Innodb_sys_foreign	Innodb_foreign
Innodb_sys_foreign_cols	Innodb_foreign_cols
Innodb_sys_indexes	Innodb_indexes
Innodb_sys_tables	Innodb_tables
Innodb_sys_tablespaces	Innodb_tablespaces
Innodb_sys_tablestats	Innodb_tablestats
Innodb_sys_virtual	Innodb_virtual

2、不能使用grant 语句创建用户了。只能赋权。Identified子句移除。

 

3、transaction_isolation 和 transaction_read_only 用来替代

tx_isolation 和tx_read_only

4.因为没有.frm文件了，sync_frm系统变量移除了。 ignore_db_dirs也被移除。

5.have_query_cache仍然保持弃用状态， SQL_CACHE和SQL_NO_CACHE子句被弃用，且不起作用。这些在将来版本会被删除。

6、log_warnings被移除了，log_error_verbosity代替

 

7、加密相关的函数ENCODE() 和 DECODE()以及ENCRYPT() 等都被移除了。

 

8、存储过程的ANALYSE() 语法被移除。

 

9、INFORMATION_SCHEMA.INNODB_LOCKS和INNODB_LOCK_WAITS表被移除。使用 Performance Schema.data_locks和data_lock_waits 的表代替。

 

10、InnoDB文件格式相关配置选项已经不存在了。它们是MySQL5.1的产物，现在5.1的产品时代已经终结。

 

11、innodb_support_xa系统变量被移除，MySQL总是支持分布式事务。

What ' s New in MySQL 8.0