This blog post will guide you through the process of exporting your current website’s database to local MySQL. Follow the process below to export your database:
Locate and access your current database:
Your database must be remotely accessible. Check your existing hosting provider on how to access your MySQL database. Most common tool used is PHPmyadmin for accessing your database. Check out this article on How to setup PHPmyadmin for your Azure Web App if your app is already running on Azure. PHPmyadmin is browser based tool that can be used to manipulate and manage your database.
Export your database and Save it locally
Access your database using PHPmyadmin and click on Export tab.
Select Custom export method to have the ability to modify how the script should be generated. Click on GO to generate the script and save the file locally .
If you have MySQL installed on your local machine you can using mysqldump.exe utility usually found in the bin folder within MySQL folder. To export a remote database run the command in this format
D:\Program Files (x86)\mysql\22.214.171.124\bin>mysqldump -P port_number -h host_name -u mysql_user -p database_name > result_file.sql
D:\Program Files (x86)\mysql\126.96.36.199\bin>mysqldump -P 48926 -h mysqlserver.cloudapp.net -u root -p mywordpressdb > mydatabaseexport.sql
This tool offers an export wizard as shown below to export your database content. Check out the documentation for using the Export wizard to export the database
Once you have successfully exported the database, then inspect your MySQL database script to check if your application stores the site URL in the database. If yes, then update the URL to use Azure app service web app URL or custom domain if the custom domain is already pre-configured on your Azure web app. Your script is ready to be imported.
Import your database
Go to your web app Settings->Feature -> MySQL in-app to access the management settings for this feature.
- Import Feature in Azure portal : Select MySQL in App setting , under Data Import and Export select “Import” to import a remote database into MySQL In App database . Enter the remote database connection information to import the database. Note that MySQL In App uses MySQL 5.7.9 version and make sure your remote database is compatible with this version of MySQL .
- Using PHPmyadmin : To open PHPmyadmin for your MySQL in-app database . Check your SQL script and make sure the USE statement is using the database name “azuredb” . This is the database used with the connection string we provide with MySQL in-app. If the USE statement has a different database name, update it to use azuredb database. Then Click Import in the top menu in PHPmyadmin .Under File to Import, click Browse and select the script your exported from your source database. Then click Go to complete the import.
When the import is completed and successful, you can update your web app to connect to azuredb database with the imported database schema. That’s it and your database migration is completed.