Fix MySQL when users can't create databases

My thanks to Onno, who wrote this; all I did was successfully use it.

How to fix mysql when a MySQL update causes users to no longer be able to create databases in DirectAdmin:

Log into DirectAdmin as Admin
Go to the Service Monitor
Stop mysqld

Log into the server using SSH as root
# mysqld_safe --skip-grant-tables &

If you don't get back to the prompt hit enter (mysql will still be running)

# mysql_fix_privilege_tables --user=root --verbose
# killall -9 mysqld_safe; killall -9 mysqld
# mysqld_safe --skip-grant-tables &

If you don't get back to the prompt hit enter (mysql will still be running)

# mysql -uroot

mysql> use mysql
mysql> UPDATE user SET password=PASSWORD('newrootpassword') WHERE user='root';
mysql> GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost IDENTIFIED BY 'newda_adminpassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> quit

# killall -9 mysqld_safe; killall -9 mysqld
# service mysqld start

Now try to log into mysql with the new passwords

# mysql -uroot -p
Enter password: <enter newrootpassword here>
mysql> quit

# mysql -da_admin -p
Enter password: <enter newda_adminpassword here>
mysql> quit

# vi /usr/local/directadmin/conf/mysql.conf
Find the line passwd=<oldda_adminpassword>
and change to passwd=<newda_adminpassword>

Log into DirectAdmin as Admin
Go to the Service Monitor
Start mysqld

Note that you can use the old passwords as your new passwords if you wish. Note also that you can replace vi with any editor of your choice.

Jeff

A similar version can also be found here:
http://help.directadmin.com/item.php?id=45

Yes but the version you find in the link provided by jmstacey did not work in the case we were working on as it was an upgrade of mySQL and not an incorrect da_admin password that caused the problem.

Take special note of this line:

# mysql_fix_privilege_tables --user=root --verbose

This fixes column lengths in several of the mySQL tables fixing the actual problem, the rest is just to get the passwords reset so you can use your system again and that is exactly what is described in the link provided by jmstacey.

Next time before you start pointing around to try and show us all how smart you are (we all know that you try to help people on this forum, just as we do) read carefully and ask yourself why 2 other smart people have not pointed you to the same knowledgebase article, thanks anyway for pointing to the article. Anybody who have lost thier da_admin password follow the instructions that jmstacey has pointed you to, if you have upgraded mySQL then use the instruction listed here.

Regards,
Onno Vrijburg

Most users do not use the search function on these forums, and if they do it's a miracle. So if by chance one comes here wondering why DirectAdmin can't manage MySQL because of a bad da_admin password there is no practical reason to be running that section of code.
That is why I posted the link.

I'll leave it at that.

Edit: I could have made the purpose of that clearer on my original post.

Thanks for your insight, let's try to help other people in stead of starting an irrelevant discussion on every topic, besides people use the forum to find answers.

Regards,
Onno Vrijburg