将 MySQL8.0.30 升级到 MySQL8.0.32
备份旧数据
root@LAPTOP-FPIQJ438:/data/backup# xtrabackup --backup --user=root --password=123456 --socket=/tmp/mysql.sock --target-dir=/data/backup/ 2024-01-08T16:46:38.987687+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/usr/local/mysql-8.0/data/ 2024-01-08T16:46:38.988068+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=root --password=* --socket=/tmp/mysql.sock --target-dir=/data/backup/ xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49) 240108 16:46:39 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1548. 2024-01-08T16:46:39.050470+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock 2024-01-08T16:46:39.058512+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30 2024-01-08T16:46:39.060647+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ... 2024-01-08T16:46:39.064725+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns 2024-01-08T16:46:39.064838+08:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption. 2024-01-08T16:46:39.064843+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details. 2024-01-08T16:46:39.064846+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found: 2024-01-08T16:46:39.064849+08:00 0 [ERROR] [MY-011825] [Xtrabackup] test/test 2024-01-08T16:46:39.064851+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.
可以发现出现 Found tables with row versions due to INSTANT ADD/DROP columns
错误，优化错误提示中 提到的表
优化表
mysql> OPTIMIZE table test; +-----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+----------+----------+-------------------------------------------------------------------+ | test.test | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.test | optimize | status | OK | +-----------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.06 sec)
继续备份
root@LAPTOP-FPIQJ438:/data/backup# xtrabackup --backup --user=root --password=123456 --socket=/tmp/mysql.sock --target-dir=/data/backup/
MySQL8.0.30配置文件
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove the leading "# " to disable binary logging # Binary logging captures changes between backups and is enabled by # default. It's default setting is log_bin=binlog # disable_log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password # datadir=/var/lib/mysql # socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid basedir=/usr/local/mysql-8.0/ datadir=/usr/local/mysql-8.0/data/ socket=/tmp/mysql.sock character-set-server=UTF8MB4
记住 数据目录datadir ,数据库端口为 默认端口为 3306
获取新版安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
删除 旧版本的 安装目录文件
root@LAPTOP-FPIQJ438:/usr/local# rm -rf mysql-8.0 root@LAPTOP-FPIQJ438:/etc/init.d# cd /etc/init.d/ root@LAPTOP-FPIQJ438:/etc/init.d# rm -rf mysql
旧版本的配置文件可以不删除，新版本的可以直接使用旧版的配置文件
解压新版本安装包
root@LAPTOP-FPIQJ438:/usr/local# tar xvJf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz root@LAPTOP-FPIQJ438:/usr/local# mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0
新版本初始化
初始化之前要将旧版本的数据目录下的数据文件全部删除，否则初始化失败
进入到新版本bin目录
root@LAPTOP-FPIQJ438:/usr/local# mkdri data root@LAPTOP-FPIQJ438:chown -R mysql.mysql /usr/local/mysql-8.0 root@LAPTOP-FPIQJ438:/usr/local# cd mysql-8.0/bin/ root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# pwd /usr/local/mysql-8.0/bin
按照旧版本的配置文件初始化
./mysqld --user=mysql --basedir=/usr/local/mysql-8.0 --datadir=/usr/local/mysql-8.0/data/ --initialize
查看临时密码，通过配置文件中的 mysqld 进程日志 log-error=/var/log/mysqld.log
root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# vim /var/log/mysqld.log
临时密码为 rFf#SqPTw2q/
关闭旧版本数据库
root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp6 0 0 :::33060 :::* LISTEN 1402/mysqld tcp6 0 0 :::3306 :::* LISTEN 1402/mysqld root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# kill 1402
启动新版本数据库
进入到/usr/local/mysql-8.0
cp -a /usr/local/mysql-8.0/support-files/mysql.server /etc/init.d/mysql
该命令也会自动在/etc/init.d下创建mysql
授权启动
chmod +x /etc/init.d/mysql service mysql start
登录修改密码
root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 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. mysql> mysql> mysql> mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> update user set host='%' where user='root'; ERROR 1046 (3D000): No database selected mysql> mysql> 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 mysql> mysql> update user set host='%' where user='root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to 'root'@'%'; Query OK, 0 rows affected (0.01 sec) mysql>
还原旧版本的数据
service mysql stop --关闭新版数据库 rm -rf /usr/local/mysql-8.0/data/* --删除新版数据库数据 xtrabackup --prepare -uroot -p --target-dir=/data/backup/ xtrabackup --copy-back --target-dir=/data/backup/ --旧版数据库还原到新版数据库 chown -R mysql.mysql /usr/local/mysql-8.0
升级数据库验证
升级命令，目标版本大于8.0.16时，启动使用–upgrade=FORCE参数
root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# ./mysqld_safe --datadir=/usr/local/mysql-8.0/data/ --upgrade=FORCE & [1] 1684 root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# 2024-01-09T06:03:41.032750Z mysqld_safe Logging to '/var/log/mysqld.log'. 2024-01-09T06:03:41.049302Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-8.0/data
查看
root@LAPTOP-FPIQJ438:/usr/local/mysql-8.0/bin# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.32 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. mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> use test 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 mysql> mysql> mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> select * from test; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec)
可以查到 旧版 test 数据
注意
升级过程前，一定要备份数据，版本的升级方式有所差异
#8.0.16以后的版本 mysqladmin -u root -p shutdown mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE & #8.0.16之前的版本 mysql_upgrade -u root -p mysqladmin -u root -p shutdown mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &