Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a huge database approx 200GB (Some tables are more than 25GB in size).

We are willing to migrate to MySQL RDS Instance.but as it does not provide any ssh access so we can't copy file system over there(Like backup taken from Hot backup tools).

we will have to take the dump using mysqldump but it may take days to complete the backup and in same in restoring it into RDS.

As backup will lock the tables for a huge time so we will also require a long downtime.

How Can we do it Quickly?I am looking for Quick and dirty solution? Also What are the limitation of the RDS Instance?

share|improve this question
    
Four(4) questions : 1) How many MyISAM tables are you exporting to RDS? 2) How many InnoDB tables are you exporting to RDS? 3) What is the name of the source database? 4) What is the name of the target database in RDS? –  RolandoMySQLDBA Jun 26 '12 at 16:45
    
1) Approx 20 MyISAM tables 2)Approx 140 InnoDB tables 3)take source and target DB name as test. there are approximate 160 tables in database.Specially InnoDB(Some have 25G+ of data) tables are Huge MyISAM(Not more than 2GB) are small in size. –  Abdul Manaf Jun 26 '12 at 18:36

2 Answers 2

Limitations of using Amazon RDS:

  • No file system access can't monitor slow, general and error log
  • No SSH access

There are a number of simple ways to import data into Amazon RDS, such as with the mysqldump or mysqlimport utilities for MySQL, import/export or SQL Loader for Oracle, and Import/Export wizard or Bulk Copy Program (BCP) for SQL Server. For more information on data import and export, please refer to the Data import for MySQL

I would suggest it's better if you create replica of all your table structure first on Amazon RDS instance and later copy data. I recommend you to use SQLyog as it has both schema and data synchronization feature and infact as target table is empty it will use bulk_insert_statements to insert rows.

Try SQLyog I am sure it will help you.

share|improve this answer

When we did something similar recently ~250GB, we first used an EC2 slave from production database as a staging slave with SSL and binary logging enabled -- to make table dumps, etc. Converting any tables to InnoDB if you need here. Load data dumps from EC2 to RDS. Then used a binary log feeding strategy to load data and keep it current. At cutover time, flush the last binary log-- and load it into RDS and you're done. I prepped the logs first (removing any set cmds that require super) and just fed them to the mysql client on rds. Next time I want to try Tungsten replicator.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.