升级需求
将一套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('[email protected]: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('[email protected]: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的话，则表示主从复制良好
本文转载自: https://blog.csdn.net/qq961573863/article/details/135491401
版权归原作者 福娃筱欢 所有， 如有侵权，请联系我们删除。
版权归原作者 福娃筱欢 所有， 如有侵权，请联系我们删除。