MySQL5.7新特性

最新推荐文章于 2024-09-18 06:17:39 发布

bamuta

最新推荐文章于 2024-09-18 06:17:39 发布

阅读量1w

点赞数 2

分类专栏： Mysql mysql5-7 文章标签： mysql mysql5-7

本文链接：https://blog.csdn.net/bamuta/article/details/52213660

版权

Mysql 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

mysql5-7

1 篇文章 0 订阅

订阅专栏

MySQL5.7新特性

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

MySQL57新特性

Added Features

1.安全提升

mysql.user中plugin列非空，如果是空值将禁用帐户。
密码默认360天过期。可以修改：

通过参数全部禁用：
[mysqld]
default_password_lifetime=0
可以动态修改：
default_password_lifetime can also be changed at runtime (this requires the SUPER privilege):
SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0;

还可以对单个用户进行修改
Require the password to be changed every 90 days:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Disable password expiration:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
Defer to the global expiration policy:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

帐户可以加锁解锁，支持create user 和 alter user，命令： lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
使用mysql_ssl_rsa_setup工具能够更容易使用ssl.

http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html
初始化数据库时用mysqld –initialize替换以前的 mysql_install_db

2. SQL mode changes

默认启用： ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
参数里可以不用sql_mode了。

3. Online ALTER TABLE

ONLINE DDL支持rename index

ALTER TABLE now supports a RENAME INDEX clause that renames an index

alter table t1 rename index index_b to idx_colb;

4.ngram and MeCab full-text parser plugins

全文索引支持中文，日语，韩文了。

As of MySQL 5.7.6, MySQL provides a built-in full-text ngram parser plugin that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese.

5.InnoDB enhancements.

- 在线修改varchar长度，支持in-place算法。

但这是条件的，只支持2种情况，一种是长度在0到-255之间增加，另一种是在256到更大之间。不支持由255之前的一个长度跳到256之后的长度。也不支持减长度。不支持的情况用的是copy算法。
所以。在定varchar长度的时候，如果长度是大于200了，但是不确定以后会不会改的情况下，就直接写个256。

VARCHAR size may be increased using an in-place ALTER TABLE, as in this example:

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

临时表TEMPORARY table优化了DDL操作的性能。

DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.

临时表的元数据不再存innodb的系统表，单独存在INNODB_TEMP_TABLE_INFO指定的表里。包含所有用户的临时表信息。这个表在第一次执行select查询时创建。
MySQL支持地理空间数据类型，在此之间，mysql存在blob类型中。地理空间数据，现在映射成内部的数据类型DATA_GEOMETRY

InnoDB now supports MySQL-supported spatial data types. Prior to this release, InnoDB would store spatial data as binary BLOB data. BLOB remains the underlying data type but spatial data types are now mapped to a new InnoDB internal data type, DATA_GEOMETRY.

Use the CREATE TABLE statement to create a table with a spatial column:

CREATE TABLE geom (g GEOMETRY);
Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table:

ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;
未压缩的临时表，存在独立的表空间里。这个表空间每次重启都会重新创建，位置：innodb_temp_data_file_path
innochecksum（innodb离线文件校验工具）增强，以前只支持2G以内的文件，现在支持大于2G的文件。
临时表不管压缩没压缩，都不会写redo，”non-redo”.因为临时表在数据库crash后不需要恢复。也就不需要redo log。但是为了支持事物是需要undo的，比如回滚操作，undo信息存在innodb_temp_data_file_path文件里（ibtmp1）。
innodb buffer pool的dump和load操作加强了，通过一个新的变量innodb_buffer_pool_dump_pct可以按百分比将最近用的page导出。这个操作是一个IO密集型的操作，可以通过 innodb_io_capacity 限制每秒buffer pool load操作的数量。（这个参数只会限制InnoDB background tasks，如flushing pages from buffer pool,从change buffer 里merging data,默认200）
从5.7.3开始，支持全文解析插件。
从buffer pool刷脏页时，5.7.4开始支持多个Page cleaner threads.一个新的参数用于指定数量：innodb_page_cleaners，默认值在5.7.8之前是1，在这个版本及之后是4.
从5.7.4 online DDL (ALGORITHM=INPLACE) 从5.7.4开始支持rebuilding innodb表，不管是否分区。相关语句：

OPTIMIZE TABLE

ALTER TABLE … FORCE

