I had a bad server crash in which I was able to restore my MySQL files, but I was unable to do a dump in order to get the .sql file. In other words, I have each individual part that was saved in the database. (i.e "databasename.table")
So, I have all my information, but after resetting the server, I am now unable to put my database back online. When I copy the files over, phpMyAdmin and MySQL Workbench show the table names, but act as if they don't exist.
So the files are there, and the names are read, but the information is not there. (i.e phpMyAdmin says something like, "databasename.table does not exist") I am unsure of what to do and I hope I was able to describe it well enough for you guys to understand it as well. If anyone is able to help me, whether it be describing how I can compile those into a .sql, or moving them over for the new server to read them, I would greatly appreciate it.
Thank you!
Note: I have replaced the hard drive in the server, so I still have everything that was saved on that hard drive, but I am unable to get it to boot, so if you guys find that I need to go through it again and find some other files, that can be done.
What I've learned: Create constant backups of databases, it just makes sense that way…
Best Answer
This will depend a bit on your table type. With MyISAM tables, you can generally just copy the folder and files and everything will work. With other table types, it sometimes doesn't work as well.
The first thing you can try is to completely replace your MySQL data directory with the one from the backup. I would move it out of the way (
mv /var/lib/mysql /var/lib/mysql-orig) so that you can easily restore it. Do this with the MySQL server not running and preferably on a testing server so you don't accidentally break something on your production machine.Since you have the old hard drive data but can't get the drive to boot, it may be relatively painless to start MySQL from that drive (you can do this in a couple of ways; chroot to the old drive and try to start it there, copy the files to a server with a working MySQL instance, stop mysqld, edit the configuration file to point to the old mysql data directory, then restart MySQL, or finally just follow the steps above to completely replace the mysql data directory on the working instance with the one from the old server.
As D. Kasipovic points out, you should check the file permissions on the files you have restored, it could be as simple as a file permission problem.
You should make sure you're connecting as the root user or another user with administrative permissions; otherwise it's likely that a MySQL permissions will keep you from being able to see the databases. Depending what method ends up working for you, you may need to create any users that had permissions on that database.