Resetting MariaDB Root Password

[indieben]

Hi, I'm having problems with doing this as everything I google seems very out of date, for example, the commands don't seem to work. Having logged in as root in SSH

mysql -u da_admin -p and using my Direct Admin login password returns:

ERROR 1045 (28000): Access denied for user 'da_admin'@'localhost' (using password: YES)

This all started as uploading an sql dump is throwing a 500 error. I somehow broke something (although I can't remember what) as a result of a PHP My Admin error that said something about maximum vars and warning me that my sql dump may effectively be corrupted.

Ubuntu 22.04 and latest DirectAdmin release.

I've got into rather a mess!

Edit: I found the following instructions: https://docs.directadmin.com/other-hosting-services/mariadb-mysql/troubleshooting.html

But when I got to
root@server:~# mysql mysql -e "UPDATE mysql.user SET authentication_string=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"
--------------
UPDATE mysql.user SET authentication_string=PASSWORD('rootpass') WHERE user='root'
--------------

ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable
root@server:~#

Thanks and Kind Regards.

 

[Richard G]

mysql -u da_admin -p and using my Direct Admin login password returns:

You should not use your Directadmin login password for mysql. Mysql password and Directadmin login password are 2 different passwords.

Your mysql root password can be found in the /usr/local/directadmin/conf/my.cnf and mysql.conf and setup.txt files. The setup.txt contains both your admin DA password and the mysql password.

 

[indieben]

Thanks for this Richard - simple when you know how! Randomly it suggests the username attached to it is da_admin but the same password worked with the username as "root".

Any idea why an SQL dump import in PHP My Admin is throwing a 500 please and also how to get around the max vars and the entries will be ignored complaint in PHP My Admin please? Well, it's pointless asking whether you will know as, to be fair, you are a blooming expert! A pointer on this would be gratefully appreciated though.

Thanks

 

[Richard G]

but the same password worked with the username as "root".

That's correct. Some time ago those passwords were different and also to be found in the setup.txt but since some time (don't know exactly when) they are both the same, so da_admin is the same as root for mysql.

How big is your database and have you enabled mod_security? If you enabled mod_security try disabling it and see if it works then.

As for the max_input_vars for phpmyadmin, you can change that in the php.ini of the php version you're using.
Try first one to, if your main (first) php version is php 8.1 for example then use:
/usr/local/php81/lib/php.ini
change the max vars in there and then restart php-fpm81 and you can do the same for other php versions running.

to be fair, you are a blooming expert

Thank you for the compliment but a lot of post does not make somebody an expert and for databases for sure I'm not and for the rest I can be helpful with a lot of things, but still am not a linux expert.