0

Ok, so when mysql updated a while back, it broke. It was simply not starting. So I tried fixing it, but hadn't been successful.

I finally gave up, copied all the directories & files under /var/lib/mysql out to a safe location, apt-get purge mysql, then apt-get install mysql.

So the server is running, but I can not, for the life of me, seem to get the old databases back in, and in a useable form.

I did get my gallery3 database in by just copying the gallery3 dir from my mysql/backup location in. But my actually gallery3 installation wouldn't talk to it. :/

Possibly there are permission issues, but I'm really clueless about sql.

Any help would be appreciated.

1 Answer 1

1

You won't get your databases back that way. If your old MySQL version was exactly(!) the same as the one you are using now, it would possibly work out but only if you would move the current /var/lib/mysql to something like /var/lib/mysql.old and move all content of the old backuped /var/lib/mysql including all subdirectories to /var/lib/mysql now.

Your problem is that you made only a raw data copy of the files containing the data but you didn't really backed-up the logic. See, if a database is created like gallery3, it inserts certain rows and data into the data dictionary of the MySQL DB itself and into the 'mysql' master schema. So only copying over a directory including datafiles won't work, as - let's call them links - to those datafiles inside the new MySQL setup are missing.

Also, if you ran the old MySQL installation with default DB type "innodb" and didn't backup the ibdata1 and ib_logfiles0/1 files in /var/lib/mysql your data is most certainly lost as InnoDB tables are completely stored inside the InnoDB tablespace file (ibdata1) and only a bit of metadata is stored in the files in the subdirectory.

Best way for a simple backup before such big hiccups is to make a mysqldump of your database (aka schema) into an .sql file and re-insert it. That is called a "logical" backup (instead of physical), as it dumps every information logically necessary to rebuild your db schema as it was before. A physical backup isn't recommended (only if you know exactly what you are doing and what risks are involved).

Hope that helps a bit

2
  • I do have the innodb files. Would that help? I understand what you are saying, and I know my lack of back up routines is dumb, but it's a home toy/hobby, so rebuilding isn't terrible. In this case, a "sudo apt-get upgrade" broke it, which it shouldn't have. Ah well. If it's repairable, I'd like to. Otherwise, reinstall gallery3 & away we go!
    – Tymanthius
    May 15, 2013 at 19:42
  • I think the repair time is longer than a reinstall of the gallery would be :)
    – JeGr
    May 17, 2013 at 11:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .