Nowadays most of the companies are switching to Cloud Computing because of the many benefits it provides like
The most popular providers of databases on the Cloud are Amazon Web Services RDS, Microsoft Azure, Google Cloud
Our tool Data loader can be used to migrate / sync data from your on-premise local databases to Cloud without writing a single piece of code.
In this article, we will show you step by step from scratch how to create a new MySQL instance and a database in Amazon AWS RDS and then load the data from local MS SQL database to MySQL database hosted on Amazon Web Server (AWS)
Lets' suppose we have a database Northwind in our local premises MS SQL Server and it has some 19 tables and we want to transfer these tables to MySQL on amazon aws rds
Before you begin, you need to have an account in Amazon AWS. If you don't have an account then you can create a new Free Trial account. Even for Free Trial account amazon will ask you for your Credit Card for verification. It will charge and reverse a small minimal amount for authentication.
Assuming we have an amazon account. The first thing we need to do is create MySQL DB Instance in Amazon cloud.
So let's begin, Fire up your browser and navigate to Amazon AWS and login into your Amazon aws RDS console
Scroll down and click on RDS in "Database" section
Then you will get the following screen. Click on DB Instances as shown below
Then you get to the following screen. In this click on Create Database button on top right side as shown below
Then Amazon AWS RDS will prompt you to choose the Database Engine which you want to create. The available choices are
Since we wanted a MySQL database, click on MySQL option and scroll down and click Next.
Note: You can check the "Only enable options eligible for RDS Free Usage Tier" checkbox if you are using Free Trial. This will help in preventing unnecessary billing for using paid features. The Amazon RDS Free Tier helps users to acquaint themselves about managed database service in the cloud. Besides this, it also helps in testing existing applications or developing new applications.
After clicking the Next button, you will be prompted to specify several database settings for the new MySQL database which you are about to create.
Since we check the Free Tier option in the previous screen, we will get only those options which are allowed in Free Tier.
You can accept the default values for most of the parameters. Like MySQL license, MySQL version, DB Instance Class, Allocated Storage Type etc.
You just need to enter the following
Enter these settings according to your choice and do remember and note down the Master Username and Password. You will need to enter this username and password later on to connect to this MySQL instance. After entering these values, click Next.
When you click Next button, Amazon RDS will show you a "Configure advance settings" page as shown below
In this page, accept all the default values and scroll down and enter the name of New MySQL database which you want to create. Since after creating the instance we need at least one database, so here please specify the name of the new MySQL database. Here we have chosen "db1" you can choose whatever you like and click "Create Database"
After you click "Create Database" button you will get the following screen and it will take some time to create a new database instance and a new database.
After sometime, you can click on RDS - > DB Instance in Amazon aws console and you will find the MySQL instance created.
Now to connect to this MySQL instance from other software application such as Data Loader we need to know this instance address. To know the address click on the mysql1 instance in the above page, and you will get to this
Here we have circled the important settings. These are Endpoint, Port, Security group.
The Endpoint is the host address for this MySQL instance, You will need to type in this as hostname in third party applications such as Data Loader.
Next is port which is default 3306.
Next important thing is the Security Group. The amazon aws will by default add a security group to allow connection to this MySQL instance from your own IP address. That means only you can connect to this MySQL instance. If you want to allow access to this MySQL instance from other IP addresses then you need to add a new inbound rule to allow other IP's. To modify the INBOUND security rule click on Security above on right-hand side in the above page and click Inbound Tab and click Edit as shown below
Now according to your choice you can edit the existing rule or add new rule. Here we will edit the existing rule to allow connections from anywhere
Select Anywhere as shown above and click the Save button.
This finishes our installation of MySQL on Amazon Cloud and we are now ready for transferring data to this database.
Since we wanted to transfer tables from our local MS SQL Server database to MySQL database on cloud, we will now start Data Loader tool (If you do not have this tool, then you can download and install it by clicking here Download Data Loader).
Start Data Loader and select MS SQL Server as Source and MySQL as the target as shown below
Click Next Button, Data Loader will prompt you to connect to your MS SQL Server database. Type in your MSSQL hostname, username, password and click connect button. Once connected successfully, you can select your desired database from download list and once you select the desired database in the dropdown list, it will show the tables in that database on the right-hand side, You can select the tables you want to migrate by clicking on this list. Since we want to transfer all tables in Northwind Database we have selected all the tables, please see below
After selecting the tables which needs to be migrated, click on Next button and Data Loader will now prompt you to connect to the Target MySQL database
Here we need to enter the Endpoint-which we have noted earlier, as hostname and Master username and password which we have specified at the time of creation of MySQL Instance
The Endpoint in our case is : mysql1.c6yqsetfdsre.ap-south-1.rds.amazonaws.com
Port is: 3306
Username: <master username which you have chosen at instance creation. See above>
Password: <master password which you have chosen at instance creation. See above>
After entering the required fields as shown below click on Connect button and select "db1" database from dropdown list and click Next.
Data Loader will now show you the "Source and Destination Tables" screen. In this screen you can specify loading options and other settings by clicking "Col Mapping" button for each table. Since this is optional we can skip this by clicking Next button
In the Next screen save the session and you can specify the Interval for scheduling. For the sake of this demo just accept the default and click Next
Now, in the final screen just press Start button to start the transfer.
That's it. Thanks for reading!