can't login to mysql as root

Look at the contents of ~root/.my.cnf which is usually where RedHat derivatives store the root pass during installation.

If not, you can just restart the server on the command line with the skip-grants flag passed to reset the password.

I followed this article

https://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7

but still getting this

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MYPASSWORD';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

vi etc/my.cnf


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I went ahead and did this:

 Open & Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
Add skip-grant-tables under [mysqld]

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.user]


I got the above error..

ALTER won't work in this case.

Follow the exact instructions of the article, which uses UPDATE of the row instead.

This should work.

SocialEngine - Ugh... I have several instances running for old projects.

For any SocialEngine related problem, open a separate ticket.

Big Note: Some SocialEngine setups use the database root user/pass for access, because SocialEngine management (from the command line) is massively cumbersome, so some people give up + just use the root user/pass. If this is true in your case, changing the root user/pass means you must also review + possibly change your SocialEngine config file. Usually this file is found at...

DocumentRoot/application/settings/database.php

Select allOpen in new window

As skullnobrains mentioned, MariaDB == MySQL.

I run very old, very hacked up SocialEngine installs using MariaDB, so MariaDB works with 10+ year old SocialEngine code. Unsure about more recent SocialEngine code.

Note: Still best to look at ~root/.my.cnf for current root user/pass, as changing this password may have many odd system side effects.

https://bugs.mysql.com/bug.php?id=79027 - Normally the sequence I use for resetting the root password, which seems the same as the URL you mention.

what skullnobrains is what helped me

the commands are in this article to be specific (i was running as root user so had to use --user=mysql)

https://www.tecmint.com/reset-root-password-in-mysql-8/

mysqld --user=mysql --init-file=/home/user/init-file.txt --console

you can also configure the init file in the config dir and run the server normally. init file merely executes statements as root during startup.

you can even keep the setting permanently. personally, i setup unix_socket auth based on the system user so the system root user can access mysql as root without password, and there is no way to be root from the network.

also note that on all debian based distributions, there is a debian-sys-maint account with root privileges that can easily be found by browsing the config files.

actually I'm still having issue with this... I downgraded php from 7.3 to 7.2, and reset my machine, I guess resetting my machine reintroduced the mysql errors I can't login again as mysql -u root -p even though im fairly certain of the password

"you can even keep the setting permanently. personally, i setup unix_socket auth based on the system user so the system root user can access mysql as root without password, and there is no way to be root from the network."

how do I do this?

can you link me to the KBA?

i have no idea what a KBA is but you would basically simply setup grants.

# install the plugin first
INSTALL PLUGIN unix_socket SONAME 'auth_socket';

# and run something like this on startup
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH unix_socket WITH GRANT OPTION ;
delete from mysql.user where User='root' and plugin <> 'unix_socket' ;
delete from mysql.user where User='debian-sys-maint' ;
FLUSH PRIVILEGES ;

you can setup ALL the required privileges in a startup script. the above will disable network root access but leave other users alone. i have a separate script that runs every 5 minutes and synces all network grants with a fixed list. and all my apps use the unix_socket authentication method. that is suitable for my situation. likely yours is a little different.

PHP version has no effect on MariaDB/MySQL login problems.

Downgrading PHP versions almost always breaks FPM.

To get your recover a problem system after a PHP downgrade, open a new question with a subject... something like...

"Help recover system after PHP-7.3 to PHP-7.2 downgrade"

You can reapply the same process. The downgrade probably triggered some idiotic post install process that reset the rout and debian user to whatever suits the current maintainer.