Skip to content

Instantly share code, notes, and snippets.

@devdrops
Last active July 23, 2024 14:20
Show Gist options
  • Save devdrops/c59ee84504d128a7913a480b1191d66e to your computer and use it in GitHub Desktop.
Save devdrops/c59ee84504d128a7913a480b1191d66e to your computer and use it in GitHub Desktop.
Mysqldump from Docker container

Mysqldump from Docker container

docker exec -i mysql_container mysqldump -uroot -proot --databases database_name --skip-comments > /path/to/my/dump.sql

OBS

  • This will generate a dump.sql file in your host machine. Awesome, eh?

  • Avoid using --compact on your dump. This will make MySQL check your constraints which will cause troubles when reading your file (damm you MySQL). And don't use --force to fix this scenario: recreate your dump without --compact ¯_(ツ)_/¯

  • You can execute the same command for both Docker and Docker Compose scenarios 😉

  • To import again your dump in a MySQL container, the best approach is to have another container with your dump files added during the build process. In my experience, the usage of making Docker read the dump from your host right to the guest caused troubles (errors like socket.error: [Errno 32] Broken pipe and the terrific ValueError: file descriptor cannot be a negative integer (-1) described here). So, in order to import again your dump:

    • From Docker Compose:
    docker-compose exec mysql_service /bin/bash -c 'mysql -uroot -proot < /path/to/my/dump.sql'
    • From Docker:
    docker exec mysql_container /bin/bash -c 'mysql -uroot -proot < /path/to/my/dump.sql'
@dominik-pakosz
Copy link

Awesome, eh!

@IliasDeros
Copy link

For importing I first copied the dump from my local machine to my container

docker cp /path/to/my/dump.sql mycontainer:/dump.sql
docker exec mysql_container /bin/bash -c 'mysql -uroot -proot < /dump.sql'

@lindseybradford
Copy link

Thank you!

@luanmateuz
Copy link

Thx!

@M-Marbouh
Copy link

Awesome! Thank you

@jpann
Copy link

jpann commented Jan 12, 2022

If you're not wanting to store your MySQL root password in whatever script is executing these, you can use the MYSQL_ROOT_PASSWORD environment variable, if your MySQL container has it defined.

Dump database:

docker exec mycontainer /bin/bash -c '/usr/bin/mysqldump -u root --password=${MYSQL_ROOT_PASSWORD} --databases mydatabase' > /path/to/my/dump.sql

Import database dump - Method 1 - Copy dump from host to container:

docker cp /path/to/my/dump.sql mycontainer:/dump.sql
docker exec mycontainer /bin/bash -c 'mysql -u root --password=${MYSQL_ROOT_PASSWORD}  < /dump.sql'

Import database dump - Method 2 - Cat contents of dump from host into container:

cat /path/to/my/dump.sql | docker exec -i mycontainer /bin/bash -c '/usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} mydatabase'

@antonga23
Copy link

Thx!

@jandahl
Copy link

jandahl commented May 17, 2022

I think I owe you a beer

@sarthakchittawar
Copy link

Bro this saved my marks big time. Wasted 3 hours figuring it out, you're an absolute legend mate.

@mhope-2
Copy link

mhope-2 commented Feb 9, 2023

awesome!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment