Solving problems encountered when importing and exporting Mysql

background

Since I converted all my tasks to Docker operation and management, I encountered a series of pitfalls, this time it was the problem of MySQL backup.

The reason is that -v was not specified when starting the mysql image, which caused the docker to become very large after a period of time. The original disk was not enough and needed to be migrated to a new disk.

There were some problems when using import and export, and a lot of time was wasted to solve them.

Solution process

Locating mysql image is too large

View the space occupied by containers

```docker system df``` 

View details

Local Volumes space usage:
VOLUME 
NAME LINKS SIZE
3e764b0633ea2c3f3dc5b0bf79dc753055d7c09451b477d3015650c66ea4a5fb 0 0B
598a592e1f9d90d2564c7c52f45f6f6a96784ad0426ec60682299fa1a1b93b96 0 0B
5eb12b3b0091810bbe3c94c5801ad2f2ff51d79b69b0ac01eb37cf00d37d89f6 0 0B
admin_logs 0 0B
f42e3ef90e4c7b3658a0fb4a877e90b298b12cb58cd63f38d9b4ad5c2ab41d73 0 0B
3361b9c615e09708170018dc7d170b8d4b498dd83cbcb155a3d41234d08e8119 1 9.3G

At this time, according to the information returned above, only two containers have VOLUMES, one is mysql and the other is redis. So use the docker inspect container_id command to view the specific information of the specific container and find that it is indeed the 9.3G volume.

CONTAINER ID IMAGE COMMAND LOCAL VOLUMES SIZE 
cb76bbc211e6 mysql "docker-entrypoint.s…" 1 7B

Back up mysql to the newly mounted disk

```docker exec -it mysql mysqldump -u username -p password database > /mnt/vdb/data/mysql/test_db.sql```
Username, password and database modification

Import mysql

1. To ensure that the previous data is still there, start another mysql container and map it to the host using -v.

2. Then execute: docker exec -i mysqld mysql -uroot -proot federation < /mnt/vdb/data/mysql/fed_db.sql

The error is coming

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 'mysqldump: [Warning] Using a password on the
command line interface can be insec'd at line 1

I searched a lot of information, but to no avail, so I decided to use a graphical tool to back up directly. I used navicat, backed up, imported it into a new image, and the import was successful.

• The rows in this are different from what I displayed after importing
• The rows in this are different from what I displayed after importing
• The rows in this are different from what I displayed after importing

• WTF? The total number is 1688, but only 1480 is displayed?
• Enter the docker container and use the command line to query. It is indeed 1688. The displayed rows are indeed wrong. So...

Summarize

When using Docker, you should consider whether you need to use external volumes. Generally, it is best to use external volumes for databases, which makes backup and migration very convenient.

Don't rely too much on the database GUI, the command line is the most proven and reliable

If it really doesn't work, you can add a lot of hard drives. They are all on the server anyway, but you must make sure that the server does not crash, otherwise the data will be easily lost.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.