{"id":18824,"date":"2024-07-30T16:02:42","date_gmt":"2024-07-30T10:32:42","guid":{"rendered":"https:\/\/opstree.com\/blog\/?p=18824"},"modified":"2024-07-31T10:39:32","modified_gmt":"2024-07-31T05:09:32","slug":"use-case-backup-and-replication-setup-between-ec2-mysql-and-rds-mysql","status":"publish","type":"post","link":"https:\/\/opstree.com\/blog\/2024\/07\/30\/use-case-backup-and-replication-setup-between-ec2-mysql-and-rds-mysql\/","title":{"rendered":"Use Case: Backup and Replication Setup Between EC2 MySQL and RDS MySQL"},"content":{"rendered":"<p>In this blog post, we\u2019ll explore a real-world scenario where we need to migrate and replicate data between an EC2-hosted MySQL database and Amazon RDS (Relational Database Service) MySQL instance. This setup is critical for maintaining data integrity, ensuring high availability, and facilitating seamless scalability in a production environment.<!--more--><\/p>\n<h3><b>Scenario Overview<\/b><\/h3>\n<p>Imagine you have an existing MySQL database running on an EC2 instance that handles live traffic for your application. To improve scalability and reduce operational overhead, you decide to migrate this database to Amazon RDS while setting up replication to keep both databases synchronized. This ensures minimal downtime and data consistency during the migration process.<\/p>\n<h3><b>Prerequisites<\/b><\/h3>\n<ul>\n<li aria-level=\"2\">Access to an EC2 instance with MySQL installed.<\/li>\n<li aria-level=\"2\">Access to an RDS MySQL instance.<\/li>\n<li aria-level=\"2\">Basic understanding of MySQL administration and AWS services.<\/li>\n<\/ul>\n<h2><b>Part 1: Taking Backup from EC2 MySQL Using MyDumper<\/b><\/h2>\n<ul>\n<li aria-level=\"1\">Commands to take a backup of your MySQL database using MyDumper.<\/li>\n<li aria-level=\"1\">Example command:<\/li>\n<\/ul>\n<pre><i>mydumper --no-locks --host=&lt;HOST&gt; --user=&lt;USERNAME&gt; --port=&lt;PORT&gt; --password='&lt;PASSWORD&gt;' --database=&lt;DATABASE&gt; --chunk-filesize=&lt;CHUNK_SIZE&gt; --compress --threads=&lt;THREADS&gt; --outputdir=&lt;OUTPUT_DIR&gt; --logfile=&lt;LOGFILE&gt;\r\n\r\n<\/i><\/pre>\n<h2><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-18832 \" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/Screenshot-from-2024-07-30-15-06-06.png\" alt=\"\" width=\"858\" height=\"386\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/Screenshot-from-2024-07-30-15-06-06.png 945w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/Screenshot-from-2024-07-30-15-06-06-300x135.png 300w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/Screenshot-from-2024-07-30-15-06-06-768x346.png 768w\" sizes=\"(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><b>Part 2: Restore Backup to RDS MySQL Using MyLoader<\/b><\/h2>\n<ul>\n<li aria-level=\"1\">Prepare RDS MySQL Instance for Restore.<\/li>\n<li aria-level=\"1\">Example command:<\/li>\n<\/ul>\n<pre>myloader --host=&lt;HOST&gt; --compress-protocol --queries-per-transaction=&lt;QUERIES_PER_TRANSACTION&gt; --user=&lt;USERNAME&gt; --port=&lt;PORT&gt; --password='&lt;PASSWORD&gt;' --source-db=&lt;SOURCE_DATABASE&gt; --database=&lt;TARGET_DATABASE&gt; --threads=&lt;THREADS&gt; --directory=&lt;DIRECTORY&gt;<\/pre>\n<h2><b>Part 3: Setting Up Replication Between EC2 MySQL and RDS MySQL<\/b><\/h2>\n<ul>\n<li aria-level=\"1\"><b>Create Replication User: <\/b>Create a user specifically for replication on EC2 MYSQL.<\/li>\n<\/ul>\n<pre><i>CREATE USER 'repl_user\u2019@\u2018%\u2019\u00a0 IDENTIFIED BY 'Repl@#3421';<\/i><\/pre>\n<ul>\n<li aria-level=\"1\"><b>Grant Replication Permissions<\/b>: Grant the necessary permissions to the replication user. The user needs the REPLICATION SLAVE privilege to connect and replicate data.<\/li>\n<\/ul>\n<pre>GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';<\/pre>\n<p>FLUSH PRIVILEGES;<\/p>\n<p><b>Part4: Configure RDS MySQL as Slave<\/b><\/p>\n<ul>\n<li aria-level=\"1\">On your RDS MySQL instance, configure it as a slave to replicate data from the EC2 MySQL master.<\/li>\n<li aria-level=\"1\">Use mysql.rds_set_external_master procedure to set up replication parameters:<\/li>\n<\/ul>\n<pre><i>CALL mysql.rds_set_external_master('&lt;EC2_IP&gt;', &lt;port&gt;, '&lt;replication_user&gt;', '&lt;password&gt;', '&lt;binlog_file&gt;', &lt;binlog_position&gt;, 0);<\/i>\r\n\r\n<i>START SLAVE;<\/i>\r\n\r\n<i>SHOW SLAVE STATUS \\G;<\/i><\/pre>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-18840 size-full\" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/d2-1.png\" alt=\"\" width=\"512\" height=\"264\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/d2-1.png 512w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/d2-1-300x155.png 300w\" sizes=\"(max-width: 512px) 85vw, 512px\" \/><\/p>\n<p><strong>Blog Pundits: <a href=\"https:\/\/www.google.com\/url?sa=t&amp;source=web&amp;rct=j&amp;opi=89978449&amp;url=https:\/\/in.linkedin.com\/in\/deepaksood619&amp;ved=2ahUKEwjxnoep0LyHAxXui68BHQ7LAEwQFnoECBcQAQ&amp;usg=AOvVaw2bCygYqWi3T8TASKoy3-TS\" target=\"_blank\" rel=\"noopener\">Deepak Sood<\/a> and <a href=\"https:\/\/opstree.com\/blog\/\/author\/sandeep7c51ad81ba\/\" target=\"_blank\" rel=\"noreferrer noopener\">Sandeep Rawat<\/a><\/strong><\/p>\n<p><strong>OpsTree is an End-to-End <a href=\"https:\/\/opstree.com\/services\/\" target=\"_blank\" rel=\"noreferrer noopener\">DevOps Solution<\/a> Provider.<\/strong><\/p>\n<div class=\"wp-block-buttons\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"https:\/\/opstree.com\/contact-us\/?utm_source=WordPress&amp;utm_medium=Blog&amp;utm_campaign=CI%2FCD+with+GitHub+Actions+-+Concepts\" target=\"_blank\" rel=\"noreferrer noopener\">Contact Us<\/a><\/div>\n<\/div>\n<p class=\"has-text-align-center\"><strong>Connect with Us<\/strong><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, we\u2019ll explore a real-world scenario where we need to migrate and replicate data between an EC2-hosted MySQL database and Amazon RDS (Relational Database Service) MySQL instance. This setup is critical for maintaining data integrity, ensuring high availability, and facilitating seamless scalability in a production environment.<\/p>\n","protected":false},"author":244582680,"featured_media":18834,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[4419],"tags":[5469,768739300],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2024\/07\/Backup-and-Replication-Setup-Between-EC2-MySQL-and-RDS-MySQL-1-1.png","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pfDBOm-4TC","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/18824"}],"collection":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/users\/244582680"}],"replies":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/comments?post=18824"}],"version-history":[{"count":6,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/18824\/revisions"}],"predecessor-version":[{"id":18842,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/18824\/revisions\/18842"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media\/18834"}],"wp:attachment":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media?parent=18824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/categories?post=18824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/tags?post=18824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}