MySQL升级-5.6升级到5.7版本&切换GTID模式

2022-06-07 00:37:01

 
   MySQL升级-5.6升级到5.7版本&切换GTID模式 目前未在生产环境中升级过数据库版本，倒是在测试环境跟开发环境升级过。
 
    可以通过mysqldump sql文件进行升级，也可以通过mysql_upgrade升级，前者耗时较长，且需要足够量的磁盘空间，本文暂不讨论，升级使用mysql_upgrade方式。   
 
 
    如果转载，请注明博文来源： www.cnblogs.com/xinysu/   ，版权归 博客园 苏家小萝卜 所有。望各位支持！
 
 
    如果是线上环境升级，常规来说分为以下几个步骤：
  1. 从库先升级
    1. 业务迁移，从库上若有只读业务或者其他，迁移到其他DB实例
    2. 从库备份
    3. 从库停止复制
    4. 升级
    5. 从库恢复复制（升级后主库仍是5.6版本，从库是5.7版本，注意是否有异常）
    6. 主从恢复正常
  2. 主从切换
  3. 新从库升级
    1. 新从库停止复制
    2. 新从库备份
    3. 升级
    4. 新从库恢复复制
    5. 主从恢复正常
    6. 恢复相关业务
    本文主要记录升级的详细步骤、主库5.6从库5.7有哪些问题以及如何从传统模式转变为GTID模式。

1 MySQL5.6升级到5.7版本

升级步骤简要如下：
  1. 安装新版本mysql，从库服务器安装5.7版本mysql
  2. 修改安装目录配置参数，修改从库的mysql配置文件，把 mysql 安装目录修改为 5.7版本的安装目录
  3. 关闭从库mysql服务
  4. 新版本mysql启动实例，使用5.7版本mysql启动待升级实例
  5. 升级字典，使用mysql_upgrade升级字典
  6. 检查，查看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

Enter password:

[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

mysql    15477 37.1 26.7 11931672 1037520 pts/4 Sl  03:34   0: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

Enter password:

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost                                  OK

mysql.event                                        OK

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

Upgrading the sys schema.

Checking databases.

sys.sys_config                                     OK

省略...

检查用户数据库及表格

省略...

Upgrade process completed successfully.

Checking if update is needed.

#6 检查日志

查看log日志正常。

2 主库5.6从库5.7存在问题

由于从库是5.7版本，mysql、performance、sys等一些系统数据库对象有发生变化，同时一些SQL也有所变动。

2.1 修改用户密码失败

1). 问题
主库修改用户密码，update mysql.user set password=password('newpasswd') where ...
  • 2018-03-29T01:22:45.058927Z 12 [ERROR] Slave SQL for channel '': Column 1 of table 'mysql.user' cannot be converted from type 'char(16)' to type 'char(32)', Error_code: 1677
  • 2018-03-29T01:22:45.059066Z 12 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin_log.000003' position 3208
 
2). 分析
修改导致从库复制异常停止，因为 5.6版本mysql.user表格的password字段，在5.7没有该字段，修改为 authentication_string
3). 处理
方案1：事先处理，执行update password 的前，配置会话不记录binlog：set session sql_log_bin=off，然后单独到主从执行该SQL
方案2：事后处理，如果已经出现这个错误，则在从库跳过该sql然后再开启复制同步，最后从库修改密码
  • set global sql_slave_skip_counter=1;
  • start slave sql_thread;
  • show slave status;
  • set session sql_log_bin=off;
  • alter user suuser@'%' identified by 'newpassword';
  • flush privileges;
  • set session 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
上一篇：如何升级centos到最新版本


下一篇：AWT和Swing