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?

Users can create multiple databases on a single MySQL server, and there is no limit to the number of databases that can be created. 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, seeservice limitations of MySQL Database on Azure.

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

The product R&D team studied and analyzed this issue on several occasions, but ultimately decided not to support it. The reasons include the following:

  1. MyISAM has flaws in terms of data integrity protection, and these flaws could cause database data to be damaged or even lost. Moreover, because these flaws are primarily due to design faults, there is no way to repair them without breaking compatibility.
  2. Because the MyISAM input/output (I/O) operations are not the optimal solution for storage on Azure, MyISAM offers little performance advantage over InnoDB.
  3. MyISAM requires significant manual repair work if data are damaged, and it cannot be adapted to a platform as a service (PaaS) operating model.
  4. The cost of migrating from MyISAM to InnoDB is minimal. In most applications, migration requires only changes to the table creation code.
  5. The development of MySQL is also moving towards 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 used for the database larger than the amount of storage space 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 will not change during usage.

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

Yes. While the Management Portal and PowerShell command line only support setting 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?

MySQL Database on Azure’s system time defaults to UTC, but you can update the time by configuring an offset with the Management 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. See the MySQL 5.7 Release Notes for more information about new functions in version 5.7. MySQL Database on Azure is compatible with the vast majority of updated functions, but does not yet support the following functions:

  • MySQL Database on Azure does not currently support the replication function improvements in version 5.7. This is because MySQL Database on Azure’s 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 to create MySQL 5.7 instances on the management portal?

Click on New in the menu on the left of the Management Portal, click on Database > , click on MySQL Databases on Azure, press Create, and then select 5.7 in the MySQL version section. Create MySQL57

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 the compatibility between application client and database is tested. In case of 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 upgrading, use the database restore function to restore the existing database to a new instance. See “Backup and restore of MySQL Database on Azure” for details. We suggest that you to 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 with 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 Management Portal: Click on New in the menu on the left of the Management Portal, perform a quick create, and select version 5.7 in the MySQL version section. 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 ”. After the preceding steps are completed, 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 to 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, it can be accomplished by first creating a subordinate instance and then promoting it to a master instance. The specific 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 given in the table below:

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 of 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. (Refer to: 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 may not be convenient 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. (See: Backup and restore a MySQL Database on Azure)

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

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