when upgrading a kolla-ansible powered OpenStack Environment from a single node mariadb to a Galera Cluster, the database gots somehow corrupted (I'm no database expert). All nodes were corrupted or not even running. (Yes, there wasn't a backup) This led finally to the challenge, to recover the database from the still existing ibd-Files
modern MySQL/MariaDB distributions with InnoDB does allow import of Tables from ibd-Files. It's recommended to also import the associated cfg-File of each table which doesn't exist in my case
without these cfg-Files an error will be raised Internal error: Drop all secondary indexes before importing table <tablename> when .cfg file is missing and this leads to my approach to seperate the primary index statements from the secondary indexes and add them after the Tablespace gets imported successfully
Create SQL Dump of a working fresh opensstack mariadb and store it for later:
mysqldump -A --compact -f -u root -p > sql_dump.sql
Import the environment of a fresh openstack mariadb (in my case Xena) into a location like /var/lib/mysql
Remove all created openstack relevant stuff in the database:
rm -r ibdata1 ib_* barbican/ cinder/ glance/ grafana/ heat/ keystone/ magnum/ neutron/ nova/ nova_api/ nova_cell0/ octavia/ placement/
Run a mariadb container which is pointing to the location with the DB files and start mysqld inside
(I used https://github.com/nexdrew/rekcod to get the runtime for the kolla container and adapted it to not shutdown on failing db start)
(maybe you have to change the ownership of the /var/lib/mysql folder)
docker run --rm --name mariadb_back -p 3306:3307 --runtime runc -v /etc/kolla/mariadb/:/var/lib/kolla/config_files/:ro -v /etc/localtime:/etc/localtime:ro -v /etc/timezone:/etc/timezone:ro -v /var/lib/mysql:/var/lib/mysql:rw -v kolla_logs:/var/log/kolla/:rw --net host -h masternode -l build-date='20221019' -l kolla_version='13.6.1' -l maintainer='Kolla Project (https://launchpad.net/kolla)' -l name='mariadb-server' -e 'KOLLA_CONFIG_STRATEGY=COPY_ALWAYS' -e 'KOLLA_SERVICE_NAME=mariadb' -e 'PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin' -e 'LANG=en_US.UTF-8' -e 'KOLLA_BASE_DISTRO=ubuntu' -e 'KOLLA_DISTRO_PYTHON_VERSION=3.8' -e 'KOLLA_BASE_ARCH=x86_64' -e 'PS1=$(tput bold)($(printenv KOLLA_SERVICE_NAME))$(tput sgr0)[$(id -un)@$(hostname -s) $(pwd)]$ ' -e 'DEBIAN_FRONTEND=noninteractive' -e 'PIP_INDEX_URL=' -e 'PIP_TRUSTED_HOST=' -e 'PIP_EXTRA_INDEX_URL=' --entrypoint /bin/bash -it quay.io/openstack.kolla/ubuntu-source-mariadb-server:xena
Inject the first part of the dump to create the tables: (should work without issues)
filter only the CREATE TABLE statements without secondary indexes using my script:
python3 filter_mariadb_openstack_sql.py sql_dump.sql create > openstack_create_filter.sql
insert the result into the databse:
mysql -u root -p -f -vvv < openstack_create_filter.sql
Discard the tablespaces: (should work without issues)
get the discard statements using my script:
python3 filter_mariadb_openstack_sql.py sql_dump.sql discard > openstack_discard_filter.sql
insert the result into the databse:
mysql -u root -p -f -vvv < openstack_discard_filter.sql
Now try to import the *.idb files: (fails for me because of incompatible FSP_SPACE_FLAGS)
Copy the *.idb files from the database you want to rescue into seperate folders in the database location:
for i in $(cat ~/working_dumps/openstack_databases); do cp ~/mariadb_backup/${i}/*.ibd ${i}/; donechown -R 42434:42434 /var/lib/mysql/*
filter the import statements from the sql dump:
python3 filter_mariadb_openstack_sql.py sql_dump.sql import > openstack_import_filter.sql
now try to import the tablespaces: mysql -u root -p -f -vvv < openstack_import_filter.sql
If this suceeds you can add the remaining secondary indexes:
filter only the KEYS, UNIQUE KEYS and CONSTRAINTs using my script:
python3 filter_mariadb_openstack_sql.py sql_dump.sql alter > openstack_alter_filter.sql
apply these statements to the database: mysql -u root -p -f -vvv < openstack_alter_filter.sql
some tables got sucessfully imported but most of them returning --> fuck
ERROR 1808 (HY000) at line 744: Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x21.)
--------------
ALTER TABLE traits IMPORT TABLESPACE
--------------
filter_mariadb_openstack_sql.py #!/usr/bin/env python3
# coding=UTF-8
import sys , re
regex_find_end_create = "ENGINE=InnoDB"
regex_ex_create_no_keys_string = "^(\s*INSERT INTO.*|\s*CONSTRAINT.*|\s*KEY.*|\s*UNIQUE.*)"
regex_ex_keys_string = "^(\s*CONSTRAINT.*|\s*KEY.*|\s*UNIQUE.*)"
regex_ex_use_db = "\s*USE.*"
regex_ex_create_table = "\s*CREATE TABLE.*"
filename = sys .argv [1 ]
command = sys .argv [2 ]
def open_file (filename ):
with open (sys .argv [1 ], encoding = "utf8" , errors = 'ignore' ) as f :
content = f .read ()
content_split = content .split ("\n " )
return content_split
def remove_regex (sql_content , regex_string ):
compiled_regex = re .compile (regex_string )
create_end = re .compile (regex_find_end_create )
result_arr = []
for line in sql_content :
result = []
result = compiled_regex .sub ('' ,line )
if result :
result = create_end .sub (regex_find_end_create + " ROW_FORMAT=redundant" ,result )
result_arr .append (result )
return result_arr
# dont know how to capsulate this in a elegant way :)
def extract_alter (sql_content , tablespace_action = "IMPORT" , add_keys = True , add_tablespace = True ):
regex_cr_tab = re .compile (regex_ex_create_table )
regex_use_db = re .compile (regex_ex_use_db )
regex_alt_key = re .compile (regex_ex_keys_string )
alter_table_key_tmp = "ALTER TABLE {tablename} ADD {sql_command};"
alter_table_tablespace_tmp = "ALTER TABLE {tablename} {action} TABLESPACE;"
result_arr = []
for line in sql_content :
use = regex_use_db .match (line )
if use :
result_arr .append (use .string )
create_table = regex_cr_tab .match (line )
if create_table :
table_name = create_table .string .split (" " )[2 ].strip ("`" )
if add_tablespace :
full_tablespace_sql = alter_table_tablespace_tmp .format (tablename = table_name , action = tablespace_action )
result_arr .append (full_tablespace_sql )
key = regex_alt_key .match (line )
if add_keys :
if key :
sql_command = key .string .strip ("," )
full_key_sql = alter_table_key_tmp .format (tablename = table_name ,sql_command = sql_command )
result_arr .append (full_key_sql )
return result_arr
def remove_trailing_commas (sql_content ):
result_arr = []
for index , line in enumerate (sql_content ):
result = []
result = line
try :
if sql_content [index + 1 ].startswith (") ENGINE" ):
result = line .rstrip ("," )
except :
pass
if result :
result_arr .append (result )
return result_arr
def print_content (sql_content ):
print ("\n " .join (sql_content ))
if __name__ == "__main__" :
content_split = open_file (filename )
if command == "create" :
proc_content = remove_regex (content_split , regex_ex_create_no_keys_string )
proc_content = remove_trailing_commas (proc_content )
print_content (proc_content )
elif command == "discard" :
proc_content = extract_alter (content_split , tablespace_action = "DISCARD" , add_keys = False )
print_content (proc_content )
elif command == "import" :
proc_content = extract_alter (content_split , tablespace_action = "IMPORT" , add_keys = False )
print_content (proc_content )
elif command == "alter" :
proc_content = extract_alter (content_split , add_tablespace = False )
print_content (proc_content )
