MySQL版本升级之5.6到5.7

向右看齐 2022-05-22 10:54 364阅读 0赞

#     #

### 两种升级方式 ###

*  *In-Place Upgrade*: Involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running [**mysql\_upgrade**][mysql_upgrade]. 
 *  *Logical Upgrade*: Involves exporting existing data from the old MySQL version using [**mysqldump**][mysqldump], installing the new MySQL version, loading the dump file into the new MySQL version, and running [**mysql\_upgrade**][mysql_upgrade].

### 主从的升级： ###

1.  主从想都替换二进制安装包为最新版本
2.  停从，通过mysql\_upgrade升级后，加参数 --skip-slaves-start 进行启动　　
3.  加参数 --skip-networking重启主，拒绝来自应用的TCP/IP的连接，关闭binlog，执行mysql\_upgrade，然后重启

*  注意点：在关闭服务时加参数 --innodb\_fast\_shutdown=0 （slow shutdown），会将所有提交的事务对应的脏页刷新到数据文件中，默认是1（fast shutdown 参考文献：http://dev.mysql.com/doc/refman/5.7/en/glossary.html\#glos\_fast\_shutdown）

## MySQL5.6版本到5.7版本的更新包括一些不兼容的特性，在升级到5.7之前，我们需要知道这些不兼容的特性并手动更新，在其中涉及到REPAIR TABLE和USE\_FRM选项的指令一定要在更新版本之前完成。 ##

## 配置项更新 ##

*  \--early-plugin-load

MySQL5.7.11，此参数的默认值为keyring\_file（是一个二进制文件的插件），InnoDB表空间在初始化InnoDB之前需要此插件来加密，但是MySQL5.7.12及以后此参数默认为空，所以5.7.11升级到5.7.12后，如果已经在之前的版本中使用此插件对InnoDB表空间进行了加密，在开启服务时需要指定参数 --early-plugin-load

*  `系统表`

`MySQL5.6中INFORMATION_SCHEMA 中存在系统变量和状态变量的表，show variables 和show status也是基于此库中的表，在5.7.6时被Performance Schema也存在这四张表，show 语句开始基于Performance Schema中的表，如果show_compatibility_56参数开启，则兼容5.6`

下面的测试库是从MySQL5.6版本中直接物理恢复到MySQL5.7环境下的

![复制代码][Image 1]

mysql> select version(); +------------+
    | version()  |
    +------------+
    | 5.7.10-log |
    +------------+
    1 row in set (0.00 sec)

mysql> show variables like '%56%';  
ERROR 1146 (42S02): Table 'performance\_schema.session\_variables' doesn't exist

mysql> use performance\_schema;  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A

Database changed  
mysql> show tables like '%variable%';  
Empty set (0.00 sec)

mysql> set global show\_compatibility\_56=ON;  
Query OK, 0 rows affected (0.00 sec)

![复制代码][Image 1]

*  使用[**mysqld --initialize**][mysqld --initialize] (or [**mysqld --initialize-insecure**][mysqld --initialize]).初始化实例

## sql mode  ##

[`ONLY_FULL_GROUP_BY`][ONLY_FULL_GROUP_BY], [`STRICT_TRANS_TABLES`][STRICT_TRANS_TABLES], [`NO_ENGINE_SUBSTITUTION`][NO_ENGINE_SUBSTITUTION], [`NO_ZERO_IN_DATE,`][NO_ENGINE_SUBSTITUTION] [`NO_ZERO_DATE`][NO_ENGINE_SUBSTITUTION] 默认开启

如以下sql在only full group by下，name非聚集字列，如果不在乎返回的address的值是否准确，则可以使用ANY\_VALUE函数，这样address字段就无需满足full group by 出现在group by 中

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

时间格式不允许

`'2010-00-01'` 或 `'2010-01-00'`, 或 `'0000-00-00'`.

### 系统表的改变 ###

mysql.user的password字段在5.7.6中已去除，认证信息记录在authentication\_string中，运行in-place upgrade 迁移password列值到authentication\_string

如果是通过[logical upgrade][]，需要注意：

*  You must include the [`--add-drop-table`][--add-drop-table] option
 *  You must not include the [`--flush-privileges`][--flush-privileges] option

## server端的更改 ##

MySQL5.7.5开始mysql\_old\_password 插件被移除