ALTER TABLE … ENGINE=INNODB (when run on an InnoDB table)

As of 5.7.4, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE … FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.
linux的Fusion-io Non-Volatile Memory (NVM)文件系统提供原子写的能力，这和innodb的doublewrite buffer冗余了，在mysql5.7.4以后，在支持原子写的Fusion-io的设备上，doublewrite自动关闭了。
mysql5.7.4 传输表空间支持分区表。
mysql5.7.5支持参数innodb_buffer_pool_size在线修改。在重设的过程，相关的内存会移到新的内存区域，这个以chunk为单位，chunk的大小于innodb_buffer_pool_chunk_size决定，可以通过状态参数innodb_buffer_pool_resize_status 监控resizing的过程。
在数据库shutdown和recovery阶段支持多线程的page cleaner，相关参数innodb_page_cleaners.
在mysql5.7.5,innodb支持地理空间数据类型的索引spatial index,并且支持online ddl操作。
在创建或重建索引时，innodb支持bulk load,这种方法叫”sorted index build”,这将提高创建索引的效率，同时可以应用在full-text 索引上，通过全局系统参数innodb_fill_factor,可以配置在每次sorted index build时每个page上使用百分比空间量（该参数默认100）。留一部分空间用于以后索引的增长。
一个新的日志记录格式（MLOG_FILE_NAME）用于记录自动上次checkpoint后表空间的修改。这将简化crash recovery后表空间的发现，并且减少redo应用之前文件系统的扫描量。这个特性将改变redo log的格式，升级或降级mysql5.7.5时需要完全关闭数据。
mysql5.7.5，能在undo表空间truncate undo logs，这个特性的启用是能参数innodb_undo_log_truncate.
从5.7.6，innodb支持native partitioning，在此之前，innodb依赖ha_partition handler，这将对每一个分区创建一个handler。当用了native parttioning，分区的innodb表将只用一个parttion-aware handler对象。这个增强特性减少了parttioned innodb tables的内存需求量。
从5.7.6，innodb支持创建 general tablesapce 。语法如下：

CREATE TABLESPACE tablespace_name
ADD DATAFILE ‘file_name.ibd’
[FILE_BLOCK_SIZE = n]

genaral tablespace 支持创建在mysql的data 目录之外，能保存多个表，并且对表支持所有row格式。
表增加一个general tablespace 可以用以下语法：

CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name

http://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

从5.7.9,innodb 表默认的行格式（row format）由compact替换为dynamic。参数innodb_default_row_format里多了一个新的配置选项。
从5.7.11 ，innodb支持对file-per-table的表空间支持data-at-rest加密。当creating或者altering一个innodb表时，由 ENCRYPTION选项来启用加密。这个特性,涉及到innodb tablespace的加密，依赖于keying plugin做加密管理。

6.JSON support

JSON 类型
从5.7.8开始，mysql支持json类型，并不是以前用字符串存储，而是以文档类型存储，json类型的数据在插入更新时会自动校验。如果不符合标准json，会报错。
Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>;

json的操作需要相关函数来完成：

Functions that create JSON values: JSON_ARRAY(), JSON_MERGE(), and JSON_OBJECT(). See Section 13.16.2, “Functions That Create JSON Values”.

Functions that search JSON values: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(), and JSON_SEARCH(). See Section 13.16.3, “Functions That Search JSON Values”.

Functions that modify JSON values: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_UNQUOTE(). See Section 13.16.4, “Functions That Modify JSON Values”.

Functions that provide information about JSON values: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE(), and JSON_VALID(). See Section 13.16.5, “Functions That Return JSON Value Attributes”.

x plugin(服务器端)：INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’; 安装完，会启动一个33060端口

X Plugin is a new MySQL Server feature available with MySQL Server 5.7.12 and higher

mysql-shell(客户端） :mysqlsh ，mysqlsh可以连接到33060端口

MySQL Shell is an advanced command-line client and code editor for the MySQL Server. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python. When MySQL Shell is connected to the MySQL Server through the X Protocol, the X DevAPI can be used to work with both relational and document data.

7.System and status variables

以前的是存在information_schema中，现在存在performance schema中，show variables和show status也是受影响的。升级过程中可以用show_compatibility_56做兼容操作，默认关的，需要打开。

8.sys schema

5.7多了一个sys schema, 收集一些系统，用于调优和诊断。

9. Condition handling.

MySQL now supports stacked diagnostics areas. 用于存储过程抓异常信息。

10.Optimizer

可以通过以下命令获取其他正在执行的session的执行计划

EXPLAIN [options] FOR CONNECTION connection_id;
optimizer hints

以前要修改执行计划有一个方法是通过optimizer_switch参数来改变，在5.7.7以后可以用优化器的hints。写法如下：

SELECT /+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) / f1
FROM t3 WHERE f1 30 AND f1 < 33;

