LLI Wiki — My Digital Knowledge Base

Azure MySQL: connect (password or Entra ID token) and import with mysqlsh

Connect to Azure Database for MySQL with mysql and mysqlsh using an Entra ID token, then import a dump and measure database sizes.

Cheat sheet for connecting to an Azure Database for MySQL flexible server with the mysql client and MySQL Shell (mysqlsh), using either a password or a Microsoft Entra ID access token, and for importing a dump.

Connect with a password

mysql -h mysql01.mysql.database.azure.com --user sqladmin -p --ssl-mode=REQUIRED

From MySQL Shell, with TLS and the CA certificate (DigiCert root bundle) on Windows:

\connect app_user@mysql01.mysql.database.azure.com?ssl-mode=REQUIRED&ssl-ca=C:\path\to\DigiCertGlobalRootCombined.pem

Or from the command line:

mysqlsh --sql app_user@mysql01.mysql.database.azure.com --password --ssl-mode=REQUIRED --ssl-ca="C:\path\to\DigiCertGlobalRootCA.crt.pem"

Then switch to SQL mode with \sql if needed.

Connect with a Microsoft Entra ID token

Sign in and check the account:

az login
az account show --query user.name

Get an access token for Azure Database for MySQL:

az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken

Use the token as the password. The cleartext plugin must be enabled because the token is sent as a clear password over the TLS connection:

mysql -h mysql01.mysql.database.azure.com --enable-cleartext-plugin \
  --user $(az account show --query user.name) \
  --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)

The same with MySQL Shell:

mysqlsh -h mysql01.mysql.database.azure.com --enable-cleartext-plugin \
  --user $(az account show --query user.name) \
  --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)

Store credentials in a client option file

vim ~/.my.cnf
[client]
user=sqladmin
password=<PASSWORD>
host=mysql01.mysql.database.azure.com
chmod 600 ~/.my.cnf

Import a dump

From a gzipped dump, with the credentials from ~/.my.cnf:

gunzip < backup.sql.gz | mysql 'AppDb'

Or by specifying host, user and database:

gunzip -c /path/backup.sql.gz | mysql -h <host> -u <user> -p <database>

From MySQL Shell, disabling foreign key checks during the import:

USE AppDb;
SET foreign_key_checks = 0;
\source C:/backups/dump.sql
SET foreign_key_checks = 1;

Create the target database if it does not exist:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `AppDb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

Size of each database

SELECT table_schema AS database_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Notes

Mastodon LLIMastodon SocialFosstodon