MySQL RDS data for a flat file to reload in another MySQL - Std CLI not working

Question

To establish a backstory, I looked at the following:

• Amazon-RDS Dedicated Server Database
• Mysqldump AWS RDS

• However, I did not find a clear solution to my problems in these matters, and only supporting facts in general - I wanted a comprehensive concrete guide to moving data in / around AWS RDS.

  I had a discussion at the Percona MySQL conference with a DBA consultant who works with RDS, he suggested the following, and this seems to be a problem often - I wanted to enter the effort to help everyone.

** This is important for RDS users according to the large MySQL Consulting company and the number of hands raised in a conversation. **

• Goal number 1. Get data from your RDS instance - You cannot move between major releases in RDS. -Can you accept it in an instance of EC2, for example, Percona, etc.
• Goal Wish List # 1a. Secondly, clear / QA data, if possible,
• Goal # 3 is to return data back to another smaller RDS or other flavor / platform.

Data:

• I have a large (40-50 GB dataset in utf-8, first of all, some Latin-1) in MySQL 5.5.x (<5.5.8). 1a. For some reason, the old administrator set the size of our instance to 1000 GB of dedicated storage, which simply complements. (?).
• All tables are now obviously InnoDB - a pair of tables is about 14 GB. This is another problem.
• Multi-axis encoding may exist: possibly UTF8; Latin-1 and Swedish-fkafkakfk?

So, with these options, I discuss the following:

Option A: Direct .sql dump; .sql reload

• Dump, in .sql standard executable files of schema and tables, etc. en masse, reload.
• ** So there was a refinement of Latin-1 for portability: mysqldump -u username -p --default-character-set=latin1 -N database > backup.sql mysql -u username -p --default-character-set=latin1 database < backup.sql**

Question RE: Option A: - Suggestions re: above code, for chunking, integrity and others, guaranteeing a smooth reset and reboot? Unforeseen conditions for show information schema with variable codes (Processing algorithm of what latin1 cannot?)

Option B: Separate dumps of ascii table files using / QA / schema

• Dump in direct ASCII (Charset? UTF-8? Should I be careful?) This data in separate separate tables, possibly in pieces for QA data.

  To output TSV DATA and SCHEMA, follow these steps:

  mysqldump --user=dbuser --password --tab=~/output/dir dbname

• Secondly, run some perl / python to clear out possible erroneous young ladies; coding issues; etc. from 8 years old 5 different database administrators and about 12 different data formats / types.

Questions RE: Option B:

• There is a lot of garbage in my data that is true to the data; Is a pipe best suited?
• I had terrible errors loading into AWS RDS from TSV etc. from core dumps, suggestions that go beyond what is published in their data loading documentation?

Answer 1

I would strongly suggest option C, which should use the ETL tool to move data. I used Community Edition to integrate Pentaho data very successfully to do what you ask (especially cleanup). This works more than just dumping and loading, but you have very good control and you can do things like sync again, etc.