When you setup a Dedicated server with a host like 1and1 they setup an admin account for root access on MySQL but the password is a mystery and login from outside the localhost isn’t possible anyway. After tons of frustration and searching I finally found the fix for this. The reason this is so important is mainly for backups, there’s no other way to backup all of you databases quickly other than using an account which can access all of your databases. When running Plesk in the default Reseller mode access to databases is limited to each domain and one account cannot access another domain’s database. This is obviously like this for security and its great but we really need access to all DB’s as root. Another reason is then use if programs like MySQL workbench which requires a master account that can access all databases if you want to administer all databases.

On with the fix, log into Plesk and navigate to “Tools & Settings \ Database Servers” now click on your database, select the “Database” tab and you should see an option for Webadmin. Once phpMyAdmin has loaded you can select the “mysql” database on the left, this database holds all of the configuration settings for MySQL.

Once in the MySQL database you should see some tables on the left, the table we want is user. Under the “Browse” tab first look and see if the root account exists, if it does we can simply modify it. If it does not we will need to create it. You should see an admin account or something similar where all options to the right of the account name have a capital Y instead of an N. We will be copying setting from this account, sort of anyway. At the top select the “Insert” tab, For Host type “localhost” without quotes, for User type “root” without quotes, For password type anything, Change all options to Y, leave all settings at the bottom and click “Go” at the bottom of the page. Ignore any errors for now we will fix them in the next step.

Open an SSH session to your server, type the following one line at a time.

/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql -uroot mysql
UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;
quit
/etc/init.d/mysqld restart

Now login into your MySQL instance and we can set a new password of your choosing.

mysql -uroot -pabcd mysql

Once logged in type

use mysql;
update user set password=PASSWORD("NEWPASSWORD") where User='root';
flush privileges;
quit

You should now be able to log into MySQL with your new root password

mysql -uroot -pYOURNEWPASSWORD mysql