1

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
  • You can use sudo dpkg-reconfigure mysql-server It will ask you to enter root password. Once you are done connect to it using mysql -uroot -pyourpassword – Valentin Bajrami Nov 14 '13 at 10:48
  • I tried sudo dpkg-reconfigure mysql-server-5.5, entered the password twice but then it goes back to the CL and just hangs. – Hedge Nov 14 '13 at 11:44
  • You could try to remove/purge and reinstall it over again. "just hangs" isn't sufficient info. – Valentin Bajrami Nov 14 '13 at 11:57
  • Have you just done a sudo mysql -uroot? With my experiences with Ubuntu, a mysql password is set on the distro install and not when you install/upgrade the package. They account for this by placing the root password in an encrypted file. Passing the mysql command without the -p option should let you in. – AWippler Nov 14 '13 at 17:18
  • Yup ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) – Hedge Nov 14 '13 at 17:19
2

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
  • starting the mysql-console with debian-sys-maint worked but when I try create user 'root'@'localhost' IDENTIFIED BY 'password'; it gives me this error ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost' – Hedge Nov 14 '13 at 21:17
  • The esiest way to connect to the database is with sudo mysql --defaults-file=/etc/mysql/debian.cnf. Then you don't need to enter the password. It is read from debian.cnf. And in no way ever cange the contents of debian.cnf without changing the password for debian-sys-maint user too. That will make the package managing of mysql-server stop working. Read /usr/share/doc/mysql-server-5.5/README.Debian.gz for more information. – Anders Jan 2 '15 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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