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'))