主从升级(mysql5.7.39-mysql8.0.25)

环境:
OS:Centos 7
当前数据库版本:5.7.39(主从目前启用了审计 server_audit.so,master_auto_position=1)
计划升级的数据库版本:8.0.28

升级顺序:先升级从库

########################从库机器上的操作######################
1.从库机器上安装好新版本的mysql
注意端口和socket不能与现有的实例相同,比如:
port=23306
socket=/data/middle/mysql8/data/mysql.sock

mysql8.0.28安装可以参考如下链接:
https://www.cnblogs.com/hxlasky/p/15960873.html

 

2.停掉旧的数据库
/home/middle/mysql57/bin/mysql -h localhost -uroot -P13306 -p
mysql> stop slave;
停掉旧数据库:
/home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p shutdown

 

3.模拟主库写入新数据,等升级完成后验证这部分数据是否传输过来 --doing


4.停掉新数据库:
/data/middle/mysql8/bin/mysqladmin -h localhost -uroot -P23306 -p -S /data/middle/mysql8/mysql.sock shutdown

 

5.备份新版本的data目录
[root@localhost mysql8]# cd /data/middle/mysql8
[root@localhost mysql8]# mv data bakdata

 

6.将旧版本目录下的data目录拷贝到新版本(新版本已经停掉)的相应目录下
[root@localhost mysql8]# cd /home/middle/mysql57 ##进入到旧数据库的安装目录
[root@localhost mysql57]# cp -rp data /data/middle/mysql8/ ##将data文件夹拷贝到新的安装目录

 

7.修改目录目录权限
[root@localhost binlog]# cd /data/middle
[root@localhost opt]# chown -R mysql:mysql ./mysql8

 

8.启动新版本数据库
[root@localhost opt]# /data/middle/mysql8/bin/mysqld_safe --defaults-file=/data/middle/mysql8/conf/my.cnf --user=mysql &

我这里的参数文件配置了跳过从库同步自启动的,所以从库启动数据库后需要手工启动主从同步
skip_replica_start

这里8.0.28版本的启动了会自动升级,不需要手动升级的.

启动过程会自动升级,日志如下:

复制代码
2023-08-24T09:14:26.125594Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-08-24T09:14:26.125935Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-08-24T09:15:02.575540Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-24T09:15:11.492208Z 0 [ERROR] [MY-010901] [Server] Can't open shared library '/data/middle/mysql8/lib/plugin/server_audit.so' (errno: 0 /data/middle/mysql8/lib/plugin/server_audit.so: cannot open shared object file: No such file or directory).
2023-08-24T09:15:11.492698Z 0 [Warning] [MY-010736] [Server] Couldn't load plugin named 'server_audit' with soname 'server_audit.so'.
2023-08-24T09:15:33.374084Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-08-24T09:15:47.558255Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' started.
2023-08-24T09:17:37.428142Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' completed.
2023-08-24T09:17:38.985698Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-08-24T09:17:38.985799Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-08-24T09:17:39.604754Z 0 [ERROR] [MY-010544] [Repl] Failed to open the relay log '/home/middle/mysql57/mysqllog/relaylog/relaylog-binlog.000007' (relay_log_pos 60485879).
2023-08-24T09:17:39.605286Z 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file '/data/middle/mysql8/mysqllog/relaylog/slave-relay-bin.index' during relay log initialization.
2023-08-24T09:17:39.633778Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2023-08-24T09:17:39.634260Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2023-08-24T09:17:39.641649Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2023-08-24T09:17:39.642432Z 0 [System] [MY-010931] [Server] /data/middle/mysql8/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/data/middle/mysql8/mysql.sock'  port: 23306  MySQL Community Server - GPL.
复制代码

 

因为我原来的数据库安装了审计插件server_audit.so,但是8.0版本已经不能使用该插件了,所以报提升找不到插件的错误,但是不影响使用

升级后查看版本信息:

 

/data/middle/mysql8/bin/mysql -h localhost -uroot -P23306 -p -S /data/middle/mysql8/mysql.sock
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)

 

9.这个时候查看从库复制信息

复制代码
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.134
                  Master_User: repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 60485672
               Relay_Log_File: relaylog-binlog.000007
                Relay_Log_Pos: 60485879
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 60485672
              Relay_Log_Space: 0
              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: NULL
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: 0
                  Master_UUID: 83cb17d9-4251-11ee-aa78-525400c8dc1f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 83cb17d9-4251-11ee-aa78-525400c8dc1f:1-207,
