Database Restore .Frm Python 3 using mysqlfrm

How I Restored My Lost Database

My database was lost due to an error in server settings. After searching the internet, I found many discussions about .frm and .ibd files.

The .frm file is an InnoDB file that stores table construction, while the .ibd file stores data or the contents of the table. However, the .ibd file contains raw data, which can’t be directly used for a database.

To restore a database using these files, there are many ways. Below, I will share a method I found to be the simplest as it can restore many tables at once using a Python script and a tool called mysqlfrm.

  1. First, find the folder where the .frm and .ibd files are located. In my case, it’s in /var/lib/mysql/(database_name). Iā€™m using CentOS 7 for my server.
  2. Copy these files to your working folder. For example, C:\Users\ilham\Downloads\(database_name).
  3. Install the mysqlfrm tool from the MySQL utilities archive.
  4. Install and run MySQL server on your machine.

Now you can start writing code. First, you need to run the mysqlfrm function.

				
					def cmd_restore(dir):
    pathfile = dir
    for i in pathfile:
        if '.frm' in i:
            os.system('mysqlfrm --server=remote:ilham211@localhost --port=3311 '
                      'C:\\Users\\Ilham\\Documents\\(Database Name)\\' + i + 
                      ' > C:\\Users\\Ilham\\Documents\\(Database Name)\\output\\' + i.replace('.frm', '') + '.sql')
            print(i)

That line of code is used to check the number of .frm in the database folder, then run the mysqlfrm command on the remote part you can fill in access to your mysql server, then save to output folder as .sql files. But the file contains some text output, so that the query on the .sql file can be run. You can write the following line of code.

				
					def delete_list(pathfile):
    list_word = ["#..done."]
    openfile = open(pathfile)
    lines = openfile.readlines()
    for i in range(0, 10):
        list_word.append(lines[i])
    openfile.close()
    return list_word

def delete_text(name_file, openfile, savefile):
    delete_list = delete_list(openfile)
    with open(openfile) as fin, open(savefile, "w+") as fout:
        for line in fin:
            for word in delete_list:
                line = line.replace(word, "")
            fout.write(line)
    fin.close()
    fout.close()
    return print('delete')

Run it using this code.

				
					def prosess(self):
    for i in self.listdiroutput:
        if '.sql' in i:
            self.delete_text(i, self.fileoutput + str(i), self.fileoutput + str(i).replace('.sql', '.txt'))

For the .frm file we have finished taking the table structure, then we have to implement it into a new database, you can create a line of python code to connect to the database then run every query that is in the output folder.


Iā€™m using a python package called pymysql, you can read the documentation on the repository pymysql. 

				
					with connection:
    with connection.cursor() as cursor:
        for i in listdir:
            if '.txt' in i:
                f = open(file + '\\' + str(i), "r")
                query = f.read().replace('(old database name)', '(new database name)')
                cursor.execute(query)

And finally all structure tables already exist in your new database, the next step is to copy the .ibd file to your new database folder, in my case, I am using laragon, my database file is at ā€œC:\laragon\data\mysql\(Database name)ā€, to restore data from your table.


Run the sql query command.

				
					ALTER TABLE (Table name) IMPORT TABLESPACE

You can use the same method as when running a query to create a table structure.


As a result, the database has been successfully restored from the start of the structure and contents of the table data, to see the line of code more clearly you can see the repository that I have created and I will update regularly from my github.