环境:
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
[root@localhost mysql8]# mkdir data
6.将旧版本目录下的data目录拷贝到新版本(新版本已经停掉)的相应目录下
[root@localhost mysql8]# cd /home/middle/mysql57 ##进入到旧数据库的安装目录
[root@localhost mysql57]# cp -r 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;
标签：25,39,mysql8,mysql5.7,server,middle,Master,mysql,data From： https://www.cnblogs.com/hxlasky/p/17656518.html