0

I understand I can use mysqldump or xtrabackup to move InnoDB from one db to another.

Considering I am okay with server down time, are there better way to move InnoDB database?

I have tried copying all "ib_*" files and seems does not work. Are there anything I've missed?

Ryan
  • 5,341
  • 21
  • 71
  • 87
  • 1
    If you copy the entire tree, then it will go intact. Note that ibdata1 and iblog* are critical, as well as *.ibd and *.frm. But that is not much faster than dump/backup + simultaneous reload. – Rick James May 24 '19 at 16:36

2 Answers2

2

For anyone wondering, Copying the entire mysql data root (in my case) /var/lib/mysql AFTER shutting down mysqld worked perfectly to migrate data to another system.

Otherwise if you need replication the cluster solutions are more appropriate and faster than DRBD

Spooler
  • 7,016
  • 16
  • 29
Colin Faber
  • 21
  • 2
0

You might try using DRBD. It's a block-level replication technology that will synchronously (or asynchronously, if replicating over a distance) create a raid-1 relationship between two or more nodes.

DRBD can be used not only with newly allocated storage, it can also replicate existing volumes - provided you can either extend that volume to make room a small amount of metadata at the end, or have another volume attached that you can use as dedicated metadata storage. Dedicated metadata is easier to remove if you only intend to use this temporarily.

The process would generally involve establishing a DRBD device on each node that stores your tables, shutting down the database on one side, then immediately starting it on the other - with identical data.

Spooler
  • 7,016
  • 16
  • 29
  • It will, but only the amount of time it takes to stop the database, remount its filesystem using the DRBD device rather than the backing device, then start the database. Most of that time will likely be spent stopping and starting the database. – Spooler May 24 '19 at 16:37