主从版本升级_主从_8.0.32_安装路径非软链接_基于二进制日志文件

升级需求

将一套MySQL 8.0.32主从环境版本升级到8.0.35版本，主从环境如下：

	主服务器	登录账号密码	端口	当前版本	目标版本
主服务器	192.168.40.160	root/mysql	3306	8.0.32	8.0.35
从服务器	192.168.40.161	root/mysql	3306	8.0.32	8.0.35

升级步骤

关键步骤如下：

1.断开应用 2.确定主从无延时 3.关闭主库 4.关闭从库 5.备份主库数据目录 6.启动主库 7.启动备库 8.查看主从同步状态

查数据库版本

主服务器数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| admin_tls_version        | TLSv1.2,TLSv1.3              |
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.32                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| replica_type_conversions |                              |
| slave_type_conversions   |                              |
| tls_version              | TLSv1.2,TLSv1.3              |
| version                  | 8.0.32                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Linux                        |
| version_compile_zlib     | 1.2.13                       |
+--------------------------+------------------------------+
13 rows in set (0.00 sec)


mysql> exit
Bye
[root@localhost ~]# mysql -V
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

从服务器数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| admin_tls_version        | TLSv1.2,TLSv1.3              |
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.32                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| replica_type_conversions |                              |
| slave_type_conversions   |                              |
| tls_version              | TLSv1.2,TLSv1.3              |
| version                  | 8.0.32                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Linux                        |
| version_compile_zlib     | 1.2.13                       |
+--------------------------+------------------------------+
13 rows in set (0.01 sec)

mysql> exit
Bye
[root@xzdcweb1 ~]# mysql -V
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

停止应用

确保业务已停止，保证数据一致性

--查询数据库中哪些线程正在执行
show processlist;

确定主从无延时

--查询主库
mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000002 | 568694023 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--查询备库
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 59.217.250.226
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 568627797
               Relay_Log_File: xzdcweb1-relay-bin.000037
                Relay_Log_Pos: 568474217
        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: 568627797
              Relay_Log_Space: 568474776
              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: 5e53417b-c152-11ed-b3a0-a8494daa8138
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400

1.如下的两参数代表主从同步能够正常的运行网络

若是是Slave_IO_Running的参数不是YES则说明从库读取不到主库的bin-log日志，日志

若是是Slave_SQL_Running参数不是YES，则说明从库在读取中继日志发生问题。blog

Slave_IO_Running: Yes

Slave_SQL_Running: Yes同步

2.查看Seconds_Behind_Master参数，io

若是为0的话，则表示主从复制良好

原文链接：mysql如何准确判断主从延时情况_判断mysql主从延时的方法(不依赖中间件)-CSDN博客

停止数据库

停止从库

--查正在运行的MySQL进程,确定安装目录和数据目录
[root@localhost ~]# ps -ef | grep mysql
mysql       926      1  1 06:16 ?        00:00:22 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf
root       1731   1712  0 06:40 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 22:09:46 CST; 8h ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 926 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─926 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 22:09:46 localhost.localdomain systemd[1]: Started MySQL Server.


--查看从同步情况
show slave status\G

--停止同步
mysql>stop slave;

--停止数据库
--1.进入原8.0.32 mysql命令行 正确关闭数据库
法1：
mysqladmin -uroot -p shutdown

[root@mysql]#cd /topsoft/mysql/mysql8032/bin
[root@mysql]# mysql -uroot -p
Enter password: 
#使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题
Errors:   0
Warnings: 17
Notices:  0

#检查没有未提交的xa事务
mysql> xa recover;
Empty set (0.00 sec)

# 确保数据都刷到硬盘上，更改成0
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| 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> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

#查看进程和端口后，已无输出记录，确保已停止
[root@xzdcweb1 ~]# ps -ef | grep mysql
root     51658 45094  0 20:07 pts/0    00:00:00 grep --color=auto mysql
[root@xzdcweb1 ~]# ss -nlt | grep 3306

停止主库

--查正在运行的MySQL进程,确定安装目录和数据目录
[root@localhost ~]# ps -ef | grep mysql
mysql       926      1  1 06:16 ?        00:00:22 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf
root       1731   1712  0 06:40 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 22:09:46 CST; 8h ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 926 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─926 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 22:09:46 localhost.localdomain systemd[1]: Started MySQL Server.
Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

--记下输出中的 File 和 Position 值，后续在从服务器上使用。

mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000002 | 568694023 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


参数说明：
Binlog_Do_DB：同步数据库
Binlog_Ignore_DB：忽略数据库即不同步的数据库
Executed_Gtid_Set：空


--停止数据库
--1.进入原8.0.32 mysql命令行 正确关闭数据库
法1：
mysqladmin -uroot -p shutdown

[root@mysql]#cd /topsoft/mysql/mysql8032/bin
[root@mysql]# mysql -uroot -p
Enter password: 
#使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题
Errors:   0
Warnings: 17
Notices:  0

#检查没有未提交的xa事务
mysql> xa recover;
Empty set (0.00 sec)

# 确保数据都刷到硬盘上，更改成0
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| 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> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

#查看进程和端口后，已无输出记录，确保已停止
[root@localhost ~]# ps -ef | grep mysql
root     43674 43397  0 20:04 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# ss -ntl | grep 3306

主库升级

主服务器备份安装目录

--查正在运行的MySQL使用的配置文件
[root@localhost ~]# cat /etc/my.cnf | grep basedir
basedir=/usr/local/mysql  #安装目录
[root@localhost ~]# cat /etc/my.cnf | grep datadir
datadir=/data1/mysql/data #数据目录


--备份安装目录
[root@localhost ~]# cd /topsoft/mysql
[root@localhost mysql]# ls -l
total 36
-rw-r--r--. 1 mysql mysql  5434 Jan  9 14:19 my.cnf
drwxrwxrwx. 9 mysql mysql   129 Jan  9 13:50 mysql8032
-rw-r-----. 1 mysql mysql 19213 Jan  9 22:12 mysqld_error.log
drwxrwxrwx. 2 mysql mysql     6 Jan  9 13:49 mysql-files
-rw-r-----. 1 mysql mysql  1998 Jan  9 22:09 mysql-slow.log
srwxrwxrwx. 1 mysql mysql     0 Jan  9 22:10 mysql.sock
-rw-------. 1 mysql mysql     4 Jan  9 22:10 mysql.sock.lock


[root@localhost mysql]# mv mysql8032 mysql8032_bak_`date +%F`
[root@localhost ~]# cd /topsoft/mysql
[root@localhost mysql]# ls -l
total 36
-rw-r--r--. 1 mysql mysql  5434 Jan  9 14:19 my.cnf
drwxrwxrwx. 9 mysql mysql   129 Jan  9 13:50 mysql8032_bak_2024-01-09
drwxr-xr-x. 9 mysql mysql   129 Jan  9 19:20 mysql8035
-rw-r-----. 1 mysql mysql 19213 Jan  9 22:12 mysqld_error.log
drwxrwxrwx. 2 mysql mysql     6 Jan  9 13:49 mysql-files
-rw-r-----. 1 mysql mysql  1998 Jan  9 22:09 mysql-slow.log
srwxrwxrwx. 1 mysql mysql     0 Jan  9 22:10 mysql.sock
-rw-------. 1 mysql mysql     4 Jan  9 22:10 mysql.sock.lock

主服务器备份数据目录

--备份数据目录
[root@localhost mysql]# cd /topsoft/
[root@localhost topsoft]# ls -l
total 8
drwxrwxrwx. 5 mysql mysql  175 Jan  9 22:10 mysql
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 22:12 mysqldata
drwxr-xr-x. 2 root  root     6 Jan  9 13:49 mysqllog

[root@localhost mysql]# cp -r mysqldata mysqldata_bak_`date +%F`
[root@localhost topsoft]# ls -l
total 8
drwxrwxrwx. 5 mysql mysql  175 Jan  9 22:10 mysql
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 22:12 mysqldata
drwxr-xr-x. 8 root  root  4096 Jan  9 19:18 mysqldata_bak_2024-01-09
drwxr-xr-x. 2 root  root     6 Jan  9 13:49 mysqllog

[root@localhost mysql]# du -sh mysqldata
14G     data
[root@localhost mysql]# du -sh mysqldata_bak_2024-01-09
14G     data_bak_2024-01-09/
[root@localhost mysql]# chown -R mysql:mysql mysqldata_bak_2024-01-09
[root@localhost mysql]# ls -l
total 8
drwxrwxrwx. 5 mysql mysql  175 Jan  9 22:10 mysql
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 22:12 mysqldata
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 19:18 mysqldata_bak_2024-01-09
drwxr-xr-x. 2 root  root     6 Jan  9 13:49 mysqllog

