CODEWITHSUNDEEP
mysqlmariadb
PKHunter
PKHunter

Reputation: 707

MariaDB latest 10.5 version cannot change root password, and it doesn't work without skip-grant-tables

I have tried everything mentioned here: How to change the MySQL root account password on CentOS7?

It does NOT answer the question. None of those suggestions work. (To mods: please don't close this question because it's beyond that other post, and no one is answering comments there.)

  1. With mariadb freshly installed on CentOS 8, using mariadb's own recommended repo, the version installed is: mysql Ver 15.1 Distrib 10.5.8-MariaDB. This somehow creates some root password because running mysql_secure_installation one cannot just use an empty password. Using the grep temporary password suggested in that other question yields nothing. There's nothing in the mariadb install log found at /usr/log/mariadb/mariadb.log.

  2. With skip-grant-tables on, either via env or via the my.cnf file, the password for root seems to work. Flush privileges, quit. Everything seems to be fine. It seems to have worked. But then when skip-grant-tables is disabled (as it should be for security), the password does NOT work again.

    mysql -u root

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    mysql -u root -p

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

I have stopped the server, yum removed mariadb, then yum installed it again. Same issue. Any ideas on what I am missing? Why is this so complex?

Upvotes: 1

Views: 3059

Answers (1)

lauxjpn
lauxjpn

Reputation: 5254

I used the Install MariaDB Server 10.5 on CentOS 8 guide, to successfully install and setup a MariaDB 10.5.8 instance and change its root password without any issues on a vanilla CentOS 8 installation.

I ran the following commands (in addition to the MariaDB repository setup) as root/superuser:

dnf update -y
dnf install -y MariaDB-server
systemctl enable --now mariadb.service
systemctl status mariadb.service
mariadb -u root

Then, in the MariaDB console, I ran the following:

alter user 'root'@'localhost' identified by 'MyRootUserPassword';
alter user 'mysql'@'localhost' identified by 'MyMysqlUserPassword';
flush privileges;
quit;

Afterwards, back on the command line, this time as a regular user, I am able to also login as the database root:

mariadb -u root -pMyRootUserPassword

There is no skip-grant-tables needed at all for a vanilla installation of MariaDB.

If you already played around with it before, but you don't care about the previous installation, make sure to completely uninstall MariaDB first and to remove the old data files and my.cnf file, so that old configuration, permissions and database users are not kept and later reused when reinstalling MariaDB.

The default data directory for MariaDB 10.5 on CentOS 8 is /var/lib/mysql, unless the path has been overwritten in the /etc/my.cnf file.

So after uninstalling MariaDb with your package manager of choice, remove/move/rename the directory to ensure that your new MariaDB installation will get a clean slate.

Upvotes: 2

Related Questions