Azure Database for MySQL – Flexible Server – CLI cheat sheet and sample scripts

[mk_sunitha]

Azure Database for MySQL - Flexible Server is a fully managed database service designed to provide more granular control and flexibility over database management functions and configuration settings. This developer friendly service helps enhance productivity by reducing the overhead associated with managing a server running MySQL.

Note: Azure Database for MySQL - Flexible Server currently supports MySQL community versions 5.7 and 8.0. 

This “cheat sheet” is designed to guide readers through the basics of using the Azure CLI with Azure Database for MySQL -Flexible Server. This information can be helpful for beginners and those seeking an overview of important scenarios for the service or wanting to automate common administrative tasks. Together with important commands and usage examples, the content contains links to related sample CLI scripts that are provided in the article Azure CLI samples - Azure Database for MySQL - Flexible Server. 

The detail included is organized into the following sections: 

• Creating, restoring, and deleting a flexible server 
• Managing server access 
• Connecting to and querying a server 
• Managing databases 
• Managing server operations 
• Enabling CI/CD using GitHub actions 
• Updating server parameters 
• Creating and managing replicas

Creating, restoring, and deleting a flexible server 

Create a MySQL flexible server, restore from a backup snapshot, delete a flexible server, and view details of a flexible server using the following commands. 

Task	Command	Usage example
Create a flexible server with firewall rules using the public access method	az mysql flexible-server create	az mysql flexible-server create --public-access <IPaddress> --name <unique-server-name> -- resource-group <resource-group>  Sample script: Create a flexible server and enable public access connectivity
Create a flexible server in an existing virtual network	az mysql flexible-server create	az mysql flexible-server create –vnet <vnet-name> --subnet <subnet-name> -n <unique-server-name> -- resource-group <resource-group>  Sample script: Create a server and enable private access connectivity (VNet Integration)
Delete a flexible server	az mysql flexible-server delete	az mysql flexible-server delete --resource-group resource-group> --server-name <server-name>
Restore to a new flexible server from a backup snapshot	az mysql flexible-server restore	az mysql flexible-server restore --resource-group <resource-group> --name <new-server-name> --source-server <source-server-name> --restore-time "2021-07-09T13:10:00Z"  Sample script: Restore a flexible server from backup
Get the details of a flexible server	az mysql flexible-server show	az mysql flexible-server show --resource-group <resource-group> --server-name <server-name>
List available flexible servers	az mysql flexible-server list	az mysql flexible-server list --resource-group <resource-group>
View all the SKUs available for MySQL - Flexible Server for a given location	az mysql flexible-server list-skus	az mysql flexible-server list-skus --location <azure-region>

Managing server access 

The following commands help you enable firewall rules to limit access to your server if you are using public access.

Task	Command	Usage example
Create a new firewall rule for a flexible server	az mysql flexible-server firewall-rule create	az mysql flexible-server firewall-rule create --resource-group <resource-group> --server-name <server-name> --rule-name allowip --start-ip-address 107.46.14.221 --end-ip-address 107.46.14.221
Delete a firewall rule	az mysql flexible-server firewall-rule delete	az mysql flexible-server firewall-rule delete --resource-group <resource-group> --server-name <server-name> --rule-name allowip
List all firewall rules for a flexible server	az mysql flexible-server firewall-rule list	az mysql flexible-server firewall-rule delete --resource-group <resource-group> --server-name <server-name>
Update start and end IP address of firewall rule	az mysql flexible-server firewall-rule update	az mysql flexible-server firewall-rule delete --resource-group <resource-group> --server-name <server-name> --rule-name allowip --start-ip-address 107.46.14.225 --end-ip-address 107.46.14.227

Note: There aren't any sample scripts associated with the tasks listed in this table. 

Connecting to and querying a server 

Run these commands to easily run queries and import data from a file. 

Task	Command	Usage example
Connect and query a flexible server using Interactive mode	az mysql flexible-server connect	az mysql flexible-server connect --name <server-name> --admin-user <username> -p <password> --resource-group <resource-group> --interactive
Import a SQL file into a database	az mysql flexible-server execute	az mysql flexible-server execute --resource-group <resource-group> --server-name <server-name> --database-name <new-db-name> --file-path <local-file-path-to-sql-script>
Execute a query for a database	az mysql flexible-server execute	az mysql flexible-server execute --resource-group<resource-group> --server-name <server-name> --database-name <db-name> --querytext “SELECT 1;”
Import a SQL file into a database	az mysql flexible-server execute	az mysql flexible-server execute --resource-group <resource-group> --server-name <server-name> --database-name <db-name> --file-path <local-file-path-to-sql-script>

