CODEWITHSUNDEEP
mysqlxamppbackupinnodb
van_folmert
van_folmert

Reputation: 4517

How to restore mysql database in XAMPP?

I have a backup of the entire xampp/mysql folder. How can I use it to recover my old db on a fresh installation of XAMPP?

Simply copying the old xampp/mysql folder to new xampp leads to mysql errors like:

InnoDB: Table veno/sls37_finder_links_termsc in the InnoDB data dictionary has tablespace id 1171, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.

Upvotes: 8

Views: 35843

Answers (4)

Gaboo
Gaboo

Reputation: 51

I got the following logs:

2020-02-11 14:26:06 0 [ERROR] mysqld: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed 2020-02-11 14:26:06 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed 2020-02-11 14:26:06 0 [ERROR] Aborting

Well, what I did as follows:

  1. Installed a new instance of XAMPP in a different directory (e.g. C:\xampp_new)
  2. Copied back the three db files from the new installation to the old one:

  • C:\xampp_new\mysql\data\mysql\db.frm to C:\xampp\mysql\data\mysql\db.frm

  • C:\xampp_new\mysql\data\mysql\db.MAD to C:\xampp\mysql\data\mysql\db.MAD

  • C:\xampp_new\mysql\data\mysql\db.MAI to C:\xampp\mysql\data\mysql\db.MAI

  1. Started MySQL service from XAMPP then it worked well.

I hope it could also be useful information for those who steps into an error like this.

Be careful when you are trying to set privileges.

Upvotes: 2

Soheil Rahsaz
Soheil Rahsaz

Reputation: 791

Continue to andread answer also copy and replace these files to the new xampp folder.

From \xampp\mysql\data\mysql

Files : proc.frm, proc.MAD, proc.MAI

Upvotes: 2

andread
andread

Reputation: 126

To restore your database to XAMPP these are the things you need: -.frm , .ibd files , and ibdata1 can be found in mysql/data folder

Steps

  • Install New XAMPP

  • Replace ibdata1 in mysql/data

  • Copy the folders of the database you want to restore [.frm and .ibd] and paste in mysql/data

  • Run the mysql and Viola it works!

If there is an #1932 error occur, just import the create_tables.sql from xampp/phpMyAdmin/sql and this solves the problem.

Upvotes: 6

Leandro Papasidero
Leandro Papasidero

Reputation: 3738

You cannot copy a database folder that has InnoDB tables because InnoDB storage engine maintains a symbiotic relationship between the InnoDB tables' physical files (frm and .ibd) and the data dictionary.

Reference: https://dba.stackexchange.com/questions/82093/moving-binary-database-folder-is-causing-issues-with-innodb-tables

You can try (InnoDB part): https://dba.stackexchange.com/questions/57120/recover-mysql-database-from-data-folder-without-ibdata1-from-ibd-files/57157#57157

Recommendation: It might be late in your case, however, before you move to another server, it is a good idea to export your databases to a sql file and Import it from phpmyadmin, Mysql Workbech, SQLyog or terminal

Export:

mysqldump -u user -p password database_name > database_name.sql

Import

mysql -u user -p password database_name < database_name.sql

Upvotes: 4

Related Questions