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
- Server, database, login and path names are generic examples; passwords are replaced by
<PASSWORD>. - Entra ID access tokens are short-lived: request a new one for each connection.