Azure Cloud

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.

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

Leave a Reply