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

Source: Internet
Author: User
Tags percona ssl connection

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.