MySQL版本升级之5.6到5.7

reko_3

两种升级方式

• 　　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.
  
  
• 　　Logical Upgrade: Involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.
  
  

主从的升级：

• 主从想都替换二进制安装包为最新版本
  
• 停从，通过mysql_upgrade升级后，加参数 --skip-slaves-start 进行启动　　
  
• 加参数 --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环境下的

　　

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)
　　

　　

　　mysql> show variables like '%56%';
　　+-----------------------------+-------+
　　| Variable_name               | Value |
　　+-----------------------------+-------+
　　| sha256_password_proxy_users | OFF   |
　　| show_compatibility_56       | ON    |
　　+-----------------------------+-------+
　　2 rows in set (0.00 sec)

• 使用mysqld --initialize (or mysqld --initialize-insecure).初始化实例
  

sql mode
　　ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, 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;　　


系统表的改变
　　mysql.user的password字段在5.7.6中已去除，认证信息记录在authentication_string中，运行in-place upgrade 迁移password列值到authentication_string
　　如果是通过logical upgrade，需要注意：

• 　　You must include the --add-drop-table option
  
  
• 　　You must not include the --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的变更，如：

　　

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
　　

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

SQL变更
　　GET_LOCK（）函数行为
　　MySQL5.7.5之前GET_LOCK()在执行第二次的额时候会释放前面获得的锁，在此版本以后支持同时获得多个锁，如：

　　

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>　　
+-----------------------+
　　
|>　　
+-----------------------+
　　
|                     1 |
　　
+-----------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> SELECT>　　
+-----------------------+
　　
|>　　
+-----------------------+
　　
|                  NULL |
　　
+-----------------------+
　　
1 row in set (0.00 sec)
　　

　　返回null说明此锁已经被释放了
　　所以依赖于释放任何先前锁的GET_LOCK（）的行为的应用程序必须针对新行为进行修改。

derived_merge被自动开启
　　5.7中优化器使用一致的机制处理from语句中的派生表和视图是为了更好地避免不必要的物化并能够通过条件下放产生更有效的执行计划。
　　但是，对于修改表的语句（例如DELETE或UPDATE），使用先前物化的派生表的合并策略可能会导致ER_UPDATE_TABLE_USED错误：
　　错误原因：外部查询表和内部更改的表属于同一张表时，如果将派生表合并到外部查询块就会触发此错误（物化方式不会导致此错误，因为实际上，它将派生表转换为单独的表）
　　如：
　　

mysql> delete from t1 where>
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
　　

　　解决：关闭optimizer_switch的derived_merge选项，此选项默认是打开的


　　

# 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
　　

　　关闭derived_merge
　　

SET optimizer_switch = 'derived_merge=off'; 　　


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


　　

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)
　　

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

ACCOUNT
ALWAYS
CHANNEL
COMPRESSION
ENCRYPTION
FILE_BLOCK_SIZE
FILTER
FOLLOWS
GENERATED (R)
GROUP_REPLICATION
INSTANCE
JSON
MASTER_TLS_VERSION
NEVER
OPTIMIZER_COSTS (R)
PARSE_GCOL_EXPR
PRECEDES
REPLICATE_DO_DB
REPLICATE_DO_TABLE
REPLICATE_IGNORE_DB
REPLICATE_IGNORE_TABLE
REPLICATE_REWRITE_DB
REPLICATE_WILD_DO_TABLE
REPLICATE_WILD_IGNORE_TABLE
ROTATE
STACKED
STORED (R)
VALIDATION
VIRTUAL (R)
WITHOUT
XID
















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


　　

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);
　　
+------+-------+-------+

　　
|>　　
+------+-------+-------+
　　
|    1 | a1    | a2    |
　　
|    1 | 2     | 2     |
　　
|    1 | 1     | 1     |
　　
+------+-------+-------+
　　
3 rows in set (0.00 sec)
　　

参考文献：
　　http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html