MySQL8.0--------up to 3 million QPS Titan Practice

Background

MySQL 8.0 official version 8.0.11 has been released, the official said MySQL 8 than MySQL 5.7 twice times faster, but also brought a lot of improvements and faster performance!

Upgrade to MySQL8.0

Upgrading from MySQL 5.7 to MySQL 8.0 only supports upgrades by using the In-place method and does not support downgrading from MySQL 8.0 to MySQL 5.7 (or downgrading from one MySQL 8.0 version to any earlier MySQL 8.0 version) 。 The only supported alternative is to back up the data before the upgrade.

New features and improvements

• Performance:

  MySQL 8.0 is twice times faster than MySQL 5.7. MySQL 8.0 brings better performance in the following areas: Read/write workloads, IO-intensive workloads, and high-competition ("Hot spot" hotspot competition issues) workloads.

• Nosql

  MySQL has been providing NoSQL storage functionality since version 5.7, and this part of the feature has also been improved in the 8.0 release. This feature eliminates the need for a standalone NoSQL document database, and the MySQL document store also provides multi-document transaction support and full ACID compliance for JSON documents in schema-less mode.

• Windowing Functions (Window Functions)

  Starting with MySQL 8.0, there is a new concept called Window function, which can be used to implement several methods of query. The window function is similar to SUM (), COUNT (), but it does not merge multiple rows of query results into one row, but instead puts the results back into multiple rows. That is, window functions do not require GROUP by

• Hide Index

  In MySQL 8.0, the index can be "hidden" and "displayed". When the index is hidden, it is not used by the query optimizer. We can use this feature for performance debugging, such as hiding an index first, and then observing its impact on the database. If the performance of the database is degraded, the index is useful, then "restore" it, and if the performance of the database does not show a change, the index is redundant and can be considered for deletion.

• Descending index

  MySQL 8.0 provides support for indexing in descending order, and the values in this index are sorted in descending order as well.

• universal table expression (Common table Expressions CTE)

  When using embedded tables in complex queries, using a CTE makes the query statement clearer.

• UTF-8 encoding

  Starting with MySQL 8, use UTF8MB4 as the default character set for MySQL.

• Json

  MySQL 8 greatly improved support for JSON, added Json_extract () functions that extract data from JSON fields based on path query parameters, and Json_arrayagg () and json_obje for grouping data into JSON arrays and objects, respectively. Ctagg () aggregation function.

• Reliability

  InnoDB now supports the atomicity of table DDL, which means that DDL on the InnoDB table can also implement transactional integrity, either fail back, or successfully commit, without the partial success of DDL, and also support the Crash-safe feature, which is stored in a single transactional data dictionary.

• High Availability (HI availability)

  The InnoDB cluster provides an integrated, native HA solution for your database.

• Security

  Improvements to OpenSSL, new default authentication, SQL role, password strength, authorization.

• Official Multi-platform

  https://dev.mysql.com/downloads/mysql/

• Linux Generic (General edition) latest version 64-bit [8.0.11]

  Https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar

Installation Practice Environment

[[email protected]_bj_master ~]# cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core)[[email protected]_bj_master ~]# uname -r3.10.0-514.21.1.el7.x86_64

Download

[[email protected]_bj_master ~]# cd /usr/local/src/[[email protected]_bj_master src]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar--2018-06-05 00:19:12--  https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar

Create a MySQL user name and user group

[[email protected]_bj_master src]# useradd -r -s /sbin/nologin -c ‘mysql application run user‘  mysql

Unzip the MySQL archive and move the unpacking package to the/usr/local directory

[[email protected]_bj_master src]# tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar[[email protected]_bj_master src]# ll mysql-*8.0.11-linux-glibc2.12-x86_64.tar*-rw-r--r-- 1 root root  654131200 Apr  8 14:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar-rw-r--r-- 1 7161 31415 603019898 Apr  8 16:29 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz-rw-r--r-- 1 7161 31415  51099557 Apr  8 16:27 mysql-test-8.0.11-linux-glibc2.12-x86_64.tar.gz[[email protected]_bj_master src]# tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz [[email protected]_bj_master src]# mv mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/ -v‘mysql-8.0.11-linux-glibc2.12-x86_64’ -> ‘/usr/local/mysql-8.0.11-linux-glibc2.12-x86_64’

Create a soft connection to facilitate future upgrade of the Ontology program does not affect the configuration file

[[email protected]_bj_master src]# ln -s /usr/local/mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/mysql -v‘/usr/local/mysql’ -> ‘/usr/local/mysql-8.0.11-linux-glibc2.12-x86_64’

Create MySQL data import/Export Data exclusive directory folder, modify permissions

[[email protected]_bj_master src]# mkdir -v /usr/local/mysql/mysql-filesmkdir: created directory ‘/usr/local/mysql/mysql-files’

Create a Data folder

[[email protected]_bj_master src]# mkdir /application/mysql/data -vmkdir: created directory ‘/application/mysql/data’

Modify MySQL directory owner and all groups

