mysql5.1.7升级到5.6_1 MySQL5.6 升级到 5.7 版本

1 MySQL5.6 升级到 5.7 版本

目前未在生产环境中升级过数据库版本, 倒是在测试环境跟开发环境升级过

可以通过 mysqldump sql 文件进行升级, 也可以通过 mysql_upgrade 升级, 前者耗时较长, 且需要足够量的磁盘空间, 本文暂不讨论, 升级使用 mysql_upgrade 方式

如果是线上环境升级, 常规来说分为以下几个步骤:

从库先升级

业务迁移, 从库上若有只读业务或者其他, 迁移到其他 DB 实例

从库备份

从库停止复制

升级

从库恢复复制 (升级后主库仍是 5.6 版本, 从库是 5.7 版本, 注意是否有异常)

主从恢复正常

主从切换

新从库升级

新从库停止复制

新从库备份

升级

新从库恢复复制

主从恢复正常

恢复相关业务

本文主要记录升级的详细步骤主库 5.6 从库 5.7 有哪些问题以及如何从传统模式转变为 GTID 模式

升级步骤简要如下:

安装新版本 mysql, 从库服务器安装 5.7 版本 mysql

修改安装目录配置参数, 修改从库的 mysql 配置文件, 把 mysql 安装目录修改为 5.7 版本的安装目录

关闭从库 mysql 服务

新版本 mysql 启动实例, 使用 5.7 版本 mysql 启动待升级实例

升级字典, 使用 mysql_upgrade 升级字典

检查, 查看 mysql log 日志#1 安装新版本 mysql

## 下载 mysql5.7.17, 拷贝到 server 下的 / opt 文件目录下

## 解压, 创建软连接, 授权

tar zvxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

ln-s/opt/mysql-5.7.17-linux-glibc2.5-x86_64/usr/local/mysql57

chown-R mysql:mysql/usr/data/mysql57

#2 修改配置参数

## 检查配置文件中那些配置是使用到了 安装目录, 把使用到底都修改

旧:basedir=/usr/local/mysql56

plugin-dir=/usr/local/mysql56/lib/plugin/

新:basedir=/usr/local/mysql

plugin-dir=/usr/local/mysql/lib/plugin/

#3 关闭 mysql

[root@sutest244 mysqlup]#/usr/local/mysql56/bin/mysqladmin--socket=/tmp/mysql3399.sock-uroot-p shutdown

Enterpassword:

[root@sutest244 mysqlup]#ps axu|grep mysql3399|grep mysqld

[root@sutest244 mysqlup]#

#4 新版本启动 mysql

[root@sutest244 mysqlup]#/usr/local/mysql/bin/mysqld--defaults-file=/data/mysqlup/mysql3399.cnf&

[1]15477

[root@sutest244 mysqlup]#ps axu|grep mysql3399|grep mysqld

mysql1547737.126.7119316721037520pts/4Sl03:340:05/usr/local/mysql/bin/mysqld--defaults-file=/data/mysqlup/mysql3399.cnf

[root@sutest244 mysqlup]#

[root@sutest244 mysqlup]#vim/data/mysqlup/data/error.log

#4.1 检查

检查启动后的错误日志, 看下是否有配置参数报错, 如果有, 修改

错误日志会有大量的字典信息报错, 这个暂不处理, 下个步骤修复#5 升级字典

[root@sutest244 bin]#/usr/local/mysql/bin/mysql_upgrade--socket=/tmp/mysql3399.sock-uroot-p

Enterpassword:

Checkingifupdateisneeded.

Checkingserver version.

Runningqueries to upgradeMySQLserver.

Checkingsystem database.

mysql.columns_priv OK

mysql.db OK

mysql.engine_cost OK

mysql.eventOK

mysql.func OK

mysql.general_log OK

mysql.gtid_executed OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.innodb_index_stats OK

mysql.innodb_table_stats OK

mysql.ndb_binlog_index OK

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.server_cost OK

mysql.servers OK

mysql.slave_master_info OK

