Full understanding of the new features of MySQL 8.0

First, the function added in MySQL 8.0

1, the new system dictionary table

Integrated transaction data dictionary for storing information about database objects, all metadata is stored using the InnoDB engine

2, support for DDL atomic operations

The DDL of the InnoDB table supports transaction integrity, either to be successful or to roll back, to write the DDL operation rollback log to the data dictionary data dictionary table mysql.innodb_ddl_log for rollback operations

3, security and user management

Added caching_sha2_password authentication plugin and is the default authentication plugin. Enhanced performance and security

Permissions support role

New password history feature restricts reuse of previous passwords

4, support for resource management

Supports creation and management of resource groups and allows the allocation of threads run by the server to specific groups for execution by threads based on the resources available to the resource group

5, innodb enhancements

Self-enhanced optimization to fix MySQL bug#199, this bug causes MySQL to take the maximum self-increment on the table as the maximum when the DB is restarted, and the next allocation is to allocate max(id)+1 if it is an archive table or After the data is deleted in other modes, the DB system restarts, and self-enhancement may be reused.

Added INFORMATION_SCHEMA.INNODB_CACHED_INDEXES to see the index pages of each index cache in the InnoDB buffer pool

InnoDB temporary tables will be created in the shared temporary table space ibtmp1

InnoDB supports NOWAIT and SKIP LOCKED for SELECT … FOR SHARE and SELECT … FOR UPDATE statements

The minimum value of innodb_undo_tablespaces is 2, and it is no longer allowed to set innodb_undo_tablespaces to 0. Min 2 ensures that rollback segments are always created in the undo tablespace, not in the system tablespace

ALTER TABLESPACE … RENAME TO syntax

Added innodb_dedicated_server to let InnoDB automatically configure innodb_buffer_pool_size according to the amount of memory detected on the server, innodb_log_file_size, innodb_flush_method

New INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view

A new dynamic configuration item, innodb_deadlock_detect, is used to disable deadlock checking, because in high-concurrency systems, when a large number of threads wait for the same lock, deadlock checking can significantly slow down the database.

Supports use of the innodb_directories option to move or restore tablespace files to a new location when the server is offline

6, MySQL 8.0 better support for document database and JSON

7, optimization

Invisible index, starting to support invisible index, (feeling the same as Oracle ), you can set the index to be invisible during the optimization of the SQL, the optimizer will not use the invisible index

Support descending index, DESC can be defined on the index, before the index can be reversed scan, but affect performance, and descending index can be completed efficiently

8, support RANK (), LAG (), NTILE () and other functions

9, regular expression enhancements, provide REGEXP_LIKE (), EGEXP_INSTR (), REGEXP_REPLACE (), REGEXP_SUBSTR () and other functions

10. Add a backup lock to allow DML during online backup while preventing operations that may result in inconsistent snapshots. Backup locks supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax

11, the character set The default character set changed from latin1 to utf8mb4

12, configuration file enhancement

MySQL 8.0 supports online modification of global parameter persistence. By adding the PERSIST keyword, you can persist the adjustment to a new configuration file. Restarting db can also be applied to the latest parameters. For adding the PERSIST keyword modification parameter command, the MySQL system will generate a mysqld-auto.cnf file that contains json format data. For example, execute:

Set PERSIST expire_logs_days=10 ; # memory and json files are modified, restart also effective

Set GLOBAL expire_logs_days=10 ; # only modify memory, restart lost

The system will generate a file containing the following in the data directory mysqld-auto.cnf:

{ “mysql_server”: {“expire_logs_days”: “10” } }

When my.cnf and mysqld-auto.cnf exist at the same time, the latter has a high priority.

13. Histogram

The MySQL 8.0 version started to support long-awaited histograms. The optimizer will use the column_statistics data to determine the distribution of field values ??and get a more accurate execution plan.

You can use ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS | DROP HISTOGRAM ON clo_name] to collect or delete histogram information

14, support for session-level SET_VAR dynamically adjust some of the parameters, help to improve statement performance.

Select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;

Insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

15, the adjustment of the default parameters

Adjust the default value of back_log to keep the same with max_connections and increase the connection processing capacity brought by burst traffic.

Modifying event_scheduler defaults to ON, which was previously disabled by default.

Adjust the default value of max_allowed_packet from 4M to 64M.

Adjust bin_log, log_slave_updates default is on.

Adjust the expiry date of expire_logs_days to 30 days, and the old version to 7 days. In the production environment, check this parameter to prevent the binlog from creating too much space.

Adjust innodb_undo_log_truncate to ON by default

Adjust the default value of innodb_undo_tablespaces to 2

Adjust the innodb_max_dirty_pages_pct_lwm default 10

Adjust the default value of innodb_max_dirty_pages_pct 90

Added innodb_autoinc_lock_mode default value 2

16, InnoDB performance improvement

Abandon the buffer pool mutex, split the original mutex into multiple, increase concurrent

Splitting the two mutexes LOCK_thd_list and LOCK_thd_remove can increase the threading efficiency by approximately 5%.

17, line buffer

The MySQL8.0 optimizer can estimate the number of rows to be read, so it can provide the storage engine with an appropriately sized row buffer to store the required data. Mass performance of continuous data scans will benefit from larger record buffers

18, improve the scanning performance

Improving the performance of InnoDB range queries improves the performance of full-table queries and range queries by 5-20%.

19, the cost model

