docker - how to migrate mysql data from local to ec2 instance

user_10686620 0
Viewed 521

In local environment, I am using docker-compose file to create multiple containers, including mysql container.
The data of mysql container is stored in ./db-data.
There are 4 tables in the database, e.g. Table A,B,C,D.

In Development environment, I've created an ec2 instance and used same docker-compose file to create containers, while the mysql database is managed be Aws RDS.

I want to copy all the data of Table A and B from local to development environment, but I wonder how can I achieve this?

docker-compose.yml

version: '2.1'

services:
  test-db:
    image: mysql:5.7
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=true
      - MYSQL_USER=admin
      - MYSQL_PASSWORD=12345
      - MYSQL_DATABASE=test
    volumes:
      - ./db-data:/var/lib/mysql
    ports:
      - 3306:3306
  test-web:
    environment:
      - NODE_ENV=local
      #- DEBUG=*
      - PORT=3030
    build: .
    command: >
      ./wait-for-db-redis.sh test-db npm run dev
    ports:
      - "3030:3030"
    depends_on:
      - test-db
docker node.js amazon-web-services amazon-ec2 mysql
2 Answers

If you want to migrate your data from mysql on the instance to RDS mysql, probably mysqldump would be the easiest choice as explained in the AWS docs:

You can also import data from an existing MySQL or MariaDB database to a MySQL or MariaDB DB instance. You do so by copying the database with mysqldump and piping it directly into the MySQL or MariaDB DB instance.

There are other options to consider if basic mysqldump does not suit you, depending on your requirements. For example migrating live database with minimal downtime, or using Percona XtraBackup software as explained in AWS docs as well, or even AWS database migration service.

user_2488230

In addition to Marcin answer, if you are using MYSQLWorkbench you can do the following:

  1. Run SELECT * FROM <Your table name>; in the table you want to extract the data
  2. Click the Export button in Result Grid
  3. Choose SQL Insert statements in Format
  4. You can then get a list of SQL Insert statement. enter image description here
user_106866200
Related