Mysql can’t create user / grant privileges on existing ones (access denied)

MySQLubuntu-10.04

After installing Mysql through apt-get on Ubuntu 12.0.4 LTS and messing around with it I'm in the position where I have no account that can do any operation to grant privileges or create a new user.

I can only connect to it via

mysql –defaults-file=/etc/mysql/debian.cnf

select host,user ,select_priv, Create_user_priv from mysql.user;

returns the following table:

+-----------+------------------+-------------+------------------+
| host      | user             | select_priv | Create_user_priv |
+-----------+------------------+-------------+------------------+
| localhost | apsc             | N           | N                |
| localhost | pma_GSn1wBscFLp0 | N           | N                |
| localhost | pp_sb_db         | N           | N                |
| localhost | debian-sys-maint | Y           | Y                |
| localhost | admin            | N           | N                |
| localhost | pma_FoXErUK90AbF | N           | N                |
+-----------+------------------+-------------+------------------+

How can I create a root-user with all privileges? A full reset would be fine by me.

Commands like the following one only return access denied errors

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION

Best Answer

There appears to be no root user in your mysql database. The debian-sys-maint user is the root equivalent. Here is how to add a root user:

mysql -u debian-sys-maint -p

When prompted for a password, enter the one located in /etc/mysql/debian.cnf once inside the database, you should be able to run:

grant all privileges on *.* to 'root'@'localhost' identified by 'yourpassword' with grant option
Related Topic