主服务器备份配置文件

--备份配置文件
[root@localhost mysql]# cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
[root@localhost mysql]# ls -l /etc/my.cnf
-rw-r--r--. 1 root root 782 Sep 18 11:53 /etc/my.cnf
[root@localhost mysql]# ls -l /etc/my.cnf_bak_2024-01-09
-rw-r--r--. 1 root root 782 Jan  9 20:15 /etc/my.cnf_bak_2024-01-09

安装数据库软件

上传mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz至/data1

--解压
[root@localhost ~]# cd /topsoft/mysql
[root@localhost mysql]# tar -xvf /opt/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /topsoft/mysql

--/usr/local目录下创建mysql的软链接
[root@localhost mysql]# mv mysql-8.0.35-linux-glibc2.17-x86_64 mysql8035
[root@localhost mysql]# ls -l
total 36
-rw-r--r--. 1 mysql mysql  5434 Jan  9 14:19 my.cnf
drwxrwxrwx. 9 mysql mysql   129 Jan  9 13:50 mysql8032_bak_2024-01-09
drwxr-xr-x. 9 mysql mysql   129 Jan  9 19:20 mysql8035
-rw-r-----. 1 mysql mysql 19213 Jan  9 22:12 mysqld_error.log
drwxrwxrwx. 2 mysql mysql     6 Jan  9 13:49 mysql-files
-rw-r-----. 1 mysql mysql  1998 Jan  9 22:09 mysql-slow.log
srwxrwxrwx. 1 mysql mysql     0 Jan  9 22:10 mysql.sock
-rw-------. 1 mysql mysql     4 Jan  9 22:10 mysql.sock.lock

启动主库

更改配置文件

[root@localhost mysql]# vi /etc/my.cnf 
basedir=/topsoft/mysql/mysql8032  #mysql安装根目录  更改为
basedir=/topsoft/mysql/mysql8035

备份自启动脚本

[root@localhost mysql]# cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak_`date +%F`
[root@localhost mysql]# ls -l /usr/lib/systemd/system/mysqld.service
-rw-r--r--. 1 root root 362 Jan  9 13:25 /usr/lib/systemd/system/mysqld.service
[root@localhost mysql]# ls -l /usr/lib/systemd/system/mysqld.service_bak_`date +%F`
-rw-r--r--. 1 root root 362 Jan  9 17:26 /usr/lib/systemd/system/mysqld.service_bak_2024-01-09

更改自启动脚本

/topsoft/mysql/mysql8032改为/topsoft/mysql/mysql8035

vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536

启动主库

[root@localhost mysql]# systemctl daemon-reload
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 17:28:12 CST; 3s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2361 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2361 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 17:28:12 localhost.localdomain systemd[1]: Started MySQL Server.
Jan 09 17:28:12 localhost.localdomain mysqld[2361]: mysqld: Can't open shared library '/topsoft/mysql/mysql8032/lib/plugin/component_reference_cache.so' (errno: 0 ...directory)
Jan 09 17:28:12 localhost.localdomain mysqld[2361]: mysqld: Cannot load component from specified URN: 'file://component_reference_cache'.
Hint: Some lines were ellipsized, use -l to show in full.

问题处理

Can't open shared library

启库后查看状态提示如下报错：

--问题描述
启库后查看状态提示不能加载共享库
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 22:11:15 CST; 1min 52s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1297 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1297 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 22:11:15 localhost.localdomain systemd[1]: Started MySQL Server.
Jan 09 22:11:15 localhost.localdomain mysqld[1297]: mysqld: Can't open shared library '/topsoft/mysql/mysql8032/lib/plugin/component_reference_cache.so' (errno: 0 ...directory)
Jan 09 22:11:15 localhost.localdomain mysqld[1297]: mysqld: Cannot load component from specified URN: 'file://component_reference_cache'.
Hint: Some lines were ellipsized, use -l to show in full.

--问题原因
/etc/my.cnf中basedir参数未更改为新安装参数导致

--解决办法
[root@localhost mysql]# vi /etc/my.cnf 
basedir=/topsoft/mysql/mysql8032  #mysql安装根目录  更改为
basedir=/topsoft/mysql/mysql8035

重启库
systemctl restart mysqld  报错消失
[root@localhost topsoft]# systemctl restart mysqld
[root@localhost topsoft]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2024-01-10 06:55:21 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1746 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1746 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

