主从版本升级_主从_8.0.32_安装路径非软链接_基于二进制日志文件
2024-01-10 07:35:15
升级需求
将一套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: 864001.如下的两参数代表主从同步能够正常的运行网络
若是是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的话，则表示主从复制良好
文章来源:https://blog.csdn.net/qq961573863/article/details/135493735
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！