Reset RDS MySQL replication

reset-rds-mysql-replication.txt

# On slave
# ========

mysql> CALL mysql.rds_stop_replication;
mysql> CALL mysql.rds_reset_external_master;

# Drop & recreate replicated databases
mysql > DROP DATABASE <xxx>;
mysql > CREATE DATABASE <xxx>;

# On master (keep MySQL shell open)
# =================================

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

# Take note of 'File' and 'Position' in the output:
mysql > SHOW MASTER STATUS;

# On master (new window)
# ======================

mysqldump \
    --databases <database_name> <...> \
    --single-transaction \
    --order-by-primary > dump.sql
    
# This is required for views because the DEFINER user may be different in master & slave
sed -i 's/DEFINER=[^*]*\*/\*/g' dump.sql 

# On master, back to the MySQL shell left open
# ============================================

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES; 

# On master
# =========

mysql \
        --host=xxxxx.yyyyy.zzzzz.rds.amazonaws.com \
        --user=<rds_user> \
        --password=<rds_password> \
        --compress < dump.sql

# On slave
# ========

# Replace <File> and <Position> with the values from above
mysql> CALL mysql.rds_set_external_master ('mysql-master.example.com', 3306, 'replication_user', 'password', '<File>', <Position>, 0);
mysql> CALL mysql.rds_start_replication;