{"id":15667,"date":"2014-10-31T22:55:40","date_gmt":"2014-10-31T17:25:40","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=15667"},"modified":"2014-10-31T22:55:40","modified_gmt":"2014-10-31T17:25:40","slug":"migrate-from-external-mysql-to-aws-rds","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/migrate-from-external-mysql-to-aws-rds\/","title":{"rendered":"Migrate from External MySQL to AWS RDS"},"content":{"rendered":"

To import data from a MySQL database that is running external to Amazon RDS, you can configure replication from that database to a MySQL RDS instance. The external MySQL database you are importing from can be running either on-premises in your data center, or in an Amazon EC2 instance. The MySQL RDS instance must be running either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later. Using replication to import the data reduces downtime.<\/span><\/p>\n

The following list shows the steps to take. Each step is discussed in more detail in later sections.<\/span><\/p>\n

    \n
  1. Prepare a MySQL RDS\u00a0instance that is either MySQL 5.5 version or later, configure it to be a read replica of the MySQL instance running external to Amazon RDS.<\/span><\/li>\n
  2. Configure the MySQL instance running external to Amazon RDS to be the replication source.<\/span><\/li>\n
  3. Use\u00a0mysqldump\u00a0to import the database from the MySQL instance running external to Amazon RDS into the Amazon RDS instance.<\/span><\/li>\n
  4. Start replication to the MySQL RDS\u00a0instance to capture updates made since the mysqldump files were created. Because the source MySQL DB instance is not in Amazon RDS, you do not use the standard Amazon RDS process to create a replica, you instead use stored procedures to establish replication.<\/span><\/li>\n
  5. After the import completes, point the applications to the MySQL RDS instance. Stop replication by running stored procedures that terminate the replication process. After the stored procedures are run, the replica runs as a standard MySQL DB instance without any need to promote the instance.<\/span><\/li>\n<\/ol>\n

     <\/p>\n

    Create MySQL RDS Instance :<\/strong><\/span><\/p>\n

      \n
        \n
      • Select RDS Instance type that can handle your production workload and configure it by the DB parameter group.<\/span><\/li>\n
      • Add rules in the ACL to allow connection from the external non \u00a0AWS MySQL server.<\/span><\/li>\n<\/ul>\n<\/ul>\n

        Prepare MySQL Master Server :<\/span><\/strong><\/p>\n

          \n
        • Set up the server id in the my.cnf file and that should be unique and\u00a0cannot match any other server-id in your replication group.<\/span><\/li>\n<\/ul>\n
          server-id               = 1\r\n\r\n<\/pre>\n
            \n
          • Next step is to set log_bin file path, this is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log<\/span><\/li>\n<\/ul>\n
            \r\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\r\n\r\n<\/span><\/pre>\n
              \n
            • Now we need to select the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.<\/span><\/li>\n<\/ul>\n
              binlog_do_db            = database_name\r\n\r\n<\/pre>\n
                \n
              • Restart MySQL in last to apply all changes that we have made in my.cnf file.<\/span><\/li>\n<\/ul>\n
                \r\nsudo service mysql restart\r\n\r\n<\/span><\/pre>\n
                  \n
                • Login on mysql and create a replication User and grant permission to that user.<\/span><\/li>\n<\/ul>\n
                  \r\nGRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';\r\nFLUSH PRIVILEGES;\r\n\r\n<\/span><\/pre>\n
                    \n
                  • Now we make a write Lock on the database so that no write operation can perform on all database till we take the complete dump of DB.<\/span><\/li>\n<\/ul>\n
                    \r\nUSE database_name;\r\nFLUSH TABLES WITH READ LOCK;\r\nSHOW MASTER STATUS;\r\n\r\n<\/span><\/pre>\n
                    You will get a table as output of this command that contains bin_log file name, bin_log position and Database name, bin_log is the position from where slave start replication.<\/span><\/p>\n
                    \r\nmysql> SHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000001 |      107 | database_name  |                |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysqldump -u root -p --opt database_name > database_name.sql;\r\nUNLOCK TABLES;\r\n\r\n<\/span><\/pre>\n
                    Start Replication:<\/span><\/strong><\/p>\n
                      \n
                    • Source Database dump from the non AWS MySQL server to RDS Instance with using MySQL source command.<\/span><\/li>\n
                    • Run the\u00a0<\/span>mysql.rds_set_external_master<\/code>\u00a0procedure on the MySQL DB instance to configure it as a read replica of the MySQL instance running external to Amazon RDS. Specify the connection information for connecting to the MySQL instance running external to Amazon RDS, the replication user credentials, and the binary log location.<\/span><\/li>\n<\/ul>\n
                      \r\ncall mysql.rds_set_external_master('Sourcedb.com',3306,'Slave_user','Password','mysql-bin.000001',107,0);\r\n\r\n<\/span><\/pre>\n
                        \n
                      • Run the\u00a0<\/span>mysql.rds_start_replication<\/code>\u00a0procedure on the Amazon RDS instance to start the replication process.<\/span><\/li>\n<\/ul>\n
                        \r\nCALL mysql.rds_start_replication;\r\n<\/span><\/pre>\n
                        Run the MySQL\u00a0SHOW SLAVE STATUS<\/code>\u00a0statement on the Amazon RDS instance to verify that it is operating as a read replica. \u00a0<\/span><\/p>\n
                        \r\nShow Slave Status;\r\n\r\n<\/span><\/pre>\n
                        Stop Replication :<\/strong>\u00a0<\/span><\/p>\n
                          \n
                        • After the databases have been replicated and you have verified the databases are synchronized, change all applications and services to connect to the MySQL DB instance. Run the\u00a0mysql.rds_stop_replication<\/code>\u00a0procedure to stop the replication process.<\/span><\/li>\n<\/ul>\n
                          \r\nCALL mysql.rds_stop_replication;\r\n\r\n<\/span><\/pre>\n
                            \n
                          • Verify that the applications and services are running correctly. Run the\u00a0<\/span>mysql.rds_reset_external_master<\/code>\u00a0procedure to remove the replication configuration.<\/span><\/li>\n<\/ul>\n
                            \r\nCALL mysql.rds_reset_external_master;\r\n\r\n<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"
                            To import data from a MySQL database that is running external to Amazon RDS, you can configure replication from that database to a MySQL RDS instance. The external MySQL database you are importing from can be running either on-premises in your data center, or in an Amazon EC2 instance. The MySQL RDS instance must be […]<\/p>\n","protected":false},"author":678,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":16},"categories":[1174],"tags":[76],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/15667"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/678"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=15667"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/15667\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=15667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=15667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=15667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}