mysql.slave_relay_log_info OK

mysql.slave_worker_info OK

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

Upgradingthe sys schema.

Checkingdatabases.

sys.sys_config OK

省略...

检查用户数据库及表格

省略...Upgradeprocess completed successfully.

Checkingifupdateisneeded.

#6 检查日志

查看 log 日志正常

2 主库 5.6 从库 5.7 存在问题

由于从库是 5.7 版本, mysqlperformancesys 等一些系统数据库对象有发生变化, 同时一些 SQL 也有所变动

2.1 修改用户密码失败

1). 问题

主库修改用户密码, update mysql.user set password=password('newpasswd') where ...2018-03-29T01:22:45.058927Z12[ERROR]SlaveSQLforchannel'':Column1of table'mysql.user'cannot be convertedfromtype'char(16)'to type'char(32)',Error_code:1677

2018-03-29T01:22:45.059066Z12[ERROR]Errorrunning query,slave SQL thread aborted.Fixthe problem,andrestart the slave SQL threadwith"SLAVE START".Westopped at log'bin_log.000003'position3208

2). 分析

修改导致从库复制异常停止, 因为 5.6 版本 mysql.user 表格的 password 字段, 在 5.7 没有该字段, 修改为 authentication_string

3). 处理

方案 1: 事先处理, 执行 update password 的前, 配置会话不记录 binlog:set session sql_log_bin=off, 然后单独到主从执行该 SQL

方案 2: 事后处理, 如果已经出现这个错误, 则在从库跳过该 sql 然后再开启复制同步, 最后从库修改密码setglobalsql_slave_skip_counter=1;

start slave sql_thread;

show slave status;

setsession sql_log_bin=off;

alter user suuser@'%'identifiedby'newpassword';

flush privileges;

setsession sql_log_bin=on;

2.2 SQL 语法问题

1). 问题

SELECT 字段超过 GROUP BY 字段报错

select id,name,age,count(*) from tbuser group by name;

其他一些 SQL 语法问题

2). 分析

5.7 跟 5.6 默认的 sql_mode 配置不一样, 如果 mysql 配置文件中没有说明 sql_mode, 升级后 sql_mode 将从 NO_ENGINE_SUBSTITUTION 修改为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION, 该模式下会导致部分在 5.6 支持的 SQL 在 5.7 报语法错误

3). 处理

方案 1: 事先处理, 在测试环境中, 详细测试程序代码在新版本数据库上的兼容性, 若有异常, 则修复程序代码中的 SQL 操作逻辑

方案 2: 事先处理, mysql 配置文件中, 指定 sql_mode 与 5.6 版本一致

方案 3: 事后处理, 如果已经在出现这个错误, 有需要快速响应处理, 可以把 sql_mode 修改为跟 5.6 版本默认的 sql_mode 一致即可

3 切换 GTID 模式

3.1 何为 GTID

Global Transaction ID, 全局唯一标识, 简称 GTID, 一个 GTID 代表在 某个实例上发生的一个事务

GTID = source_id:transaction_id, 其中 source_id 代表执行该事务的实例的 server_uuid,transaction_id 是自增值, 从 1 开始, 故 GTID 实际表示为: 在 source_id 实例上面发生的 第 transaction_id 个事务

3.2 GTID 相关配置参数ENFORCE_GTID_CONSISTENCY

warn

如果出现 GTID 不兼容的语句用法, 在错误日志会记录相关信息, 那么需要调整应该程序避免不兼容的写法, 直到完全没有产生不兼容的语句, 可以通过应该程序去排查所有的 sql, 也可以设置后观察错误日志一段时间, 这一步非常重要

on

启动强制 GTID 一致性

GTID_MODE

说明

OFF

新事务是非 GTID, Slave 只接受不带 GTID 的事务, 传送来 GTID 的事务会报错

OFF_PERMISSIVE

新事务是非 GTID, Slave 只接受不带 GTID 的事务也接受带 GTID 的事务

ON_PERMISSIVE