secure-auth 系统变量仅支持值1

\--skip-secure-auth 选项被弃用

old\_password系统变量的值1（将密码hash为41位的hash值）不再被允许

old\_password ()函数被移除

字段类型YEAR(2)被更改为YEAR(4)

MySQL5.7.2开始mysql.user系统表中的plugin字段不允许为空，运行mysql\_upgrade会进行如下操作

UPDATE mysql.user SET plugin = 'mysql_native_password'
    WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
    FLUSH PRIVILEGES;

需要注意sql\_mode的变更，如：

![复制代码][Image 1]

mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t (d DATE DEFAULT 0); SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES'; INSERT INTO t (d) VALUES(DEFAULT);Query OK, 0 rows affected (0.52 sec) mysql> SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> INSERT INTO t (d) VALUES(DEFAULT); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'd' at row 1

![复制代码][Image 1]

## InnoDB变更 ##

如果采用IN-PLACE的升级方式则需要具体关注

## SQL变更 ##

**GET\_LOCK（）函数行为**

MySQL5.7.5之前GET\_LOCK()在执行第二次的额时候会释放前面获得的锁，在此版本以后支持同时获得多个锁，如：

![复制代码][Image 1]

mysql> select version(); +------------+
    | version()  |
    +------------+
    | 5.6.33-log |
    +------------+
    1 row in set (0.00 sec) mysql> SELECT GET_LOCK('lock1',10); +----------------------+
    | GET_LOCK('lock1',10) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec) mysql> SELECT GET_LOCK('lock2',10); +----------------------+
    | GET_LOCK('lock2',10) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec) mysql> SELECT RELEASE_LOCK('lock2'); +-----------------------+
    | RELEASE_LOCK('lock2') |
    +-----------------------+
    |                     1 |
    +-----------------------+
    1 row in set (0.00 sec) mysql> SELECT RELEASE_LOCK('lock1'); +-----------------------+
    | RELEASE_LOCK('lock1') |
    +-----------------------+
    |                  NULL |
    +-----------------------+
    1 row in set (0.00 sec)

![复制代码][Image 1]

返回null说明此锁已经被释放了

所以依赖于释放任何先前锁的GET\_LOCK（）的行为的应用程序必须针对新行为进行修改。

### derived\_merge被自动开启 ###

5.7中优化器使用一致的机制处理from语句中的派生表和视图是为了更好地避免不必要的物化并能够通过条件下放产生更有效的执行计划。

但是，对于修改表的语句（例如DELETE或UPDATE），使用先前物化的派生表的合并策略可能会导致ER\_UPDATE\_TABLE\_USED错误：

错误原因：外部查询表和内部更改的表属于同一张表时，如果将派生表合并到外部查询块就会触发此错误（物化方式不会导致此错误，因为实际上，它将派生表转换为单独的表）

如：

mysql> delete from t1 where id in (select t1.id from t1 inner join t2 using(id) where t2.a1=100);
    ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

解决：关闭optimizer\_switch的derived\_merge选项，此选项默认是打开的

![复制代码][Image 1]

# 5.6
    optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,
    index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,
    mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,
    semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
    # 5.7
    optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,
    index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,
    mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,
    semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,
    use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

![复制代码][Image 1]

关闭derived\_merge

SET optimizer_switch = 'derived_merge=off';

### 关键字和保留字 ###

如果要引用保留字，必须使用反引号括起或跟在限定名称的逗点后，否则报语法错误，如

![复制代码][Image 1]

mysql> CREATE TABLE interval (begin INT, end INT);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval (begin INT, end INT)' at line 1
    mysql> CREATE TABLE `interval` (begin INT, end INT);
    Query OK, 0 rows affected (1.14 sec)
    mysql> CREATE TABLE test.interval (begin INT, end INT);
    Query OK, 0 rows affected (1.84 sec)

![复制代码][Image 1]

**MySQL 5.7中有而MySQL 5.6中没有的关键字和保留字；有R标记的为保留字**

