Need help with some recovery

Question

So i've gone and did something terribly wrong out of haste and not thinking properly. I have a PXC (percona cluster. I guess mysql cluster with Galera?) running with just 1 one being bootstrapped (already bad) to run as a production server.

Now we had a request come in from a coworker who urgently needed some data recovered from a database from about a week ago, because he wanted to recover some settings from it. We don't take any backups using xtrabackup or mysqldumps or whatever. But we do take LVM snapshots. So i tried restoring the files from a specific database on another (test) mysql server i had running. But because it isn't a PXC the restore constantly failed.

i decided to create a new database (lets name it DB-B) on the PXC, run a mysqldump of the database i wanted a restore of (lets name that DB-A), import it into the new database. delete the files of that database in it's directory and then i copied in the files from the database directory off the LVM backup.

Of course the PXC cluster immediately crashed (because i had forgotten to turn it off) and the system could not start again. Now from what little MySQL knowledge i have i guess this is because innodb still keeps track of the files and seqno in the ibdata file.

Now in the logs i can see that this is the issue preventing the PXC from starting up again. Even with all of the innodb_force_recovery settings 1-5 i get the following error:

2020-07-01T11:36:11.840315Z 13339321 [ERROR] InnoDB: Space id and page no stored in the page, read in are [page id: space=535955, page number=4], should be [page id: space=620409, page number=4]
2020-07-01T11:36:11.840385Z 13339321 [ERROR] InnoDB: Trying to access page number 1630627123 in space 620409, space name DB-B/liveuser_authlog, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.

Now the PXC does start if i set innodb_force_recovery=6 which i know is bad. But i know that it's the DB-B database which is causing problems and i was hoping i could just delete it. Restart the server and i could go on to regret all my mistakes another day. But when i try to start it after i've dropped the DB-B database i keep getting this error:

2020-07-01T13:18:40.771446Z 0 [Warning] InnoDB: Tablespace 620453 was not found at ./DB-B/mail_queue.ibd, and innodb_force_recovery was set. All redo log for this tablespace will be ignored!
2020-07-01T13:18:40.949631Z 0 [ERROR] [FATAL] InnoDB: Missing MLOG_FILE_NAME or MLOG_FILE_DELETE for redo log record 39 (page 559566:3) at 3425204536239
2020-07-01 08:18:40 0x7f5a56830880  InnoDB: Assertion failure in thread 140025975212160 in file ut0ut.cc line 943

is there any way i can resolve this by removing the DB-B records somewhere and restart this PXC and then try to restore this database elsewhere. The whole DB-B database is not a production database anyways so i do not mind deleting it.

My config:

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/


# Node/SQL Settings
[mysqld]
server-id=1
port=3306
datadir=/mysql-data/data
socket=/mysql-data/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
innodb_force_recovery=6
max_allowed_packet=1G
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
innodb_stats_on_metadata=0
max_connections = 2048

# InnoDB Settings
innodb_buffer_pool_size = 24G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
optimizer_switch="derived_merge=off"

# PXC Cluster Settings
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=ams-clus
wsrep_cluster_address=gcomm://ip1,ip2,ip3

wsrep_node_name=name-1
wsrep_node_address=ip1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=usr:passwd

pxc_strict_mode=DISABLED
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Answer 1

So i was able to recover from this. I will leave this here for people whom encounter the same things.

The part where PXC fails to start and returns the error:

2020-07-01T13:18:40.949631Z 0 [ERROR] [FATAL] InnoDB: Missing MLOG_FILE_NAME or MLOG_FILE_DELETE for redo log record 39 (page 559566:3) at 3425204536239

Seems to be a bug related to either PXC (Percona) or MySQL. However it is directly related to corrupted InnoDB Tables. Most likely something going wrong in the InnoDB REDO log where it attempted to recover tables of whom the data was no longer present.

My only option at this time was to set innodb_force_recovery=6 to avoid the REDO logs to be triggered and to proceed to mysqldump all the databases away from this server. At this stage you cannot recover from this as the recovery mode 6 will (from mysql 5.6 and onward i think) set the innodb engine in Read-Only mode.