Detailed explanation of importing/exporting MySQL data in Docker container

Preface

We all know that the import and export of MySQL data can be solved with a mysqldump command, but what if MySQL is running in a docker environment?

The solution is to use the mysqldump command, but we need to enter the mysql container of docker to execute it, and configure volumes so that the exported data files can be copied to the host machine's disk.

So the operation steps can be divided into:

• Configuring Docker volumes
• Enter the mysql container of docker and export the data file

As for data import, it is too simple to explain.

Let's first look at the common options of the mysqldump command:

• --all-databases, -A: Back up all databases
• --databases, -B: Used to back up multiple databases. If this option is not present, mysqldump uses the first name parameter as the database name and the rest as the table name. With this option, mysqldum treats each name as a database name.
• --force, -f: Continue the backup even if a SQL error is found
• --host=host_name, -h host_name: backup host name, default is localhost
• --no-data, -d: export only the table structure
• --password[=password], -p[password]: password
• --port=port_num, -P port_num: Specify the port number for TCP/IP connection
• --quick, -q: Quick export
• --tables: Overrides the --databases or -B option, and the following parameters are treated as table names
• --user=user_name, -u user_name: User name
• --xml, -X: Export as xml file

Configuring volumes

First, I use docker-compose to arrange the docker containers. For the complete configuration code, please see this project: liumapp/rabbitmq-mysql-redis-in-docker

Please note the following lines in the docker-compose.yml configuration file of this project:

 mysql:
 container_name: mysql
 image:mysql:5.5.60
 restart: always
 volumes:
  - ./mysql/data:/var/lib/mysql
  - ./mysql/conf/mysqld.conf:/etc/mysql/mysql.conf.d/mysqld.cnf

The volumes I configured for the mysql container are to map the project's mysql/data directory to the /var/lib/mysql in the docker container.

So when I enter the mysql container of docker and execute the export command, I only need to export the data in the /var/lib/mysql/ directory, and I can find the corresponding data file in the ./mysql/data/ directory of the host machine.

Enter the container to export data

First execute

docker ps

Find the name of the mysql container

Then execute

docker exec -it mysql /bin/bash

Entering the container

Execute Command

whereis mysql

Find the running path of mysql, here is: /usr/local/mysql/bin, enter it with cd

cd /usr/local/mysql/bin

Please note that the path here refers to the path inside the Docker container and has nothing to do with your host path.

Execute the export command

mysqldump -u username -p database name> save file .sql

After entering the password, the export is basically successful. Please note that the path to save the file should be set under volumes, that is, /var/lib/mysql/

Then enter

exit

Exit the container and return to the host machine, we can find the exported data file

If you want to export in csv format, change the mysqldump command to:

mysql -u username --password=password --database=database name --execute='SELECT `FIELD`, `FIELD` FROM `TABLE` LIMIT 0, 10000 ' -X > Save file.sql

You can

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.