I have a file *.SQL more than 1GB, when I import to Mysql on local or Vagrant, it always successful. But with the same file, I import to Mysql on Docker, it very very slow (about 10MB / 15 minutes). I have checked many times with the same result. I tried with many files; it seems to occur with specified files. I don't know the cause. I need help.
This's my docker-compose.yml:
version: "2.0"
services:
database:
image: "mysql/mysql-server:5.5"
ports:
- "3307:3306"
volumes:
- "db:/var/lib/mysql"
- ./sql:/home/sql
environment:
MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
volumes:
db: {}
I use docker in windows ... My solution whas moving the .sql file to Docker mysql container and executing the import manually.
Export .sql from your mysql production server...
Copy the file into Docker mysql container
docker cp base_ok.sql docker_mysql_1:/home/base_ok.sql
enter into the bash of the docker mysql container
docker exec -ti docker_mysql_1 bash
import the DDBB
mysql -p -u root magento < base_ok.sql
note that my docker container is docker_mysql_1 you can list all your containers typing docker ps
I'm a bit late for answering, but I struggled with this and I think the solution could help others.
I've read a lot, I tried the barrier=0 in my ext4 filesystem but nothing. Finally the simple solution that worked for me was enlarging innodb buffer pool size to a decent value
mysql.cnf:
[mysql]
innodb_buffer_pool_size=128M
That's it.
You can copy existing conf to a local dir, then mount it: docker cp -a
<container>:/etc/mysql/conf.d mysql_conf
# change mysql.cnf
docker run ... -v /path/to/mysql_conf:/etc/mysql/conf.d ...
Or for what I know passing it directly to docker run:
docker run ... mysql:latest --innodb-buffer-pool-size=128M
I discovered that that solution wasn't enough per se, it somehow happen to work sometimes while still so slow on others (not dig in why).
Since I use it just for a development version of mysql on my pc, I'm not particularly worried about data corruption, etc, so I now have:
[mysql]
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 4
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_checksums = 0
general_log = 0
slow_query_log = 0
innodb_lru_scan_depth=256
Then I launched an optimize table on all tables for just make sure that "innodb_file_per_table" was effective.
Adjust options that scares you most accordingly if you are on a production environment.