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:
- Getting Azure Subscription, Creating App Service Plan and WordPress Web Service using My SQL In App
- Connecting Word Press website with WordPress.com and Installing and configuring Jet Pack
- Creating Azure Database for MySQL and exporting the Data from My SQL In App to Azure Database for MySQL Server, Handling Security
- Creating Azure Storage Account and configuring WordPress to use Azure Storage to store media files and Optimizing with persistent connection
- Configuring Custom domain, SSL Certificates.
- Configuring Azure CDN, configuring back up scheduling in azure and Auto Scaling
- How to use REDIS CACHE with WORDPRESS on Azure
- 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.
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.
2 Select Azure MySQL
Select “Azure Database for MySQL (preview)” and click on “Create”
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”
4 Enter Information for Azure MySQL instance, choose pricing tier
Job is submitted to Azure and it may take few minutes to complete.
5 Job is submitted to Azure
Click on “Go to Resource”
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.
7 Disabled the SSL for Database Server.
Click on “Disabled” it will show like below, and click on “Save” button.
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”
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.
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.
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.
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.
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.
14 Enter Credentials
15 Get Values for Server name and User ID
For Database name, Click on “Manage” in “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”
17 PhpMyAdmin export database
Now fill out your “Export” form and click save
18 Export to New database instance
If you get below error then
19 Export Error
Add below IP address range to allow all the IP address to connect with your database server and try again.
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.
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/
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
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}
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”
25 Saving Connection String in Application Settings
Now switch “MySQL in App” to “Off” and click on “Save”
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.
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.
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”