Can't restore MySQL root privileges

July 3, 2015 4.6k views
MySQL LAMP Stack Linux Commands Ubuntu

Hello everyone!

I manage a droplet on DigitalOcean running Ubuntu 12.04 LTS (GNU Linux 3.2.0-24-virtual i686) and need to run some MySQL Events which requires root privileges that I don't have on my user. In first place, I didn't create the droplet and don't know if the "root" account even exists. But even so, I am trying to change the root password.

I tried to follow some steps mentioned in many different places in the community, as here and here and in this tutorial too, but no results. The problem is: in every try, some command are not present on my ubuntu distro.

For example, the command returns:

root@droplet:~# sudo mysqld --skip-grant-tables &
[1] 5097
root@droplet:~# sudo: mysqld: command not found
root@droplet:~# sudo mysqladmin password 123newpass
sudo: mysqladmin: command not found
root@droplet:~# sudo mysqld_safe --skip-grant-tables &
[1] 5948
root@droplet:~# sudo: mysql_safe: command not found

Just to inform, the command "mysql" works fine. And to stop/start my MySQL and Apache I use the following command:

root@droplet:~# /opt/lampp/lampp start

How can I fix it? What should I do to give root privileges?

1 comment
  • I think you should check where mysql is: whereis mysql
    or whereis mysql_safe or locate all mysql: locate mysql

2 Answers

I have had to do this once or twice and I remember it being a lot of frustrating tinkering until if finally worked.

Here is what I have in my notes. This may or may not be helpful to you:

Stop your MySQL service

sudo /etc/init.d/mysql stop

Start your MySQL with privileges

/usr/bin/mysqld_safe --skip-grant-tables

Connect to mysql DB with your root user. password is: mysql

 /usr/bin/mysql -uroot -p 

Update root privileges

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',Event_priv='Y',Trigger_priv='Y' where User='mas_web';

After that, you just need to stop the MySQL service and start again.

Thank you for your support.

Same problem trying these steps.

  • The command sudo /etc/init.d/mysql stop returns command not found. But OK, I can stop mysql using /opt/lampp/lampp stop.
  • The commands /usr/bin/mysqld_safe --skip-grant-tables and /usr/bin/mysql -uroot -p returns No such file or directory.

My big problem is not about what command to execute, is about what the hell they don't work.

Have another answer? Share your knowledge.