A pit encountered in MySQL import and export

Time:2019-11-8

background

  • Since I converted all my tasks into docker operation and management, I encountered a series of pits. This time, it was the problem of MySQL backup.
  • The reason is that MySQL image was not specified when it was started-v, which leads to a large docker after a period of time. The original disk is not enough and needs to be migrated to a new disk.
  • There are some problems in using import and export, which waste a lot of time to solve.

Solving process

Too large MySQL image

View the space occupied by the container

```docker system df```

A pit encountered in MySQL import and export

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. Therefore, use the docker inspect container [ID] command to view the specific information of the specific container, and it is found that the volume of 9.3g is indeed the one.

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

Backup Mysql to the newly attached disk

```Docker exec - it MySQL MySQL dump - U user name - P password database > / MNT / VDB / data / MySQL / test_db.sql```
User name, password and database modification

Import MySQL

1. To ensure that the previous data is still available, a MySQL container is created and mapped to the host using - V.
2. Then execute: docker exec - I mysqld MySQL - uroot - proot Federation < / MNT / VDB / data / MySQL / fed_db.sql

The mistake 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’ at line 1

I searched a lot of data, but it didn’t work, so I decided to use graphical tools to directly back up, so I used Navicat, backup, and import in the new image,And import succeeded
A pit encountered in MySQL import and export

  • The rows in this is different from the ones I imported
  • The rows in this is different from the ones I imported
  • The rows in this is different from the ones I imported

A pit encountered in MySQL import and export

  • WTF? Total 1688, only 1480?
  • Enter the docker container and use the command line to query,It’s 1688. It’s showing. Rows is wrong,So…

summary

  1. When using docker, you should consider whether you need to plug in the volume. In general, it is better to plug in the database, which is convenient for backup and migration
  2. Don’t rely too much on the GUI of the database, the command line is the most reliable
  3. It’s really not good. You can add the hard disk. It’s all on the server anyway. But make sure that the server doesn’t bounce, or the data will be lost easily.

Recommended Today

The use of progressbarcontrol, a progress bar control of devexpress – Taking ZedGraph as an example to add curve progress

scene WinForm control – devexpress18 download installation registration and use in vs: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/100061243 When using ZedGraph to add curves, the number of curves is slower if there are many cases. So in the process of adding curve, the progress needs to be displayed, and the effect is as follows     Note: Blog home page:https://blog.csdn.net/badao_liumang_qizhi […]