查看版本

在/topsoft/mysql/mysql8035目录下/mysql -uroot -p登录主库

[root@localhost mysql]# /topsoft/mysql/mysql8035/bin/mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost mysql]# /topsoft/mysql/mysql8035/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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@localhost :(none) 17:28:59>use mysql;
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
root@localhost :mysql 17:29:02>select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.01 sec)

显示是新版本的版本号，升级成功

查看主库信息

root@localhost :mysql 17:31:40>show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000003 |      157 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

更改环境变量

注释掉含8032的MYSQL_HOME和PATH参数

[root@localhost mysql]# vi /etc/profile
...
unset -f pathmunge
LANG=en_US.UTF-8
export PATH=$PATH:/topsoft/mysql/mysql8035/bin
#export PATH=$PATH:/topsoft/mysql/mysql8032/bin
MYSQL_DATA=/topsoft/mysqldata
MYSQL_HOME=/topsoft/mysql/mysql8035
#MYSQL_HOME=/topsoft/mysql/mysql8032

[root@localhost mysql]# source /etc/profile
[root@localhost mysql]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

备库升级

备服务器备份安装目录

--查正在运行的MySQL使用的配置文件
[root@localhost ~]# cat /topsoft/mysql/my.cnf | grep basedir
basedir=/topsoft/mysql/mysql8032  #mysql安装根目录
[root@localhost ~]# cat /topsoft/mysql/my.cnf | grep datadir
datadir=/topsoft/mysqldata #mysql数据文件所在目录

--备份安装目录
[root@localhost ~]# cd /topsoft/mysql
[root@localhost mysql]# ls -l
total 24
-rw-r--r--. 1 mysql mysql  5434 Jan  9 14:19 my.cnf
drwxrwxrwx. 9 mysql mysql   129 Jan  9 13:50 mysql8032
-rw-r-----. 1 mysql mysql 11649 Jan  9 16:57 mysqld_error.log
drwxrwxrwx. 2 mysql mysql     6 Jan  9 13:49 mysql-files
-rw-r-----. 1 mysql mysql  1602 Jan  9 14:53 mysql-slow.log
[root@localhost mysql]# mv mysql8032 mysql8032_bak_`date +%F`
[root@localhost mysql]# ls -l
total 20
-rw-r--r--. 1 mysql mysql 5406 Jan  9 13:23 my.cnf
drwxrwxrwx. 9 mysql mysql  129 Jan  9 13:20 mysql8032_bak_2024-01-09
-rw-r-----. 1 mysql mysql 5873 Jan  9 17:09 mysqld_error.log
drwxr-xr-x. 2 mysql mysql    6 Jan  9 13:21 mysql-files
-rw-r-----. 1 mysql mysql 1206 Jan  9 14:53 mysql-slow.log

备服务器备份数据目录

--备份数据目录
[root@localhost topsoft]# cp -r mysqldata mysqldata_bak_`date +%F`
[root@localhost topsoft]# ls -l
total 8
drwxrwxrwx. 4 mysql mysql  146 Jan  9 17:11 mysql
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 17:09 mysqldata
drwxr-xr-x. 8 root  root  4096 Jan  9 17:12 mysqldata_bak_2024-01-09
drwxr-xr-x. 2 root  root     6 Jan  9 13:16 mysqllog
[root@localhost topsoft]# chown -R mysql:mysql mysqldata_bak_2024-01-09
[root@localhost topsoft]# ls -l
total 8
drwxrwxrwx. 4 mysql mysql  146 Jan  9 17:11 mysql
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 17:09 mysqldata
drwxr-xr-x. 8 mysql mysql 4096 Jan  9 17:12 mysqldata_bak_2024-01-09
drwxr-xr-x. 2 root  root     6 Jan  9 13:16 mysqllog

备服务器备份配置文件

--备份配置文件
[root@localhost topsoft]# cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
[root@localhost topsoft]# ls -l /etc/my.cnf
lrwxrwxrwx. 1 root root 21 Jan  9 13:26 /etc/my.cnf -> /topsoft/mysql/my.cnf
[root@localhost topsoft]# ls -l /etc/my.cnf_bak_2024-01-09 
-rw-r--r--. 1 root root 5406 Jan  9 17:14 /etc/my.cnf_bak_2024-01-09