The InnoDB buffer can estimate how many tables and indexes are in the cache, which allows the optimizer to choose the access mode to know if the data can be stored in memory or must be stored on disk.

20, refactoring SQL analyzer

Improve the SQL analyzer. The old analyzer has serious limitations due to its grammatical complexity and top-down analysis, making it difficult to maintain and extend.

Second, MySQL 8.0 in the abandoned features

• Deprecated validate_password plugin
• Deprecation of ALTER TABLESPACE and DROP TABLESPACE ENGINE Clauses
• Discard JSON_MERGE() -> JSON_MERGE_PRESERVE() instead
• Abandoned have_query_cache system variable

Third, MySQL 8.0 is removed

Query cache functionality was removed and related system variables were also removed

Mysql_install_db is replaced by mysqld –initialize or –initialize-insecure

The INNODB_LOCKS and INNODB_LOCK_WAITS tables under INFORMATION_SCHEMA have been deleted. Replaced with Performance Schema data_locks and data_lock_waits tables

Four tables under INFORMATION_SCHEMA removed: GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS

InnoDB no longer supports compressed temporary tables.

PROCEDURE ANALYSE() syntax is no longer supported

InnoDB Information Schema Views Renamed
Old the Name the Name New
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

Remove’s server option:

–temp-pool
–ignore-builtin-innodb
–des-key-file
–log-warnings
–ignore-db-dir

Remove configuration options:

Innodb_file_format
innodb_file_format_check
innodb_file_format_max
innodb_large_prefix

Remove the system variable

information_schema_stats -> information_schema_stats_expiry
ignore_builtin_innodb
innodb_support_xa
show_compatibility_56
have_crypt
DATE_FORMAT
DATETIME_FORMAT
the time_format
max_tmp_tables
global.sql_log_bin (session.sql_log_bin reserved)
log_warnings -> log_error_verbosity
multi_range_count
secure_auth
sync_frm
TX_ISOLATION -> transaction_isolation
tx_read_only -> transaction_read_only
ignore_db_dirs
the query_cache_limit
the query_cache_min_res_unit
the query_cache_size
the query_cache_type
query_cache_wlock_invalidate
innodb_undo_logs -> innodb_rollback_segments

Remove the state variable

Com_alter_db_upgrade
Slave_heartbeat_period
Slave_last_heartbeat
Slave_received_heartbeats
Slave_retried_transactions, Slave_running
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks
Innodb_available_undo_logs Status

Remove function

JSON_APPEND() –> JSON_ARRAY_APPEND()

ENCODE()

DECODE()

DES_ENCRYPT()

DES_DECRYPT()

Remove’s client option:

–ssl –ssl-verify-server-cert is deleted, with –ssl-mode = VERIFY_IDENTITY | alternative DISABLED | REQUIRED

–secure-auth

 

 

MySQL 8 official version 8.0.11 has been released. Officially stated that MySQL 8 is 2 times faster than MySQL 5.7, and it also brings a lot of improvements and faster performance!

The following is the record of the installation process of the person 2018.4.23 days. The entire process takes about an hour, and the make && make install process takes longer.

I. Environment

CentOS 7.4 64-bit Minimal Installation

II. Preparation

??1. Installation dependencies

Yum -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel

??2. Download the source package

Wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.11.tar.gz (this version comes with boost)

??Create mysql user

Groupadd mysql
useradd -r -g mysql -s /bin/false mysql

??4 create an installation directory and data directory

Mkdir -p /usr/local/mysql
mkdir -p /data/mysql

Three. Install MySQL8.0.11

??1. Extract the source package

Tar -zxf mysql-boost-8.0.11.tar.gz -C /usr/local

??2. Compile & Install

Cd /usr/local/mysql-8.0.11
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306 -DWITH_BOOST=/usr/local/ Mysql-8.0.11/boost
make && make install

3. Configure the my.cnf file

Cat /etc/my.cnf
[mysqld]
server-id=1
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql

## Please add parameters according to the actual situation

4 directory permissions modify

Chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysql
chmod 755 /usr/local/mysql -R
chmod 755 /data/mysql -R

5. Initialization

Bin/mysqld –initialize –user=mysql –datadir=/data/mysql/
bin/mysql_ssl_rsa_setup

6. Start mysql

Bin/mysqld_safe –user=mysql &

??7. Modify account password

Bin/mysql -uroot -p
mysql> alter user ‘root’@’localhost’ identified by “123456”;

Mysql> show databases;
+——————–+
| Database |
+——————- – +
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

##Add Remote Account

Mysql> create user root@’%’ identified by ‘123456’;
Query OK, 0 rows affected (0.08 sec)

????Mysql> grant all privileges on *.* to root@’%’;
????Query OK, 0 rows affected (0.04 sec)

????Mysql> flush privileges;
????Query OK, 0 rows affected (0.01 sec)

??8. Create a soft link (non-essential)

Ln -s /usr/local/mysql/bin/* /usr/local/bin/

Mysql -h 127.0.0.1 -P 3306 -uroot -p123456 -e “select version();”
mysql: [Warning] Using a password on the command line interface can be insecure.
+———- -+
| version() |
+———–+
| 8.0.11 |
+———–+

??9. Add to start (non-essential)

Cp support-files/mysql.server /etc/init.d/mysql.server

Hereby explain: MySQL official recommended to use binary installation. (The following figure is a screenshot of the official document)