新事务是 GTID, Slave 只接受不带 GTID 的事务也接受带 GTID 的事务

ON

新事务是 GTID, Slave 只接受带 GTID 的事务

切换顺序

需要严格按照以下顺序, 不可跳跃

OFF <= => OFF_PERMISSIVE <= => ON_PERMISSIVE <= => ON

3.3 传统复制切换 GTID 复制#step 1

# 修改 ENFORCE_GTID_CONSISTENCY 为 warn , 运行一段时间, 检查错误日志里边是否存在于 GTID 不兼容的语句用法, 并尽快修复

# 主从都执行, 先后顺序不要求

set@@global.enforce_gtid_consistency=warn;

#step 2

# 修改 ENFORCE_GTID_CONSISTENCY 为 on , 确定没有不兼容语法后, 可以修改为 ON

# 主从都执行, 先后顺序不要求

set@@global.enforce_gtid_consistency=on;

#step 3

# 设置 GTID_MODE 为 off_permissiv

# 主从都执行, 先后顺序不要求

SET@@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

#step 4

# 设置 GTID_MODE 为 off_permissiv=on_permissiv

# 主从都执行, 先后顺序不要求

SET@@GLOBAL.GTID_MODE=ON_PERMISSIVE;

#step 5

# 检查全部实例 正在进行的匿名交易数目, 也就是非 GTID 事务有没有都传送到从库上了, 需要等到这个变量为 0 才是可以进行下面操作

# 主从都执行, 先后顺序不要求

SHOW STATUS LIKE'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

#step 6

# 检查所有实例上面的 slave 的非 GTID 是否都执行完了

show master status;#取file跟pos到从库去执行查看

SELECT MASTER_POS_WAIT('bin_log.000003',88748605);#返回结果大于等于 0 则说明事务已经完全复制完成

#step 7

# 清理 binlog, 切换到新的 binlog 上面

# 主从都执行, 先后顺序不要求

flush logs;

#step8

# 启动 GTID

# 主从都执行, 先后顺序不要求

SET@@GLOBAL.GTID_MODE=ON;

#step 9

# 修改 cnf 文件

# 主从都执行, 先后顺序不要求

gtid_mode=on

enforce-gtid-consistency=on

binlog_gtid_simple_recovery=1

3.4 GTID 复制切换传统复制#step 1

# 停止从库

# 所有从库都执行, 先后顺序不要求

stop slave;

#step 2

# 重置 chanage master to 语句, 关闭 master_auto_position

# 所有从库都执行, 先后顺序不要求

show slave status \G;#取 sql_thread 的 file 跟 position 位置, Relay_Master_Log_File Exec_Master_Log_Pos

change master to master_log_file='mysql-bin.000003',master_log_pos=4563,master_auto_position=0;

#step 3

# 测试同步是否正常

# 主库对数据进行操作, 看从库的 position 有没有变化, 同时看数据是否变更

#step 4

# 修改 GTID_MODE 为 ON_PERMISSIVE

# 主从都执行

SET@@GLOBAL.GTID_MODE=ON_PERMISSIVE;

#step 5

# 修改 GTID_MODE 为 OFF_PERMISSIVE

# 主从都执行

SET@@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

#step 6

# 修改 GTID_MODE 为 OFF

# 主从都执行

SET@@GLOBAL.GTID_MODE=OFF;

#step 7

# 清理 binlog, 切换到新的 binlog 上面

# 主从都执行, 先后顺序不要求

flush logs;

#step8

# 禁用 GTID, 其中 enforce-gtid-consistency 可以不关闭, 还是进行 GTID 的一致性检查

# 主从都执行, 先后顺序不要求

SET@@GLOBAL.GTID_MODE=OFF;

#step9

# 检验同步情况

#10

# 修改 cnf 文件, 注释 GTID 的参数

# 主从都执行, 先后顺序不要求

#gtid_mode=on

#enforce-gtid-consistency=on

#binlog_gtid_simple_recovery=1

来源: https://www.cnblogs.com/xinysu/p/8675286.html