Lost my user database in mysql

[nimzov]

I log as root into mysql server, but I can't see anymore my "user" database.

Is there a way to recreate it ?

Thanks.

nimzo

 

[leonAzul]

If the database is truly hosed, then you will have to restore from the backups you have been keeping.

Check for the existence of the files; it might just be that you need to re-link the database to the server. How it became "detached" is something to look into .

 

[nimzov]

I have no backup as this is a new installation.

I have redone make-install but the user database
still will not show up.

nimzo

 

[deep]

I have redone make-install but the user database
still will not show up.

I assume you're referring to the "user" table in the "mysql" database? How are you trying to access it? Can you cut + paste your SQL?

 

[nimzov]

I assume you're referring to the "user" table in the "mysql" database? How are you trying to access it? Can you cut + paste your SQL?

Sorry. Correct I should have said the user table from the mysql database.

I connect to mysql as root then type "show databases" but the mysql database does not show.

nimzo

 

[leonAzul]

Try

databaseName < "/path/to/myDB.sql";

to load the database file into the database name.

 

[nimzov]

Thanks.

Server version: 5.0.85 FreeBSD port: mysql-server-5.0.85

I have searched for user.sql or mysql.sql and there is no such files.

I have reinstalled mysql and there is still no mysql database.

show databases. Shows this:

+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+

And test is empty.


nimzo

 

[nimzov]

I have found some files in var/db/mysql

Some files are named:

user.MYB
user.MYI
user.ftm
etc.

Can I reconstruct mysql database from those files ?

I hope so, but I don't know how to proceed.

nimzo

 

[leonAzul]

That is correct. A database needs to be created and populated with tables and records before you can see them. Start with

[URL="http://www.webdevelopersnotes.com/tutorials/sql/mysql_primer_creating_a_database.php3"]create database [I]myDatabaseName[/I];[/URL]

then activate it using

select database [I]myDatabaseName[/I];

so you can use

[URL="http://www.webdevelopersnotes.com/tutorials/sql/mysql_training_course_creating_tables.php3"]create table [I]tableName[/I];[/URL]

. You can then begin loading data into the database.

 

[leonAzul]

I have found some files in var/db/mysql

Some files are named:

user.MYB
user.MYI
user.ftm
etc.

Can I reconstruct mysql database from those files ?

I hope so, but I don't know how to proceed.

nimzo

Sorry, you posted while I was responding

Copy the existing files to a backup location.

Use "create database dbName;" using the same name as the old database, and note where the new files are created--most likely in the same directory from which the old files where removed. If the database data is recognized from the old files, then you are done. If not, then copy the old files back into that directory, restart the server and activate the database. You should be good to go.

And this time, please dump a backup

 

[Terry]

Are you talking about the MySQL user database, that holds the credentials of those allowed to access the db? You should be able to see that by doing

use mysql;
select * from user;

 

[nimzov]

Hooray!

After typing

/usr/local/etc/rc.d/mysql-server stop

and

/usr/local/etc/rc.d/mysql-server start

I got all my databases back.

I guess my mistake was forgetting to stop and restart the server.

Sorry and thanks for the help.

nimzo

 

[leonAzul]

Did you back it up yet?

 

[nimzov]

Yes but the data was not that important. Most if the project data I can regenerate it programmatically. And
I am the only user of the database.

I don't understand why reinstalling would not solved the problem while stop-restart would.

Thanks.

nimzo