Can I restore a MySQL table from a file (non-sql) backup?

Question

I am using XAMPP 5.6.23-0 on a Mac.

I have experienced many problems with MySQL, where the server simply refuses to restart after something has happened (not sure which events make it crash). In the end, the only working solution was to re-install XAMPP. The problem is that the new installation does not automatically pick up the databases that were created in the old installation, even if they are in the same physical location on my disk.

The obvious restore method for a MySQL table or database is of course running the restore by importing an earlier SQL backup from phpMyAdmin, but you need to have that backup, and that is impossible to create after the MySQL server dies and refuses to restart. Running a backup after every single database change is not feasible and should not be required IMHO.

I have tried to completely remove the remaining xamppfiles folder before a reinstall of XAMPP, and that works, but of course the databases I have created are not available anymore. I dug out the files from a Time Machine backup, manually added them to the xamppfiles/var/mysql directory and changed permissions to match those of the databases included with the XAMPP package (chmod 770 on the custom database directories in xamppfiles/var/mysql).

Running phpMyAdmin shows that the custom databases and the tables are visible, but when I try to access any of them, there is an error that the table does not exist in the engine. I did add the user account in the new XAMPP installation that has the same name, password and privileges as used in the old installation, so it is not an issue with access privileges. Simply the message that the table does not exist in the engine.

There must be some file(s) in the XAMPP installation that the MySQL server uses to find the various databases and tables. If anyone can point me to that file I can probably dig it out of the Time Machine backup as well and try to make the new installation (same XAMPP version) run with that configuration file to regain access to my custom databases.

Any pointers are welcome. If this cannot be done, also please let me know so I can stop wasting my time on XAMPP and get a stable MySQL server installation instead.

Answer 1

Your best solution is going to be to install MySQL, stop the service, then completely replace the entire "datadir" with the one from the backup, then cross your fingers before restarting the MySQL service.

The datadir is probably the folder you found in xamppfiles/var/mysql; that sounds right but I don't use XAMPP to know for sure. You can open up the command line client and issue the command SHOW VARIABLES LIKE "%datadir%"; to confirm the proper path.

You can't simply restore the parts of what are missing alongside the existing structure, because you also need the information about which users have access to the tables and other metadata that isn't stored directly in the individual database folder. Restore the entire folder so you get all the correct metadata, database and table structure and data, and permissions.

This is a bit beyond the scope of what MySQL is meant to do, so success is not guaranteed (proper backups usually involve making a .sql file export, which you can do from phpMyAdmin or the command line; I use the 'mysqldump' command line client).