Note: There aren't any sample scripts associated with the tasks listed in this table. 

Managing databases 

Create and delete databases on MySQL flexible server using the following commands. 

Task	Command	Usage example
Create a MySQL database on a flexible server	az mysql flexible-server db create	az mysql flexible-server db create --resource-group <resource-group> --server-name <server-name> --database-name <new-db-name>
Delete a database on a flexible server	az mysql flexible-server db delete	az mysql flexible-server db delete --resource-group <resource-group> --server-name <server-name> --database-name <db-name>
List the databases for a flexible server	az mysql flexible-server db list	az mysql flexible-server db list --resource-group <resource-group> --server-name <server-name>
Show the details of a particular database	az mysql flexible-server db show	az mysql flexible-server db show --resource-group <resource-group> --server-name <server-name> --database-name <db-name>

Note: There aren't any sample scripts associated with the tasks listed in this table. 

Managing server operations 

You can easily start, stop, restart, and update your server using the following commands. 

Task	Command	Usage example
Start a flexible server	az mysql flexible-server start	az mysql flexible-server start --name <server-name> --resource-group <resource-group>  Sample script: Restart, stop, start a flexible server
Stop a flexible server	az mysql flexible-server stop	az mysql flexible-server stop --name <server-name> --resource-group <resource-group>  Sample script: Restart, stop, start a flexible server
Restart a flexible server	az mysql flexible-server restart	az mysql flexible-server restart --name <server-name> --resource-group <resource-group>  Sample script: Restart, stop, start a flexible server
Update a flexible server	az mysql flexible-server update	az mysql flexible-server update --name <server-name> --resource-group <resource-group> --sku-name Standard_D4ds_v4 --tier GeneralPurpose  Sample script: Monitor metrics and scale a server

Enabling CI/CD using GitHub actions 

You can set up a GitHub action to enable continuous deployment for updating your database using the following commands. 

Task	Command	Usage example
Create a GitHub action workflow file for a database on a flexible server	az mysql flexible-server deploy setup	az mysql flexible-server deploy setup --server-name <server-name> --resource-group <resource-group> --admin-user <username> --admin-password <password> --sql-file mydbschema.sql --repo <github-user>/<github-repository> --database-name <db-name> --action-name mydbAction
Run an existing workflow in your GitHub repository	az mysql flexible-server deploy run	az mysql flexible-server deploy run --action-name mydbAction --branch userBranch --resource-group <resource-group> --server-name <server-name>

Note: There aren't any sample scripts associated with the tasks listed in this table.  

Updating server parameters 

The following commands help you view and update your server parameters for the server. This will allow you fine tune the server for performance or debugging needs.

Task	Command	Usage example
List all the parameter values for a flexible server	az mysql flexible-server parameter list	az mysql flexible-server parameter list --resource-group <resource-group> --server-name <server-name>
Update a given parameter for your flexible server	az mysql flexible-server parameter set	az mysql flexible-server parameter set --resource-group <resource-group> --server-name <server-name> --name require_secure_transport --value OFF
Get the value of a given parameter for your flexible server	az mysql flexible-server parameter show	az mysql flexible-server parameter show --resource-group <resource-group> --server-name <server-name> --name require_secure_transport

Note: The sample script for each of the tasks listed in this table is here: List and update server parameters 

Creating and managing replicas 

You can manage your read replicas with these commands giving you more control on how you want to use replication for your application using the following commands. 

Task	Command	Usage example
Create a read replica for a server	az mysql flexible-server replica create	az mysql flexible-server replica create --replica-name <replica-name> --resource-group <resource-group> --source-server <source-server-name>
List all read replicas for a given server	az mysql flexible-server replica list	az mysql flexible-server replica list --resource-group <resource-group> --name <source-server-name>
Stop replication to a read replica and make it a read/write server	az mysql flexible-server replica stop-replication	az mysql flexible-server replica stop-replication --resource-group <resource-group> --name <replica-name>

Note: The sample script for each of the tasks listed in this table is here: Create and manage read replicas in a flexible server 

Conclusion

We hope this CLI ”cheat sheet” helps you get started quickly with using Azure Database for MySQL – Flexible Server and simplifies your development and administration experience! 

If you have any feedback or questions, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!