SELECT /+ BKA(t1) NO_BKA(t2) / * FROM t1 INNER JOIN t2 WHERE …;

SELECT /+ NO_ICP(t1, t2) / * FROM t1 INNER JOIN t2 WHERE …;

SELECT /+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / * FROM t1 …;

EXPLAIN SELECT /+ NO_ICP(t1) / * FROM t1 WHERE …;

http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

11.Triggers

以前版本对单个需要触发的DML事件，只支持1个触发器，现在支持多个。

Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted.

12.Logging

native syslog support。支持直接打日志到syslog
mysql客户端也支持打log，需要加–syslog,linux默认打到/var/log/message

mysql –syslog -uroot -p

13.Generated Columns

表中的列可以是由其他列生成的。而这一列的值可以是VIRTUAL 或是 STORED的，区别是virtual 类似于视图，没有数据，只有计算公式，需要的时候再计算，和Oralce11g的虚拟列一样。另1个stored的时，每次相关列有DML操作的时候这一列都会去更新存储，数据是落地的，类似于物化视图。默认是virtual。

http://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

14.mysql client

旧版本的mysql客户端在执行Control+C，如果有SQL在运行会中断SQL，没有会退出mysql客户端，新版本也会中断SQL，但是不会退出。

15.Database name rewriting with mysqlbinlog

对于以row格式的binlog，使用工具mysqlbinlog时，可以通过参数–rewrite-db修改dbname,使用格式：--rewrite-db='dboldname->dbnewname'

16.HANDLER with partitioned tables

分区表也可以用handler了。

什么是handler? handler有以下特性：

HANDLER is faster than SELECT:

A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for subsequent HANDLER statements for that table; it need not be reinitialized for each one.

There is less parsing involved.

There is no optimizer or query-checking overhead.

The handler interface does not have to provide a consistent look of the data (for example, dirty reads are permitted), so the storage engine can use optimizations that SELECT does not normally permit.

hander不是标准的SQL语法，可以降低优化器对SQL的解析与优化开锁，性能比select快40-45%。但是它不是一致性读（如脏读）

The HANDLER ... OPEN statement opens a table, making it accessible using subsequent HANDLER ... READ statements.

This table object is not shared by other sessions and is not closed until the session calls HANDLER ... CLOSE or the session terminates.

17. Index condition pushdown support for partitioned tables

分区表上也支持ICP了。

18.WITHOUT VALIDATION support for ALTER TABLE … EXCHANGE PARTITION

分区交换支持without validation选项。默认是WITH VALIDATION，如果是with validation，会逐行的去校验是否满足分区边界，有不符合的会报错。without validation不会报错，速度也很快。如果数据不正确使用without validation会有问题：

CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES 
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;

mysql> select * from e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+

 INSERT INTO e2 VALUES (51, "Ellen", "McDonald");

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS  WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 |  1 |
| p1 |  0 |
| p2 |  0 |
| p3 |  3 |

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.81 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS  WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 |  0 |
| p1 |  0 |
| p2 |  0 |
| p3 |  3 |
+----------------+------------+

mysql> select * from e;
+------+-------+----------+
| id   | fname | lname|
+------+-------+----------+
|   51 | Ellen | McDonald |
| 1669 | Jim   | Smith|
|  337 | Mary  | Jones|
| 2005 | Linda | Black|
+------+-------+----------+
4 rows in set (0.00 sec)

mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
mysql> select * from e where id=51;
Empty set (0.00 sec)
mysql> select * from e where fname='ellen';
+----+-------+----------+
| id | fname | lname|
+----+-------+----------+
| 51 | Ellen | McDonald |
+----+-------+----------+
1 row in set (0.00 sec)

分区交换是做成功了，全遍历分区表可以查到数据，但是按分区键ID去查，找不到数据，按非分区键可以找到数据。