cbc1c304-425a-11ee-9b39-52540051cd25:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.02 sec)

ERROR: 
No query specified
复制代码

 

9.启动从库
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql>reset slave;
mysql>start slave;

 

10.查看新增的数据是否同步过来
mysql> select count(1) from tb_test02;
+----------+
| count(1) |
+----------+
| 3000000 |
+----------+
1 row in set (0.23 sec)


11.审计报错的插件处理(在做升级之前把该插件删除掉)
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'server_audit%';
Empty set (0.00 sec)
mysql> show plugins;
发现是没有安装这个插件的

 

尝试重启数据库看是否还有该错误提示:

[root@localhost opt]#/data/middle/mysql8/bin/mysqladmin -h localhost -uroot -P23306 -p -S /data/middle/mysql8/mysql.sock shutdown
[root@localhost opt]#/data/middle/mysql8/bin/mysqld_safe --defaults-file=/data/middle/mysql8/conf/my.cnf --user=mysql &

 

依然报这个错误

2023-08-24T09:37:21.546838Z 0 [ERROR] [MY-010901] [Server] Can't open shared library '/data/middle/mysql8/lib/plugin/server_audit.so' (errno: 0 /data/middle/mysql8/lib/plugin/server_audit.so: cannot open shared object file: No such file or directory).
2023-08-24T09:37:21.546913Z 0 [Warning] [MY-010736] [Server] Couldn't load plugin named 'server_audit' with soname 'server_audit.so'.

 

尝试把给文件拷贝过去,但是不能使用该插件
cp /home/middle/mysql57/lib/plugin/server_audit.so /data/middle/mysql8/lib/plugin/
cd /data/middle/mysql8/lib/plugin
chown -R mysql:mysql server_audit.so

再次重启看看,报另外一个错误:

 

2023-08-24T09:40:43.325684Z 0 [ERROR] [MY-010901] [Server] Can't open shared library '/data/middle/mysql8/lib/plugin/server_audit.so' (errno: 0 /data/middle/mysql8/lib/plugin/server_audit.so: undefined symbol: PSI_server).
2023-08-24T09:40:43.325780Z 0 [Warning] [MY-010736] [Server] Couldn't load plugin named 'server_audit' with soname 'server_audit.so'.

 

解决办法:
在做审计之前把原来的该插件删除掉,并重启动
关闭审计:
mysql> set global server_audit_logging=off;
mysql> uninstall plugin server_audit;

另外一个办法是升级完成后删除元数据:
mysql> select * from mysql.plugin;
+--------------+-----------------+
| name | dl |
+--------------+-----------------+
| server_audit | server_audit.so |
+--------------+-----------------+
1 row in set (0.00 sec)

 

delete from mysql.plugin;

 

 

 

 

#####################升级原来的主库##############################
1.将升级后的实例变成主库(8.0.28)
mysql> stop slave;
mysql> reset master;
mysql> reset slave all;

2.这里我们尝试将原来的主库(5.7)变成新的从库
验证下mysql 8.0-->mysql5.7的架构是否可行

change master to master_host='192.168.1.135',
master_user='repl',
master_password='mysql',
master_port=23306,
master_auto_position=1;

start slave;

验证1:dml没有问题,在mysql8 update,delete,insert可以同步到mysql5.7
验证2:ddl有问题,报如下的错误:
Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/home/middle/mysql57/share/charsets/Index.xml' file' on query. Default database: 'db_test'. Query: 'create table tb_test04(id int,name varchar(32))'

这个时候升级到8.0版本,可以参考如下链接:
https://www.cnblogs.com/hxlasky/p/17656518.html

结论:
mysql 8.0-->mysql5.7的架构是不可行的.

若想原来的主库变成新的从库:
方法1:升级到8.0
方法2:使用 xtrabackup 重新部署从库

posted @   slnngk  阅读(172)  评论(0)  编辑  收藏  举报
相关博文：
· 主从环境升级(mysql57-->mysql8)
· mysql主从升级(直接先升级主库,旧binlog拷贝到新版本目录,需要停业务)
· mysql8.0.25版本设置主从数据库，并且从库只读
· centos7 mysql5.6 主从复制
· 数据库笔记-1
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
历史上的今天：
2022-08-25 主库备份后异机进行恢复(适用于DSC到单节点的恢复)
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