Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got mysql running on my home system. I was playing with access (trying to allow access from something other than localhost) and being a complete db noob/loser if locked my root user out.

The only way i can get in at the moment is if a start a mysqld_safe process with the --skip-grant-tables & option (discovered after googling).

I've tried UPDATE User set Host='localhost' where user='root'; and that doesn't seem to work.

Is there a way to 'start fresh'? I'd still like to keep the data so don't really want to do a full reinstall.

I'm not worried about security since its just my local box (i.e quick and dirty fix will do). Just looking for a 'reset' option or something. I'm a sql hacker, so not afraid of command line, just don't have a clue what I'm doing. help! :-(

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

During --skip-grant-tables, you cannot use the GRANT or REVOKE statements, so you can't use the built in functions to create/remove users.

You essentially want to create/edit a row in the mysql.user table. To do this yourself:

  • Use mysqldump on another machine to grab root user's row from mysql.user. Then, run the resulting INSERT query command on your "mysql" database. This will effectively copy the global permissions for that user, including their password. Remember to run FLUSH PRIVILEGES; after inserting the row.
  • Alternatively, I've picked out a root style access line for you. For a MySQL 5 server, the following queries will create a full access user with username "toor", password "mypassword":

Pastebin link (edit: query was breaking the SF interface)

The user created has access from localhost only. Good Luck!

share|improve this answer
    
Thanks for that info, I'll give it a go and let you know how I get on :-D –  David Archer Jun 29 '09 at 16:32
add comment

You can use the REVOKE command to remove permissions, but you may want to check the user, db, tables_priv and columns_priv tables in the mysql database after you've run it to make sure that it has done what you expected it to do. If not, you can delete all the rows in those tables that match what you're looking for. Just make sure you always have a row in user that has:

             Host: localhost
             User: root
         password:

You may have a password in there. Without a password, you can log in to the database as root from the local machine. Obviously this isn't ideal if there are other people who can log into the server.

One thing you should remember to do is when ever you modify the permissions, either using GRANT/REVOKE or modifying the tables yourself, is to run

mysql> FLUSH PRIVILEGES;

to make mysql take notice of the changes.

share|improve this answer
add comment

The fastest way I've ever seen to resolve the lost password issue is with the init-file config option:

  1. To your /etc/my.cnf (in Debian: /etc/mysql/my.cnf) add:
    [mysqld] #this should already be here, add the following line after it
    init-file = /tmp/init.sql
  2. Put a grant statement in that file:
    cat <<EOF>/tmp/init.sql
    grant all privileges on *.* to 'temp'@'localhost' identified by 'temp' with grant option;
    EOF
  3. Restart mysql:
    sudo /etc/init.d/mysql restart
  4. Remove the line created in step 1 and the file created in step 2.

Now you can log in and make corrections with proper GRANT, REVOKE, and SHOW GRANTS FOR statements via: mysql -utemp -ptemp

This is an improvement to the suggestion that MySQL themselves give here.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.