Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    5

    Unhappy Unanswered: opps ... Deleted perms for root, host localhost

    Opps...
    using Webmin to remove old MySQL Users I accidentally Deleted perms for user root, host localhost for Webmin module MySQL Database Server (Generated by script mysql/save_user.cgi)

    How do I restore root, host and localhost user permissions?

    Error = Access denied for user: '@localhost' to database 'mysql'

    I ran ...

    /usr/bin/mysqladmin -u root password my-password
    /usr/bin/mysqladmin -u root -h server_name password my-password

    Error = /usr/bin/mysqladmin: unable to change password; error: 'Access denied for user: '@localhost' to database 'mysql''

    I update MySQL to v4.0.17, hoping this would reset perms, but no luck ... :-(

    Thank you,
    Eric

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: opps ... Deleted perms for root, host localhost

    If the root user was the only one with access to the mysql db, then you need to start the server with the option --skip-grant-tables. Then connect with the mysql client and grant new permissions the the root user. Restart the mysqld server normally and connect to the db using the root user.

    http://www.mysql.com/doc/en/Resetting_permissions.html

  3. #3
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    5

    Red face

    Thanks for your help.

    Unfortunately I've completely re-installed MySQL v4.0.17 (RedHat 9.0), and still have "Access denied for user: '@localhost' to database 'mysql'".

    It gets worse, now I'm missing mysqld.pid

    Executing /etc/rc.d/init.d/mysql start ..
    No mysqld pid file found. Looked for /var/lib/mysql/dslxxx-xxx-xxx.pid.

    Do you know how I can restore the mysqld.pid file, and give access for user: '@localhost' to database 'mysql'?

    Thanks again,
    Eric

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    The pid file gets written when mysqld starts so that the process can be killed. The user that mysqld is run as probably doesn't have write access to the data directory. It should be run as mysql. chown the data directory to mysql:mysql and try to start the server with user=mysql in the configuration file under the section [mysqld]. If you don't have a configuration file, look at the examples in the support-files directory of the mysql installation. I am not exactly sure what that is with RedHat, assuming you used an RPM file. You could check out /usr/local/mysql. The example files are my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf. Copy the appropriate one to /etc and rename it my.cnf. Use whatever one sounds appropriate for your use. There are comments in each one that give hints as to such.
    When you get it running, use the link from my previous post to restore the permissions.

  5. #5
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    5
    Thanks for your help, I'm getting close but still no success.

    The data directory /var/lib/mysql is owned by mysql/mysql.

    First I start mysql:
    [root@xxx-xxx-xxx root]# /usr/bin/mysqld_safe --skip-grant-tables
    Starting mysqld daemon with databases from /var/lib/mysql

    then the curser (puTTY) goes to the left margin and locks up (weird),
    however MySQL Server has started, so I open another session and try to set a root password:

    [root@dslxxx-xxx-xxx root]# mysqladmin -u root password newpassword
    mysqladmin: unable to change password; error: 'Access denied for user: '@localhost' to database 'mysql''

    This is what's in etc/my.cnf:
    ==================
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    ==================

    :-(

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    There is something going on here. It should not give Access Denied errors when the server is started with --skip-grant-tables. Try starting mysqld straight up with this:
    /usr/bin/mysqld --skip-grant-tables &
    rather than mysqld_safe. It will return you to the command prompt and you can try to log in using mysql.

  7. #7
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    5
    That file does not exist --> /usr/bin/mysqld

    [root@xxx-xxx-xxx root]# locate mysqld
    /var/log/mysqld.log
    /var/log/mysqld.log.1
    /var/log/mysqld.log.2
    /var/log/mysqld.log.3
    /var/log/mysqld.log.4
    /var/run/mysqld
    /var/run/mysqld/mysqld.pid
    /etc/rc.d/init.d/mysqld.lock
    /usr/bin/mysqld_multi
    /usr/bin/mysqld_safe
    /usr/bin/safe_mysqld
    /usr/bin/mysqldump
    /usr/bin/mysqldumpslow
    /usr/lib/mysql/mysqld.sym
    /usr/lib/mysql/libmysqld.a
    /usr/sbin/mysqld
    /usr/share/man/man1/mysqld_multi.1.gz
    /usr/share/man/man1/mysqld.1.gz
    /usr/share/man/man1/mysqld_safe.1.gz
    /usr/share/man/man1/mysqldump.1.gz
    /usr/share/mysql-test/r/mysqldump.result
    /usr/share/mysql-test/t/mysqldump.test
    /usr/include/mysql/mysqld_error.h

    ----------------------------------------------------------------------------------

    The only way to stop mysql is re-boot the server,
    Executing /etc/rc.d/init.d/mysql stop ..

    No mysqld pid file found. Looked for /var/lib/mysql/xxx-xxx-xxxx-my.isp.name.pid.

    ---------------------------------------------------------------------------------

    I will try anything at this point ... other ideas?

    Thanks again for your replys,
    Eric

  8. #8
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    I was looking back at your past posts and you said that after you completely reinstalled, you got access denied errors. That should not be. There must be something with the installation. Try uninstalling and reinstalling. If you used an RPM last time, try a tar'd and gzip'd binary installation. If you used that last time, try an RPM this time. Make sure that mysql_install_db gets run. There has to be something going on at that point.

  9. #9
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    5

    Smile

    Success! This time I completely removed all MySQL files before doing a clean install. I can't thank you enough for your insight/assistance. I will be very careful w/ root/host/localhost perms in the future.

    Thanks again, hope you have a great weekend!

    With kind regards,
    Eric

  10. #10
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    You too...

  11. #11
    Join Date
    Dec 2003
    Posts
    10
    There's one more way you could have reset your password. When you start the server using
    prompt>mysqld_safe --skip-grant-tables &
    you can enter mysql without any problem, then
    mysql>use mysql
    mysql>update user set Password = PASSWORD(<new-password>) where User = 'root';
    This would reset your root password.
    Then restart the mysql server without --skip-grant-tables option.
    Just a add on.

    Thanks,
    Jyothi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •