rds mysql export data



*** For more information about Amazon RDS data transfer pricing, go to Amazon Relational Database Service Pricing.


http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

3 mysql instances are involved:
* source instance [master]
* middle instance [slave, read replica as proxy]
* dest instance [slave of slave, also a read replica]

why is the middle instance needed ?


//=== export data to external mysql db
* source instance: The RDS MySQL DB instance must be running version 5.6.13 or later.
* dest instance: The MySQL instance external to Amazon RDS must be running the same version as the Amazon RDS instance, or higher.


* source rds instance is configured as master ["replication source"]

* the external mysql db instance is configured as slave ["read replica"]
[Replication Slave Configuration]

Configure ingress(inbound) rules on the system running the "source instance" to allow the "dest instance" to connect during replication.


After replication on the dest instance has caught up with the source instance,
use the MySQL> STOP SLAVE command to terminate replication.



*** Use the mysql.rds_set_configuration stored procedure on the "source instance"?
to set the binary log retention period long enough that the binary logs are not purged during the export.
For more information, see Accessing MySQL 5.6 Binary Logs.

*** To further ensure that the binary logs of the source instance are not purged,
create an Amazon RDS Read Replica ["the middle instance"] from the source instance. For more information, see Creating a Read Replica.

[???]
After the Amazon RDS Read Replica ["the middle instance"] has been created,
call the "mysql.rds_stop_replication" stored procedure to stop the replication process.
The source instance will no longer purge its binary log files,
so they will be available for the replication process.




//=== create a read replica
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.Create

You can create a Read Replica from an existing MySQL or PostgreSQL DB instance using the
* AWS Management Console,
* CLI, or
* API


* You create a Read Replica by specifying the SourceDBInstanceIdentifier

* when you initiate the creation of a Read Replica,
Amazon RDS takes a DB snapshot of your source DB instance and begins replication.
--> you will experience a brief I/O suspension on your source DB instance as the DB snapshot occurs.
The I/O suspension typically lasts about one minute and can be avoided
if the source DB instance is a Multi-AZ deployment


An active, long-running transaction can slow the process of creating the Read Replica,
so wait for long-running transactions to complete before creating a Read Replica.


*** When creating a Read Replica, there are a few things to consider.

* First, you must enable automatic backups on the "source instance" by setting
the backup retention period to a value other than 0.

This requirement also applies to a Read Replica that is the source DB instance for another Read Replica.

For MySQL DB instances, automatic backups are supported only for Read Replicas running MySQL 5.6
but not for MySQL versions 5.1 or 5.5.

??? To enable automatic backups on the "Read Replica" ( the middle instance? ),
first create the Read Replica, then modify the Read Replica to enable automatic backups.


* To create a Read Replica from a source MySQL instance
Use the rds-create-db-instance-read-replica command.

-s === --source-db-instance-identifier

http://docs.aws.amazon.com/AmazonRDS/latest/CommandLineReference//CLIReference-cmd-CreateDBInstanceReadReplica.html
[ex]
mysql> rds-create-db-instance-read-replica SimCoProd01Replica01 -s SimcoProd01

DBINSTANCE simcoprod01replica01 db.m1.large mysql 10 master creating
us-east-1b 0 n 5.1.50 simcoprod01
SECGROUP default active
PARAMGRP default.mysql5.1 in-sync



[ex]
mysql> rds-create-db-instance-read-replica SimCoProd01Replica02 -s SimCoProd01 -z us-east-1a

DBINSTANCE simcoprod01replica02 db.m1.large mysql 10 master creating
us-east-1a 0 n 5.1.50 simcoprod01
SECGROUP default active
PARAMGRP default.mysql5.1 in-sync



//===
You can create a MySQL Read Replica in a different region than the source DB instance to improve your
disaster recovery capabilities, scale read operations into a region closer to end users,
or make it easier to migrate from a data center in one region to a data center in another region


*** run the create Read Replica command in the region where you want the Read Replica,
and specify the Amazon Resource Name (ARN) of the source DB instance.



//=== Use the mysqldump utility to create a snapshot,
which copies the data from Amazon RDS to your local client computer.
Then run another utility to load the data into the MySQL instance running external to RDS.

*** to run mysqldump on a client, and then pipe the dump into the mysql client utility, which loads the data into the external MySQL instance.

mysqldump -h RDS instance endpoint -u user -p password --port=3306 --single-transaction --routines --triggers --databases database database2 --compress --compact | mysql -h MySQL host -u master user -p password --port 3306






//=== create read replica for MyISAM instance

If your MySQL DB instance uses a non-transactional engine such as MyISAM,
you will need to perform the following steps to successfully set up your Read Replica.

These steps are required to ensure that the Read Replica has a consistent copy of your data.
Note that these steps are not required if all of your tables use a transactional engine such as InnoDB.

* Stop all DML and DDL operations on non-transactional tables in the source DB instance and
wait for them to complete. SELECT statements can continue running.

* Flush and lock the tables in the source DB instance.

* Create the Read Replica using one of the methods in the following sections.

* Check the progress of the Read Replica creation using, for example, the DescribeDBInstances API operation.
Once the Read Replica is available, unlock the tables of the source DB instance and resume normal database operations.