Borked my XAMPP upgrade after Yosemite, what now?

[tripnosis]

I had XAMPP running on Mavericks without a problem.

Then unfortunately decided to upgrade to Yosemite (not a beta version, the final public version).

As others have reported, MySQL stopped runnning.

So I upgraded XAMPP to the latest version 1.8.3-5 I think it is --- that was released specifically to address the MySQL problem on Yosemite.

Before upgrading, I made backups of the following folders: "htdocs" and "var/mysql".

After upgrading, when I looked on PHPMyAdmin, my custom created database username was gone, and while all my databases are there, there are no tables in them.

Is there any way I can restore my tables using the "var/mysql" folder I had backed up?

(In serious doo doo right now... please help.)

[danielwhite1]

I'm no expert at all of this but had a similar problem yesterday.

I got around it by doing something along the lines of:
- Deleting my databases in phpMyAdmin
- Created the databases again in phpMyAdmin
- Created the tables again with 1 column. Named that column 'ID'.
- Then edited the permissions of the folder in var/mysql/dbname and files inside that folder to allow myself to read and write. (Lots of password entering!)
- I then, from the copy of my old database files, opened the .frm and .ibd files (for each db table) and copied and pasted the contents over from my backup files to the newly created .frm and .ibd ones.
- Once I finished I restarted MySQL and Apache and my tables reappeared.

That then gave me the ability to see my databases with their tables and columns in phpMyAdmin. I could then export the database and get the SQL. Which I made sure I saved!

[vivekrajns]

@danielwhite1 I tried this!

Initially I faced a file permission error! I fixed it by duplicating the file permission from the existing file (new table id ->id.idb&id.frm).

Then its showing "#1146 - Table 'db_name.table_name' doesn't exist".

[danielwhite1]

Did you transfer all the file contents for the database data.opt file too?

You may also want to try the ibdata1 files in the var/mysql folder too and delete the ib_datalog0 and 1 files. (Make a copy of them first!).

[vivekrajns]

@danielwhite1 yes! Tried copying the entire folders! Still facing the same issue

[Nobbie]

Which was the previous release of Xampp on your PC?

Can you tell us, which folders and files you have in your backup from var/mysql?

[vivekrajns]

@Nobbie Previous release : 1.8.3 - 3

Files and directories under var/mysql/

Macintosh.local.err

ibdata1

viveks-MacBook-Pro.local.err

login/

auto.cnf

mysql_upgrade_info

bike/

performance_schema

bname/

person/

cdcol/

person_db/

device_specification/

phpmyadmin

temp/

ib_logfile0

ib_logfile1

.

.

.

[Nobbie]

Did you backup your previous my.ini (or my.cnf or my.conf - i dont know the extension for OSX) from the bin folder? There may be a mismatch which "Engine" is used by MySQL (InnoDB vs. MyISAM). Try to recover the previous setup.

Can you step back to the old Xampp in order to run a SQL backup? This would give you a full export of your own data in *.sql files. I dont know if there is a problem between older/newer MySQL releases (InnoDB).

[MarosS]

Hi, I have the same problem, but it does not work for me. I also tried other solutions on the forums and blogs. But I still have issue.
Can someone help me restore one database only from .idb and .frm files? It's not private data, so I can send you these files.
You will send me back sql dump of it, I will create new database in phpmyadmin and import this dump.
I tried some programs for restoring database, but they are trial, I do not want pay full price of licence only for one database.

Restoring this database is very important for me, because it's one webpage which is only in development, so I do not have
other backup somehere else.

Thank you

[JJ_Tagy]

First, you should use your own thread instead of taking someone else's thread.

Hi, I have the same problem, but it does not work for me.

What does not work? You should probably describe what you have done/tried.

Can someone help me restore one database only from .idb and .frm files?

It is not going to be that simple. Your best option is to do as Nobbie recommended and go back to the same version/setup of XAMPP with those files and export the data as SQL.

[MarosS]

I did wanted start new thread, because I have the same problem as tripnosis in first post. Upgraded to Yosemite, then upgraded xampp to the the latest version. I tried solutions which descirbe danielwhite1 :

- Deleting my databases in phpMyAdmin
- Created the databases again in phpMyAdmin
- Created the tables again with 1 column. Named that column 'ID'....

- But I only created new databases and tried to put there one table at first. But I did have access.
Then I downgraded xampp back to to the version 1.8.3-beta1 ( I had installer in my download folder fortunately. An tried again solution from danielwhite1.
Then I tried other solutions ( I do not remember all of them, I have been trying fix it 2 evenings). Everytime I get the same result, I have seen tables, or name of table was visible, but when I click on it, table doesn't exist or I did have permissions (in this case I changed owner/group of database on file system to the root, nobody on mysql, also tried permission to the 777.
I also tried make backup on old version(as Nobbie recommended), but it was not successful. It was canceled when it tried to backup databases.

I do not have any backup, I think that I upgraded XAMPP on my windows computer, and I have never had issues with this, so I did backup xampp.

[MarosS]

I finally resolve my issue.
I let xampp upgraded to 1.8.3.
- I created new Database(probably you can try do it on your primary database, but make a backup of mysql folder with idb files), copy my project (it was wordpress) and install it again with all plugins and custom codes into new database - so I had the exact table structure as previous. If you do not remember structure, you can try use only one field as danielwhite1 mentioned, but for me it does not work. I found some program, for restoring DB. It was a trial, so it shows me structure, but do not allow export it. I do not remember name, because I tried many of them. PS: it was for windows.
- then I run SQL for each tabe ALTER TABLE mydb2.table DISCARD TABLESPACE; - this delete idb files
- Copy all your old ibd files - you will need change permission for folder
- change owner of idb files for the same owner and group as .frm files
- run SQL for each table ALTER TABLE mydb.table IMPORT TABLESPACE;
- Restart mysql and for some people these works, for me not. I was unable to dump mysql.
my other steps were:
- turn off mysql
- open config file for mysql my.cnf and add line innodb_force_recovery = 1 into it.
- turn on mysql. Now I was able to export. At first I got error, but I tried export table by table. It works.
- remove line innodb_force_recovery = 1 from config file. restart mysql.
- I delete an old primary database, create it again with the same name, import mysql dump and everything works