MySQL5.6升级到5.7及回退

1.1 升级说明

MySQL升级：MySQL中不存在打补丁的说法，MySQL的所谓的补丁就是升级到一个新版本，然后把原始数据拉起来进行升级。

1.2 升级方式

1.2.1 inplace就地升级

在一台服务器上，进行原版本升级到新版本，风险较大。如果是主从环境可以先就地升级从库，然后再升级主库，滚动方式升级。

1.2.2 逻辑迁移升级

准备新的服务器，然后将数据从源库中逻辑导出，然后再导入到新的数据库中，数据量大的时候，会很慢。例如：
如果是一主一从(主->从1)，在有条件的情况下，可以新准备一台服务器，采用物理备份的方式将数据备份恢复到新的服务中，然后构建从库的从库(主->从1->从2)，最后将从2进行inplace方式升级，然后进行业务验证，验证完成后构建主->从2。升级从1，将(主->从1)的从1断开，从1升级完成后，构建(主->从1，主->从2)，此时可以升级主库，主库停止写业务，主从关系变更为（从1->从2）原从1承担写库，待主库完成升级后重新加入主从即可。

1.2.3 前提建议

1.不管哪种方式升级，都先冷备全量数据，包括日志，便于回退。
2.升级之前一定要充分的测试验证，包含业务验证。

1.2.4 升级注意事项

1.支持GA版本之间的升级，不支持跨版本升级。
2.5.6升级到5.7时，先将5.6升级到最新版本，然后再升级到5.7
3.5.6升级5.7时，先将5.5升级到最新版本，然后从5.5的最新版本升级到5.6最新版本，最后从5.6最新版本升级到5.7最新版本
4.回退方案提前准备好，做好充足的演练；墙裂建议做好备份，尤其是升级到8.0最新版本时
5.降低停机时间，在业务不繁忙的月黑风高的后半夜升级。
6.升级过程中需要重启节点，

1.3 inplace升级过程(生产思路)

1.部署新版本的数据库软件
2. 设置参数：innodb_fast_shutdown=1，然后关闭数据库。 #表示不干净的关闭数据库，建议设置0，表示干净的关闭，该落盘的数据都落盘
3.冷备份数据库数据
4.使用新版本数据库，拉起旧版本的数据进行启动，参数（--skip-grant-tables --skip-networking）
5.启动完成后，验证业务功能
6.恢复业务，升级完成。

1.4 5.6inplace升级到5.7

5.6.50升级到5.7.32。

[root@localhost ~]# mysql --versionmysql  Ver 14.14 Distrib 5.6.50, for linux-glibc2.12 (x86_64) using  EditLine wrapper

1.4.1 部署要新版本的MySQL(5.7)

1.解压文件

tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gzln -s  /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64 mysql57

1.4.2 停服务

1停止业务发起新连接到数据库
停止前先断开业务，show processlist; 查看当前连接情况，如果连接比较多，需要手工杀掉，可以拼接语句批量杀掉
拼接语句：
select concat("kill ",id,";") from information_schema.processlist;
2.调整快速关闭参数

set global innodb_fast_shutdown=0;

例如：

mysql> select @@innodb_fast_shutdown;+------------------------+| @@innodb_fast_shutdown |+------------------------+|  1 |+------------------------+1 row in set (0.00 sec)mysql> set global innodb_fast_shutdown=0;Query OK, 0 rows affected (0.00 sec)mysql> select @@innodb_fast_shutdown;+------------------------+| @@innodb_fast_shutdown |+------------------------+|  0 |+------------------------+1 row in set (0.00 sec)

3.关闭服务
如果是多实例，可以使用该关闭方法：mysqladmin -S /tmp/mysql56.sock shutdown
单实例关闭：/etc/init.d/mysqld stop
关闭服务：

