Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My provider has supplied a Ubuntu 12.04 LTS LAMP installation with a bunch of software preinstalled. The system is supposed to be mantained using Parallels Plesk (which I unfortunately didn't know and realize and it was not what I wanted).

When trying to install a mysql database I ran into the issue that I couldn't login as root. Googling for a solution I found: https://help.ubuntu.com/community/MysqlPasswordReset

which states:

USE THIS AS A LAST RESORT METHOD, YOU WILL LOSE ALL YOUR MYSQL DATA

sudo apt-get --purge remove mysql-server mysql-common mysql-client

sudo apt-get install mysql-server mysql-common mysql-client

In the next step be sure to chance the your-new-password with the password you want!

mysqladmin -u root password your-new-password
sudo /etc/init.d/mysql restart

mysql -u root -p

and I used this method but it didn't fix the problem:

wf@tol:~$ sudo mysqladmin -u root password ...
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

so i tried this approach:

sudo service mysql stop
mysql stop/waiting
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
140516 11:15:14 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is  
deprecated and will be removed in a future release. Please use the full name instead.

where you are supposed to be able to update the root user's password. Only now did i find out that there is no root user:

mysql> select host,user,password from user;
+-----------+------------------+-------------------------------------------+
: host      : user             : password                                  :
+-----------+------------------+-------------------------------------------+
: localhost : pma_ge6fIb1m66WN : *A91A0B3F55E08D656C1F02B49C2E3B9A952***** :
: localhost : horde            : *10BAAE10EA4CB8E80F3DFD5D0F8FE157C82***** :
: localhost : pp_sb_db         : *CFC07C49312DFC07CF7C11F8C1EEAD8CB1B***** :
: localhost : debian-sys-maint : *0542B4030EE6D3BA94A760B9B99D6B1A42E***** :
: localhost : admin            : *B1121453FC3ECD98D6959602B59D8A0257B***** :
: localhost : apsc             : *5432E7B03E9C9C22FCFF79CD137E372B289***** :
: localhost : pma_uSHcuKHoQEMx : *F0015847972CEE46D3CC86CEDCDF5F17647***** :
+-----------+------------------+-------------------------------------------

So i thought how about recreating it as outlined in: http://www.helpfromfriend.com/database/mysql/how-to-recreate-root-account-in-mysql/

 insert into user(host,user,password) values ('localhost','root',password('********'));
 Query OK, 1 row affected, 3 warnings (0.00 sec)


update user set 
  Select_priv='Y',Insert_priv='Y',Update_priv='Y',
  Delete_priv='Y',Create_priv='Y',Drop_priv='Y',
  Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',
  File_priv='Y',Grant_priv='Y',References_priv='Y',
  Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',
  Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',
  Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',
  Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',
  Alter_routine_priv='Y',Create_user_priv='Y' 
where user='root';

now I was up for a surprise to find out that this question is not a duplicate of:

http://stackoverflow.com/questions/21714869/error-1044-42000-access-denied-for-root-with-all-privileges/21744960?noredirect=1#comment36603426_21744960

all the conditions in the accepted answer of that question above are met but you can't access root.

What would be a solution to get the root user working in this situation in a non-invasive way?

I ended up

  1. finding out that there was still some data in /var/lib/mysql and some configuration in /etc that blocked proper installation of mysql - i moved those directories and files and reinstalled
  2. this ended in a system that doesn't work properly any more e.g. you can't send e-mail any more since the system itself needs the mysql database and it's users. I reinstalled the whole linux box from an image without plesk.

which is obviously a big bummer after having spent a few hours analyzing why the root user was not available in the first place.

share|improve this question
    
Why do you need a user named root? Plesk more or less just renames it to admin. Why not use the admin user instead? –  faker May 22 at 14:40
    
plesk seems to try to fix something which is not broken. I have a gazillion tools and scripts that work with mysql and the "standard" setup. Plesk even breaks ubuntu's standard installation tools. No thank you. –  Wolfgang Fahl May 22 at 14:56
    
Plesk is meant to be the only system you'll use to manage your server. Good for you that you noticed early that it's not the right tool for you! I too dislike it a lot. That being said, it's now hard to say what may have happened on a system which now does not exist anymore... –  faker May 22 at 15:04

1 Answer 1

When you install MySQL, you're supposed to use mysql_secure_installation to set the root pass.

Reinstall and run the above.

share|improve this answer
    
this was one of the things i tried. Didn't work since there were plesk leftover's at the time i tried and I didn't realize it. –  Wolfgang Fahl May 22 at 14:57
    
Even after reinstalling? Can you show us what is in your mysql config? (generally found at /etc/my.cnf) –  Vasili Syrakis May 22 at 21:36

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.