Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security: Part 3

//Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security: Part 3

Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security: Part 3

Are you looking to create your very own Scalable optimized WordPress Site/blog using Azure MySQL, Azure Storage and Azure CDN, then this post series is for you?

I have divided the series into below parts:

  1. Getting Azure Subscription, Creating App Service Plan and WordPress Web Service using My SQL In App
  2. Connecting Word Press website with WordPress.com and Installing and configuring Jet Pack
  3. Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security
  4. Creating Azure Storage Account and configuring WordPress to use Azure Storage to store media files and Optimizing with persistent connection
  5. Configuring Custom domain, SSL Certificates.
  6. Configuring Azure CDN, configuring back up scheduling in azure and Auto Scaling
  7. How to use REDIS CACHE with WORDPRESS on Azure
  8. How to install application insights in WordPress on Azure

If you are not following along please check the previous part 1 first.

3.Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security

At this point, your site is connected with WordPress.com now we are moving to part 3, which is Creating Azure Database for MySQL, and exporting the Data from My SQL in App to Azure Database for MySQL server, we will also handle security and will optimize with persistent connection.

Let us go to https://Portal.Azure.com/

Now go to All Resources and then click “Add” button, type “Azure MySQL” in search area and hit enter.

Search for Azure MySQL

1 Search for Azure MySQL

Right now we are interested in the first item, “Azure Database for MySQL (preview)” currently it is in preview but by the time you view this post, may be it is released to public as well.

Select Azure MySQL

2 Select Azure MySQL

Select “Azure Database for MySQL (preview)” and click on “Create”

Create Azure MySQL

3 Create Azure MySQL

At this point, add the Server Name, choose Resource Group, you can choose existing one, mention password and then for Pricing Tier choose “Basic” as this is our blog site it usually don’t need high throughput unless you are top blogger on internet, click “OK and complete the information and click “Create”

Enter Information for Azure MySQL instance, choose pricing tier

4 Enter Information for Azure MySQL instance, choose pricing tier

Job is submitted to Azure and it may take few minutes to complete.

Job is submitted to Azure

5 Job is submitted to Azure

Click on “Go to Resource”

Deployment completed.

6 Deployment completed.

Now we will disable the SSL enforce Status to false and we will add our IP Address and our Azure Web Service app’s IP Addresses, so that our WordPress site can access it. I will write an article on how to enable SSL in Azure MySQL so that it can communicate with our WordPress Site on encrypted channel, since it needs some changes in WordPress site. For now we are going to disable the SSL but we will add Connection security next.

Disabled the SSL for Database Server.

7 Disabled the SSL for Database Server.

Click on “Disabled” it will show like below, and click on “Save” button.

SSL Disable

8 SSL Disable

In mean while we will change our sites settings as below: Platform 64bit usually put more performance through put than 32 bit variant and Always On will keep your site in ram which will speed up the access time in case your WordPress site is not accessed for a long time and Click on “Save”

Optimizing Site Response Time

9 Optimizing Site Response Time

Now click on “Properties” under “settings” heading and copy all the IP address under “OUTBOUND IP ADDRESSES”, we will use these IP Address and add them in MySQL Server, so that our WordPress Site can access the Server without problem.

App Service IP Addresses

10 App Service IP Addresses

Now Add All the IP Address in “Connection Security” window and click on Save, use the Same IP Address for “START IP” and “END IP”, please make sure to add 1 IP Address at a time, since we have at least 4 or more IP Addresses copied from above window.

Connection Security

11 Connection Security

Your screen will look like below, and of course, IP Addresses will be different in your case, Add All IP Address and click on “Save” Button.

Insert IP Addresses of App Service

12 Insert IP Addresses of App Service

Now we are ready to move our database from MySQL in App to Azure MySQL. Go to “MySQL in APP” and click on export.

Export MySQL Database

13 Export MySQL Database

Now fill the information, if you do not know the Username and Database and Server, then you can always get this information from the MySQL Database instance.

Enter Credentials

14 Enter Credentials

Get Values for Server name and User ID

15 Get Values for Server name and User ID

For Database name, Click on “Manage” in “MySQL in APP”

Manage MySQL in App

16 Manage MySQL in App

A new window will open, if it prompt for login, please login. In our case, we have five databases listed as below: as we know that 4 out of 5 are MySQL databases (information_schema, mysql, performance_schema, sys) so this left us with one database “localdb”

PhpMyAdmin export database

17 PhpMyAdmin export database

Now fill out your “Export” form and click save

Export to New database instance

18 Export to New database instance

If you get below error then

Export Error

19 Export Error

Add below IP address range to allow all the IP address to connect with your database server and try again.

Enable IP Address for All

20 Enable IP Address for All

If for some reason you are unable to proceed then we can proceed for Export Option, Go to “Manage” in “MySQL in APP” and click on “Export” and click “Go”, save the resultant script to the local computer.

Exporting Databases

21 Exporting Databases

Now we will use Workbench to connect with MySQL database server, follow the link to download https://www.mysql.com/products/workbench/
Installing Workbench