[root@localhost ~]# netstat -lntup	Active Inteet connections (only servers)Proto Recv-Q Send-Q Local AddressForeign Address StatePID/Program nametcp0  0 0.0.0.0:22  0.0.0.0:*LISTEN  14827/sshd  tcp0  0 127.0.0.1:250.0.0.0:*LISTEN  6526/master tcp60  0 :::3306 :::*LISTEN  16824/mysqldtcp60  0 :::22:::*LISTEN  14827/sshd  tcp60  0 ::1:25  :::*LISTEN  6526/master [root@localhost ~]# ps -ef|grep mysqlroot  16646  1  0 15:43 pts/000:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/localhost.localdomain.pidmysql 16824  16646  0 15:43 pts/000:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data/3306/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306root  16855  14763  0 15:52 pts/000:00:00 grep --color=auto mysql[root@localhost ~]# /etc/init.d/mysqld stop  # 停止服务Shutting down MySQL.. SUCCESS! [root@localhost ~]# ps -ef|grep mysqlroot  16878  14763  0 15:53 pts/000:00:00 grep --color=auto mysql[root@localhost ~]# [root@localhost ~]# netstat -lntupActive Inteet connections (only servers)Proto Recv-Q Send-Q Local AddressForeign Address StatePID/Program nametcp0  0 0.0.0.0:22  0.0.0.0:*LISTEN  14827/sshd  tcp0  0 127.0.0.1:250.0.0.0:*LISTEN  6526/master tcp60  0 :::22:::*LISTEN  14827/sshd  tcp60  0 ::1:25  :::*LISTEN  6526/master [root@localhost ~]# 

1.4.3 备份数据

1.冷备份
如果数据和日志都需要备份

[root@localhost data]# cp -r 3306 3306_bak # 生产环境中建议把数据单独其他盘

1.4.4 升级

1.4.4.1 升级前

1.使用5.7服务，拉起5.6的数据，然后升级

/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &

--defaults-file=/etc/my.cnf f # 为了读取5.6的数据

[root@localhost ~]# mysql# mysql 登录验证  此时启动成功，可以登录，但并未完成升级Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;  # 升级前5.6没有sys库+--------------------+| Database|+--------------------+| information_schema || mysql  || performance_schema || sysbenchdb || test|+--------------------+5 rows in set (0.01 sec)mysql> 

1.4.4.2 升级

升级：

/usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql.sock --force  # 该操作是升级系统表

一堆ok
例如：

[root@localhost ~]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql.sock --force Checking server version.Running queries to upgrade MySQL server.Checking system database.mysql.columns_priv OKmysql.dbOKmysql.engine_cost  OKmysql.eventOKmysql.func OKmysql.general_log  OKmysql.gtid_executedOKmysql.help_categoryOKmysql.help_keyword OKmysql.help_relationOKmysql.help_topicOKmysql.innodb_index_statsOKmysql.innodb_table_statsOKmysql.ndb_binlog_index OKmysql.pluginOKmysql.proc OKmysql.procs_privOKmysql.proxies_priv OKmysql.server_cost  OKmysql.servers  OKmysql.slave_master_infoOKmysql.slave_relay_log_info OKmysql.slave_worker_infoOKmysql.slow_log OKmysql.tables_priv  OKmysql.time_zoneOKmysql.time_zone_leap_secondOKmysql.time_zone_nameOKmysql.time_zone_transition OKmysql.time_zone_transition_typeOKmysql.user OKUpgrading the sys schema.Checking databases.sys.sys_config OKsysbenchdb.sbtest1 OKsysbenchdb.sbtest10OKsysbenchdb.sbtest11OKsysbenchdb.sbtest12OKsysbenchdb.sbtest13OKsysbenchdb.sbtest14OKsysbenchdb.sbtest15OKsysbenchdb.sbtest16OKsysbenchdb.sbtest17OKsysbenchdb.sbtest18OKsysbenchdb.sbtest19OKsysbenchdb.sbtest2 OKsysbenchdb.sbtest20OKsysbenchdb.sbtest3 OKsysbenchdb.sbtest4 OKsysbenchdb.sbtest5 OKsysbenchdb.sbtest6 OKsysbenchdb.sbtest7 OKsysbenchdb.sbtest8 OKsysbenchdb.sbtest9 OK# 应用表Upgrade process completed successfully.Checking if update is needed.

1.4.4.3 升级后

show databases;
查看是否有sys库。查看user表是否有authentication_string字段

[root@localhost ~]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> mysql> show databases;+--------------------+| Database|+--------------------+| information_schema || mysql  || performance_schema || sys|		# 此时有sys库| sysbenchdb || test|+--------------------+6 rows in set (0.00 sec)

1.4.5 重启服务

