我使用 LVM 快照对生产 mysql 数据库执行热备份,然后尝试在开发计算机上恢复所述备份。但是,dev
mysql数据库无法启动,并抱怨[InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 5.7.33, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
在prod
和上dev
,mysql> SELECT @@datadir;
给出/data/mysql
(/data
是一个lvm卷)。
对prod
和dev
,mysql> SELECT @@innodb_fast_shutdown
给出1
。
mysql版本信息:
prod$ mysql --version
mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper
dev$ mysql --version
mysql Ver 8.0.34-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
所以看起来dev
我的热备份是崩溃的结果。也许这是因为我用来FLUSH TABLES WITH READ LOCK;
获取一致的快照,但它不会产生与升级兼容的 datadir 映像。
如果我用FLUSH TABLES WITH READ LOCK
/命令替换/命令,那么机器不会抱怨崩溃状态。但是,我不想这样做,因为我不想在计算机上创建快照的短暂时间内丢失现有数据库连接或拒绝传入连接。如何在不关闭 mysql 守护进程的情况下创建适用于升级的一致快照?UNLOCK TABLES
systemctl stop mysql
systemctl start mysql
dev
prod
创建和恢复备份的细节:
仅供参考,prod
正在运行 Ubuntu 16.04,并且dev
正在运行 Ubuntu 20.04。
我的备份过程如下所示:
锁定所有表:
prod mysql> FLUSH TABLES WITH READ LOCK;
创建快照:
prod# lvcreate --extents 100%FREE --snapshot --name mysql-backup /dev/myvg/mylv
解锁所有表:
prod mysql> UNLOCK TABLES;
挂载快照并创建数据的 tar 存档:
prod# mount /dev/myvg/mysql-backup /mnt/bkup prod# tar -C /mnt/bkup -aczf ~/bkup.tar.gz mysql
清理
prod# umount /mnt/bkup prod# lvremove --yes /dev/myvg/mysql-backup
将 tar 文件移至开发计算机。将数据解压到
/data/mysql
.启动 mysql 守护进程:
dev# systemctl start mysql Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
检查
/var/log/mysql/error.log
:2023-10-03T14:21:47.688509Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34-0ubuntu0.20.04.1) starting as process 79121 2023-10-03T14:21:47.746245Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2023-10-03T14:21:47.747076Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-10-03T14:21:48.125425Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 5.7.33, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html 2023-10-03T14:21:48.125480Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error. 2023-10-03T14:21:48.404686Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine. 2023-10-03T14:21:48.405086Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2023-10-03T14:21:48.406142Z 0 [ERROR] [MY-010119] [Server] Aborting 2023-10-03T14:21:48.407096Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34-0ubuntu0.20.04.1) (Ubuntu).
mysql 8.0 不会升级数据文件,除非您首先使用 mysql 5.7 执行干净关闭。幸运的是,您可以在机器上执行以下操作
prod
:/mnt/bkup
如上述问题所述。/mnt/bkup/mysql
)。/mnt/bkup/mysql
并传输到dev
机器上。为了弄清楚为临时 mysql 进程提供哪些参数,我使用了该命令
prod# mysqld --print-defaults
,然后调整了--socket
、--port
、--datadir
、--tmpdir
和--log_error
参数,这样它们就不会干扰mysqld
正在运行生产数据的主实例。这是我想出的对我有用的方法:
之后,我可以继续 tar
/mnt/bkup/mysql
并将其传输到dev
机器,如果我初始化 mysql 守护程序,我会得到以下信息:成功!