Rebuild SQL data files

[Red Squirrel]

I had a HDD failure so ordered a new one and waiting for it to come in. My mySQL databases were on there (data files) so I'm just wondering how to go about rebuilding them once I install the new drive. I have a .sql backup dump of the entire DBs, but not sure how it will work to recreate the data files, since those also contain the root password and such. I probably should have backed up those as well right?

Using mysql 5.0.18 on FC5

 

[Nothinman]

Any accounts will have to be resetup if you didn't save the 'mysql' database, but otherwise it should be fine as long as the mysqldump files are complete.

 

[Red Squirrel]

yeah everything is saved including mysql, though I don't think I'll be able to just do mysql -uroot -ppassword < file.sql as root/password will be in the file.sql but not registered in mysql. (the actual data files wont even exist period). Is there a command to rebuild the basic schema?

 

[LuckyTaxi]

you use the root/password to new db setup.

mysql -uroot -ppassword < file.sql

make sure you include the following line at the top of the sql file.

'use <db name>'

or just run

mysql -uroot -ppassword dbname < file.sql

 

[Nothinman]

yeah everything is saved including mysql, though I don't think I'll be able to just do mysql -uroot -ppassword < file.sql as root/password will be in the file.sql but not registered in mysql. (the actual data files wont even exist period). Is there a command to rebuild the basic schema?

The mysql database is where the passwords are registered, if you restore that your old accounts will be restored.

 

[Red Squirrel]

Yes, but how do I authenticate in order to do that restore, when there is 0 DB data (mysql won't start). I would imagine I need to run some kind of command to at least build a basic database structure with a default or blank root password, then do the restore.

 

[Crusty]

Have you reinstalled mysql since you lost your data? I'm not sure how you would force it to reset to the default install in FC5, but in Ubuntu you can force a package to clean up after itself when you remove it instead of just leaving the old files on the drive. Once it's removed you would then just reinstall it to get a blank schema back in mysql, including a new root account.

 

[Nothinman]

Yes, but how do I authenticate in order to do that restore, when there is 0 DB data (mysql won't start)

Then you have other issues because by default MySQL comes with the mysql database and a blank root password. Just reinstall the package and it'll give you back the default databases.

 

[Red Squirrel]

Yes the default mysql comes with that, but if the drive that its on fails, then its gone. I'll try reinstalling. I was hoping to avoid that route. I never really bothered backing up the data files as I just did a full sql dump instead.

 

[Red Squirrel]

Actually did not have to do anything at all, I just tried to start mysql for kicks, and it actually detects that theres no data files so it auto generates the default data. Just had to set the permissions to the mysql user as it created it under root. Then I fired it up, now my backup dump is restoring. So far no errors. *crosses fingers*