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

總結

以上是生活随笔為你收集整理的mysql5.1.7升级到5.6_1 MySQL5.6 升级到 5.7 版本的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。