First published on MSDN on Nov 20, 2018
Scaling up from Basic Tier to General Purpose or Memory Optimized servers is not supported and the only way to achieve this is by exporting your databases from your Basic Tier server and restore the databases at a newly created server in the desired service Tier.
You can use mysqldump to backup your databases and then restore them to the destination. Below is a quick tutorial on how to do this operation using MySQL commands or MySQL workbench.
To use MySQL commands or MySQL workbench please make sure that you have MySQL Workbench installed on your machine, a tutorial can be found here: https://docs.microsoft.com/en-us/azure/mysql/connect-workbench
MySQL commands
Backup:
a- Navigate on your device to the MySQL workbench folder to get the installed path
Example: C:\Program Files\MySQL\MySQL Workbench 8.0 CE
b- Launch a cmd window and navigate to the MySQL Workbench path
Example: cd C:\Program Files\MySQL\MySQL Workbench 8.0 CE
c- Run the following command to backup a database locally
The following example will make a dump for database called: DBName and store it at: C:\Users\test\Desktop\arm error DumpFile.sql
Example : mysqldump -h server.mysql.database.azure.com -P 3306 -u user@server -p DBName > C:\Users\test\Desktop\DumpFile.sql
Note: Please dump only user databases and not system database (sys, mysql, information_schema, performance_schema) as that would cause issues during the restore process.
Restore:
a- Create a General Purpose Azure Database for MySQL server
b-Connect to your newly created server using the following command
mysqldump -h newserver.mysql.database.azure.com -P 3306 -u user@newserver -p
c- Create a blank database to restore into it, one benefit of doing it this way is to confirm that you can connect successfully to the newly created server.
create schema restoredb;
d- Switch to use the “restoredb” database
use restoredb;
e- Restore the dumpfile.sql into the “restoredb” database by running the following command
source C:\Users\test\Desktop\DumpFile.sql
Workbench
Backup:
a- Launch MySQL Workbench on your workstation.
b- Connect to your Basic Tier Database server
(referencing: https://docs.microsoft.com/en-us/azure/mysql/connect-workbench )
c- Click on the Data Export utility from the sidebar and then choose which database to Backup and the destination of the backup. Please note that you will need to select the "Include Create Schema" checkbox and then click the Start Export button.
Note: Please dump only user databases and not system database (sys, mysql, information_schema, performance_schema) as that would cause issues during the restore process.
Restore:
a- Launch MySQL Workbench on your workstation.
b- Connect to your newly created General Purpose Azure Database for MySQL server.
(referencing: https://docs.microsoft.com/en-us/azure/mysql/connect-workbench )
c- Click on the Data Import/Restore utility from the sidebar and then specify the database backup location and click Restore.
This is not limited to migrating from Basic to General Purpose/ Memory Optimized Tiers and applies to moving an Azure Database for MySQL instance to another.
The following link discusses general concepts of migrating MySQL databases in the Azure space: https://docs.microsoft.com/en-us/azure/mysql/concepts-migrate-dump-restore