How can I import a sql db to memsql (using docker and MemSQL “Cluster in a Box”)?


#1

I want to import a sql dump file (db schema and data) using docker and MemSQL “Cluster in a Box”.
From Windows PowerShell I tried the following command:

mysql --host=localhost --user=root --port=3307 --database=DB_NAME < FILE_NAME

but it didn’t work and I got a message to call on master aggregator. Any ideas?


#2

3307 is the wrong port. The correct port number is 3306 - the master aggregator.


#3

…but mysql57 is listening on port 3306


#4

Run show aggregators in the memsql connection - the node labelled the master aggregator is the one you should be connecting to.


#5

OK, you are right. The correct port for master aggregator is 3306.
But I’m still confused because MYSQL5.7 (already installed localy) is listening to port 3306, too.

Let me take it from the beginning.
I have already MYSQL5.7 installed localy on my PC.
I have already created mysqldumpfile.sql (schema & data), stored localy on my disk (C:/…/mysqldumpfile.sql)
I installed Docker.
I followed the following steps:
docker run -i --init --name memsql-ciab
-e LICENSE_KEY=$env:LICENSE_KEY -p 3307:3306 -p 8080:8080
memsql/cluster-in-a-box:latest
docker start memsql-ciab
docker exec -it memsql-ciab memsql
memsql> CREATE DATABASE mydbname

And after that I tried the following command from Windows PowerShell :
mysql --host=localhost --user=root --port=3306 < C:…\mysqldumpfile.sql

The above command is not working because as I understood is trying to connect with mysql server (not memsql) and thats why a password must be defined.

Please any advise…


#6

I see that you are trying to forward a different port to MemSQL. To avoid confusion, let’s say you are using 4000:3306 (changing 3307 to 4000 since 3307 is the port of the leaf node within the docker container).

Any commands you run within the docker container (like the docker exec commands) need to connect to 3306. A client running outside docker on the Windows side (like your command from Windows PowerShell) should connect to 4000.

Alternatively, you can also change the port you install MemSQL on, but the above should work for your existing docker cluster-in-a-box install.


#7

Thanks for your answer.
I had tried it already but it didn’d work and returned error (ERROR 2005 (HY000): Unknown MySQL server host).

I thought that I had found the solution by replacing the ‘localhost’ with ‘host.docker.internal’:

mysql --host=host.docker.internal --user=root --port=3306 -p < C:…\mysqldumpfile.sql

But unfortunately it didn’t. I connected with local mysql database and not with memsql database in which I wanted to import the dump file.


#8

Can you paste the command args you used that returned the “Unknown MySQL server host” error?


#9

Also, this wasn’t the error you were seeing, but another possible problem is that the MySQL client may be trying to connect through a socket file, which you can fix by using --host 127.0.0.1 instead of --host localhost. (https://docs.memsql.com/troubleshooting/latest/network-errors/#error-2002-hy000-can-t-connect-to-local-mysql-server-through-socket-var-run-mysqld-mysqld-sock)

The strategy of forwarding a different port to the docker container’s 3306 should work - it works for us. There is probably just some issue in the host or port you are specifying for the mysql client connection.


#10

The problem solved by

  • using the port 4000:3306 instead of 3307:3306 and by
  • using --host=127.0.0.1 instead of --host=localhost

Thanks a lot.