When I run mysql_secure_installation for the first time on OpenSUSE 13.1 it asks me for the root password (or leave blank if none) and it comes up with an error “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysql/mysql.sock’ (2 “No such file or directory”)”
Does this mean there’s a root password set, and I don’t know what it is, or is it something else? How do I resolve this error, and how do I reset the mySQL root password?
No. This means that mysql cannot be reached, probably because it isn’t running at all.
How do I resolve this error, and how do I reset the mySQL root password?
Run “sudo systemctl start mysql” to start the mysql server, and then run mysql_secure_installation again.
To start mysql automatically on boot, run “sudo systemctl enable mysql”.
You can do both in YaST->System->Services Manager as well.
Btw, by default no root password is set for mysql. That’s why you should run mysql_secure_installation (although it’s not strictly necessary).
But by default also only connections from localhost are accepted.
Thanks, that worked. Just out of interest how do I reset the mysql root password in case I forget it?
Don’t forget it. Resetting passwords is not easy as well it should be. The whole idea of a lock is to keep people out lose the key and you gotta call the locksmith. A password is just a key. Don’t lose it.
Actually it is quite easy provided you can login as root to the machine where the mysql server is running:
https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html#resetting-permissions-genericAlthough, on openSUSE you cannot just run mysqld as root as suggested there, but it should work like this:
sudo mysqld_safe --user=mysql --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock --datadir=/var/lib/mysql --skip-grant-tables
The recommended way of changing a mysql/mariadb user password (including root’s password) is
SET PASSWORD = PASSWORD(‘password’);
when logged in as that user because this ensures that the password is encrypted immediately.
However, you should delete the entry in the hidden file .mysql_history immediately after you have made this change.