1

Have the stand-alone Proxmox node with LVM thin and VM 130 volume /dev/pve/vm-130-disk-0 inside it. VM 130 was deleted accidentally. Mysql database was lost with VM. After we've discovered it in a day, all VMs on this node was stopped to prevert write to /dev/pve. Fresh backup was deleted too with no chance of recovery. After deleting new VMs was not created.

How I can recovery database tables from lost Volume (earlier known as /dev/pve/vm-130-disk-0) ?

What I'm tried (no luck):

  1. Rollback LVM metadata on vm130 to deletion moment. As result in output of command "lvs" I can see volume /dev/pve/vm-130-disk-0, but its inactive and cannot be activated because an error: device-mapper: reload ioctl on (253:7) failed: No data available While restoring I was forced to use "lvconvert --repair" which damaged LVM like here https://bugs.launchpad.net/ubuntu/+source/lvm2/+bug/1625201 Workaround from link helps activate pve/data, but not /dev/pve/vm-130-disk-0.

  2. testdisk to find VM partitions on physical disk /dev/sda3. 18 partitions was found, but no one have known test strings from VM 103. Tested with bgrep. Note thease partitons covers not whole pshysical disk.

  3. Searching with bgrep over /dev/sda3 text strings from VM 130. Strings found in two different placed on disk outside the VM partitions founded by testdisk.

  4. Recovery mysql tables with 'undrop-for-innodb' searhing whole disk /dev/sda3. Got 12 GB pages for different databases, including DB which I'm looking for. But dictionary/SYS_TABLES.sql produce huge table IDs like 5643947289462206311 and strange symbols \0! in the table name:

    2020203D2020 4E414D455F434F SYS_TABLES "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0std\n\0\0!\0!\0!\0database_name\0INSERT INTO tbl_log_\n SET log_id " 5643947289462206311 NULL NULL NULL 1600742439 "" 741488441

    Also, dictionary/SYS_INDEXES.sql can find nothing using thease huge table IDs. Good howto in first answer here: https://dba.stackexchange.com/questions/23251/is-there-a-way-to-recover-a-dropped-mysql-database

Inside /dev/pve/vm-130-disk-0:

# fdisk -l

     Disk /dev/sda: 32 GiB, 34359738368 bytes, 67108864 sectors
     Units: sectors of 1 * 512 = 512 bytes
     Sector size (logical/physical): 512 bytes / 512 bytes
     I/O size (minimum/optimal): 512 bytes / 512 bytes
     Disklabel type: dos
     Disk identifier: 0x65ab60ca

     Device     Boot    Start      End  Sectors  Size Id Type
     /dev/sda1  *        2048 64286719 64284672 30.7G 83 Linux
     /dev/sda2       64288766 67106815  2818050  1.4G  5 Extended
     /dev/sda5       64288768 67106815  2818048  1.4G 82 Linux swap / Solaris

     Above /dev/sda1 is ext4 with mysql database files.

mysql
     - mysql-server-5.5               5.5.47-0+deb8u1                  amd64
     - tables stored in innoDB format.
     - tables stored in separate files (my.cnf):
            innodb_file_per_table = 1
     - binary log forced enabled, but it rotated very othen:
            expire_logs_days    = 7

Proxmox details:

# uname -a
    Linux wz020 4.15.18-12-pve #1 SMP PVE 4.15.18-35 (Wed, 13 Mar 2019 08:24:42 +0100) x86_64 GNU/Linux
# pveversion
    pve-manager/5.4-3/0a6eaa62 (running kernel: 4.15.18-12-pve)
# pvs
    PV         VG  Fmt  Attr PSize PFree
    /dev/sda3  pve lvm2 a--  1.64t 6.00g
# vgs
    VG                           #PV #LV #SN Attr   VSize VFree
    pve                            1  26   0 wz--n- 1.64t 6.00g

I would be grateful for any advice and ideas. Thanks!

Alan Tone
  • 33
  • 6
  • Thanks for everyone! I [gave up with LVM]( https://serverfault.com/questions/1000936/lvm-after-vgcfgrestore-got-device-mapper-reload-ioctl-on-25419-failed-no-d/1001273#1001273) and will continue with 'undrop-for-innodb'. – Alan Tone Jan 31 '20 at 17:50

2 Answers2

0

I think at first you should restore LVM. And only then think about how to restore mysql db file in file system.

Similar question: Any way to recover ext4 filesystems from a deleted LVM logical volume?

uor
  • 1
0

Recovery mysql tables with 'undrop-for-innodb' searhing whole disk /dev/sda3. Got 12 GB pages for different databases, including DB which I'm looking for. But dictionary/SYS_TABLES.sql produce huge table IDs like 5643947289462206311 and strange symbols \0! in the table name:

You need SYS_TABLES/SYS_INDEXES to find index by table name. It looks like your SYS_* are corrupted (or maybe stream_parser mistakenly found pages that don't belong).

So, no SYS_* tables but hopefuly your data somewhere in these 12G worth of pages. What to do? Try grep. For example, if you know that a table must have a string foo@bar.com try to find indexes that contain it and then check with c_parser if it's really the table you're looking for.

It's very manual, involved and time-consuming process. I would try to recover the database otherwise and then review your backup process in a postmortem.

akuzminsky
  • 738
  • 4
  • 9