How to Synchronize RDS Data to a Local User-Defined Database

Key Steps to Synchronizing Data from RDS to a Local Database

  • The version of the database needs to be 5.6.16 or above
  • Set the following key parameters in my.cnf:
    server-id ###Required for slave node configuration
  • master-info-repository=file### Required for slave node configuration
  • relay-log-info_repository=file### Required for slave node configuration
  • binlog-format=ROW### Required for slave node configuration
  • gtid-mode=on###Required for enabling GTID
  • enforce-gtid-consistency=true###Required for enabling GTID
  • innodb_data_file_path=ibdata1:200M:autoextend###Using parameter backup-my.cnf in RDS physical backups
  • innodb_log_files_in_group=2###Using parameter backup-my.cnf in RDS physical backups
  • innodb_log_file_size=524288000###Using parameter backup-my.cnf in RDS physical backups
  • reset slave;####Used to reset the synchronization relationship of the local MySQL database. An error may occur during this step.
  • mysql> reset slave;
  • ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set server-id to allow either a master or a slave. Added error messages may be found in the MySQL error log.
  • The error is due to the RDS backup file including the master/slave synchronization relationship of RDS. The relationship must get removed as follows:
  • truncate table slave_relay_log_info;
  • truncate table mysql.slave_master_info;
  • truncate table mysql.slave_worker_info;
  • Restart MySQL.
  • SET @@GLOBAL.GTID_PURGED
  • =’818795a2–8aa8–11e5–95b1:1–289,8da7b8ab-8aa8–11e5–95b1:1–75′;
  • Open the compressed backup file, and you will find the xtrabackup_slave_info file. The first line indicates the GTID that the RDS used when the backup finished, and the command in the line must get executed in the local MySQLdatabase.

Conclusion

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Intelligently Generate Frontend Code from Design Files: Image Segmentation

Loading Scenes in Unity

Debugging mischiefs — A newline

AWS Network Firewall

96. Unique Binary Search Trees

Let’s get some Rammers

Automating Email Notifications for File Updates — Java WatchService & Mail API

Java Collections: Under the hood — Vector E01

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

AWS: Regions, Availability Zones (AZs) and Data Centers

Migration of a Workload running in a Corporate Data Center to AWS using the Amazon EC2 and RDS…

AWS Systems Manager Automation — Part 1

How to Create Amazon S3 Bucket