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?

Improve this question
4
  • 1
    Run a replication slave and do mysqldump backups from the slave. Commented Jul 4, 2021 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. Commented Jul 4, 2021 at 16:59
  • The replication should have been set up before the DB was loaded. Now you just get to wait. Commented Jul 4, 2021 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. Commented Jul 7, 2021 at 6:29

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.