CODEWITHSUNDEEP
mysqldockerdocker-compose
user257980
user257980

Reputation: 1089

docker-compose transfer mysql to another host

I'm using docker-compose to run MYSQL container and linking it to www-server

 mysql:
  image: mysql
  container_name: lip-mysql
  ports:
    - "3030:3306"
  environment:
    - MYSQL_ROOT_PASSWORD=root
    - MYSQL_DATABASE=user-db
    - MYSQL_USER=user
    - MYSQL_PASSWORD=user
  volumes: 
    - ./etc/mysql:/var/lib/mysql

When I'm running it on my own computer and want to tranfer it to the production. I do this

  1. Copy to volume etc/mysql directory to server
  2. then building this docker-compose build
  3. then starting docer-compose up -d
  4. When trying to access data. I get error "Base table or view not found: 1146 Table 'user-db.Packages' doesn't exist". I founded out that the database structure is there, but cant accesses to data.

How I should move my mysql data to new environment?

Upvotes: 2

Views: 3562

Answers (2)

user257980
user257980

Reputation: 1089

Here's the solution what I used.

  • First transfer your mysql files to another volumes: - ./etc/mysql:/var/lib/mysql server
  • Then create docker-compose.yml and import the volume like in posting.
  • Here comes the issue what I had. VERSION NUMBERING MYSQL IMAGE. Use same version numbering when starting mysql. If you are using latest-tag you should get the error message to use mysql_upgrade. Use it if you want to use latest image.

Here is the right docker-compose.yml to start:

mysql:
  image: mysql:5.7
  container_name: lip-mysql
  ports:
    - "3030:3306"
  environment:
    - MYSQL_ROOT_PASSWORD=root
    - MYSQL_DATABASE=user-db
    - MYSQL_USER=user
    - MYSQL_PASSWORD=user
  volumes: 
    - ./etc/mysql:/var/lib/mysql

Upvotes: 0

Eslam
Eslam

Reputation: 1653

Can you instead dump your MySQL DB from your computer and restore it to the other environment.

dump DB:

docker exec -it lip-mysql mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

Restore DB:

docker exec -it lip-mysql mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Can you also pin the MySQL docker image in your docker-compose file to avoid not-intended MySQL engine updates.

Upvotes: 5

Related Questions