Sign up ×
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 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

3 Answers 3

up vote 47 down vote accepted

Follow the steps below.

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

    $ mysqld --skip-grant-tables
  2. 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

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

$ mysql -u root -p

Enter password: my_password

Fix for MySQL “Unable to lock ibdata1” error

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
I found that stopping mysql before issuing the 1st command stops the lock error from happening - service mysql stop – Frank Tzanabetis May 28 at 3:09

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
@CanadianLuke no defaults skips any configuration files present that it'd otherwise include, force continues operation even if any of the issued SQL commands fails. When in doubt, check the manual – deucalion May 26 at 11:39
It needs to be edited in to the answer – Canadian Luke May 26 at 14:31

1st login to mysql with using below command mysql -uroot Then use below command to set password for root user mysql> set password for root@localhost=password('yourpassword');

share|improve this answer
Welcome to Super User! This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute. – DavidPostill Oct 13 at 14:18

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.