MySQL5.7.35升级到MySQL8.0.36数据同步

前言

最近，因为一些问题，考虑将现在的MySQL5.7.35升级至MySQL8.0.36版本。
为了检验读写分离后的数据库的读。特意引入一个新的，版本为MySQL8.0.36的从库并加入现有集群，这样可以逐步调整canal权重，尽可能降低检验读库时发生问题后对业务的影响。以下为模拟测试

规划

现有环境 现有环境 预期环境
数据库版本 MySQL5.7.35 MySQL5.7.35 MySQL8.0.36
数据节点 节点1 节点2 节点3
主库IP 192.168.30.136 192.168.30.136 192.168.30.136
本地IP 192.168.30.136 192.168.30.137 192.168.30.86
操作系统 CentOS Linux release 7.9.2009 CentOS Linux release 7.9.2009 Ubuntu 22.04.1
测试Root用户密码 root root root

数据库配置
节点1

[client]
port          = 3306
socket        = /data/mysql/mysql.sock
#default_character_set = utf8

[mysql]
prompt="\u@m01 \R:\m:\s [\d]> "
no-auto-rehash
#default_character_set = utf8

[mysqld]
user       = mysql
port       = 3306
basedir    = /data/mysql
datadir    = /data/mysql/data
socket     = /data/mysql/mysql.sock
pid-file   = /data/mysql/mysql.pid
log_error  = /data/mysql/data/error.log
character_set_server = utf8mb4
skip_name_resolve = 1
log_slave_updates = 1
expire_logs_days = 1
innodb_file_per_table = 1
auto_increment_increment = 1
auto_increment_offset = 1
log_bin=mysql-bin
relay_log=mysql-relay
server_id = 1
binlog_format = row
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:100M:autoextend
max_allowed_packet = 128M
gtid_mode = on
enforce_gtid_consistency = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

plugin_load_add = server_audit

[mysqldump]
quick
max_allowed_packet = 128M

节点2

[client]
port          = 3306
socket        = /data/mysql/mysql.sock
#default_character_set = utf8

[mysql]
prompt="\u@m02 \R:\m:\s [\d]> "
no-auto-rehash
#default_character_set = utf8

[mysqld]
user       = mysql
port       = 3306
basedir    = /data/mysql
datadir    = /data/mysql/data
socket     = /data/mysql/mysql.sock
pid-file   = /data/mysql/mysql.pid
log_error  = /data/mysql/data/error.log
character_set_server = utf8mb4
skip_name_resolve = 1
log_slave_updates = 1
expire_logs_days = 1
innodb_file_per_table = 1
auto_increment_increment = 1
auto_increment_offset = 1
log_bin=mysql-bin
relay_log=mysql-relay
server_id = 2
binlog_format = row
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:100M:autoextend
max_allowed_packet = 128M
gtid_mode = on
enforce_gtid_consistency = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

[mysqldump]
quick
max_allowed_packet = 128M

节点3

[client]
port          = 3306
socket        = /data/mysql/mysql.sock
#default_character_set = utf8

[mysql]
prompt="\u@m03 \R:\m:\s [\d]> "
no-auto-rehash
#default_character_set = utf8

[mysqld]
user       = mysql
port       = 3306
basedir    = /data/mysql
datadir    = /data/mysql/data
socket     = /data/mysql/mysql.sock
pid-file   = /data/mysql/mysql.pid
log_error  = /data/mysql/data/error.log
character_set_server = utf8mb4
skip_name_resolve = 1
log_slave_updates = 1
expire_logs_days = 1
innodb_file_per_table = 1
auto_increment_increment = 1
auto_increment_offset = 1
log_bin=mysql-bin
relay_log=mysql-relay
server_id = 3
binlog_format = row
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:100M:autoextend
max_allowed_packet = 128M
gtid_mode = on
enforce_gtid_consistency = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

[mysqldump]
quick
max_allowed_packet = 128M

经过实际验证，以上参数在MySQL5.7.35和MySQL8.0.36版本中均可直接使用，无需修改

数据集群搭建同步
为了严格测试生产步骤，本次使用Percona公司的innobackupex工具做数据导出&恢复
由于innobackupex的备份和恢复均需要保持相同版本，所以需要在3台测试机上均安装匹配MySQL5.7.35的工具
下载&安装：

匹配MySQL5.7.35
$ apt-get update
$ apt-get install wget
$ wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
$ tar xvf percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
$ mv percona-xtrabackup-2.4.27-Linux-x86_64 /data/soft/percona-xtrabackup-2

匹配MySQL8.0.36
$ wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
$ tar -xvf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
$ mv percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17 /data/soft/percona-xtrabackup-8

备份5.7.35全量数据

$ /data/soft/percona-xtrabackup-2/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/data/mysql/mysql.sock --user=xtraback_test --password=xtraback_test /data/tmp

恢复数据到从库、节点2和节点3

