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'@'' 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

2 Answers 2

up vote 25 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';

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


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

Try it:

mysql --no-defaults --force --user=root --host=localhost --database=mysql 
UPDATE user SET Password=PASSWORD('NEWPASSWORD') where USER='root';
share|improve this answer
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 '14 at 17:17

Your Answer


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.