0

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

1 Answer 1

0

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-05T14: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-05T14:06:41.926719Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-10-05T14:06:41.927847Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-05T14:06:44.251288Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-05T14:06:55.445792Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-10-05T14:07:03.036193Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' started.
2023-10-05T14:08:02.968532Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' completed.

Success!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.