MySQL/Windows/Docker - How to import a dump file to MySQL container

< environment >
OS : Windows
DB : MySQL
virtualization software ： Docker
shell ： PowerShell / git bash / command prompt

I tried import to MySQL container on Windows using dump file, it was a really tough job.

How to export dump file using PowerShell command (execute from outside of container)

When you get MySQL dump file from outside of container, the command is like this.

I have a MySQL container named "mysql".

docker-compose exec mysql mysqldump --user=root --password=password myapp01 > dump_file_20221119.sql

To execute, replace "--user" and "--password" in your environment.
And the scheme is "myapp01".

After executing this command, dump_file_20221119.sql will be created.
There is no problem so far.

How to import dump file

PowerShell (execute from outside of container) [failed]

I tried the most basic import command.

docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql

The result is:

PS C:\kaki\work> docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql
発生場所 行:1 文字:90
+ ... ql --host=localhost --user=root --password=password myapp01 < dump_fi ...
+                                                                 ~
演算子 '<' は、今後の使用のために予約されています。
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

You can't use the character '<' in PowerShell.

I looked up how do I make the character escape, but there is no way to do that.

(Reference)
about Special Characters - PowerShell | Microsoft Learn

I tried "<" and "<", it didn't work.

PowerShell (execute from outside of container - other command) [failed]

When you execute import command, you can use '-e' option instead of the character '<'.

(Reference)
MySQL :: MySQL 5.6 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

(example)
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

So, I tried :

docker-compose exec mysql mysql --user=root --password=password -e "source dump_file_20221119.sql" myapp01

The result is:

ERROR at line 1: Failed to open file 'dump_file_20221119.sql'

I found out that the "-e" option only target files inside the container.

I tried store the dump file in the container and execute the command, it recognized the file.

Although, the error occurred.

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '��m'.

The cause is that PowerShell default character code is CP932(SJIS).

(Note)
The writer is Japanese.

(Reference)
How to export dump file on Windows, import it to Ubuntu

I thought if "binary-mode" was set "1", it would work.
But, it didn't.

(Reference)
mysql Client Options

Execute command:

docker-compose exec mysql mysql --user=root --password=password -e "source dump_file_20221119.sql" myapp01 --binary-mode

The result:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source dump_file_20221119.sql' at line 1

When I google the word "binary-mode" option, I found "--binary-mode=1" and "binary-mode 0".
But, I did't understand quite well. (I tried them, but I got errors)

I'm not sure why there are so many different statement. Anyway, I refer the one from official website.

But, if I solve the problem, I have to set the file in the container. So, it is not an efficient approach.

When I export using MySQL Workbench appended command, I could successfully import without mentioned errors.

The command is:

export using MySQL Workbench appended command

& "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" --result-file=dump_file_20221119.sql myapp01 --user=root --password=password --host=127.0.0.1 --port=3306 --set-gtid-purged=OFF --skip-lock-tables --skip-add-locks --skip-column-statistics

The shell is Powershell.
When you execute Powershell command that contains space, the error occurred.
So, you have to do other approach.

You need to put the dump file in MySQL container.

Execute PowerShell script [failed]

I thought it would work if I created PowerShell script file and executed.
So, I tried.

I created the file "dump-import.ps1".
The file contents is:

dump-import.ps1

Write-Host "dump importing..."

# docker-compose exec mysql bash
docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql

Execute command:

PowerShell -ExecutionPolicy RemoteSigned .\dump-import.ps1

The result:

PS C:\kaki\work\tmp> PowerShell -ExecutionPolicy RemoteSigned .\dump-import.ps1
発生場所 C:\kaki\work\dump-import.ps1:4 文字:90
+ ... ql --host=localhost --user=root --password=password myapp01 < dump_fi ...
+                                                                 ~
演算子 '<' は、今後の使用のために予約されています。
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : RedirectionNotSupported

I got exactly same error.

It works the command "docker-compose exec mysql bash" correctly.

use git bash [failed]

I gave up using PowerShell, so I experimented with git bash.

The result is:

$ docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql
the input device is not a TTY.  If you are using mintty, try prefixing the command with 'winpty'

The error message says use "winpty".

Anyway, add "winpty" on the head of the command.

(Reference)
the input device is not a TTY. If you are using mintty, try prefixing the command with 'winpty' · Issue #2888 · vercel/hyper

So, re-execute.

$ winpty docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql
stdin is not a tty

The error message says "stdin is not a tty".

I found the page after researching.

docker-compose on wsl: stdin is not a tty · Issue #166 · rprichard/winpty

It's a long standing issue with Docker.
The way it does terminal detection only works with cmd.exe and powershell.exe at the moment. Any third-party terminal breaks that detection and gives the message.

It seems impossible to add "winpty" on the head of docker command, it is necessary to use directly powershell or cmd. (If you use Windows)

As a side note, you can check out about "winpty" here.

[Git Bash] winpty コマンドについて調べてみた([Git Bash] About winpty)

"winpty" command is interface to interact between Windows console program and UNIX virtual terminal.

use command prompt [failed]

So, I used cmd.

C:\kaki\work>docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql
the input device is not a TTY.  If you are using mintty, try prefixing the command with 'winpty'

Next, I re-execute the command with "winpty" on the head.

C:\kaki\work\ryuki-prd>winpty docker-compose exec mysql mysql --host=localhost --user=root --password=password myapp01 < dump_file_20221119.sql
'winpty' は、内部コマンドまたは外部コマンド、
操作可能なプログラムまたはバッチ ファイルとして認識されていません。

It didn't work.

execute the command inside container [success(But, inefficient)]

I tried to execute import command after entering the container.

It is necessary to set share directory between host and container.
The example is here :

docker-compose.yml

  mysql:
    image: mysql:5.7

# (omission)

    volumes:
      - db-data:/var/lib/mysql
      - ./shared_db:/shared_db  # set share directory between host and container

Store exported dump file in "shared_db" directory, after login mysql container.

And execute import command in "shared_db" directory inside of the container.

Drawback : Need to rebuild container. annoying.

Also, it is not suitable at all to edit docker-compose.yml and rebuild container only to import dump file.

use MySQL Workbench. [success]

After this long research, I recommend using MySQL Workbench to import dump file to MySQL container on Windows.

There is a annoying error about character encoding when I import the dump file with the command above.
So, it is better to use MySQL Workbench to export.

MySQL Workbench：export

1. Server -> Data Export

2．Start Export

Select the schema to export. ("myapp01" in the image.)

Select Export Self-Contained File.
(If you don't check the option, the dump files will be created for each tables, which create disaster in your file system.)

Set export file name.

After click "Start Export" button.

3. Data export complete

MySQL Workbench：import

1．Delete existing schema

delete the schema to import "myapp01".

2. Create schema

Recreate deleted schema.(myapp01)

Set the name, and click "Apply".

You can leave Algorithm and Lock Type as default.

Scheme will be created.

3. Server -> Data Import

4．Start Import

Select "Import from Self-Contained File", and set export file.

Set target scheme.

Then, click "Start Import".

5．Import Completed

observation

I hope Powershell accepts the character "<".