Can't connect to local MySQL server and lost root password

Question

I can't connect to a MySQL server, so I stopped MySQL with:

sudo /etc/init.d/mysql stop
# Stopping mysql (via systemctl): mysql.service.

Then, login with root and start MySQL again:

sudo -s
mysqld_safe --skip-grant-tables &
# .... mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
# 2021-05-29T03:01:11.967630Z mysqld_safe mysqld from pid file /var/lib/mysql...... ended

After that I executed:

mysqld_safe --skip-grant-tables
# Logging to '/var/lib/mysql/......err'.
# 2021-05-29T03:03:40.507375Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
# 2021-05-29T03:03:40.834556Z mysqld_safe mysqld from pid file /var/lib/mysql/...... ended
# [1]+  Done                    mysqld_safe --skip-grant-tables

At last I executed:

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
mysql -u root
# ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I also forgot my root password.

Answer 1

H, welcome.

You dont' need to call mysqld_safe two times, only one time is necessary. The parameter --skip-grant-tables makes any local connection to MySQL happens without password.

The message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Means that your server isn't listening on default unix socket located on /var/run/mysqld/mysqld.sock.

Kill every mysqld instance with:

ps -ef | grep mysql
# copy every id and...
kill <id>

Look for any clue in MySQL logs, probrably in /var/log/mysql/error.log and then try to start with mysqld_start --skip-grant-tables.