Create databases and users in Azure Database for MySQL

APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server

This article describes how to create users in Azure Database for MySQL.

Note

This article contains references to the term slave, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.

When you first created your Azure Database for MySQL server, you provided a server admin user name and password. For more information, see this Quickstart. You can determine your server admin user name in the Azure portal.

The server admin user has these privileges:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER

After you create an Azure Database for MySQL server, you can use the first server admin account to create more users and grant admin access to them. You can also use the server admin account to create less privileged users that have access to individual database schemas.

Note

The SUPER privilege and DBA role aren't supported. Review the privileges in the limitations article to understand what's not supported in the service.

Password plugins like validate_password and caching_sha2_password aren't supported by the service.

To create a database with a non-admin user in Azure Database for MySQL

  1. Get the connection information and admin user name. To connect to your database server, you need the full server name and admin sign-in credentials. You can easily find the server name and sign-in information on the server Overview page or on the Properties page in the Azure portal.

  2. Use the admin account and password to connect to your database server. Use your preferred client tool, such as MySQL Workbench, mysql.exe, or HeidiSQL.

    If you're not sure how to connect, see connect and query data for Single Server or connect and query data for Flexible Server.

  3. Edit and run the following SQL code. Replace the placeholder value db_user with your intended new user name. Replace the placeholder value testdb with your database name.

    This SQL code creates a new database named testdb. It then creates a new user in the MySQL service and grants all privileges for the new database schema (testdb.*) to that user.

    CREATE DATABASE testdb;
    
    CREATE USER 'db_user'@'%' IDENTIFIED BY 'StrongPassword!';
    
    GRANT ALL PRIVILEGES ON testdb . * TO 'db_user'@'%';
    
    FLUSH PRIVILEGES;
    
  4. Verify the grants in the database:

    USE testdb;
    
    SHOW GRANTS FOR 'db_user'@'%';
    
  5. Sign in to the server, specifying the designated database and using the new user name and password. This example shows the mysql command line. When you use this command, you'll be prompted for the user's password. Use your own server name, database name, and user name.

mysql --host mydemoserver.mysql.database.azure.com --database testdb --user db_user@mydemoserver -p