0

I am trying to find a backup/restore procedure for a MySQL 8 DB with 400M+ Rows in InnoDB tables. MySQLdump takes days and therefore I am trying this now:

  • Mount a LVM volume on the DB Folder
  • Create LVM snapshot
  • Restore on other server from Snapshot

The files are all there, but MySQL will not start:

2021-07-04T15:48:14.415660Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-07-04T15:48:14.637407Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2021-07-04T15:48:14.637473Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 196 = ['test/SUM_stats_cat_d.ibd', 'website/wp_actionscheduler_actions.ibd']

Aparently there is more then the simple Folder of the database to copy. The local server holds other DBs which seem to be in conflict to the import.

Is there a way to get around this? If not, what would be a better strategy for backup/recovery?

merlin
  • 2,093
  • 11
  • 39
  • 78
  • 1
    Run a replication slave and do mysqldump backups from the slave. – Michael Hampton Jul 04 '21 at 16:56
  • The dump is not an issue 30m. The replication on other servers is. In this case to local dev server and also to a new staging/test environment. Runs for 22h already. – merlin Jul 04 '21 at 16:59
  • The replication should have been set up before the DB was loaded. Now you just get to wait. – Michael Hampton Jul 04 '21 at 17:06
  • Would it be possible to create and use other Schemas on the slave? @MichaelHampton If yes the slave could also be used for other purposes. – merlin Jul 07 '21 at 06:29

1 Answers1

0

I think LVM needs to be established while the disk is idle -- it involves carving out part of the device.

LVM handles the entire disk partition, not just a directory tree.

Between creating the snapshot and using it on another server, you must copy it to the other server.

I prefer to stop mysqld while doing the snapshot, but theoretically that is not necessary.

For speed, LVM is the fastest, by far.

Rick James
  • 2,463
  • 1
  • 6
  • 13