How to backup large MySQL database with the help of LVM snapshots?

merlin

11/4/22, 4:31 PM

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?

109

1 + 4

mysql

lvm

Michael Hampton

11/4/22, 4:56 PM

Run a replication slave and do mysqldump backups from the slave.

merlin

11/4/22, 4:59 PM

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.

Michael Hampton

11/4/22, 5:06 PM

The replication should have been set up before the DB was loaded. Now you just get to wait.

merlin

11/7/22, 6:29 AM

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.

Score:0

Server

Rick James

11/6/22, 8:59 PM

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.

0 + 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How to backup large MySQL database with the help of LVM snapshots?

TH: จะสำรองฐานข้อมูล MySQL ขนาดใหญ่ด้วยความช่วยเหลือของสแน็ปช็อต LVM ได้อย่างไร

RO: Cum se face backup pentru bazele de date MySQL mari cu ajutorul instantaneelor LVM?

RU: Как сделать резервную копию большой базы данных MySQL с помощью снимков LVM?

VI: Làm cách nào để sao lưu cơ sở dữ liệu MySQL lớn với sự trợ giúp của ảnh chụp nhanh LVM?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.