0
768
Viewsdocker - how to migrate mysql data from local to ec2 instance
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
2 answers
Answer question0
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.
0
In addition to Marcin answer, if you are using MYSQLWorkbench you can do the following:
- Run
SELECT * FROM <Your table name>;in the table you want to extract the data - Click the
Exportbutton inResult Grid - Choose
SQL Insert statementsin Format - You can then get a list of SQL Insert statement.
