mysqldump , create table if not exists

//=== http://stackoverflow.com/questions/8603182/how-to-update-table-if-table-exists-with-mysql-dump

mysqldump -uadmin -p > dump.sql

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
`major` int(11) NOT NULL DEFAULT '0',
...
)


how to change it to
IF TABLE EXISTS, UPDATE IT ???




//=== http://stackoverflow.com/questions/2220469/create-table-if-not-exists-from-mysqldump

* first dump containing table definitions (--no-data --skip-add-drop-table)
* second dump with only data (--no-create-info --skip-add-drop-table)



$ sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' dump_schema.sql

RDS as replica slave, RDS as replica master

//=== http://it.3hd.me/2015/05/mysql-replication-from-remote-rds-to.html

on master side (remote RDS)

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);"
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_passwd';

[Q] is 'on *.*' must? why not only on the target database 'on db_to_be_replicated.*' ?
[Q] 'repl_user'@'%' shd be changed to 'repl_user'@'slave_public_ip' ?
[Q] 'repl_user'@'slave_public_ip' --> 'repl_user'@'slave_host_domain_name' ??
[Q] is REPLICATION CLIENT optional?




//=== http://it.3hd.me/2015/04/mysql-replication-set-up.html

on slave side (RDS as replica slave)

*** since RDS not allow stop slave/start slave/change master to
-->
mysql> CALL mysql.rds_stop_replication;

mysql> CALL mysql.rds_set_external_master ('master.masters.com', 3306,
'repl_user', 'password', 'myrds-bin.00003', 107, 0);

mysql> CALL mysql.rds_start_replication


*** Do not use mysql.rds_set_external_master to manage replication between two Amazon RDS DB instances.
*** Use it only when replicating with an instance of MySQL running external to RDS,
i.e.
one RDS instance as master,
the other one external instance as slave




//===

* logon to master RDS by repl_user to verify the granted privileges
$ mysql -h rds_master -u repl_user -p
mysql> show grants;

amazon ec2 instance migration

*** sop for amazon ec2 instance migration from one account(old) to another(new)

*** account number:
support -> support Center


0. [optional] scp data to digitalocean

1. create and share private AMI on the old account
[instance -> Actions -> create image ?, then register ?]

[
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/creating-an-ami-ebs.html
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/creating-an-ami-ebs.html

* create a snapshot first
more efficient to create snapshots of your volumes prior to creating your AMI. This way, only small, incremental snapshots need to be created when the AMI is created, and the process completes more quickly

* create image
choose Instances and select your instance. Choose Actions, Image, and Create Image.

* register ami
]

[
To create an AMI from a snapshot using the console :

* Open the Amazon EC2 console at https://console.aws.amazon.com/ec2/.

* In the navigation pane, under Elastic Block Store, choose Snapshots.

* Choose the snapshot and choose Actions, Create Image.

* In the Create Image from EBS Snapshot dialog box, complete the fields to create your AMI, then choose Create. If you're re-creating a parent instance, then choose the same options as the parent instance.

]


*** Both the AMI and the snapshot incur charges to your account until you delete them.





//=== SOP
1. create and share private AMI on the old account

2. find and copy the shared AMI on the new account

3. no need to restore ~/.ssh/authorized_keys [already recovered within the AMI]

4. elastic ip change and domain name update
allocate new elastic ip
assign elastic ip to the new instance
[on elastic ip dashboard -> actions -> associate address -> new instance id/tag]


5. restore security groups

6. release old elastic ip

7. release old ami/snapshot
[ in the old account, modify permission to remove the shared ami for the new account number;
deregister the ami
]
[ in the old account, modify permission for the snapshot;
delete the snapshot
delete the volume
]

8. delete key pairs
9. delete security groups