前言：由于安全掃描，漏洞問題需進(jìn)行升級，實際這是個無底洞
環(huán)境
- CentOS 7
- MySQL 5.7.27 --> MySQL 5.7.34
采用的是 in-place 方式升級
MySQL升級
1.檢查XA事務(wù)
# XA檢查是否有值
mysql> XA RECOVER;
Empty set (0.00 sec)
# 若有值，則需要 COMMIT 或 ROLLBACK xid
mysql> XA COMMIT xid;
或
mysql> XA ROLLBACK xid;
2.完全關(guān)閉MySQL
mysql> SET GLOBAL innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@BDGatewayT ~]# service mysqld stop
Shutting down MySQL.... SUCCESS!
3.升級前備份數(shù)據(jù)文件
cd /app
cp -rp mysql_data/ mysql_data_20210623
4.升級 MySQL 二進(jìn)制安裝或軟件包
cd /usr/local
lrwxrwxrwx. 1 root root 46 Nov 12 2019 mysql -> /usr/local/mysql-5.7.27-linux-glibc2.12-x86_64 -- 原來的軟鏈
drwxr-xr-x. 9 root mysql 129 Nov 12 2019 mysql-5.7.27-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 Jun 24 14:58 mysql-5.7.34-linux-glibc2.12-x86_64
-rw-r--r-- 1 root root 665389778 Mar 26 14:43 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
-- 刪除軟鏈
rm -rf mysql
-- 重建軟鏈
ln -s mysql-5.7.34-linux-glibc2.12-x86_64 mysql
lrwxrwxrwx 1 root root 35 Jun 24 15:16 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64 -- 新的軟鏈昏翰，鏈接到了 5.7.34 版本
drwxr-xr-x. 9 root mysql 129 Nov 12 2019 mysql-5.7.27-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 Jun 24 14:58 mysql-5.7.34-linux-glibc2.12-x86_64
-rw-r--r-- 1 root root 665389778 Mar 26 14:43 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
5.啟動MySQL
service mysqld start
6.執(zhí)行inplace升級
cd /usr/local/mysql/bin/
# mysql_upgrade -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
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
gateway.g_api_dtl OK
gateway.g_api_group OK
gateway.g_app_authorization OK
gateway.g_app_dtl OK
gateway.g_gateway_ins OK
gateway.g_gray_release OK
gateway.g_secret_key OK
gateway.g_stream_controler OK
gateway.ms_account OK
gateway.ms_account_role_relation OK
gateway.ms_resource OK
gateway.ms_role OK
gateway.ms_role_resource_relation OK
gateway.ms_route OK
gateway.ms_variable OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
7.關(guān)閉并重新啟動
關(guān)閉并重新啟動 MySQL 服務(wù)器以確保對系統(tǒng)表所做的任何更改生效
# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
一鍵升級腳本
需要修改配置文件中對應(yīng)的信息鹏溯，且腳本放在同一目錄下
[root@initnode101 ~]# clear
[root@initnode101 ~]# cat upgrading_mysql_config.txt
# MySQL 所在的bin路徑
mysql_bin_path=/usr/local/mysql/bin
# MySQL的root的密碼
mysql_user_root=root
mysql_root_password=123456
# 新版本的二進(jìn)制文件路徑
mysql_new_version=/tmp/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
# MySQL安裝路徑
mysql_base_local=/usr/local/
[root@initnode101 ~]# cat upgrading_mysql.sh
:<<!
描述：用于inplace升級MySQL小版本
腳本流程：
1.加載環(huán)境變量
2.驗證變量
3.連接MySQL，設(shè)置慢關(guān)機(jī)
4.關(guān)閉MySQL
5.替換為新版的MySQL
6.升級
7.再次重啟
!
#!/bin/bash
set -e
# ------------- 加載配置文件 --------------------------
## 已存在相同的環(huán)境變量旷余，不進(jìn)行加載
if [[ "$mysql_bin_path" = "" ]] && [[ "$mysql_root_password" = "" ]] && [[ "$mysql_new_version" = "" ]];then
echo "加載配置信息到環(huán)境變量"
source ./upgrading_mysql_config.txt
else
echo "[Error] 已存在相同的被使用的變量，請檢查扁达！"
exit 1
fi
# ---------------- 校驗變量信息 ----------------------
## 判斷所傳路徑或文件是否存在
if [ ! -d "$mysql_bin_path" ];then
echo "[Error] mysql_bin_path 路徑不存在..."
exit 1
fi
if [ ! -d "$mysql_base_local" ];then
echo "[Error] mysql_base_local 路徑不存在..."
exit 1
fi
if [ ! -f "$mysql_new_version" ];then
echo "[Error] mysql_new_version 文件不存在..."
exit 1
fi
# ------------ 配置免密 --------------
mysql_login(){
cat > /tmp/up.password << EOF
[client]
user=$mysql_user_root
password=$mysql_root_password
EOF
}
del_file(){
if [ -f /tmp/up.password ];then
rm -rf /tmp/up.password
fi
}
# 配置免密
mysql_login
# ---------- 查看XA事務(wù) --------------------
## 查看XA事務(wù)
echo "查看XA事務(wù)..."
xa_count=`"$mysql_bin_path"/mysql --defaults-extra-file=/tmp/up.password -e"XA RECOVER"|grep -v "[Warning] Using a password"|wc -l`
if [[ "$xa_count" -gt 0 ]];then
echo "[Error] 存在未關(guān)閉XA事務(wù)正卧，請確認(rèn)！"
del_file
exit 1
fi
# ---------- 設(shè)置 innodb_fast_shutdown 參數(shù) --------------------
echo "設(shè)置 innodb_fast_shutdown = 1 參數(shù)"
## 使用慢速關(guān)機(jī)跪解，InnoDB在關(guān)機(jī)前執(zhí)行完全清除和更改緩沖區(qū)合并炉旷，確保在版本之間的文件格式差異的情況下充分準(zhǔn)備數(shù)據(jù)文件。
"$mysql_bin_path"/mysql --defaults-extra-file=/tmp/up.password -e"set global innodb_fast_shutdown = 0"
# 獲取數(shù)據(jù)文件路徑
base_dir=`"$mysql_bin_path"/mysql --defaults-extra-file=/tmp/up.password -e"show variables like 'datadir'"|grep datadir|awk -F ' ' '{print $2}'`
if [ $base_dir = '' ];then
echo "[Error] 數(shù)據(jù)文件路徑有誤惠遏！mysql 參數(shù) basedir 沒有獲得對應(yīng)的值砾跃。"
del_file
exit 1
fi
if [ ! -d "$base_dir" ];then
echo "[Error] MySQL 數(shù)據(jù)文件路徑[$base_dir]不存在，請確認(rèn)节吮！"
del_file
exit 1
fi
# --------------- 關(guān)閉MySQL ------------------------------------
echo "關(guān)閉MySQL..."
"$mysql_bin_path"/mysqladmin --defaults-extra-file=/tmp/up.password shutdown
# ------------ 解壓新版的MySQL到指定路徑 -----------------------------
## 獲取解壓后的文件名稱
tar_name=`echo ${mysql_new_version/\/tmp\//''}`
untar_name=`echo ${tar_name/".tar.gz"/''}`
echo $untar_name
echo $mysql_base_local$untar_name
# 解壓新版本MySQL
if [ ! -d "$mysql_base_local$untar_name" ];then
echo "解壓MySQL新版本文件..."
tar zxvf "$mysql_new_version" -C "$mysql_base_local"
else
echo "[Warning] 已存在被解壓文件..."
fi
# ----------- 升級前備份下原數(shù)據(jù)文件 ----------------------
## 是否進(jìn)行備份原數(shù)據(jù)文件[拷貝原data文件為bak]
while true
do
echo "是否需要將原數(shù)據(jù)文件 $base_dir 進(jìn)行備份抽高，備份前請先確認(rèn)磁盤容量是否足夠。請輸入 [Y/N]"
read chooise
case $chooise in
Y | y)
echo "開始備份透绩，請等待..."
cp -rp $base_dir ${base_dir%/*}.bak.`date '+%Y%m%d%H%M%S'`
echo "備份完成翘骂，備份文件名為 ${base_dir%/*}.bak.日期"
break
;;
N | n)
echo "跳過備份操作..."
break
;;
*)
echo "請輸入正確的值！"
;;
esac
done
# ----------- 替換軟鏈為新版的MySQL -----------------------
## 刪除舊軟鏈
cd $mysql_base_local
if [ -L mysql ];then
echo "刪除舊軟鏈..."
rm -rf mysql
else
echo "[Error] 不存在軟鏈 mysql"
del_file
exit 1
fi
## 重建軟鏈
echo "重建軟鏈到新版本..."
ln -s $untar_name mysql
# 啟動數(shù)據(jù)庫
echo "啟動數(shù)據(jù)庫..."
service mysqld start
echo "進(jìn)行inplace升級..."
"$mysql_bin_path"/mysql_upgrade --defaults-extra-file=/tmp/up.password
echo "升級完成需要再次重啟下..."
service mysqld restart
# 清楚免密配置文件
del_file
