Solutions to problems encountered in importing and exporting MySQL

Time:2021-1-17

background

Since I converted all my tasks into docker operation and management, I encountered a series of problems, this time it was the problem of MySQL backup.

The reason is that – V is not specified when starting MySQL image, 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 when using import and export, which waste a lot of time to solve.

Solution process

Positioning MySQL image too large

View the space occupied by the container


```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. Therefore, docker inspect container is used_ The ID command looks at the specific information of the specific container and finds that it is indeed the volume of 9.3g.


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

Back up Mysql to a new disk

```Docker exec - it MySQL MySQL dump - U user name - P password database > / MNT / VDB / data / MySQL / test_ db.sql ```
The user name, password and database can be modified by themselves

Import MySQL

1. In order to ensure that the previous data is still there, we set up another MySQL container and use – V to map to the host.

2. Then execute: docker exec – I mysqld MySQL – uroot – proot Federation < / MNT / VDB / data / MySQL / fed_ db.sql

Here comes the mistake

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 to back up, import in the new image, and import successfully.

  • The rows in this is not the same as the one I imported
  • The rows in this is not the same as the one I imported
  • The rows in this is not the same as the one I imported

  • WTF? Total 1688, only 1480?
  • Enter the docker container and use the command line to query. It’s really 1688. It’s really the displayed rows. So

summary

When using docker, you should consider whether you need plug-in volume. Generally, it’s better to plug-in all databases, so backup and migration are very convenient

Do not rely too much on the GUI of the database, the command line is the most reliable

You can’t, you crazy add hard disk, anyway, are on the server, but to ensure that the server will not jump, otherwise the data is easy to lose.

Well, the above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support for developer.

Recommended Today

Practice of query operation of database table (Experiment 3)

Following the previous two experiments, this experiment is to master the use of select statements for various query operations: single table query, multi table connection and query, nested query, set query, to consolidate the database query operation.Now follow Xiaobian to practice together!Based on the data table (student, course, SC, teacher, TC) created and inserted in […]