Jump to content

mmmm... anybody know something bout MySQL


scoopy
 Share

Recommended Posts

While trying to backup all databases and after trying some upgrading --- I am no longer able to connect to my MySQL databases or create any new ones. I have several I was working on, but only one must be salvaged.

 

Accessessing website through browser:

Fatal error: Call to undefined function: message_die() in /var/www/html/bud/db/db.php on line 88

 

Accessing MySQL through Webmin:

DBI connect failed : Access denied for user: '@localhost' to database 'mysql'

 

Accessing through phpMyAdmin: (user:root password:none)

no databases

Your configuration file contains settings (root with no password) that correspond to the default MySQL privileged account. Your MySQL server is running with this default, is open to intrusion, and you really should fix this security hole.

 

 

I also tried via shell: (maybe a syntax problem here, cause I suck at syntax stuff)

mysql> GRANT ALL PRIVILEGES ON db.*

   -> TO root@'localhost';

ERROR 1044: Access denied for user: '@localhost' to database 'db'

mysql> GRANT ALL PRIVILEGES ON db.*

   -> TO scoopy@'localhost';

ERROR 1044: Access denied for user: '@localhost' to database 'db'

mysql>

 

Maybe there is a file somewhere I can do something with... or maybe I could use something like Open Office (in windows side) to recreate my database before continuing.

 

I had already tried creating a new db and was going to import the one I would like to save, but I can't even create a new db. I guess I must restore these privileges.

 

All apps are current RPM's using MDK 9.1

 

Any ideas here are greatly appreciated.

Link to comment
Share on other sites

Guest Gerynar

Ok,

Looks like the first error is php not being able to find the function "message_die()", can't be of much help there as I am not familiar with the particular php-script.

 

The others look like MySQL isn't letting the scripts into the database server due to a bad password (wrong/not set in updated db).

 

I don't think you quite got the syntax right for the command line program. Don't put 'localhost' in quotes.

 

It should look a little more like this:

the root user should be given access to all db's created on your server, so don't just limit to db.*

 

It's also not a good idea to give complete access to non-root users. (scoopy).

 

 

 

mysql> GRANT ALL PRIVILAGES ON *.* TO root@localhost

   -> IDENTIFIED BY '<some_password>' WITH GRANT OPTION;



mysql> GRANT ALL PRIVIAGES ON *.* TO root@"%"

   -> IDENTIFIED BY '<same_password_as_above>' WITH GRANT OPTION;



mysql> GRANT <list of needed privilages> ON db.* TO scoopy@localhost 

   -> IDENTIFIED BY '<some password>';



mysql> GRANT <same list as above> ON db.* to scoopy@"%" 

   ->IDENTIFIED BY '<same Password as above>';

 

The first two set up root as the superuser for all databases on your server. The first one is specific to 'localhost', the second is from any other host. (so you can remotely access the server).

 

The second two set up scoopy the same way with restricted access. Figure out exactly what scoopy needs to be able to do with the database and limit his/her privilages to just those items. The possible list is as follows:

  • ALL PRIVILEGES
    ALTER
    CREATE
    DELETE
    DROP
    FILE
    INDEX
    INSERT
    PROCESS
    REFERENCES
    RELOAD
    SELECT
    SHUTDOWN
    UPDATE
    USAGE

    •  
      see
       
      /usr/share/doc/MySQL-3.23.52/manual_toc.html for a really nice manual on using MySQL. (replace the 3.23.52 with your version of MySQL if different).
       
      After you enter those GRANT's, you need to flush the privilages:
       
flush privilages


 
to have the MySQL server re-read the privilage information.
 
Hope this helps.

Link to comment
Share on other sites

It looks to me like it could be that you used to have a hostname (like mycomputer rather than localhost for example) if that changed you wouldn't have access and you'd get

 

ERROR 1044: Access denied for user: '@localhost' to database 'db'

Link to comment
Share on other sites

Thanks, but no matter what I type in for this GRANT command, I can't get it right. (***= what I type as password)

mysql> GRANT ALL PRIVILAGES ON *.* TO root@localhost

   -> IDENTIFIED BY '***' WITH GRANT OPTION;

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIVILAGES ON *.* TO root@localhost

IDENTIFIED BY '***' WITH

mysql>

NOTE: I am using MySQL-4.0.11a-5mdk.i586.rpm from 9.1 download discs.

 

ALSO: This is going on day 3 of googling and searching for an answer and I am ready to screem. Good news is I think I have found where MySQL hid my backup database (/ with same name as db and disguised as a C source file) but I can't use any db's until I get access to them once again.

Link to comment
Share on other sites

thanks, all this was supposed to work, but it didn't, so yesterday I wiped all mysql stuff, including hidden history files for root and scoopy. Reinstalled mysql and now have it all working again except my restored database.

 

I am open on suggestions on the best way to import stuff from my old database into a new one. I have copies of old db (the backup file thing disquised a C source file, or the MYD/MYI/frm files). We are using database in a php nuke site.

 

thanks again,

Link to comment
Share on other sites

Not sure if this is your problem, but I've had access denied problems accessing MySQL before - in my case from JSP pages.

 

It turned out to be a problem with resolving hostnames, MySQL isn't that great at it - try using an ip address instead of localhosy or any other hostname

Link to comment
Share on other sites

Dam... thought I had it all running smoothly again. Checked my brand new site and brand new database and it was running. Left for a couple hours (without doing a thing) and came back to find the same problems with the new stuff.

 

MySQL loses its connection to the database. Webmin give's me this message:

DBI connect failed : Access denied for user:

 

So you think it may be a "localhost" issue?

Link to comment
Share on other sites

It may be a name resolution issue - that's always the first thing I check if I get any denied issues with MySQL - try using ip addresses if that's applicable. I guess you could probably use 127.0.0.1 - or whatever localhost usually resolves to

Link to comment
Share on other sites

Seems we got it all back now.

 

Most likely the root of this was when I attempted to backup my database. It did seem to end premature and probally corrupted a few of the tables in the process which was causing all the havoc.

 

I found a little program called MySQLcc that was able to fix these tables. I reinstalled a new copy of the nuke site and copied the fixed db files over.

 

Guess I need to back up my backups :wink:

 

thanks,

Gerynar, static, and phunni

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...