Mysql with docker: Can't connect to local MySQL server through socket

Question

I cannot use MySQL anymore in my Docker container:

root@mysql-container:/# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

mysqld is running:

root@mysql-spirit-ssl:/etc/mysql/conf.d# /etc/init.d/mysql start
[info] A MySQL Server is already started.

Trying to stop mysqld timed out:

root@mysql-container:/# /etc/init.d/mysql stop
............................................................[info] Attempt to shutdown MySQL Community Server 5.7.17 timed out.

So I tried to start using the mysqladmin way:

root@mysql-container:/# /usr/bin/mysqladmin --port=8889 -u root shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)'

So I checked that MySQL daemon is running:

root@mysql-container:/# ps -eax
  PID TTY      STAT   TIME COMMAND
    1 ?        Ssl    0:01 mysqld

And that socket exists:

root@mysql-container:/# ls -l /var/run/mysqld/mysqld.sock
-rwxrwxrwx. 1 mysql mysql 0 Jan  4 10:12 /var/run/mysqld/mysqld.sock

I already tried to:

restart my Docker container
comment bind address in my.cnf and restart my Docker container
kill mysqld process => does not work, process is still listed by ps -eax
recreate my Docker container
restart Docker
restart the server
delete pid and sock files, and /etc/init.d/mysql start

Result of cat /var/log/mysql/error.log:

2018-02-27T15:27:35.966028Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2018-02-27T15:27:35.966061Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

However I cannot kill that mysqld process, either with pkill mysqld, kill -9 1 or initctl --system stop mysql.

Could this be related to Docker?

RotS · Accepted Answer · 2018-02-28 13:25:19Z

2

Remark: The MySQL daemon could not be killed because it was owned by Docker user systemd+ and it was the entry point of the container. Indeed mysqld was process with PID 1. This means that MySQL daemon could be restarted by simply restarting the Docker container, and that MySQL configuration could be modified in between.

I noticed in MySQL logs tail -f /var/log/mysql/error.log that a data recovery was triggered on daemon start due to an anomaly detected during internal log scan: the database was not closed properly. However the recovery could not repair the data and an intentional crash was performed. As a consequence, the container was restarted and so on. This infinite loop prevented mysqld to start and the socket to be used by the client mysql.

1) This configuration of /etc/mysql/conf.d/my.cnf enabled to skip the recovery:

[mysqld]
innodb_force_recovery=4

and to use mysql client with socket to dump important schemas and/or delete corrupted schemas. Do not forget to remove this line from my.cnf after you're done!

2) Perfoming a mysql upgrade and repair could also have been beneficial:

docker exec -it mysql-container mysql_upgrade -u root -p --force
mysqlcheck -u root -p --auto-repair --check --all-databases

Restarting the Docker container is necessary after this step.

3) Also, deleting MySQL internal logs (that were scanned and triggered the recovery) was necessary:

cd /var/lib/mysql/mysql/
rm ibdata1 ib_logfile0 ib_logfile1

Now I can use MySQL again, from inside and outside the container.

edited Feb 28, 2018 at 13:25

answered Feb 28, 2018 at 13:19

RotS

2,4703 gold badges30 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

slhck Over a year ago

My case was very similar, and deleting the ib* files helped. I had a mysql process running as root on the host though that I probably had to kill as well.

Collectives™ on Stack Overflow

Mysql with docker: Can't connect to local MySQL server through socket

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related