rds slave from external master

🔗Setup AWS MySQL RDS instance as slave of an external master

Configure external mysql to become master:

server-id=1
binlog-format= mixed
log-bin=mysql-bin
binlog-do-db=databasename

Setup the master replica user:

mysql> create user `replica`@`%` identified by 'PASSWORD';
mysql> grant replication slave on *.* to 'replica'@'%';

Use the following command to ensure that nothing can write to the master database during a database dump. Also note the filename and position of the binary log because you will need these values to complete the replication configuration:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+-------------------+-----------+--------------+--------------------------------------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB                                 |
+-------------------+-----------+--------------+--------------------------------------------------+
| mysqld-bin.000205 | 207450664 | epazote      | test,information_schema,performance_schema,mysql |
+-------------------+-----------+--------------+--------------------------------------------------+
1 row in set (0.00 sec)

If this is a replica do this:

mysql> show slave status\G

And look for:

Master_Log_File: mysql-bin-changelog.023713
Read_Master_Log_Pos: 1934704

Within AWS RDS you can also do:

mysql> call mysql.rds_stop_replication;
mysql> show slave status\G

Take a dump:

mysqldump -h host.tld --user='user' --password='secret' --add-drop-database --compress --hex-blob --opt --skip-comments --skip-lock-tables --single-transaction db_name > out.sql

to dump all databases use --all-databases

To get the the log position this can be used:

--master-data=1

example:

mysqldump -h host.tld --master-data=1 --compress --hex-blob --opt --skip-comments --skip-lock-tables --single-transaction db_name -u user -p > out.sql

Then, lift the read lock from the master:

mysql> UNLOCK TABLES;

or within AWS use:

mysql> call mysql.rds_start_replication;

If you get a Last_IO_Error: Got fatal error 1236..., if you can afford it, don't unlock/start the replication after having the dump

Load the dump:

mysql --compress -h host.tld -u user database < out.sql

--compress seems to improve the speed

After loading a full dump to RDS, Call the stored procedure mysql.rds_set_external_master like this:

mysql> call mysql.rds_set_external_master ('servername', port, 'user', 'password', 'binlog-file', binlog-offset, 0);

Example:

mysql> call mysql.rds_set_external_master ('10.10.3.2', 3306, 'replica', 'password', 'mysql-bin-changelog.122', 108433, 0);

Then start the replication by doing:

mysql> call mysql.rds_start_replication;

Check if slave is running:

mysql> show slave status;

🔗Promote RDS to master

This will promote the slave to become master:

mysql> call mysql.rds_stop_replication;
mysql> call mysql.rds_reset_external_master;

🔗mysql.rds_set_configuration

In Amazon RDS, The default value of binlog retention hours is NULL (do not retain binary logs), to keep logs for 7 days use:

call mysql.rds_set_configuration('binlog retention hours', 168);

This could help to prevent getting the 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', Error_code: 1236

IT notes