节点2
$ /data/soft/percona-xtrabackup-2/bin/innobackupex --defaults-file=/etc/my.cnf --user=mysql --apply-log /data/tmp/2024-06-05_16-37-14
$ /data/soft/percona-xtrabackup-2/bin/innobackupex --defaults-file=/etc/my.cnf --user=mysql --copy-back /data/tmp/2024-06-05_16-37-14
$ chown -R mysql.mysql /data/mysql/data
$ service mysqld start
$ mysql -uroot -p
root@m02 16:14:  [(none)]> show databases;
root@m02 16:14:  [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.30.136', MASTER_USER='rep', MASTER_PASSWORD='rebornrep', MASTER_AUTO_POSITION=1;
root@m02 16:14:  [(none)]> start slave;
root@m02 16:14:  [(none)]> show slave status\G

节点3
$ /data/soft/percona-xtrabackup-2/bin/innobackupex --defaults-file=/etc/my.cnf --user=mysql --apply-log /data/tmp/2024-06-05_16-37-14
$ /data/soft/percona-xtrabackup-2/bin/innobackupex --defaults-file=/etc/my.cnf --user=mysql --copy-back /data/tmp/2024-06-05_16-37-14
$ chown -R mysql.mysql /data/mysql/data
$ service mysqld start
$ mysql -uroot -p
root@m03 16:24:  [(none)]> show databases;
root@m03 16:24:  [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.30.136', MASTER_USER='rep', MASTER_PASSWORD='rebornrep', MASTER_AUTO_POSITION=1;
root@m03 16:25:  [(none)]> start slave;
root@m03 16:25:  [(none)]> show slave status\G

主节点（节点1）查看从库状态

root@m01 16:27:  [(none)]> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         1 | 306154ca-2301-11ef-8386-0800270e68e0 |
|         2 |      | 3306 |         1 | 9bff5a2e-1e64-11ef-b363-080027f0b9f3 |
+-----------+------+------+-----------+--------------------------------------+

至此，测试环境已经初步搭建完成；下一步，开始将其中的一个MySQL5.7.35版本的节点升级至MySQL8.0.36版本并恢复MySQL的主从同步。

在节点3进行操作

root@m03 16:37:  [(none)]> stop slave;
--- 关闭实时同步
root@m03 16:37:  [(none)]> shutdown;
--- 关闭数据库

下载&安装MySQL8.0.36

$ cd /data/soft
$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
$ tar -xvf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
$ mv /data/mysql /data/mysql57
$ mv mysql-8.0.36-linux-glibc2.28-x86_64 /data/mysql
$ cp -r /data/mysql57/data /data/mysql
$ chown -R mysql.mysql /data/mysql

至此，人工操作可以暂停！

下面使用MySQL软件对数据库的数据文件进行升级变更；
升级操作只需要启动MySQL8.0.36就可以开始；
MySQL8.0.36在Ubuntu 22环境下的快捷操作方式的配置请参考这篇文章：
MySQL 使用 systemctl 快捷管理

$ systemctl start mysql
--- 启动MySQL8.0.36
$ systemctl status mysql
--- 检查MySQL8.0.36状态
$ tail -f -n 300 /data/mysql/data/error.log

待error.log文件打印出类似如下内容，则表示升级已经完成

2024-06-05T16:45:06.226028Z mysqld_safe Logging to '/data/mysql/data/error.log'.
2024-06-05T16:45:06.277393Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
2024-06-05T16:45:06.699356Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2024-06-05T16:45:06.699383Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2024-06-05T16:45:06.699449Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2024-06-05T16:45:06.699579Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2024-06-05T16:45:06.699600Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2024-06-05T16:45:06.699847Z 0 [System] [MY-010116] [Server] /data/mysql/bin/mysqld (mysqld 8.0.36) starting as process 5489
2024-06-05T16:45:06.720691Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=150994944. Please use innodb_redo_log_capacity instead.
2024-06-05T16:45:06.726033Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-06-05T16:45:06.726145Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-05T16:45:07.919231Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-05T16:45:12.821697Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-06-05T16:45:17.102763Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' started.
2024-06-05T16:45:37.131317Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' completed.
2024-06-05T16:45:37.425817Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-06-05T16:45:37.425911Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-06-05T16:45:37.519998Z 8 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-06-05T16:45:37.528461Z 8 [System] [MY-014002] [Repl] Replica receiver thread for channel '': connected to source 'rep@192.168.30.136:3306' with server_uuid=a077e1a9-1e64-11ef-b0bd-08002705a760, server_id=1. Starting GTID-based replication.
2024-06-05T16:45:37.541902Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-06-05T16:45:37.542088Z 0 [System] [MY-010931] [Server] /data/mysql/bin/mysqld: ready for connections. Version: '8.0.36'  socket: '/data/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

此时，可以登录数据库进行检查了

$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

root@m03 16:55:  [(none)]>

此时，由于该集群为测试集群，并无跳过开机启动主从同步的设置，所以检查主从状态时发现主从已经恢复，即MySQL5.7.35至MySQL8.0.36的数据实时主从同步已经完成。

root@m03 16:55:  [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.30.136
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1919
               Relay_Log_File: mysql-relay.000004
                Relay_Log_Pos: 457
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

检查全部环境，无误后，全部操作已完成；

至此，升级操作已完成

留下评论

您的邮箱地址不会被公开。 必填项已用 * 标注