mysql replication set up


mysql replication : slave(read replica) read data from master (source instance)
[ user@slave connect to master to read data
instead of
user@master connect to slave to write data ]


//=== https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

0. On the master, you must enable binary logging and configure a unique server ID.
[ /etc/mysql/my.cnf
bind-address = 172.31.42.230
server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
log_bin= myrds-bin # /var/lib/mysql/myrds-bin.000x
]


1. On each slave to connect to the master, configure each with a unique server ID

2. create a separate user that will be used by your slaves to logon/connect to the master
to read the binary log for replication.


[ex]
on the master, to set up a new user, repl, that can connect for replication "from" any slavehost.myslaves.com
--> @%.myslave.com

on the master
mysql> CREATE USER 'repl'@'%.myslaves.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.myslaves.com';



//=== Replication with a MySQL Instance Running External to Amazon RDS
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.External.Repl.html

master instance : source instance, mysql instance external to RDS
slave instance : read replica, RDS mysql instance

0. master -
add binlog_do_db=target_db_name to /etc/mysql/my.cnf
$ sudo service mysql restart

1. master - make the source instance read-only:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only= ON;

2. master - to determine the binlog location.
mysql > SHOW MASTER STATUS;

File Position
------------------------------------
myrds-bin.000031 107


3. master - mysqldump to export; slave - mysql to import
mysqldump --databases target_db_name --single-transaction --compress --order-by-primary
–u local_user -plocal_password | mysql --host=slave –-port=3306 –u slave_user_name –pslave_password
[ why need --single-transaction ? ]

4. master - make the source instance writeable again:
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;


5. slave - To find the IP address of the slave instance ( RDS MySQL instance)
host RDS_MySQL_DB_host_name

master- allow the slave IP to connect to master instance(external mysql instance) for replication read.

6. master- create user 'repl_user' for replication
mysql> CREATE USER 'repl_user'@'%.myslaves.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repl_user'@'%.myslaves.com';



7. slave -
mysql > stop slave
mysql > change master to ...[set up master info so slave can connect to master properly]
mysql > start 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





//=== http://dba.stackexchange.com/questions/76194/add-database-to-existing-replication-which-uses-binlog-do-db
[ex]
to copy a new database, database4, onto a slave that already has 3 databases replicating,
the problem then is that replication will need to be paused whilst you do this.


1) MASTER - restart the master with new binlog-do-db ( remove all binlog-do-db in order to start populating the binary log with database4's data)

2) SLAVE - Check and wait for the slave Database's replication up to date (SHOW SLAVE STATUS > Seconds_Behind_master).

3) MASTER - mysql> FLUSH TABLES WITH READ LOCK; (to prevent new data from entering the master)

USE database4;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
[write down the binlog filename and position]

4) SLAVE - Check replication is fully caught up and no data is replicating
(read_master_log_pos and exec_master_log_pos should be the same and not changing in "show slave status;")

5) SLAVE - STOP SLAVE; (to stop any data replicating into slave)

6) Master - Run MySQLDUMP with --single-transaction option

7) Master - Once MySQLDUMP has started running unlock the master Database by
mysql> UNLOCK TABLES; ( hopefully keeping downtime to a minimum )

* MySQLDUMP may lock the database4 schema while it works depending on your system

8) Slave - Once MySQLDUMP has completed, import it into the slave Database

9) Slave - check the newly imported database4 looks correct

10)Slave - adding replicate-do-db=database4 to the my.cnf file and restart the slave instance.



//===
[Q] how to set replicate_do_db on rds mysql slave?
--> seems not supported yet by RDS.

http://stackoverflow.com/questions/18370746/can-you-replicate-a-specific-database-or-table-using-amazons-rds

"""...
By default RDS simply just replicates the entire master's databases over to the slave's.
But we only want to do specific tables.
...

These settings exists in MySQL, I do not see them on the custom parameter settings for RDS ...

--replicate-ignore-db=db_name
--replicate-ignore-table=db_name.tbl_name
..."""



//=== reference links
https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.External.Repl.html

http://dba.stackexchange.com/questions/76194/add-database-to-existing-replication-which-uses-binlog-do-db

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html