安装数据库软件

上传mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz至/opt

--解压
tar -xvf /opt/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /topsoft/mysql/
cd /topsoft/mysql/
mv mysql-8.0.35-linux-glibc2.17-x86_64 mysql8035

--赋予权限
chown -R mysql:mysql mysql8035

启动备库

更改配置文件

--备份配置文件
[root@localhost mysql]# vi /etc/my.cnf 
basedir=/topsoft/mysql/mysql8032  #mysql安装根目录  更改为
basedir=/topsoft/mysql/mysql8035

备份自启动脚本

[root@localhost mysql]# cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak_`date +%F`
[root@localhost mysql]# ls -l /usr/lib/systemd/system/mysqld.service
-rw-r--r--. 1 root root 362 Jan  9 13:25 /usr/lib/systemd/system/mysqld.service
[root@localhost mysql]# ls -l /usr/lib/systemd/system/mysqld.service_bak_`date +%F`
-rw-r--r--. 1 root root 362 Jan  9 17:26 /usr/lib/systemd/system/mysqld.service_bak_2024-01-09

更改自启动脚本

/topsoft/mysql/mysql8032改为/topsoft/mysql/mysql8035

vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536

启动备库

[root@localhost mysql]# systemctl daemon-reload
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 17:28:12 CST; 3s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2361 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2361 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 17:28:12 localhost.localdomain systemd[1]: Started MySQL Server.
Jan 09 17:28:12 localhost.localdomain mysqld[2361]: mysqld: Can't open shared library '/topsoft/mysql/mysql8032/lib/plugin/component_reference_cache.so' (errno: 0 ...directory)
Jan 09 17:28:12 localhost.localdomain mysqld[2361]: mysqld: Cannot load component from specified URN: 'file://component_reference_cache'.
Hint: Some lines were ellipsized, use -l to show in full.

问题处理

Can't open shared library

启库后查看状态提示如下报错：

--问题描述
启库后查看状态提示不能加载共享库
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 22:11:15 CST; 1min 52s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1297 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1297 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

Jan 09 22:11:15 localhost.localdomain systemd[1]: Started MySQL Server.
Jan 09 22:11:15 localhost.localdomain mysqld[1297]: mysqld: Can't open shared library '/topsoft/mysql/mysql8032/lib/plugin/component_reference_cache.so' (errno: 0 ...directory)
Jan 09 22:11:15 localhost.localdomain mysqld[1297]: mysqld: Cannot load component from specified URN: 'file://component_reference_cache'.
Hint: Some lines were ellipsized, use -l to show in full.

--问题原因
/etc/my.cnf中basedir参数未更改为新安装参数导致

--解决办法
[root@localhost mysql]# vi /etc/my.cnf 
basedir=/topsoft/mysql/mysql8032  #mysql安装根目录  更改为
basedir=/topsoft/mysql/mysql8035

重启库
systemctl restart mysqld  报错消失
[root@localhost topsoft]# systemctl restart mysqld
[root@localhost topsoft]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2024-01-10 06:55:21 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1746 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1746 /topsoft/mysql/mysql8035/bin/mysqld --defaults-file=/topsoft/mysql/my.cnf

查看版本

在/topsoft/mysql/mysql8035目录下/mysql -uroot -p登录主库

[root@localhost mysql]# /topsoft/mysql/mysql8035/bin/mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost mysql]# /topsoft/mysql/mysql8035/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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@localhost :(none) 17:28:59>use mysql;
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
root@localhost :mysql 17:29:02>select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.01 sec)

显示是新版本的版本号，升级成功

查看备库同步信息

root@localhost :mysql 19:24:53>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.40.160
                  Master_User: replication
                  Master_Port: 2213
                Connect_Retry: 60
              Master_Log_File: mybinlog.000003
          Read_Master_Log_Pos: 157
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mybinlog.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: 157
              Relay_Log_Space: 749
              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: 160
                  Master_UUID: d9eafe5f-aeaf-11ee-a9c8-000c29528229
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql从库默认启动时slave也会自动启动复制。

1.如下的两参数代表主从同步能够正常的运行网络

若是是Slave_IO_Running的参数不是YES则说明从库读取不到主库的bin-log日志

若是是Slave_SQL_Running参数不是YES，则说明从库在读取中继日志发生问题。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes同步

2.查看Seconds_Behind_Master参数，若是为0的话，则表示主从复制良好