Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Before I screw up something, when login using $ mysql -u root -p , and show databases,

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| game_data          |
| test               |
+--------------------+

Then I am trying to create some new user and notice something is wrong with the PRIVILEGES.

So I deleted the new users, and I guess I removed the 'root' and 'Admin' accidentally.

Then I try to create 'root' again, but get Access denied error when doing grant all privileges.

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If I login mysql again using $ mysql -u root -p , and show databases,

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

All the other databases are gone.

How do I fix mysql now?

I cannot cannot find the database 'mysql', cannot create database, create user, anything I try to do will get an error

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES).

Should I reinstall mysql using macports? If reinstall, I will lost the database 'game_data', right?

share|improve this question

migrated from stackoverflow.com Jun 3 '13 at 8:32

This question came from our site for professional and enthusiast programmers.

    
Try accesing the server with 'root'@'127.0.0.1' which is diferent from 'root'@'localhost'. Then issue a command to create the user 'root'@'localhost' and grant all privileges to it. –  Gimmy Jun 3 '13 at 8:06
add comment

2 Answers

up vote 8 down vote accepted

Follow steps below

1.Start the mysql server instance or daemon with the --skip-grant-tables option. (security setting)

$ mysqld --skip-grant-tables

2.Then Execute these statements.

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

3.Finally, restart the instance/daemon without the --skip-grant-tables option.

$ /etc/init.d/mysql restart

4.You should be able to connect with your new password.

$ mysql -u root -p

Enter password: my_password

NEW UPDATE

Mysql Unable to lock ibdata1 error fix

sudo mv /usr/local/mysql/data/ibdata1 /usr/local/mysql/data/ibdata1.bak
sudo mv /usr/local/mysql/data/ib_logfile0 /usr/local/mysql/data/ib_logfile0.bak
sudo mv /usr/local/mysql/data/ib_logfile1 /usr/local/mysql/data/ib_logfile1.bak
sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1
sudo cp -a /usr/local/mysql/data/ib_logfile0.bak /usr/local/mysql/data/ib_logfile0
sudo cp -a /usr/local/mysql/data/ib_logfile1.bak /usr/local/mysql/data/ib_logfile1
sudo /etc/init.d/mysql restart
share|improve this answer
    
"$ mysqld --skip-grant-tables" and " InnoDB: Unable to lock ./ibdata1, error: 35 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. " ? –  Vogelsire Jun 3 '13 at 7:42
    
see my update above –  Yogus Jun 3 '13 at 7:45
add comment

Try it:

mysql --no-defaults --force --user=root --host=localhost --database=mysql 
UPDATE user SET Password=PASSWORD('NEWPASSWORD') where USER='root';
FLUSH PRIVILEGES;
share|improve this answer
2  
Can you describe what the --no-defaults --force switches do? Otherwise, it's a VLQ duplicate of the above, accepted answer. –  Canadian Luke Feb 21 at 17:17
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.