Three on the 1th day, MySQL management (performance tuning, high availability and monitoring)

1. Performance

1) Performance improvement

MySQL5.7 provides more consistent linear performance and scalability on systems that support multi-processor and highly concurrent CPU threads. The key to achieving this is to eliminate InnoDB kernel contention and mutex locking through the concurrency of the efficiency of the Oracle InnoDB storage engine.

In OLTP read-only mode, MySQL5.7 is 3 times times more powerful than MySQL5.6.

In OLTP read/write mode, MySQL5.7 performance is up totwice times higher than MySQL5.6.

2. Security

1) SSL is turned on by default

"Created November 27, 2016"

1) supported in MySQL5.7 and Percona5.7 versions.

2) MySQL5.7 when booting, use OpenSSL to automatically generate SSL and RSA certificates and key files.

3) Secure Sockets Layer (SE) ........ ..... (also make lazy, which day Plus!) ）

4) Installation section see MySQL 5.7 Installation

5) MySQL 5.7 Record password tool

Mysql_config_editor set--login-path=client--host-localhost--user=root--password

A hidden file is generated in the/root directory. Mylogin.cnf, view is garbled

* After the root password is changed, you need to re-execute Mysql_config_editor

* Log in to MySQL--login-path=client

* MariaDB 10.1 version does not have this tool

6) Specify the user to use SSL connection

Create user

Grant all privileges on * * to [email protected] '% ' identified by ' 123456 ' require SSL;

* MySQL 5.6/MARIADB 10.1 also supports SSL connection

2) no longer explicitly display user passwords

"Created November 27, 2016"

MySQL 5.6/mysql 5.7 and Percona 5.6/5.7 support

MARIADB 10.1 for Binlog and user password-related operations are not encrypted.

3) Sql_mode Change

"Created November 27, 2016"

MySQL 5.7/mariadb 10.1 is enabled by default for Strict_trans_tables strict mode. The function of this mode is to verify the data, error data can not be inserted, errors and transaction rollback.

For example, if the age field is an init numeric shape, a warning is issued when a character type is inserted, and if Sql_mode is empty, it continues to be inserted. If Strict_trans_tables mode, a warning is issued and the transaction is rolled back.

3. Upgrade of the InnoDB storage engine

1) Do not lock the table when changing the index name

"Created November 27, 2016"

Mysql5.7/percona Version 5.7 Support

2) The online DDL modifies the varchar field property when the table is not locked

"created November 27, 2016"

MySQL 5.7 has been enhanced for DDL functionality, and modifying the varchar field has not locked the table.

* Algorithm for specifying the algorithm to create or delete an index

* Copy means to create a temporary table and copy the data in full form, with the original table plus global read lock, according to the method prior to MySQL version 5.1

* InPlace indicates that creating a field or deleting a field does not require creating a temporary table

* Default indicates whether the algorithm is through inplace or copy

ALTER TABLE sbtest algorithm=inplace,modify pad varchar (n) not null;

Don't want to write, rest!!!!!!. Continue tomorrow!

This article is from the "Rise" blog, please be sure to keep this source http://binbinwudi8688.blog.51cto.com/3023365/1877142

Three on the 1th day, MySQL management (performance tuning, high availability and monitoring)