19.Master dump thread improvements

dump减少了锁争用，提高了master的吞吐量。

The master dump thread was refactored to reduce lock contention and improve master throughput. Previous to MySQL 5.7.2, the dump thread took a lock on the binary log whenever reading an event; in MySQL 5.7.2 and later, this lock is held only while reading the position at the end of the last successfully written event. This means both that multiple dump threads are now able to read concurrently from the binary log file, and that dump threads are now able to read while clients are writing to the binary log.

20.Globalization improvements

全球化改进，增加了gb18030字符集

MySQL 5.7.4 includes a gb18030 character set that supports the China National Standard GB18030 character set.

21.Changing the replication master without STOP SLAVE

以前的版本要执行change master to 命令前必须要执行stop slave,现在不需要执行,现在取决于IO线程和SQL线程的运行状态。

If the SQL thread is stopped, you can execute CHANGE MASTER TO using any combination of RELAY_LOG_FILE, RELAY_LOG_POS, and MASTER_DELAY options, even if the slave I/O thread is running. No other options may be used with this statement when the I/O thread is running.

If the I/O thread is stopped, you can execute CHANGE MASTER TO using any of the options for this statement (in any allowed combination) except RELAY_LOG_FILE, RELAY_LOG_POS, or MASTER_DELAY, even when the SQL thread is running. These three options may not be used when the I/O thread is running.

Both the SQL thread and the I/O thread must be stopped before issuing CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1.

22.Test suite

The MySQL test suite now uses InnoDB as the default storage engine.

test suite是mysql的自动测试框架，主要用于mysql找了patch后，通过执行一个case得到一个结果，然后与标准输出的结果做比对，没有差异就通过了。

http://dev.mysql.com/doc/mysqltest/2.0/en/preface.html

23.Multi-source replication is now possible

MySQL也支持多主复制了。

24.Group Replication Performance Schema tables.

增加了几个组复制相关的表。
MySQL 5.7 adds a number of new tables to the Performance Schema to provide information about replication groups and channels. These include the following tables:

replication_applier_configuration

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_status_by_worker

replication_connection_configuration

replication_connection_status

replication_group_members

replication_group_member_stats

25.Group Replication SQL

增加了组复制相关的SQL命令。
The following statements were added in MySQL 5.7.6 for controlling group replication:

START GROUP_REPLICATION

STOP GROUP_REPLICATION

Deprecated Features

以下特性在5.7里已经deprecated，在后续的版本可能会移除。

1.sql_mode

ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE 是严格模式的一部分，现在废弃后，以后会移除。

2.关于帐户管理有些语句淘汰了，所以相关内容也是废弃了。

由于推荐使用create user 创建用户，而不是grant，所以 NO_AUTO_CREATE_USER也没什么用了，所以也deprecated.
除了权限分配外，不要用grant修改帐户属性，包括ssl认证，资源限制，应该用create user 或者alter user.
执行CREATE USER and GRANT时，不要用IDENTIFIED BY PASSWORD ‘hash_string’, 要用 IDENTIFIED WITH auth_plugin AS 'hash_string' for 。正确的姿势如下：

create user ‘test1’@’%’ identified with mysql_native_password by ‘123’;
create user ‘test1’@’%’ identified with mysql_native_password as ‘23AE809DDACAF96AF0FD78ED04B6A265E05AA257’
password()函数弃用，因此SET PASSWORD … = PASSWORD(‘auth_string’) 也弃用了，但是 SET PASSWORD … = ‘auth_string’没有弃用。无论如何标准推荐用法是alter user.
参数 old_passwords是弃用的。将来会被移除。

3.group by 排序

在5.7group by 是不排序的，如果要排序就使用order by 。

4. 在执行计划explain语句不要再使用extended和partitions，仍然会识别，但是已经没有用了。

5. –skip-innodb，–innodb=OFF, –disable-innodb, 弃用了，因为5.7innodb是不能禁用的。

6. 客户端用SSL命令的变化：

The client-side --ssl and --ssl-verify-server-cert options are deprecated. Use --ssl-mode=REQUIRED instead of --ssl=1 or --enable-ssl. Use --ssl-mode=DISABLED instead of --ssl=0, --skip-ssl, or --disable-ssl. Use --ssl-mode=VERIFY_IDENTITY instead of --ssl-verify-server-cert options. (The server-side --ssl option is not deprecated.)

