Service consulting FAQ

Is the amount of storage space for data backups limited?

Data backups do not count toward your storage limit.

Is there a limit to the number of databases on a single server?

You can create multiple databases on a single MySQL server, and there is no limit to the number of databases that you can create. However, multiple databases share server resources, and performance requirements rise as the number of databases grows. We recommend that you create multiple MySQL servers.

What are the current limitations of MySQL Database on Azure?

For more information about limitations, see service limitations of MySQL Database on Azure.

Why doesn’t MySQL Database on Azure support databases in MyISAM format?

The product R&D team has studied and analyzed this issue several times. They ultimately decided not to support it, for the following reasons:

  • MyISAM has flaws in terms of data integrity protection, and the flaws can cause data to be damaged or even lost. Moreover, because the flaws result primarily from design faults, there is no way to repair them without breaking compatibility.
  • Because the MyISAM input/output (I/O) operations are not the optimal solution for storage on Azure, MyISAM offers little performance advantage over InnoDB.
  • MyISAM requires significant manual repair work if data is damaged, and it cannot be adapted to a platform as a service (PaaS) operating model.
  • The cost of migrating from MyISAM to InnoDB is minimal. In most applications, migration requires changes only to the table creation code.
  • The development of MySQL is also moving toward InnoDB. For example, MySQL 5.7 doesn’t use MyISAM at all, and the system database has also been transitioned to InnoDB.

Why is the default size for new, empty database servers set to 530 MB? Why is the displayed amount of storage space that's used for the database larger than the amount of storage space that's actually used?

For performance reasons, we use two 256-MB log files for new database-instance configurations. Consequently, the storage space usage figure that you see in the Azure portal includes the size of the log files. However, the size of the log files does not change during usage.

Can I use commands to set permissions on MySQL Database on Azure?

Yes. Although the Azure portal and PowerShell command line supports setting only read-write permissions for the entire database when you create users or databases, you can use the grant command to configure user permissions in greater detail.

What is the system time set to in MySQL Database on Azure? How can I change it?

The MySQL Database on Azure system time defaults to UTC, but you can update the time by configuring an offset with the Azure portal or PowerShell. For more information, see Change time zones on MySQL on Azure.

What functions does MySQL 5.7 provide? Can you use all these functions on MySQL Database on Azure?

MySQL Database on Azure is fully compatible with MySQL Community Edition. For more information about new functions in version 5.7, see the MySQL 5.7 Release Notes. MySQL Database on Azure is compatible with the vast majority of updated functions, but it does not yet support the following functions:

  • The replication function improvements in version 5.7. This is because the MySQL Database on Azure master-subordinate sync and replication functions make certain improvements to MySQL Database on Azure Services, rather than simply using MySQL replication technology.
  • InnoDB Buffer Pool Online Resize in version 5.7.
  • Query Rewrite Plugin.
  • InnoDB Transparent Page Level Compression.
  • Password expiration.

How do I create MySQL 5.7 instances in the Azure portal?

In the Azure portal, in the left pane, select New > Database > MySQL Databases on Azure > Create and then, under MySQL version, select 5.7.

Create MySQL 5.7

I already have a MySQL Database on Azure database instance, but it is version 5.6. How do I upgrade it to 5.7?

We suggest that you understand the changes in version 5.7 before you upgrade, and that you do not upgrade until you've tested the compatibility between application client and database. If you're in a production environment, we suggest that you upgrade the database when there are fewer operations to be processed by doing the following:

Step 1: (Optional) If you are worried about the impact of compatibility issues on the production environment after the upgrade, use the database restore function to restore the existing database to a new instance. For more information, see Backup and restore a MySQL Database on Azure. We suggest that you use the Backup Now feature to make a snapshot of the existing database server and then restore this snapshot to another new instance.

Step 2: Export the current database server data by using a MySQL management tool such as MySQL Workbench or a MySQL utility such as mysqldump.

mysqldump --databases <数据库名> --single-transaction --order-by-primary -r <备份文件名> --routines -h<服务器地址> -P<端口号> –u<用户名> -p<密码>

Step 3: Create a 5.7 instance and an account in the Azure portal. To do so, in the left pane of the Azure portal, select New, perform a quick create and then, under MySQL version, select version 5.7. Also, create accounts in the original database instance through the Azure portal.

Step 4: Import the data to the 5.7 instance with the common MySQL management tool Workbench or by using the SQL phrase source <backup file name>. After you've completed the preceding steps, you can switch the connection string of the application layer to a new server to start use.

How do I downgrade an MP service layer (MP1 or MP2) to an MS service layer (MS1-MS6)?

Because MP service layers use different storage schema than do MS service layers, MySQL Database on Azure does not currently support directly downgrading from MP to MS. If you are certain that you need to perform this type of downgrade operation, first create a subordinate instance and then promote it to a master instance. The steps are shown below (a downgrade from MP1 to MS6 is given as an example; the downgrade operation for other service layers is similar).

The names of the resources involved in this example are provided in the following table:

Resource MP1 MS6
Resource group Default-MySQL-ChinaEast Default-MySQL-ChinaEast
Server mysqldemoserver mysqlmsserver
Location ChinaEast ChinaEast
Test database replicadb replicadb

Step 1: Ensure that there is at least one complete backup of the MP1 instance, and that the backup status is Ready. Depending on your settings, this process might take up to one day.

Daily backup ready

Step 2: Execute the PowerShell command to create an MS6 subordinate instance (read-only instance).

New-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName mysqlmsserver -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast -Location ChinaEast -SkuObject @{name='MS6'} -Properties @{replicationMode='AzureSlave'; creationSource=@{server='mysqldemoserver';region='chinaEast'}; version = '5.6';}

Step 3: Wait until the latency between the subordinate instance (MS6) and the master instance (MP1) is sufficiently low (for example, less than 60 seconds), and then suspend all the client program’s write operations on the MP1 master instance.

Replica latency

Step 4: Create the replicadb database on the MP1 master instance, and wait until this test database has synced to the MS6 subordinate instance. Now the data on the two instances is fully synced.

Replica database synced

Step 5: Promote the MS6 subordinate instance to a master instance. For more information, see Promote subordinate instances.

Finally, you can modify the client program connection string so that it points to the new MS6 instance, and then delete the MP1 instance.

Note

In certain circumstances it might be inconvenient to modify the client program connection string. We have provided a process for your reference in which there is no need to modify the client program connection string. The following steps do not require you to modify the client program connection string, but they might temporarily cause downtime for upper layer applications (usually for a few minutes).

Step 6: Create a manual instant backup of the MS6 instance. For more information, see Backup and restore a MySQL Database on Azure.

Step 7: Delete the MP1 instance, and then restore the manual instant backup created in Step 6 to a new MS6 instance with the same name as the original MP1 instance (in this example, mysqldemoserver).

Finally, delete the temporary MS6 instance mysqldemoserver, and then restore the client program’s normal read and write operations on the MS6 instance.