Programing

data.sql MySQL Docker 컨테이너 가져 오기

lottogame 2020. 11. 29. 09:34
반응형

data.sql MySQL Docker 컨테이너 가져 오기


data.sql이있는 경우 mysql Docker 컨테이너로 데이터베이스를 가져 오려면 어떻게해야합니까? 데이터베이스 데이터를 가져 오는 방법. 고정 된 세계에서 이것은 복잡성의 층을 추가합니다. 몇 가지 방법을 부탁드립니다.

여기 내 docker-compose.yml :

nginx:
  build: ./nginx/
  container_name: nginx-container
  ports:
    - 80:80
  links:
    - php
  volumes_from:
    - app-data

php:
  build: ./php/
  container_name: php-container
  expose:
    - 9000
  links:
    - mysql
  volumes_from:
    - app-data

app-data:
  image: php:7.0-fpm
  container_name: app-data-container
  volumes:
    - ./www/html/:/var/www/html/
  command: "true"

mysql:
  image: mysql:latest
  container_name: mysql-container
  ports:
    - 3306:3306
  volumes_from:
    - mysql-data
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: name_db
    MYSQL_USER: user
    MYSQL_PASSWORD: password

mysql-data:
  image: mysql:latest
  container_name: mysql-data-container
  volumes:
    - /var/lib/mysql
  command: "true"

최신 mysql 또는 mysql : 5.7에서는이 작업을 수행 할 수없는 것 같습니다. 그래서 대신 mariaDB를 사용합니다. 다음은 내 docker-compose.yaml코드입니다.

version: '3'

services:
  mysql:
    image: mariadb:10.3
    container_name: mariadb
    volumes:
      - container-volume:/var/lib/mysql
      - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: name_db
    ports:
      - "3306:3306"

volumes:
  container-volume:

나중에 데이터베이스를 가져올 수 있습니다.

docker exec -i mysql-container mysql -uuser -ppassword name_db < data.sql


/docker-entrypoint-initdb.d/yourdump.sql볼륨 마운트를 사용 하여 sql-dump를 마운트하십시오.

mysql:
  image: mysql:latest
  container_name: mysql-container
  ports:
    - 3306:3306
  volumes:
    - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: name_db
    MYSQL_USER: user
    MYSQL_PASSWORD: password

그러면 컨테이너가 시작되는 동안 sql-dump의 가져 오기가 트리거 됩니다. "새 인스턴스 초기화"아래의 https://hub.docker.com/_/mysql/참조 하십시오.


볼륨을 마운트하고 싶지는 않지만 로컬 머신에서 파일을 덤프하려는 경우 또 다른 옵션은 파이프를 사용하는 것 cat yourdump.sql입니다. 이렇게 :

cat dump.sql | docker exec -i mysql-container mysql -uuser -ppassword db_name

See: https://gist.github.com/spalladino/6d981f7b33f6e0afe6bb


Import using docker-compose

cat dump.sql | docker-compose exec -T <mysql_container> mysql -u <db-username> -p<db-password> <db-name>

combine https://stackoverflow.com/a/51837876/1078784 and answers in this question, I think the best answer is:

cat {SQL FILE NAME} | docker exec -i {MYSQL CONTAINER NAME} {MYSQL PATH IN CONTAINER} --init-command="SET autocommit=0;"

for example in my system this command should look like:

cat temp.sql | docker exec -i mysql.master /bin/mysql --init-command="SET autocommit=0;"

also you can use pv to moniter progress:

cat temp.sql | pv | docker exec -i mysql.master /bin/mysql --init-command="SET autocommit=0;"

And the most important thing here is "--init-command" which will speed up the import progress 10 times fast.


you can copy the export file for e.g dump.sql using docker cp into the container and then import the db. if you need full instructions, let me know and I will provide


I can import with this command

docker-compose exec -T mysql mysql -uroot -proot mydatabase < ~/Desktop/mydatabase_2019-10-05.sql

You can run a container setting a shared directory (-v volume), and then run bash in that container. After this, you can interactively use mysql-client to execute the .sql file, from inside the container. obs: /my-host-dir/shared-dir is the .sql location in the host system.

docker run --detach --name=test-mysql -p host-port:container-port  --env="MYSQL_ROOT_PASSWORD=my-root-pswd" -v /my-host-dir/shared-dir:/container-dir mysql:latest


docker exec -it test-mysql bash

Inside the container...

mysql -p < /container-dir/file.sql 

Custom parameters:

  • test-mysql (container name)
  • host-port and container-port
  • my-root-pswd (mysql root password)
  • /my-host-dir/shared-dir and /container-dir (the host directory that will be mounted in the container, and the container location of the shared directory)

참고URL : https://stackoverflow.com/questions/43880026/import-data-sql-mysql-docker-container

반응형