How to configure MySQL RDS with External Replica

If you are using a DB instance which is managed by AWS then no need to any secondary replicas. But in case if you need any additional replica for your internal purpose or migrate your RDS to On-prem Mysql instance or MySQL running on EC2 instance then you need a secondary replica.

Pre-requirements:

  • Master - MySQL RDS instance – DB engine version 5.6 or later.
  • Slave - MySQL running on On-Prem or EC2 instance.
  • Replication user – Create a user for replication on Master.
  • Network – Both master and slave must communicate with each other.

Prepare the Master:

Make sure you must have enabled automatic backups. If you enabled this then bin log is enabled automatically.

Create a user for replication and grant replication privilege.

CREATE USER 'replica'@'%' IDENTIFIED BY 'slavepass';  
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';  

Prepare Secondary:

In RDS, the MySQL database has some additional tables, it’ll replicate those tables too. So before configuring replication, we have to create those tables on Slave’s MySQL database.

CREATE TABLE `rds_configuration` (  
  `name` varchar(100) NOT NULL,
  `value` varchar(100) DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



CREATE TABLE `rds_global_status_history` (  
  `collection_end` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `collection_start` timestamp NULL DEFAULT NULL,
  `variable_name` varchar(64) NOT NULL,
  `variable_value` varchar(1024) NOT NULL,
  `variable_delta` int(20) NOT NULL,
  PRIMARY KEY (`collection_end`,`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


 CREATE TABLE `rds_global_status_history_old` (
  `collection_end` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `collection_start` timestamp NULL DEFAULT NULL,
  `variable_name` varchar(64) NOT NULL,
  `variable_value` varchar(1024) NOT NULL,
  `variable_delta` int(20) NOT NULL,
  PRIMARY KEY (`collection_end`,`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


 CREATE TABLE `rds_heartbeat2` (
  `id` int(11) NOT NULL,
  `value` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `rds_history` (  
  `action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `called_by_user` varchar(50) NOT NULL,
  `action` varchar(20) NOT NULL,
  `mysql_version` varchar(50) NOT NULL,
  `master_host` varchar(255) DEFAULT NULL,
  `master_port` int(11) DEFAULT NULL,
  `master_user` varchar(16) DEFAULT NULL,
  `master_log_file` varchar(50) DEFAULT NULL,
  `master_log_pos` mediumtext,
  `master_ssl` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `rds_replication_status` (  
  `action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `called_by_user` varchar(50) NOT NULL,
  `action` varchar(20) NOT NULL,
  `mysql_version` varchar(50) NOT NULL,
  `master_host` varchar(255) DEFAULT NULL,
  `master_port` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `rds_sysinfo` (  
  `name` varchar(25) DEFAULT NULL,
  `value` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `host` (  
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges;  Merged with database privileges';

Set server-id = 2 for making this server as a slave.

vi /etc/mysql/my.cnf  
[mysqld]
server-id               = 2  

Save the file and restart MySQL.
service mysql restart

Dumping the Master Databases:

Before dumping the database you have to make a note of the bin-log and its position.
Run show master status on the master server.
Here I’ll dump powerupcloud and bhuvanesh databases;

mysqldump --single-transaction --routines --triggers --events -h XXX.ciqk2zkqxh3j.us-east-1.rds.amazonaws.com -u powercloud ---databases powerupcloud bhuvanesh> dbbackup.sql  

Once the backup is done import the backup file to Slave.

mysql -u root -p < dbbackup.sql  

Configure Replication:

Run the below command to configure replication.

CHANGE MASTER TO  
master_host = 'RDS END Point name',  
master_port = 3306,  
master_user = '<Replication user>',  
master_passwowrd = '<Replication users pasword>',  
master_log_file='<bin log file name-noted from Master>',  
master_log_pos=<Log position-noted from master>;  
CHANGE MASTER TO  
MASTER_HOST='XXX.ciqk2zkqxh3j.us-east-1.rds.amazonaws.com',  
MASTER_USER='repl',  
MASTER_PASSWORD='slavepass',  
MASTER_LOG_FILE='mysql-bin-changelog.000241',  
MASTER_LOG_POS=649;  

Then start the slave;

start slave;  

Check the slave status.

show slave status\G;  

Test the replication is working or not:

Create a database on master and it’ll be replicated to Slave.
On Master:

On Slave:

Known Issues:

Error 'Unknown or incorrect time zone: 'UTC'' on query. Default database: 'XXX'. Query: 'BEGIN'

Check the time zone on Master before configuring replication and set the same on Slave.

comments powered by Disqus