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.
# # 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