mysql replication from remote RDS to localhost


//=== http://dba.stackexchange.com/questions/15440/replicate-rds-mysql-to-a-non-amazon-host

[errors]

ERROR 1045 (28000): Access denied for user 'repl_user'@'%' (using password: YES)

Slave I/O: error connecting to master 'repl_user@xxxx.xxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60 retries: 11, Error_code: 1045


Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'


//=== https://www.percona.com/blog/2014/10/08/mysql-replication-got-fatal-error-1236-causes-and-cures/
max_allowed_packet
"""...
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; ...

This is a typical error on the slave(s) server. It reflects the problem around max_allowed_packet size. max_allowed_packet refers to single SQL statement sent to the MySQL server as binary log event from master to slave.

This error usually occurs when you have a different size of max_allowed_packet on the master and slave (i.e. master max_allowed_packet size is greater then slave server).

When the MySQL master server tries to send a bigger packet than defined on the slave server, the slave server then fails to accept it and hence the error.

In order to alleviate this issue please make sure to have the same value for max_allowed_packet on both slave and master.
..."""


"""...
Got fatal error 1236 from master when reading data from binary log:
‘Could not find first log file name in binary log index file’

This error occurs when the slave server required binary log for replication no longer exists on the master database server.
In one of the scenarios for this,
your slave server is stopped for some reason for a few hours/days and
when you resume replication on the slave it fails with above error.
..."""





[tips]
//=== 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] is REPLICATION CLIENT optional?



//=== on slave side(localhost)

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


* logon to localhost
mysql -uroot -p
mysql> stop slave
mysql> change master to
MASTER_HOST = 'rds.',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000526',
MASTER_LOG_POS=4;
xxx MASTER_USER = 'repl_user',
xxx MASTER_PASSWORD = 'xxx';


[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE ...


[ in mysql-5.6, for security concern
remove repl_user and repl_passwd info from change master to ;
use start slave user='...' password='...' ]
mysql> start slave user='repl_user' password='repl_passwd'