[[email protected]_bj_master src]# chown root.mysql -R /usr/local/mysql-8.0.11-linux-glibc2.12-x86_64

Modify MySQL Data directory with data import/export the owning user and group of the dedicated directory

[[email protected]_bj_master src]# chown mysql.mysql -R /application/mysql/data /usr/local/mysql/mysql-files -vownership of ‘/application/mysql/data’ retained as mysql:mysqlownership of ‘/usr/local/mysql/mysql-files’ retained as mysql:mysql

Renaming does not use the system comes with MySQL profile/etc/my.cnf [Debian class system in/ETC/MYSQL/MY.CNF]

[[email protected]_bj_master src]# mv /etc/my.cnf{,.old} -vmv: overwrite ‘/etc/my.cnf.old’? y‘/etc/my.cnf’ -> ‘/etc/my.cnf.old’

Initialization

[[email protected]_bj_master src]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/application/mysql/data --basedir=/usr/local/mysql2018-06-04T16:32:33.389541Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 6812018-06-04T16:32:39.406682Z 5 [Note] [MY-010454] [Server] A temporary password is generated for [email protected]: mD4Tbl#0d8:W2018-06-04T16:32:42.307915Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.11) initializing of server has completed

To start an SSL connection (optional)

[[email protected]_bj_master src]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --datadir=/application/mysql/data --basedir=/usr/local/mysql

Creating a startup script [Systemd]

[[Email protected]_bj_master src]# Cat/usr/lib/systemd/system/mysqld.service # # Simple MySQL systemd service file # # SYSTEMD supports lots of fancy features, look here (and linked docs) for a full list: # Http://www.freedesktop. org/software/systemd/man/systemd.exec.html # # Note:this file (/usr/lib/systemd/system/mysql.service) # would be Written on package upgrade, "Copy the file to # #/etc/systemd/system/mysql.service # # to make needed Chan  Ges.  # # # Systemd-delta can used to check differences between the "Mysql.service files. # [Unit] description=mysql Community Server after=network.target after=syslog.target [Install] wantedby=multi-user.t Arget alias=mysql.service [Service] user=mysql Group=mysql # Execute pre and post scripts as root Permissionsstarton  Ly=true # Needed to create system tables etc.  #ExecStartPre =/usr/bin/mysql-systemd-start Pre # Start main Service Execstart=/usr/local/mysql/bin/mysqld_safe # Don ' t SiGnal startup success before a ping works #ExecStartPost =/usr/bin/mysql-systemd-start Post # Give up if ping don ' t get an Answer timeoutsec=600 Restart=always Privatetmp=false

SYSTEMD loading the boot configuration file

[[email protected]_bj_master src]# systemctl daemon-reload

Create MySQL profile my.cnf, specify Data directory

[[email protected]_bj_master src]# cat /etc/my.cnf[mysqld]user = mysqlbind_address = 0.0.0.0character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir = /application/mysql/data

Start the MySQL service

[[Email protected]_bj_master src]# systemctl start Mysqld[[email protected]_bj_master src]# systemctl  Status mysqld mysqld.service-mysql Community Server loaded:loaded (/usr/lib/systemd/system/mysqld.service; disabled; Vendor preset:disabled) active:active (running) since Tue 2018-06-05 00:42:23 CST; 5s ago Main pid:2273 (mysqld_safe) CGroup:/system.slice/mysqld.service├─2273/bin/sh/usr/local/mysql/bin/m Ysqld_safe└─2424/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/application/mysql/data--plu Gin ... June to 00:42:23 Sea_bj_master systemd[1]: Started MySQL Community server.jun 00:42:23 sea_bj_master systemd[1]: Startin G MySQL Community Server ... June 00:42:23 Sea_bj_master mysqld_safe[2273]: 2018-06-04t16:42:23.125697z mysqld_safe Logging to '/APPLICATI...RR '. June to 00:42:23 Sea_bj_master mysqld_safe[2273]: 2018-06-04t16:42:23.144258z mysqld_safe Starting mysqld daemon ... Datahint:some lines were ellipsized, use-l to Show in full. 

Connect to the MySQL service and modify the initial password

[[Email protected]_bj_master src]# mysql-p ' md4tbl#0d8:w ' mysql: [Warning] Using a password on the command line Interf Ace can be insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 8Server version:8.0.11copyright (c), 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> set password = ' 123 '; Query OK, 0 rows affected (0.04 sec) mysql> \s--------------MySQL Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Commu Nity SERVER-GPL) Connection id:8current database:current User: [Email protected]ssl:not I n usecurrent pager:stdoutusing outfile: ' Using delimiter:; Server Version:8.0.11protocol version:10connection:localhost via UNIX socketserver characterset:uTf8mb4db characterset:utf8mb4client Characterset:utf8mb4conn. Characterset:utf8mb4unix socket:/tmp/mysql.sockuptime:1 min 0 secthreads:2 questions:7 Slow queri es:0 opens:238 Flush tables:2 Open tables:73 Queries per second avg:0.116--------------

MySQL8.0--------up to 3 million QPS Titan practice