Two on the 1th Day, MySQL management (performance tuning, high availability and monitoring)

"Contrast characteristics"

Created November 27, 2016

1. Performance improvement

2. Security improvements

1) SSL is turned on by default

2) no longer explicitly display user passwords

3) changes in Sql_mode

3. Upgrade of the InnoDB storage engine

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

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

3) Innodb/myisam Storage Engine supports Chinese full-text indexing

4) InnoDB Buffer pool preheating Improvement

5) Online adjustment innodb_buffer_pool_size do not restart the MySQL process

6) Recover (shrink) Undo Log Rollback Log physical file space

7) InnoDB provides universal table space

8) Create a InnoDB stand-alone table space to specify the storage path

9) Migrating a single InnoDB table to a remote server

10) Modify InnoDB Redo Log transaction log file size more humane

11) deadlock can be printed in the error log

12) Support InnoDB read-only transactions

13) Support InnoDB table space data defragmentation

4. Support in JSON format

1) supports storing data in JSON format

2) Dynamic column supports storing data in JSON format

5. Support Virtual columns (function index)

1) MySQL 5.7 Support function Index

2) MARIADB 10.0/10.1 support function Index

6. Function improvement

1) Support for killing slow SQL statements

2) supports multiple insert/delete/update triggers on a single table

3) Introduction of thread pool (thread pool) technology

4) Provide audit log function

5) Support explain update

6) pressing CTRL + C in MySQL 5.7 will not exit the client

7) The error log can be printed to the system log file

8) support for creating roles

9) Support TOKUDB storage engine

7. Optimizer improvements

1) Semi-connection optimization for sub-query Selete

2) Optimizing derived subqueries

3) Optimize sort limit

4) Optimizing in conditional expressions

5) Optimize UNION ALL

6) Support index push-down optimization

7) Support multi Range Read index optimization

8) Support batched Key Access (BKA) index optimization

9) Support hash join index optimization

8. Semi-synchronous replication improvements

1) Introduction to semi-synchronous replication

2) installation configuration for semi-synchronous replication

9. Gtid Replication Improvements

1) GTID

2) in MySQL 5.6 gtid mode synchronous replication error cannot skip the workaround

3) Improvement of Gtid replication in MySQL5.7

4) Gtid Replication traps

5) Improvements to Gtid replication in MariaDB 10.1

MySQL 5.6/5.7 Security recovery from library crash

MariaDB 10.0/10.1 security recovery from vault crash

Slave multi-threaded replication from libraries

Slave supports multi-source replication

MySQL 5.7 Set synchronous replication filtering without restarting the MySQL service process

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

1th Day two, MySQL management (performance tuning, high availability and monitoring)