升級(jí)方法介紹
MySQL升級(jí)方式有兩種诚卸，一種叫In-Place Upgrade，另一種叫Logical Upgrade (邏輯升級(jí)方式)绘迁。
Logical Upgrade:利用mysqldump來直接導(dǎo)出SQL文件合溺，然后導(dǎo)入到新庫(kù)中，適應(yīng)于跨大版本的升級(jí)方案缀台，做法相對(duì)安全棠赛，并能整理表中碎片。但如果有數(shù)據(jù)量較大的庫(kù)需要mysqldump導(dǎo)出膛腐，時(shí)間上的消耗就會(huì)很大睛约，升級(jí)效率就會(huì)受到影響。
In-Place Upgrade:它的工作方法簡(jiǎn)單快速哲身，就是直接替換掉原來版本MySQL的安裝目錄和my.cnf配置文件辩涝，利用mysql_upgrade腳本來完成系統(tǒng)表的升級(jí)。
本文演示使用In-Place Upgrade升級(jí)MySQL律罢。
升級(jí)步驟
操作之前膀值，查看版本
# /usr/local/mysql/bin/mysql -V
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.24-72.2, for Linux (x86_64) using 6.0
設(shè)置參數(shù)innodb_fast_shutdown為0
需要將innodb_fast_shutdown參數(shù)設(shè)置為0
#mysql -u root -pp@ssw0rd --execute="SET GLOBAL innodb_fast_shutdown=0"
或
set global innodb_fast_shutdown=0;
注：innodb_fast_shutdown有 0、1误辑、2 三個(gè)值沧踏。
參數(shù)值為0代表MySQL關(guān)閉，InnoDB需要完成所有的full purge和merge Insert buffer操作巾钉，這個(gè)過程需要一定的時(shí)間翘狱，有時(shí)可能會(huì)花上幾個(gè)小時(shí)。
參數(shù)值為1是該參數(shù)的默認(rèn)值砰苍，表示關(guān)閉MySQL時(shí)不完成full purge和Merge insert buffe操作潦匈，但是緩沖池中的臟頁(yè)還是會(huì)寫到磁盤中。
參數(shù)值為2時(shí)赚导，表示既不完成full purge和Merge insert buffer操作茬缩，也不將緩沖池中的臟頁(yè)刷新到磁盤，而是將日志寫入日志文件中吼旧。
關(guān)閉MySQL服務(wù)
命令如下：
# myadm stop
Port 3306 Instance being shutdown
Port 3307 Instance being shutdown
# dbs
=========================================
PORT STATUS BUFFER_POOL DATABASE
3306 [Down] [2G] dbmonitor zlz
3307 [Down] [2G] dbmonitor zlz
或
[root@source bin]# /usr/local/mysql/bin/mysqladmin -uroot -poracle123 shutdown
替換MySQL軟件
將MySQL5.7的安裝包拷貝到目標(biāo)機(jī)器：
#scp tool_mysql57_ssd.tar.gz root@10.x.x.x:/data
替換MySQL軟件包：
# tar -zxvf tool_mysql57_ssd.tar.gz
# tar -zxvf Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz
mv /data/Percona-Server-5.7.26-29-Linux.x86_64.ssl101 /usr/local
cd /usr/local
mv mysql mysql56.bak
mv Percona-Server-5.7.26-29-Linux.x86_64.ssl101 mysql
# /data/Percona-Server-5.7.26-29-Linux.x86_64.ssl101/bin/mysql -V
/data/Percona-Server-5.7.26-29-Linux.x86_64.ssl101/bin/mysql Ver 14.14 Distrib 5.7.26-29, for Linux (x86_64) using 6.0
修改參數(shù)文件 my.cnf
把MySQL5.6的配置文件替換成5.7版本的my.cnf凰锡。
vim /data/mysqldata/my3306/my.cnf
注釋掉2個(gè)參數(shù)
#innodb_read_ahead=0
#key_buffer=64M
增加1個(gè)參數(shù)
log_timestamps=system
在MySQL 5.7.2 新增了 log_timestamps 這個(gè)參數(shù)，該參數(shù)主要是控制 error log、genera log掂为，等等記錄日志的顯示時(shí)間參數(shù)
且默認(rèn)安裝后error_log,slow_log 日志時(shí)間戳默認(rèn)為UTC裕膀，因此會(huì)造成與系統(tǒng)時(shí)間不一致，與北京時(shí)間相差8個(gè)小時(shí)
mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.00 sec)
因?yàn)閘og_timestamps 是一個(gè)GLOBAL的全局參數(shù),所以直接在登錄后去set全局參數(shù),重啟后就會(huì)直接失效
因此需要在mysql的配置文件中[mysqld]中增加一條log_timestamps的配置勇哗。
修改1個(gè)參數(shù)
cp /usr/local/mysql/lib/mysql/libjemalloc.so.1 /usr/lib64/libjemalloc.so.1
cd /usr/lib64/
ll -h | grep libjemalloc
修改[mysqld_safe]
修改前:
[mysqld_safe]
malloc-lib=/usr/local/mysql/lib/mysql/libjemalloc.so
修改后:
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1
啟動(dòng)MySQL實(shí)例
注：在啟動(dòng)過程中昼扛，需要添加–skip-grant-tables和–skip-networking參數(shù)，來保證沒有 任何的應(yīng)用連接欲诺，讓升級(jí)過程更加安全抄谐。
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/my3306/my.cnf --socket=/data/mysqldata/my3306/run/mysql.sock --port=3306 &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/my3307/my.cnf --socket=/data/mysqldata/my3307/run/mysql.sock --port=3307 &
升級(jí)系統(tǒng)表數(shù)據(jù)字典信息 mysql_upgrade
命令如下：
#/usr/local/mysql/bin/mysql_upgrade -u root -pp@ssw0rd --socket=/usr/local/mysql/mysql.sock
輸出結(jié)果：
[root@zlz-test:/usr/local/mysql]
# /usr/local/mysql/bin/mysql_upgrade -u root -s --socket=/data/mysqldata/my3306/run/mysql.sock --port=3306
Enter password:
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
[root@zlz-test:/usr/local/mysql]
# /usr/local/mysql/bin/mysql_upgrade -u root -s --socket=/data/mysqldata/my3307/run/mysql.sock --port=3307
Enter password:
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
沒有報(bào)錯(cuò)，表示系統(tǒng)表數(shù)據(jù)字典信息升級(jí)成功扰法。
重啟MySQL服務(wù)
首先停掉MySQL服務(wù)斯稳，命令如下：
# myadm stop
Port 3307 Instance being shutdown
Port 3306 Instance being shutdown
或
/usr/local/mysql/bin/mysqladmin -uroot -poracle123 shutdown
然后正常啟動(dòng) MySQL 服務(wù)，命令如下：
# myadm start
Port 3306 Instance being startup
Port 3307 Instance being startup
或
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
說明：正常啟動(dòng)MySQL數(shù)據(jù)庫(kù)迹恐，不要使用–skip-grant-tables和-skip-networking參數(shù)挣惰。
驗(yàn)證MySQL版本
目前己經(jīng)是MySQL5.7版本，證明升級(jí)成功：
# /usr/local/mysql/bin/mysql -V
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.7.26-29-log |
+---------------+
1 row in set (0.00 sec)
其他
啟動(dòng)報(bào)錯(cuò)
啟動(dòng)報(bào)錯(cuò)殴边，就看alert.log 憎茂，一般是哪個(gè)參數(shù)文件報(bào)錯(cuò)改哪個(gè)就好了。
啟動(dòng)報(bào)錯(cuò)：'/usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqld
由于libjemalloc.so.1 在 basedir 中存在锤岸，可是MySQL又限制了只能在/usr/lib64竖幔、/usr/lib等等標(biāo)準(zhǔn)庫(kù)中加載這些動(dòng)態(tài)文件所以啟動(dòng)的時(shí)候會(huì)報(bào)錯(cuò)。
報(bào)錯(cuò)如下:
[root@zlz-test:/usr/lib64]
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/my3307/my.cnf --socket=/data/mysqldata/my3307/run/mysql.sock --port=3307 &
[1] 26006
[root@zlz-test:/usr/lib64]
# mysqld_safe Adding '/usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqld
2021-04-20T09:30:41.480555Z mysqld_safe Logging to '/data/mysqldata/my3307/log/alert.log'.
2021-04-20T09:30:41.510366Z mysqld_safe Starting mysqld daemon with databases from /data/mysqldata/my3307/data
^C
mysqld_safe --malloc-lib must be located in one of the directories: /usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu
其實(shí)查看報(bào)錯(cuò)的提示就很明顯是偷，就是對(duì)于加載這些庫(kù)只能在/usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu這個(gè)庫(kù)中
解決
1拳氢、將libjemalloc.so.1文件拷貝到 /usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu 這幾個(gè)目錄中
cp /usr/local/mysql/lib/mysql/libjemalloc.so.1 /usr/lib64/libjemalloc.so.1
2透敌、修改[mysqld_safe]
修改前:
[mysqld_safe]malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so.1
修改后:
[mysqld_safe]malloc-lib = /usr/lib64/libjemalloc.so.1
3羊赵、重啟MySQL
[root@centos7 mysql]# numactl --interleave=all /bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_3306.cnf mysqld_safe Adding '/usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqldmysqld_safe Adding '/usr/local/mysql/lib/libHotBackup.so' to LD_PRELOAD for mysqld2016-12-28T09:03:30.653874Z mysqld_safe Logging to '/u01/mysql/mysql_3306/logs/trace/mysql.err'.2016-12-28T09:03:30.675215Z mysqld_safe Transparent huge pages are already set to: never.2016-12-28T09:03:30.692654Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql/mysql_3306/data
可見已經(jīng)啟動(dòng)成功了
當(dāng)然可以去修改mysqld_safe文件代碼幽崩，但是對(duì)于要腳本部署就比較麻煩了褐缠，需要使用sed對(duì)代碼進(jìn)行替換，因?yàn)槊總€(gè)版本MySQL的mysqld_safe都會(huì)有所不同竣贪。所以這邊就使用拷貝的方法暫時(shí)解決堂鲤。
注意
MySQL 的basedir是一個(gè)軟連接孩哑，那libHotBackup.so也需要修改
具體最終操作如下:
1玛瘸、拷貝相關(guān)動(dòng)態(tài)鏈接庫(kù)
cp /usr/local/mysql/lib/mysql/libjemalloc.so.1 /usr/lib64/libjemalloc.so.1
cp /usr/local/mysql/lib/libHotBackup.so /usr/lib64/libHotBackup.so
mv /usr/local/mysql/lib/libHotBackup.so /usr/local/mysql/lib/libHotBackup.so.bak
2蜕青、修改my.cnf
[mysqld_safe]malloc-lib = /usr/lib64/libjemalloc.so.1
