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-files=/etc/my.cnf --skip-grant-tables --skip-networking &

--/defaults-files=/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-files=/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-files=/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 ::

上一篇:Linux——防火墙、SELinux规则


下一篇:[转]Docker 大势已去,Podman 即将崛起