Database migration is well known for being a complex and time-consuming process, especially big databases. But AWS DMS makes it very easy to accomplish. In this tutorial, I’m going to show you how to configure AWS DMS to migrate a production MySQL database to AWS RDS without any downtime.
When it comes to database migration, companies usually perform it during a low peak time, set the application to a read-only mode or introduce a maintenance mode. This requires lots of planning, creating a partnership between the IT and Business teams, analyzing the low peak time of a database usage and many additional factors.
Our goal is to ensure a smooth transition to the cloud without any impact to the business.
Our use case is as below:
A company wants to migrate their MySQL database to AWS without any manual process of mysqldump etc. They have not set a date to turn of the current database and would like to point to the AWS database at any-time. Our task is to configure DMS to perform a real-time database migration and keep it in sync.
Step 1.
Configure Binary Log in the MySQL server.
AWS DMS requires the binary log to be enabled in the source target (our MySQL database). This means we have to update the MySQL config.
# CentOS location /etc/my.cnf [mysql] expire_logs_days=2 binlog_format=row socket=mysql.sock server_id = 1
We will set the expire_logs_datys to 2, set the server ID to 1 and select row format. AWS DMS requires the above setting for it to successfully start migrating the database. MySQL needs to be restarted to reflect the new changes.
Step 2. Creating the replication task.
Go to the AWS Database Migration Service and select “Get started”. In this page, we will set a name for our replication task and configure the EC2 instance responsible for migrating the data.
The instance class is the type of the EC2 which is responsible for migrating the data. The more powerful the instance the faster it migrates.
* Select the VPC which you have access to, as you would need to modify the security group to allow the EC2 instance to connect to the Source and Target database.
Step 3: Database endpoints
In this step, we will configure the database connection details for our source (MySQL) and the target (RDS instance)
Step 4: Task
During this step, we define the table mappings such as which tables we would like to migrate to the target (RDS instance), migration type etc.
Migration types:
- Migrate Existing data
Migrate the current data from the source. If new data is added it will not be migrated. This is useful when the database is on read-only.
2. Migrate existing data and replicate ongoing changes.
Migrate the current data and any new data that has been updated. (Note: for the initial task, close any connections to the database as AWS DMS will perform a bulk load and it could impact the source database. Once, it is 100% Sync turn on the application)
3. Replicate data changes only.
If the data has been already imported and you are planning to just sync the data (example: you have imported the data manually and now decided to keep in sync with the RDS instance)
Task Settings
In this section, we will configure to Drop tables on target (RDS instance) and enable logging. It is important to enable logging as it is the only source of identify errors happening during the sync.
Enable validation ensures that the data in the target matches the data in the source.
Table Mappings
In this section, we can select which tables we wish to migrate to the RDS instance. Since we are planning to migrate the entire database I have selected the database in the “Schema name is” drop-down. You can create multiple selection rules. Once, you are happy with the selection click on the “Add Selection Rule” button and then click on “Create task” button.
You will be redirected to the “task” page and then to start the migration task, click on the “Start/Resume” button. The status will change to “Starting”.
By clicking the “Table statistics”, you view the progress of the migration.
If you are planning to migrate a database, AWS DMS helps you automate and simplify the process. If your migration task has failed, please leave a comment below.
What if an import task fails?
There might be scenarios when a migration is incomplete due to a failure. Since we have enabled logging we can easily find out the problem.
Search directly in the Cloudwatch logs by entering “E:” This will display the log lines that consist of an error.
For example in our case, the RDS instance cannot insert the statement.
I’m a passionate engineer based in London.
Currently, I’m working as a Cloud Consultant at Contino.
Aside my full time job, I either work on my own startup projects or you will see me in a HIIT class 🙂