Using Percona Xtrabackup to migrate MySQL server from one VM to another VM

MySQL & MariaDB Percona XtraBackup
,
1

Scenario: Using percona xtrabackup to take online backup and migrate to another VM.

Steps performed:

  1. Run backup command
  2. Run prepare command
  3. Copy the files from this VM to another VM and overwrite the data dictionary of MySQL server (currently in stopped state) in this new VM
  4. Start the MySQL server in this new VM.

Question:
Currently in 3rd step during copy, we don’t copy the redo logs and undo logs assuming that in prepare step, all committed txns changes are applied from redo logs and uncommitted txns changes are rollbacked using undo logs and we have a consistent state, thus they are not needed.

I understand that undo logs are also used for MVCC but does the new server needs these old undo logs since new transactions might never need them and it can create a new undo log after start.

Is our assumption correct or is there a scenario where it is mandatory to copy them?

2

This is not recommended. Overwirting server data directory with files from backup directory is not recommended. You will see weird crashes later on.

After you copied prepared files to the destination VM,

  1. you should remove (or move the server datafiles to a different directory as a backup)
  2. use the xtrabackup --copy-back command or --move-back command
  3. start server
  4. If all goes well, you can remove your old server data directory
3

Hi @Nitish1,
Your steps are all weird. There are no extra files needed to be copied. Here are the simplest steps to migrate from server A to server B:

  • A# xtrabackup --backup --parallel=4 --stream=xbstream --compress >backup.xbs
  • B# systemctl stop mysql; rm -rf /var/lib/mysql/*
  • A# scp backup.xbs new.host.local:/var/lib/mysql/
  • B# cd /var/lib/mysql; xbstream -vx < backup.xbs
  • B# xtrabackup --decompress --remove-original
  • B# xtrabackup --prepare
  • B# chown -R mysql:mysql /var/lib/mysql
  • B# rm backup.xbs (or move it somewhere else for safe keeping)
  • B# systemctl start mysql

That’s all you need to do. Configure B as a replica of A, let B catch up. Then stop A, stop replication, move apps to B. Done.

4

Thanks @satya.bodapati, I presented my steps in very crude form mentioning only major steps, we do keep backup of current data directory in new server to restore in case of failures.

It looks like you are suggesting to not remove undo/redo log present after running prepare command?
One reason why we wanted to remove these logs were because of their size when taking online backup of large databases, we could make copy faster if they were not needed by not copying them

But my question is still unanswered:
After a successful prepare phase, when we get a transactionally consistent data after applying redo/undo logs, are these logs needed still needed if i intend to use this data for running a new mysql server?

5

image
image627×789 7.21 KB

Hi @matthewb, I understand there are no extra files needs to be copied, my question is not that. If you see the above image, this is result after a successful backup and prepare command.

My question is: Since in prepare stage, undo/rego logs are applied to bring the data to consistent state, do i need to copy the undo/redo log files (eg: undo_001, undo_002 etc) to new server? Since this data will be consistent, on startup, there should be no need for this new server to run crash recovery again and it can create new redo/undo logs as new txns run on this new server

6

copy-back is exactly for this purpose

  1. Figure out the files that are necessary for server startup
  2. Move tablespaces to exact locations as recorded during backup.

copy-back skips many files that are not necessary for server.

Xtrabackup applies redo log, so there is no need to copy redo files. AFAIR, xtrabackup consumes the redo files and no redo files are left at the end of prepare.

undo tablespaces should be copied. They are still necessary for purging delete marked records.
Ofcourse, all other tablespaces should be copied.

But we don’t recommend the manual copy

7

Don’t do any manual copying. Just let the tool do what it needs to do.

8

Thanks @matthewb @satya.bodapati, will follow the advice.

@satya.bodapati , can you explain a little about this statement:
“undo tablespaces should be copied. They are still necessary for purging delete marked records.”
Will the absence of undo logs in this scenario prevent the innodb engine from starting?

We tried the scenarios in our test setup:
Scenario1: Start the new server without copying undo logs.
Scenario2: Start the new server with undo logs.

While scenario 2 works, scenario 1 was also working for us except once when it failed with innodb throwing this error:
[ERROR] InnoDB: Unable to open undo tablespace ‘.//undo001’.

I tried to reproduce it by running an OLTP workload on test server and parallelly taking backup using xtrabackup and restored it on new server without undo logs, but it started without failure.
At this point, it is hard to reproduce the failure of scenario1 since it is working on all my test backups, if you have any ideas, please share

In your statement, since data is in consistent state after prepare, there is no need to restore this deleted record, and it only needs to be purged. If there was something to restore, it would have been done during the prepare phase. When the purge thread starts in the new server, it can safely purge them if it does not find any undo logs for this record.

I read a little on this, that Innodb index only store the latest version of data and any old versions are moved to undo logs which are used for both MVCC and recovery.

Please think of my question as discussion, I understood your advice and will use that but I am curious why it is failing

9

@Nitish1, can you please explain your reasoning on wanting to skip copying the redo logs? These are not multi-GB files. I’m having trouble understanding why anyone would want to do this in the first place. Do you believe there is some sort of performance gain, or other optimization by not copying these files?