mysql hot backup with upgrade support

Question

I perform a hot backup of a production mysql database using an LVM snapshot, and then I attempt to restore said backup on a development machine. However, the dev mysql database will not start, and complains that [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

On prod and dev, mysql> SELECT @@datadir; gives /data/mysql (/data is an lvm volume).

On prod and dev, mysql> SELECT @@innodb_fast_shutdown gives 1.

mysql version information:

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))

So it seems like the dev thinks my hot backup is the result of a crash. Maybe this is because I am using FLUSH TABLES WITH READ LOCK; to get me a consistent snapshot, but it doesn't result in an upgrade-compatible datadir image.

If I replace the FLUSH TABLES WITH READ LOCK / UNLOCK TABLES commands with systemctl stop mysql / systemctl start mysql commands, then the dev machine doesn't complain about the crashed state. However, I don't want to do this because I don't want to lose existing DB connections or deny incoming connections during the brief period where I am creating my snapshot on the prod machine. How can I create a consistent snapshot that works for upgrades without shutting down the mysql daemon?

Nitty gritty on creating and restoring the backup:

FYI prod is running Ubuntu 16.04, and dev is running Ubuntu 20.04.

My backup procedure looks like this:

1. Lock all tables:

   prod mysql> FLUSH TABLES WITH READ LOCK;
   

2. Create a snapshot:

   prod# lvcreate --extents 100%FREE --snapshot --name mysql-backup /dev/myvg/mylv
   

3. Unlock all tables:

   prod mysql> UNLOCK TABLES;
   

4. Mount the snapshot and create a tar archive of the data:

   prod# mount /dev/myvg/mysql-backup /mnt/bkup
   prod# tar -C /mnt/bkup -aczf ~/bkup.tar.gz mysql
   

5. Cleanup

   prod# umount /mnt/bkup
   prod# lvremove --yes /dev/myvg/mysql-backup
   

6. Move the tar file to the development machine. Untar the data into /data/mysql.

7. Start the mysql daemon:

   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.
   

8. Examine /var/log/mysql/error.log:

   2023年10月03日T14: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月03日T14:21:47.746245Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
   2023年10月03日T14:21:47.747076Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
   2023年10月03日T14: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月03日T14:21:48.125480Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
   2023年10月03日T14:21:48.404686Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
   2023年10月03日T14:21:48.405086Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
   2023年10月03日T14:21:48.406142Z 0 [ERROR] [MY-010119] [Server] Aborting
   2023年10月03日T14:21:48.407096Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34-0ubuntu0.20.04.1) (Ubuntu).
   

Answer 1

mysql 8.0 will not upgrade the data files unless you first perform a clean shutdown using mysql 5.7. Fortunately, you can do this as follows on the prod machine:

1. Hot-copy a consistent snapshot of the production database LVM volume and mount it to /mnt/bkup as described in the question above.
2. Spawn a temporary mysql 5.7 process with datadir pointing to the mounted snapshot (in my case /mnt/bkup/mysql).
3. When the temporary mysql process has finished initializing, send it the SIGTERM signal to provoke it to perform a clean shutdown.
4. Then tar the contents of /mnt/bkup/mysql and transfer it to the dev machine.

In order to figure out what arguments to give the temporary mysql process, I used the command prod# mysqld --print-defaults and then tweaked the --socket, --port, --datadir, --tmpdir, and --log_error arguments so they would not interfere with the primary mysqld instance which is running off the production data.

Here's what I came up with, which works for me:

TMP=/tmp/db-bkup
PIDFN=$TMP/mysqld.pid
LOGFN=$TMP/error.log
mkdir -p $TMP
rm -f $LOGFN
mysqld --user=mysql --pid-file=$PIDFN --socket=$TMP/mysqld.sock --port=3307 --basedir=/usr --datadir=/mnt/bkup/mysql --tmpdir=$TMP --lc-messages-dir=/usr/share/mysql --skip-external-locking --bind-address=127.0.0.1 --key_buffer_size=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam-recover-options=BACKUP --query_cache_limit=1M --query_cache_size=16M --log_error=$LOGFN --expire_logs_days=10 --max_binlog_size=100M &
while :
do
 if cat $LOGFN | grep "\[ERROR\]"; then
 echo "mysqld failed to clean up my hot copy."
 exit 1
 fi
 if cat $LOGFN | grep "\[Note\] mysqld: ready for connections."; then
 PID=`cat $PIDFN`
 kill -SIGTERM $PID
 timeout 1m tail --pid=$PID -f /dev/null
 if [ $? -eq 124 ]; then
 kill -SIGKILL $PID
 fi
 break
 fi
 sleep 1
done

After this I can proceed to tar /mnt/bkup/mysql and transfer it to the dev machine, where if I initialize the mysql daemon, I get the following:

2023年10月05日T14:06:41.874464Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34-0ubuntu0.20.04.1) starting as process 5582
2023年10月05日T14:06:41.926719Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023年10月05日T14:06:41.927847Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023年10月05日T14:06:44.251288Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023年10月05日T14:06:55.445792Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023年10月05日T14:07:03.036193Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' started.
2023年10月05日T14:08:02.968532Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' completed.

Success!