Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. sock' (2)

[pascalandy]

Hi,

I'm getting this error:

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

Details:

[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ # Find the ID for galera_proxy
[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ ctn_NAME=galera_proxy && \
> ctnID=$(docker ps -q --filter label=com.docker.swarm.service.name=$ctn_NAME)
[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ echo "$ctnID"
d2eeee1f793d
[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ echo && echo "galera_proxy ID is: $ctnID"

galera_proxy ID is: d2eeee1f793d
[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ echo && echo

[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster
$ docker exec -it $ctnID mysql -uproxyuser -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.
sock' (2)
[node1] (local) root@10.0.2.3 ~/docker-stack-this/PXC-Cluster

You can quickly recreate my setup using play-with-docker. See the steps I have done here: https://github.com/pascalandy/docker-stack-this/tree/master/PXC-Cluster

Thanks!

[xinity]

hi pascal,

it shouldn't work like that, look at the mysql db of proxysql, connection can't be made from the container itself. you should do something like : mysql -H${proxysql_container} -uproxysqluser -p .....

let me know :)

[pascalandy]

Hello Rachid,

I still don't get it I guess :-/

I tried everything but I don't understand the logic of not using docker exec -it. how the command mysql can work as it's not install on the node1? What should I type in exactly with this stack ?

$ mysql -H110.20.1.6 -uproxysqluser -p
bash: mysql: command not found
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster
$ mysql -H10.20.1.6 -uproxysqluser -p
bash: mysql: command not found
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster
$ ^C
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster
$ mysql -H2c8ab3f0a110 -uproxysqluser -p
bash: mysql: command not found
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster
$ ^C
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster
$ mysql galera_proxy.1.ked1ae73mef25rkvjyp3ydeza -uproxysqluser -p
bash: mysql: command not found

$ docker ps

CONTAINER ID        IMAGE                        COMMAND             CREATED             STATUS              PORTS                NAMES
2c8ab3f0a110        perconalab/proxysql:latest   "/entrypoint.sh "   5 minutes ago       Up 5 minutes        3306/tcp, 6032/tcp   galera_proxy.1.ked1ae73mef25rkvjyp3ydeza
f7b7780f50ab        quay.io/coreos/etcd:latest   "etcd"              6 minutes ago       Up 6 minutes                             galera_etcd.1.ppcwfvcwxuwhdkilhqnjtj9d3
[node1] (local) root@10.0.106.3 ~/docker-stack-this/PXC-Cluster

$ docker service ls

ID                  NAME                            MODE                REPLICAS            IMAGE                                PORTS
gy60ha3yubmd        galera_proxy                    replicated          1/1                 perconalab/proxysql:latest           *:3306->3306/tcp,*:6032->6032/tcp
lyrtjsn7z4h1        galera_etcd                     replicated          1/1                 quay.io/coreos/etcd:latest
r1ij0hkwzacx        galera_percona-xtradb-cluster   global              3/3                 percona/percona-xtradb-cluster:5.7

At the moment, with my single instance of Percona in prod, I can do this:

docker exec -it $ctnID mysql --user=root --password=$ENV_PASS \
-e "SHOW DATABASES;"

[xinity]

To put it simply PWD doesn't provide anything rather than a Docker engine.

So in your case you will need a container which is connected into the galera overlay network and hosts a mysql client.

In production apps will connect through proxysql using their own mysql driver (pdo mysql for php based apps)

Have fun :-)

[pascalandy]

I get the concept.

In the case of a single instance, I simply docker exec into Percona container and do some mysql admin task. Then I connect the CMS apps to the Percona container.

There is something I don't understand in the flow. How are you suppose to create DB, drop, do mysql dump stuff if not doing docker exec into one of the three Peronna container in the DB cluster?

On step 3, can you clarify where are we suppose to run: mysql -h$(docker-machine ip pxcm1) -uproxyuser -p

[xinity]

How are you suppose to create DB, drop, do mysql dump stuff if not doing docker exec into one of the three Peronna container in the DB cluster?

you should not use docker exec to issue mysql commands if you need to access a pxc node, do it through the proxysql container using a mysql client take a look at how galera cluster works ( https://www.percona.com/software/mysql-database/percona-xtradb-cluster ).

On step 3, can you clarify where are we suppose to run: mysql -h$(docker-machine ip pxcm1) -uproxyuser -p

this should be done from a node outside the cluster architecture, rather a host (for admin purpose) or using mysql application driver (like pdo for php)

You are trying to use pxc like a standalone mysql server, which is not the case, there are concept and architecture that should be taken in account when dealing with pxc clustering architecture.

in the case of my tutorial, every should be issued through the proxysql which is the only instance that knows nodes existing in the cluster and deals for you about load-balancing / resiliency

Have fun :)

[pascalandy]

Got it! I'll try it with phpadmin :)

[pascalandy]

I added phpadmin into the mix. Tried to log in (wiht pass s3cr3TL33tP@ssw0rd) via the GUI and it's not connecting.

version: '3.1'

services:
  proxy:
    image: perconalab/proxysql
    networks:
      - galera
    ports:
      - "3306:3306"
      - "6032:6032"
    env_file: proxysql.env
    deploy:
      mode: replicated
      replicas: 1
      labels: [APP=proxysql]
     # service restart policy
      restart_policy:
        condition: on-failure
        delay: 5s
        max_attempts: 3
        window: 120s
      # service update configuration
      update_config:
        parallelism: 1
        delay: 10s
        failure_action: continue
        monitor: 60s
        max_failure_ratio: 0.3
      # placement constraint - in this case on 'worker' nodes only
      placement:
        constraints: [node.role == manager]

  etcd:
    image: quay.io/coreos/etcd
    command:  etcd
    volumes:
      - /usr/share/ca-certificates/:/etc/ssl/certs
    env_file: etcd.env
    networks:
      - galera
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints: [node.role == manager]

  percona-xtradb-cluster:
    image: percona/percona-xtradb-cluster:5.7
    networks:
      - galera
    env_file: galera.env
    deploy:
      mode: global
      labels: [APP=pxc]
     # service restart policy
      restart_policy:
        condition: on-failure
        delay: 5s
        max_attempts: 3
        window: 120s
      # service update configuration
      update_config:
        parallelism: 1
        delay: 10s
        failure_action: continue
        monitor: 60s
        max_failure_ratio: 0.3
      # placement constraint - in this case on 'worker' nodes only
      placement:
        constraints: [node.role == worker]

  phpmyadmin:
    depends_on:
      - proxy
    image: phpmyadmin/phpmyadmin
    restart: always
    ports:
      - 8080:80
    environment:
      PMA_HOST: proxy
      MYSQL_ROOT_PASSWORD: s3cr3TL33tP@ssw0rd
    networks:
      - galera

networks:
  galera:
    # Use a custom driver
    driver: overlay
    internal: true
    ipam:
      driver: default
      config:
      - subnet: 10.20.1.0/24

$ docker service ls
ID                  NAME                            MODE                REPLICAS            IMAGE                                PORTS
1qotf1ps4z8t        galera_etcd                     replicated          1/1                 quay.io/coreos/etcd:latest
35swyu27ytvg        galera_percona-xtradb-cluster   global              3/3                 percona/percona-xtradb-cluster:5.7
fb14920kxrng        galera_proxy                    replicated          1/1                 perconalab/proxysql:latest           *:3306->3306/tcp,*:6032->6032/tcp
pr4m4yr0uilr        galera_phpmyadmin               replicated          1/1                 phpmyadmin/phpmyadmin:latest         *:8080->80/tcp

[xinity]

As you login via the proxy (which is the right thing to do), you can't log in in as root, basically you will log in as a user known by proxysql which will "route" you to a working node.

we are not dealing with docker issues right now, but more with understanding how proxysql and galera cluster in PXC works together.

Again, i advise you to dig in :)

Have fun ! :)

[pascalandy]

You are right this is not a Docker issue. Simply me not knowing how to operate ProxySql :-/

I used those logins and still can't connect:

MYSQL_PROXY_USER=proxyuser
MYSQL_PROXY_PASSWORD=s3cr3TL33tPr0xyP@ssw0rd

Thank you for everything!

[xinity]

all right :)

give me some time to test your own setup and let you know where it breaks :)

Stay tuned !

[Qanah]

hello, I have the same issue

also, should I run it in RancherOS what will happen if I run it like this also, if I used the latest version of image: percona/percona-xtradb-cluster and image: perconalab/proxysql is this will responsible for my issue

for i in pxcm1 pxcw1 pxcw2 pxcw3;
    do docker-machine create -d virtualbox  $i;
    sleep 15;
    docker-machine ssh $i "sudo apt update -qq && sudo apt install -qqy ca-certificates";
done