<table style="width:892px;height:235px;"> &nbsp; 
 <tbody> 
 <tr> 
 <td><code>ACCOUNT</code></td> 
 <td><code>ALWAYS</code></td> 
 <td><code>CHANNEL</code></td> 
 </tr> 
 <tr> 
 <td><code>COMPRESSION</code></td> 
 <td><code>ENCRYPTION</code></td> 
 <td><code>FILE_BLOCK_SIZE</code></td> 
 </tr> 
 <tr> 
 <td><code>FILTER</code></td> 
 <td><code>FOLLOWS</code></td> 
 <td><code>GENERATED</code> (R)</td> 
 </tr> 
 <tr> 
 <td><code>GROUP_REPLICATION</code></td> 
 <td><code>INSTANCE</code></td> 
 <td><code>JSON</code></td> 
 </tr> 
 <tr> 
 <td><code>MASTER_TLS_VERSION</code></td> 
 <td><code>NEVER</code></td> 
 <td><code>OPTIMIZER_COSTS</code> (R)</td> 
 </tr> 
 <tr> 
 <td><code>PARSE_GCOL_EXPR</code></td> 
 <td><code>PRECEDES</code></td> 
 <td><code>REPLICATE_DO_DB</code></td> 
 </tr> 
 <tr> 
 <td><code>REPLICATE_DO_TABLE</code></td> 
 <td><code>REPLICATE_IGNORE_DB</code></td> 
 <td><code>REPLICATE_IGNORE_TABLE</code></td> 
 </tr> 
 <tr> 
 <td><code>REPLICATE_REWRITE_DB</code></td> 
 <td><code>REPLICATE_WILD_DO_TABLE</code></td> 
 <td><code>REPLICATE_WILD_IGNORE_TABLE</code></td> 
 </tr> 
 <tr> 
 <td><code>ROTATE</code></td> 
 <td><code>STACKED</code></td> 
 <td><code>STORED</code> (R)</td> 
 </tr> 
 <tr> 
 <td><code>VALIDATION</code></td> 
 <td><code>VIRTUAL</code> (R)</td> 
 <td><code>WITHOUT</code></td> 
 </tr> 
 <tr> 
 <td><code>XID</code></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

## ##

### 表联合查询    ###

使用union连接的单个查询语句中如果有order by或limit关键字需要将此单个语句使用括号引起。如：

![复制代码][Image 1]

mysql> select * from t1 limit 1 union select * from t2 limit 2;
    ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT
    mysql> (select * from t1 limit 1) union (select * from t2 limit 2);
    +------+-------+-------+
    | id   | name1 | name2 |
    +------+-------+-------+
    |    1 | a1    | a2    |
    |    1 | 2     | 2     |
    |    1 | 1     | 1     |
    +------+-------+-------+
    3 rows in set (0.00 sec)

![复制代码][Image 1]

###  升级实践（采用in-place方式） ###

![复制代码][Image 1]

#关闭快速关闭服务选项，防止关闭服务后有脏页未刷进磁盘，升级后表格式变化无法进行recovery
    mysql -u root -p123456 -S /tmp/mysql_3306.sock  -e "SET GLOBAL innodb_fast_shutdown=0"
    #关闭MySQL服务
    mysqld_multi --defaults-file=/etc/3306.cnf --password='123456' stop 3306
    #冷拷贝到部署MySQL5.7的环境
    #启动服务
    mysqld_multi --defaults-file=/etc/3306.cnf --password='123456' start 3306
    #更改配置文件，解决版本不兼容的参数配置
    关注点为sql_mode、并行复制
    #使用mysqld_upgrade 检查并修改和MySQL5.7结构不一致的地方
    mysql_upgrade -uroot -p'123456' -S /tmp/mysql_3306.sock 
    #重启服务，加载变更的数据结构

![复制代码][Image 1]

#### 注意点 ####

mysq\_upgrade 不会更改help表的内容，需要手动升级，sql文件存储路径在`share或` `share/mysql目录下`

mysql -uroot -S /tmp/mysql_3306.sock <fill_help_tables.sql

[mysql_upgrade]: http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html
[mysqldump]: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
[Image 1]: 
[mysqld --initialize]: http://dev.mysql.com/doc/refman/5.7/en/mysqld.html
[ONLY_FULL_GROUP_BY]: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
[STRICT_TRANS_TABLES]: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables
[NO_ENGINE_SUBSTITUTION]: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_engine_substitution
[logical upgrade]: http://dev.mysql.com/doc/refman/5.7/en/upgrading.html#upgrade-procedure-logical
[--add-drop-table]: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_add-drop-table
[--flush-privileges]: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_flush-privileges