Yeah, the issue was that there were no .frm files, only .ibd and the main backup files, but MySQL Workbench/HeidiSQL didn’t recognize anything I tried to open, so this time around i’m just trying to take preventable measures.
I see, I was thinking of the batch script I posted in my replies earlier though, has that worked as root?
ok that seemed to have worked, I updated the file name to the one you originally posted and that seems to work without a problem. Any reason why I can’t just use this bat file instead of the original one you sent? I know the quick and lock tables parameters are there, are they that important to use?
Hey, I have just ran all the steps here on my local build running xampp this time instead of MySQL, everything goes well if you have assigned the correct permissions to the new user.
Pretty much the important part is
GRANT PROCESS ON *.* TO `newuser`@`localhost`;
GRANT SELECT ON `yourdatabase`.* TO `newuser`@`localhost`;
Where you change newuser and yourdatabase to actual names you’re going to use.
Any reason why I can’t just use this bat file instead of the original one you sent? I know the quick and lock tables parameters are there, are they that important to use?
Depends under which user you ran the dump, if you ran it as root then it would have succeeded with or without additional parameters, why the –single-transaction parameter is important is because if you don’t assign the “LOCK” permission, you’re gong to get an error.
You can use –quick if you’re exporting large databases, when it comes to active servers their database can be huge. This method will save information row by row instead of retrieving the whole content of the table.
–lock-tables is disabled when saving on a running server to avoid errors and ESX crapping its pants when it encounters a locked table. You can remove this parameter if you’re running a small server without a lot of SQL requests but I don’t recommend it.
By default, single-transaction will export only the snapshot of the InnoDB memory, meaning all additional changes will not be included in the dump.
Appreciate the details! As of right now it seems like it works for the time being. I’ll try re-adding a user with your instructions again and test it and get back to you with an update
No problem, I’m glad we got it working.
If you run into any more issues feel free to post them here and we’ll try to get it sorted.