22 Installing Workbench

There is an excellent documentation on Azure Docs, which explains how to connect to the MySQL server-using workbench

https://docs.microsoft.com/en-us/azure/mysql/connect-workbench

Now I assume you have downloaded and successfully connected to MySQL sever. Please follow below link navigate to “Data Import” heading, just connect to server, click option “Import from Disk” navigate to the file which we saved earlier in export part and click “Start Import”, everything supposed to work fine.

https://docs.microsoft.com/en-us/azure/mysql/concepts-migrate-import-export

Importing databases using Workbench

23 Importing databases using Workbench

Now is the part for connecting your WordPress to new MySQL server. Please proceed for following, Go to database server, “Connection strings” and then copy the connection string for “Web App” and modify it as per your MySQL Server credentials.

Database={your_database}; Data Source={your_databaseserver}; User Id={your_UserId}; Password={your_password}

Copy Connection string

24 Copy Connection string

Now go to “Application Settings”, add “Connection strings”, and add connection string with Name MYSQLCONNSTR and value with your modified Web App connection string and click “Save”

Saving Connection String in Application Settings

25 Saving Connection String in Application Settings

Now switch “MySQL in App” to “Off” and click on “Save”

Shutdown MySQL in App Instance

26 Shutdown MySQL in App Instance

Now Access you Site, it should be working fine. If you are getting any errors at this point, make sure that your credentials are correct.

Successfully did the Import

27 Successfully did the Import

Congratulations! You have successfully exported your MySQL Database to Your New Azure MySQL Instance.

After you are done, Please do not forget to remove the IP Address that we added to enable all to connect to over MySQL Server.

Delete Allow All IP Addresses

Delete Allow All IP Addresses

Upcoming next Creating Azure Storage Account and configuring WordPress to use Azure Storage to store media files.

By |2018-10-11T09:15:08+00:00October 12th, 2017|Azure Cloud|17 Comments

About the Author:

17 Comments

  1. […] Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for My… […]

  2. […] Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for My… […]

  3. […] Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for My… […]

  4. Tamojit December 7, 2018 at 2:29 pm - Reply

    I am unable to export data from Mysql in app to mysql. Every time it throws same connection error but when I try to import database it import easily.

    • ScientistZ December 7, 2018 at 3:56 pm - Reply

      May be this is because you need to open the port 6603 in order to open up a connection.

  5. Tamojit Goswami December 7, 2018 at 3:08 pm - Reply

    How can I migrate from MySQL In App to another MySQL In App? Please I need it urgently.

    • ScientistZ December 7, 2018 at 3:57 pm - Reply

      it is totally possible, all you need is to export the data from one MySQL in APP and Import into an other, it will be done through php admin for my sql. good luck!

      • Tamojit Goswami December 9, 2018 at 5:42 pm - Reply

        Thanks for your valuable reply. I think maximum file size should be 8192 kb.

        Can you please provide me any documentation for the steps. Thank you.

  6. Tamojit Goswami December 10, 2018 at 10:35 am - Reply

    When we try to Import in phpmyadmin, if file size greater than 8192 kib, then what to do?

    • ScientistZ December 10, 2018 at 11:13 am - Reply

      You can use mysql migration wizard if u are getting the file size issue

      • Tamojit Goswami December 10, 2018 at 1:06 pm - Reply

        Is it using Workbench? I am not sure how to do using mysql migration wizard.

        • ScientistZ December 10, 2018 at 1:08 pm - Reply

          Yes using workbench, all u need to do is enter source and target and make sure both are accessible and start migration process. Good luck!

          • Tamojit Goswami December 10, 2018 at 1:32 pm

            I am unable to getting you. I just mention you the steps,if anything missing plz rectify.

            Export the source db using phpmyadmin and save the exported file into localy.
            open Workbench and select Import option, Choose db file that want to import and select database where I want to import.
            Now goto Target Web app where to migrate and open “MySQL in App” and clicks on “Import & Export” option.
            In next screen choose Import and provide connection string/credintial which I used in Workbench to connect with MySQL server. (In this step I got error every time. It says that plz check your connection detail.)

            I got stuck in here every time.

          • Tamojit Goswami December 10, 2018 at 1:54 pm

            I got error every time when import, and don’t know all my steps has been removed from his post.

          • ScientistZ December 10, 2018 at 2:50 pm

            Are you able to connect to both databases and make sure the user you are using have appropriate permission.

  7. Tamojit Goswami December 11, 2018 at 7:36 am - Reply

    I am able to connect database through phpmyadmin. Means I open the app and clicks on “Manage”. For Source and target both the app I can “Manage” and open phpmyadmin.

    But how can I check user permission? Do you want to say that can I connect my Mysql server from phpmyadmin?

    • ScientistZ December 11, 2018 at 8:35 am - Reply

      Using workbench try to open a new query against target database and run this command “SHOW GRANTS yourusername”

Leave A Comment

Skip to toolbar