@TOC
前言
做的项目上线之前做漏洞扫描，发现MySQL5.7.31有漏洞，要升级到最新版本，查了下当前最新的是5.7.33，因为是主从结构的，没有这类升级经验，就在网上找了很多帖子，清一色都是先升级从再升级主，最终切换了主从，分析之后发现这种升级方式主要是考虑到升级期间MySQL会写入数据，而我们是可以把往MySQL写数据的应用停掉的，所以相比其他升级方式，我采用了一种我自己称为冷升级的方式
另：这篇文章是继我上一篇部署MySQL主从的文章写的，各位有疑惑的话可以看一下Linux部署MySQL主从(5.7.31版本).
下载并上传MySQL
下载方式我这里就不写了，可以去搜其他博客
我下载的版本是mysql-5.7.33-el7-x86_64.tar.gz
停应用
# 停掉连接MySQL写数据的应用后
# 登录主MySQL查询日志状态，多查询几次(间隔时间)
# 确定Position的数字不动了就表示MySQL没有数据写入了
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 1630 | | | a35148ef-7cec-11eb-8e8e-000c29c5e6ab:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
升级从库
停止备份
# 查询slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1630
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 476
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1630
Relay_Log_Space: 681
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a35148ef-7cec-11eb-8e8e-000c29c5e6ab:7
Executed_Gtid_Set: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3,
a35148ef-7cec-11eb-8e8e-000c29c5e6ab:7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
# 停止备份
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
停止从MySQL
[mysql@192 mysql]$ service mysql stop
Shutting down MySQL..2021-03-04T14:12:37.998307Z mysqld_safe mysqld from pid file /mysql/data/mysqld.pid ended
SUCCESS!
[2]- 完成 mysqld_safe --defaults-file=/mysql/my.cnf --user=mysql
[mysql@192 mysql]$
继承旧版本的配置
cp -R /mysql/data /mysql/data.bak31
ln -snf /mysql/mysql-5.7.33-el7-x86_64 /mysql/mysql
ll
[root@192 ~]# cd /mysql
# 备份旧MySQL数据
[root@192 mysql] cp -R data data.bak31
[root@192 mysql] ll
总用量 12
drwxr-x--- 6 mysql mysql 4096 3月 4 22:12 data
drwxr-x--- 6 root root 4096 3月 4 22:15 data.bak31
-rw-rw-r-- 1 mysql mysql 1816 3月 1 15:38 my.cnf
lrwxrwxrwx 1 root root 30 3月 4 21:14 mysql -> /mysql/mysql-5.7.31-el7-x86_64
drwxr-xr-x 9 mysql mysql 129 6月 2 2020 mysql-5.7.31-el7-x86_64
drwxr-xr-x 9 root root 129 3月 4 22:13 mysql-5.7.33-el7-x86_64
# 修改软链接指向
[root@192 mysql] ln -snf /mysql/mysql-5.7.33-el7-x86_64 /mysql/mysql
[root@192 mysql] ll
总用量 12
drwxr-x--- 6 mysql mysql 4096 3月 4 22:12 data
drwxr-x--- 6 root root 4096 3月 4 22:15 data.bak31
-rw-rw-r-- 1 mysql mysql 1816 3月 1 15:38 my.cnf
lrwxrwxrwx 1 root root 30 3月 4 22:18 mysql -> /mysql/mysql-5.7.33-el7-x86_64
drwxr-xr-x 9 mysql mysql 129 6月 2 2020 mysql-5.7.31-el7-x86_64
drwxr-xr-x 9 root root 129 3月 4 22:13 mysql-5.7.33-el7-x86_64
# 修改owner
[root@192 mysql] chown -R mysql:mysql /mysql
启动并升级从MySQL
# 启动MySQL
[root@192 mysql] service mysql start
Starting MySQL. SUCCESS!
# 升级数据字典
[root@192 mysql] 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
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
登录并验证slave
可以看到版本已经升级并且slave是正常的
[root@192 mysql]# su - mysql
上一次登录：四 3月 4 21:18:01 CST 2021pts/0 上
[mysql@192 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
# 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# 查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1630
Relay_Log_File: 192-relay-bin.000004
Relay_Log_Pos: 357
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1630
Relay_Log_Space: 602
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a35148ef-7cec-11eb-8e8e-000c29c5e6ab:7
Executed_Gtid_Set: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3,
a35148ef-7cec-11eb-8e8e-000c29c5e6ab:7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
关闭slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
升级主库
主库的升级步骤和上面写的从库升级步骤是一样的，只是不需要启动slave而已，这里我就写一下按照上面的步骤升级主库之后的操作
查看master状态
可以看到master节点的日志文件换成了新的，我在写这篇文章时用的是虚拟机MySQL一边操作一边写的，可以肯定绝对没有往库里写数据，猜测应该是写入了升级日志数据(如果有大佬清楚是什么日志的话请指教一下，不胜感激)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 194 | | | a35148ef-7cec-11eb-8e8e-000c29c5e6ab:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
修改从库配置
# 按照master状态修改连接属性
mysql> CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=194;
Query OK, 0 rows affected (0.00 sec)
# 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 194
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 522
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3,
a35148ef-7cec-11eb-8e8e-000c29c5e6ab:7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
升级成功
Q.E.D.