重启数据库：
升级完成后，还处理安全模式，因为在拉起数据的库的时候加了参数“--skip-grant-tables --skip-networking”，此时需要重启数据库
此时配置文件中的basedir可以调整成新版本的
修改配置：

 [root@localhost ~]# cat /etc/my.cnf[mysqld]user=mysqlbasedir=/usr/local/mysql57 # 由之前的56版本改成57datadir=/data/3306/dataserver_id=56port=3306socket=/tmp/mysql.socklower_case_table_names=1 重启服务[root@localhost ~]# /etc/init.d/mysqld restart # 重启服务Shutting down MySQL..2022-01-03T08:28:21.739297Z mysqld_safe mysqld from pid file /data/3306/data/localhost.localdomain.pid ended SUCCESS! Starting MySQL. SUCCESS! [1]+  Done/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking[root@localhost ~]#[root@localhost ~]# netstat -lntup	Active Inteet connections (only servers)Proto Recv-Q Send-Q Local AddressForeign Address StatePID/Program nametcp0  0 0.0.0.0:22  0.0.0.0:*LISTEN  14827/sshd  tcp0  0 127.0.0.1:250.0.0.0:*LISTEN  6526/master tcp60  0 :::3306 :::*LISTEN  16824/mysqldtcp60  0 :::22:::*LISTEN  14827/sshd  tcp60  0 ::1:25

以下方式启动也可以：
/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my.cnf &

1.4.6 验证
启动完成后，验证业务

[root@localhost ~]# mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@localhost ~]# mysql -uroot -p123Waing: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.32 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databses;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1mysql> show databases;+--------------------+| Database|+--------------------+| information_schema || mysql  || performance_schema || sys|| sysbenchdb || test|+--------------------+6 rows in set (0.01 sec)mysql> use sysbenchdb;Reading table information for completion of table and column namesYou can tu off this feature to get a quicker startup with -ADatabase changedmysql> select * from sbtest1 limit 3;+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id | k| c| pad |+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+|  1 | 3231 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 ||  2 |  557 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 ||  3 | 2758 | 16516882386-05118562259-88939752278-62800788230-55205008755-06868633780-74894238647-69655573455-70526404237-73358617781 | 73198647949-50059256035-48039302709-77824424754-93913530645 |+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+3 rows in set (0.00 sec)

注意：升级只是升级了系统表，和数据量没有关系。

1.5小结

1.升级前也可以单独把所有的表结构进行备份
2.生产环境中建议把数据单独其他盘
生产环境中操作步骤建议复制粘贴执行，手敲容易出错
5.重启时，不建议直接杀进程！！
4.5.6中没有sys库，升级后检查库中是否由sys库

2.1 回退

1.调整配置文件

[root@localhost ~]# cat /etc/my56.cnf [mysqld]user=mysqlbasedir=/usr/local/mysql  # 56的应用datadir=/data/3306_bak/data/  # 备份的数据server_id=3356port=3356# 新起一个端口，生产用原来端口即可socket=/tmp/mysql56.sock[root@localhost ~]# 

2.修改备份数据属主

[root@localhost data]# chown -R mysql.mysql 3306_bak[root@localhost data]# ls -ld 3306_bakdrwxr-xr-x 3 mysql mysql 18 Jan  3 16:23 3306_bak

3.启动服务

[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my56.cnf &[1] 20111[root@localhost ~]# 220103 23:26:07 mysqld_safe Logging to '/data/3306_bak/data/localhost.localdomain.err'.220103 23:26:07 mysqld_safe Starting mysqld daemon with databases from /data/3306_bak/data

4.检查：

[root@localhost ~]# netstat -lntupActive Inteet connections (only servers)Proto Recv-Q Send-Q Local AddressForeign Address StatePID/Program nametcp0  0 0.0.0.0:22  0.0.0.0:*LISTEN  14827/sshd  tcp0  0 127.0.0.1:250.0.0.0:*LISTEN  6526/master tcp60  0 :::22:::*LISTEN  14827/sshd  tcp60  0 ::1:25  :::*LISTEN  6526/master tcp60  0 :::3356 :::*LISTEN  20250/mysqld[root@localhost ~]## 3356 端口已监听

验证

[root@localhost ~]# mysql -uroot  -S /tmp/mysql56.sockERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@localhost ~]# mysql -uroot -p -S /tmp/mysql56.sockEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.50 MySQL Community Server (GPL)  # 此时已回退到升级前的版本Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database|+--------------------+| information_schema || mysql  || performance_schema || sysbenchdb || test|+--------------------+5 rows in set (0.02 sec)mysql> use sysbenchdbReading table information for completion of table and column namesYou can tu off this feature to get a quicker startup with -ADatabase changedmysql> select * from sbtest1 limit 1;+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id | k| c| pad |+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+|  1 | 3231 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+1 row in set (0.00 sec)mysql> 

2.2 小结
1.生产环境中做好数据、日志备份，及my.cnf的备份，回退的时候也会比较方便快速。