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

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

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 scriptCreate 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 scriptCreate 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 scriptRestore 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 scriptRestart, 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 scriptRestart, 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 scriptMonitor 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! 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.