For the C API, MYSQL_OPT_SSL_ENFORCE and MYSQL_OPT_SSL_VERIFY_SERVER_CERT options for mysql_options() correspond to the client-side --ssl and --ssl-verify-server-cert options and are deprecated. Use MYSQL_OPT_SSL_MODE with an option value of SSL_MODE_REQUIRED or SSL_MODE_VERIFY_IDENTITY instead.

7. `log_warnings`变量和`--log-warnings`选项弃用了，用`log_error_verbosity`变量代替。

8.参数 binlog_max_flush_queue_time弃用。

9.参数`innodb_support_xa` 弃用

这个参数的作用是支持2阶段提交的分布式事物，在5.7永远是启用的。

10.`metadata_locks_cache_size`和`metadata_locks_hash_instances`废弃。在5.7后什么也不做。

11.sync_frm 废弃

12.全局变量 character_set_database and collation_database废弃

可以用character_set_server 和collation_server

13. ENCRYPT(), ENCODE(), DECODE(), DES_ENCRYPT(), and DES_DECRYPT() 这些全部废弃，建议用AES_ENCRYPT() and AES_DECRYPT()

下面例子是做了一次加密，又做了次解密：
select AES_DECRYPT(aes_encrypt(‘test’,’salt1’),’salt1’) ;

14.地理空间函数 MBREqual()废弃，用MBREquals()代替

15.INFORMATION_SCHEMA PROFILING 表废弃，用 Performance Schema代替。

16.INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS废弃。

17.mysqld_safe support for syslog output is deprecated. Use the native server syslog support used instead

18.5.1之前的版本包含特殊字符，该特性已经废弃，因此，mysqlcheck命令的 `--fix-db-names and --fix-table-names` 废弃，ALTER DATABASE 语句的 UPGRADE DATA DIRECTORY NAME从句废弃了。而要从5.1之前的版本升级到新版本，首先要先升级到5.1。如5.0要升级为5.5.而先从5.0升到5.1，再从5.1升级到5.5.

19.`mysql_install_db`已经集成到了mysqld，初始化数据用mysqld，使用 `--initialize or --initialize-insecure` 选项。并且会为’root’@’localhost’用户生成一个随机密码。

20.mysql_install_db废弃

21.mysql_plugin工具废弃

替代的方法是：loading plugins at server startup using the –plugin-load or –plugin-load-add option, or at runtime using the INSTALL PLUGIN statement.

22.`mysql_kill(), mysql_list_fields(), mysql_list_processes(), and mysql_refresh()` 已经废弃

The same is true of the corresponding COM_PROCESS_KILL, COM_FIELD_LIST, COM_PROCESS_INFO, and COM_REFRESH client/server protocol commands. Instead, use mysql_query() to execute a KILL, SHOW COLUMNS, SHOW PROCESSLIST, or FLUSH statement, respectively.

Removed Features

1.由于4.1版本之前hash格式密码已经被移除。有以下相关的变更：

mysql_old_password authentication plugin is removed
--secure-auth 选项由于是默认，目前是废弃的，以后会移除。
--skip-secure-auth不再支持
系统变量secure_auth 值只有1，0不再支持。
系统变量old_passwords值为1 (produce pre-4.1 hashes)不再支持；
The OLD_PASSWORD() function is removed.

2.字段类型 YEAR(2) 移除，建议year(4)或year

3.系统变量`innodb_mirrored_log_groups`只支持1，所以没有用。

4.系统变量`storage_engine` 用 `default_storage_engine`代替

5.系统变量`thread_concurrency` 移除；

6.系统变量`timed_mutexes` 没有任何影响；

7.ALTER TABLE的ignore从句。、

8.INSERT DELAYED 不再支持。服务器能认，但是会忽略delayed关键字，这种insert会当成 nondelayed insert，

另外，DELAYED相关的几个选项也移除了。

The –delayed-insert option for mysqldump.
The COUNT_WRITE_DELAYED, SUM_TIMER_WRITE_DELAYED, MIN_TIMER_WRITE_DELAYED, AVG_TIMER_WRITE_DELAYED, and MAX_TIMER_WRITE_DELAYED columns of the Performance Schema table_lock_waits_summary_by_table table.
mysqlbinlog no longer writes comments mentioning INSERT DELAYED.