herrfeder/recover_openstack_mariadb_from_sqldump.md

## recover_openstack_mariadb_from_sqldump.md

      
    Raw
  

              recover_openstack_mariadb_from_sqldump.md
            
          
    Problem


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


Approach


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}/; done
chown -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

Result


some tables got sucessfully imported but most of them returning --> fuck

this seems as there is a inconsistency between different MariaDB versions or some other stuff I don't know about
I tried to adjust ROW_FORMAT and other stuff without luck
this blogpost goes really deep into this topic: http://mysqlentomologist.blogspot.com/2019/05/on-importing-innodb-tablespaces-and-row.html


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)
No results found