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

升级需求

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

	主服务器	登录账号密码	端口	当前版本	目标版本
主服务器	59.217.250.226	root/topnet@123	3306	8.0.32	8.0.35
从服务器	59.217.250.227	root/topnet@123	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，则说明从库在读取中继日志发生问题。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes同步

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

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

停止数据库

停止从库

--查正在运行的MySQL进程,确定安装目录和数据目录
[root@xzdcweb1 ~]# ps -ef | grep mysql
root     33033     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
mysql    33383 33033  0  2023 ?        03:44:54 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xzdcweb1.err --pid-file=/data1/mysql/data/xzdcweb1.pid --socket=/var/lib/mysql/mysql.sock
root     50368 45094  0 20:01 pts/0    00:00:00 grep --color=auto mysql
[root@xzdcweb1 ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 12:41:33 CST; 3 months 21 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 83
   CGroup: /system.slice/mysqld.service
           ├─33033 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
           └─33383 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...

Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.

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

--停止同步
mysql>stop slave;

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

[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
root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
[root@localhost ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
     Docs: man:systemd-sysv-generator(8)
   CGroup: /system.slice/mysqld.service
           ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
           └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...

Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
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.23 mysql命令行 正确关闭数据库
法1：
mysqladmin -uroot -p shutdown

[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 /usr/local/
[root@localhost local]# ls -l
total 0
drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  2 root root   6 Apr 11  2018 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx.  1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
drwxr-xr-x.  5 root root  49 Dec 15  2022 share
drwxr-xr-x.  2 root root   6 Apr 11  2018 src


[root@localhost data1]# cp -r mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0.32-linux-glibc2.12-x86_64_bak_`date +%F`
[root@localhost data1]# ls -l
total 428996
-rwxr--r--. 1 root  root       1530 Sep 18 15:43 bf.sh
drwxr-xr-x. 2 root  root        278 Jan  9 01:00 db
drwxr-xr-x. 2 root  root         27 Jan  9 01:00 log
drwxr-xr-x. 4 mysql mysql        29 Mar 13  2023 mysql
drwxr-xr-x. 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root  root        129 Jan  9 20:11 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
-rw-r--r--. 1 root  root  439284500 Jan  8 17:12 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

--删除/usr/local目录下mysql的软链接
lrwxrwxrwx.  1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64

[root@localhost local]# unlink mysql
[root@localhost local]# ls -l
total 0
drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  2 root root   6 Apr 11  2018 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
drwxr-xr-x.  5 root root  49 Dec 15  2022 share
drwxr-xr-x.  2 root root   6 Apr 11  2018 src

不建议rm mysql（规避误操作）

主服务器备份数据目录

--备份数据目录
[root@localhost mysql]# cd /data1/mysql
[root@localhost mysql]# ls -l
total 4
drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp
[root@localhost mysql]# cp -r data data_bak_`date +%F`
[root@localhost mysql]# ls -l
total 8
drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
drwxr-xr-x. 10 root  root  4096 Jan  9 20:14 data_bak_2024-01-09
drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp
[root@localhost mysql]# du -sh data
14G     data
[root@localhost mysql]# du -sh data_bak_2024-01-09/
14G     data_bak_2024-01-09/
[root@localhost mysql]# chown -R mysql:mysql data_bak_2024-01-09/
[root@localhost mysql]# ls -l
total 8
drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:14 data_bak_2024-01-09
drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp

主服务器备份配置文件

--备份配置文件
[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 data1]# cd /data1
[root@localhost data1]# tar -xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /data1/

--/usr/local目录下创建mysql的软链接
[root@localhost local]# ln -s /data1/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql
[root@localhost local]# ls -l
total 0
drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  2 root root   6 Apr 11  2018 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx.  1 root root  42 Jan  9 20:25 mysql -> /data1/mysql-8.0.35-linux-glibc2.17-x86_64
drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
drwxr-xr-x.  5 root root  49 Dec 15  2022 share
drwxr-xr-x.  2 root root   6 Apr 11  2018 src

启动主库

查看版本

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

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

启动主库

建议重开一会话启动服务

[root@localhost init.d]# systemctl start mysqld
[root@localhost init.d]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 20:40:36 CST; 2s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 48107 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 49535 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysqld.service
           ├─49546 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
           └─49915 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...

Jan 09 20:40:28 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
Jan 09 20:40:36 localhost.localdomain mysqld[49535]: Starting MySQL........ SUCCESS!
Jan 09 20:40:36 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
[root@localhost init.d]# ps -ef | grep mysql
root     49546     1  0 20:40 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
mysql    49915 49546 36 20:40 ?        00:00:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
root     50341 48949  0 20:40 pts/0    00:00:00 grep --color=auto mysql

问题处理

启库服务运行但是未加载目录和配置文件

--问题描述
升级后启库后查看服务状态不正常
[root@xzdcweb1 data1]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
[root@xzdcweb1 ~]# systemctl start mysqld
[root@xzdcweb1 ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (exited) since Mon 2023-09-18 12:41:33 CST; 3 months 22 days ago
     Docs: man:systemd-sysv-generator(8)

Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
[root@xzdcweb1 ~]# ps -ef | grep mysql
root     64535 64356  0 21:10 pts/1    00:00:00 grep --color=auto mysql

以下是升级前关库前查看的服务和进程：
[root@localhost ~]# ps -ef | grep mysql
root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
[root@localhost ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
     Docs: man:systemd-sysv-generator(8)
   CGroup: /system.slice/mysqld.service
           ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
           └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...

Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

--问题原因
mysql8.0.13之后启库时会自动进行升级字典，需要一些时间

--解决办法
缓一会儿再启动就正常了

可查看日志，时间关系未进行查看
如果配置文件中无日志文件参数，默认在数据目录下

查看主库信息

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

备库升级

备服务器备份安装目录

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


--备份安装目录
[root@xzdcweb1 ~]# cd /usr/local/
[root@xzdcweb1 local]# ls -l
total 0
drwxr-xr-x   6 root root  58 Jul  5  2021 apr
drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
lrwxrwxrwx   1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x   6 root root  56 Jul  3  2021 python3
drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
drwxr-xr-x.  3 root root  47 Jul  5  2021 src
drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl

[root@xzdcweb1 local]# cd /data1
[root@xzdcweb1 data1]# ls -l
total 428992
drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
drwxr-xr-x 4 mysql mysql        29 Mar 13  2023 mysql
drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
-rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
[root@xzdcweb1 data1]# cp -r mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0.32-linux-glibc2.12-x86_64_bak_`date +%F`
[root@xzdcweb1 data1]# ls -l
total 428992
drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
drwxr-xr-x 4 mysql mysql        29 Mar 13  2023 mysql
drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
-rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

--删除软链接
[root@xzdcweb1 data1]# cd /usr/local/
[root@xzdcweb1 local]# ls -l
total 0
drwxr-xr-x   6 root root  58 Jul  5  2021 apr
drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
lrwxrwxrwx   1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x   6 root root  56 Jul  3  2021 python3
drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
drwxr-xr-x.  3 root root  47 Jul  5  2021 src
drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl
[root@xzdcweb1 local]# unlink mysql
[root@xzdcweb1 local]# ls -l
total 0
drwxr-xr-x   6 root root  58 Jul  5  2021 apr
drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x   6 root root  56 Jul  3  2021 python3
drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
drwxr-xr-x.  3 root root  47 Jul  5  2021 src
drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl

注意：
不建议rm mysql (规避误操作)

备服务器备份数据目录

--备份数据目录
[root@xzdcweb1 local]# cd /data1/mysql
[root@xzdcweb1 mysql]# ls -l
total 4
drwxr-xr-x 10 mysql mysql 4096 Jan  9 20:07 data
drwxr-xr-x  2 mysql mysql    6 Sep 18 12:41 tmp
[root@xzdcweb1 mysql]# du -sh data/
4.0G    data/
[root@xzdcweb1 mysql]# cp -r data data_bak_`date +%F`
[root@xzdcweb1 mysql]# ls -l
total 8
drwxr-xr-x 10 mysql mysql 4096 Jan  9 20:07 data
drwxr-xr-x 10 root  root  4096 Jan  9 21:01 data_bak_2024-01-09
drwxr-xr-x  2 mysql mysql    6 Sep 18 12:41 tmp
[root@xzdcweb1 mysql]# du -sh data
4.0G    data
[root@xzdcweb1 mysql]# du -sh data_bak_2024-01-09/
4.0G    data_bak_2024-01-09/

备服务器备份配置文件

--备份配置文件
[root@xzdcweb1 mysql]# cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
[root@xzdcweb1 mysql]# ls -l /etc/my.cnf
-rw-r--r-- 1 root root 747 Sep 18 12:16 /etc/my.cnf
[root@xzdcweb1 mysql]# ls -l /etc/my.cnf_bak_2024-01-09
-rw-r--r-- 1 root root 747 Jan  9 21:02 /etc/my.cnf_bak_2024-01-09

安装数据库软件

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

--解压
[root@xzdcweb1 mysql]# cd /data1
[root@xzdcweb1 data1]# ls -l
total 428992
drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
drwxr-xr-x 5 mysql mysql        56 Jan  9 21:01 mysql
drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
-rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
[root@xzdcweb1 data1]# tar -xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /data1/

[root@xzdcweb1 data1]# ls -l
total 428992
drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
drwxr-xr-x 5 mysql mysql        56 Jan  9 21:01 mysql
drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
drwxr-xr-x 9 root  root        129 Jan  9 21:05 mysql-8.0.35-linux-glibc2.17-x86_64
-rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

--/usr/local目录下创建mysql目录的软链接
[root@xzdcweb1 data1]# ln -s /data1/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql
[root@xzdcweb1 data1]# ls -l /usr/local/
total 0
drwxr-xr-x   6 root root  58 Jul  5  2021 apr
drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
drwxr-xr-x.  2 root root   6 Apr 11  2018 games
drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
lrwxrwxrwx   1 root root  42 Jan  9 21:08 mysql -> /data1/mysql-8.0.35-linux-glibc2.17-x86_64
drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
drwxr-xr-x   6 root root  56 Jul  3  2021 python3
drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
drwxr-xr-x.  3 root root  47 Jul  5  2021 src
drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl

启动备库

查看版本

[root@xzdcweb1 data1]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

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

启动备库

[root@xzdcweb1 ~]# systemctl start mysqld
[root@xzdcweb1 ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-09 21:15:36 CST; 6s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 64762 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 65533 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 46
   CGroup: /system.slice/mysqld.service
           ├─345 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
           └─719 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --u...

Jan 09 21:15:25 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
Jan 09 21:15:36 xzdcweb1 mysqld[65533]: Starting MySQL...........[  OK  ]
Jan 09 21:15:36 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
[root@xzdcweb1 ~]# ps -ef | grep mysql
root       345     1  0 21:15 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
mysql      719   345 55 21:15 ?        00:00:14 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xzdcweb1.err --pid-file=/data1/mysql/data/xzdcweb1.pid --socket=/var/lib/mysql/mysql.sock
root      1147 64356  0 21:15 pts/1    00:00:00 grep --color=auto mysql

问题处理

启库服务运行但是未加载目录和配置文件

--问题描述
升级后启库后查看服务状态不正常
[root@xzdcweb1 data1]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
[root@xzdcweb1 ~]# systemctl start mysqld
[root@xzdcweb1 ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (exited) since Mon 2023-09-18 12:41:33 CST; 3 months 22 days ago
     Docs: man:systemd-sysv-generator(8)

Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
[root@xzdcweb1 ~]# ps -ef | grep mysql
root     64535 64356  0 21:10 pts/1    00:00:00 grep --color=auto mysql

以下是升级前关库前查看的服务和进程：
[root@localhost ~]# ps -ef | grep mysql
root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
[root@localhost ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
     Docs: man:systemd-sysv-generator(8)
   CGroup: /system.slice/mysqld.service
           ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
           └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...

Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

--问题原因
mysql8.0.13之后启库时会自动进行升级字典，需要一些时间

--解决办法
缓一会儿再启动就正常了

可查看日志，时间关系未进行查看
如果配置文件中无日志文件参数，默认在数据目录下

查看备库同步信息

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的话，则表示主从复制良好