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 --version
mysql 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.gz
ln -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 Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 14827/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6526/master
tcp6 0 0 :::3306 :::* LISTEN 16824/mysqld
tcp6 0 0 :::22 :::* LISTEN 14827/sshd
tcp6 0 0 ::1:25 :::* LISTEN 6526/master
[root@localhost ~]# ps -ef|grep mysql
root 16646 1 0 15:43 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/localhost.localdomain.pid
mysql 16824 16646 0 15:43 pts/0 00: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=3306
root 16855 14763 0 15:52 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]# /etc/init.d/mysqld stop # 停止服务
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# ps -ef|grep mysql
root 16878 14763 0 15:53 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]#
[root@localhost ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 14827/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6526/master
tcp6 0 0 :::22 :::* LISTEN 14827/sshd
tcp6 0 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 2
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 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
sysbenchdb.sbtest1 OK
sysbenchdb.sbtest10 OK
sysbenchdb.sbtest11 OK
sysbenchdb.sbtest12 OK
sysbenchdb.sbtest13 OK
sysbenchdb.sbtest14 OK
sysbenchdb.sbtest15 OK
sysbenchdb.sbtest16 OK
sysbenchdb.sbtest17 OK
sysbenchdb.sbtest18 OK
sysbenchdb.sbtest19 OK
sysbenchdb.sbtest2 OK
sysbenchdb.sbtest20 OK
sysbenchdb.sbtest3 OK
sysbenchdb.sbtest4 OK
sysbenchdb.sbtest5 OK
sysbenchdb.sbtest6 OK
sysbenchdb.sbtest7 OK
sysbenchdb.sbtest8 OK
sysbenchdb.sbtest9 OK # 应用表
Upgrade process completed successfully.
Checking if update is needed.
1.4.4.3 升级后
show databases;
查看是否有sys库。查看user表是否有authentication_string字段
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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=mysql
basedir=/usr/local/mysql57 # 由之前的56版本改成57
datadir=/data/3306/data
server_id=56
port=3306
socket=/tmp/mysql.sock
lower_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 Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 14827/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6526/master
tcp6 0 0 :::3306 :::* LISTEN 16824/mysqld
tcp6 0 0 :::22 :::* LISTEN 14827/sshd
tcp6 0 0 ::1:25
以下方式启动也可以：
/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my.cnf &
1.4.6 验证
启动完成后，验证业务
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p123
Warning: 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 3
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 1
mysql> 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 names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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=mysql
basedir=/usr/local/mysql # 56的应用
datadir=/data/3306_bak/data/ # 备份的数据
server_id=3356
port=3356 # 新起一个端口，生产用原来端口即可
socket=/tmp/mysql56.sock
[root@localhost ~]#
2.修改备份数据属主
[root@localhost data]# chown -R mysql.mysql 3306_bak
[root@localhost data]# ls -ld 3306_bak
drwxr-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 -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 14827/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6526/master
tcp6 0 0 :::22 :::* LISTEN 14827/sshd
tcp6 0 0 ::1:25 :::* LISTEN 6526/master
tcp6 0 0 :::3356 :::* LISTEN 20250/mysqld
[root@localhost ~]# # 3356 端口已监听
验证
[root@localhost ~]# mysql -uroot -S /tmp/mysql56.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql56.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 sysbenchdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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的备份，回退的时候也会比较方便快速。
