All Products
Search

This Product

    ApsaraDB RDS:Common SQL commands for MySQL

    Document Center

    ApsaraDB RDS:Common SQL commands for MySQL

    Last Updated:Mar 28, 2026

    ApsaraDB RDS for MySQL is 100% compatible with open-source MySQL. This page covers the SQL commands you need most for database and account management.

    For the complete SQL reference, see the MySQL Reference Manual.

    Important

    ApsaraDB RDS for MySQL is a managed service. The high-privilege root account is not available. You cannot run GRANT ALL PRIVILEGES ON *.* to grant global full control over all databases.

    Quick reference

    OperationCommand
    Create a databaseCREATE DATABASE <name>;
    Delete a databaseDROP DATABASE <name>;
    Create an accountCREATE USER 'user'@'host' IDENTIFIED BY 'password';
    Delete an accountDROP USER 'user'@'host';
    Grant privilegesGRANT <privilege> ON <level> TO 'user'@'host';
    View account privilegesSHOW GRANTS FOR 'user'@'host';
    Revoke privilegesREVOKE <privilege> ON <level> FROM 'user'@'host';

    Database management

    Create a database

    -- Minimal syntax
CREATE DATABASE <database_name>;

-- With character set and collation (recommended)
CREATE DATABASE <database_name>
  DEFAULT CHARACTER SET <charset>
  COLLATE <collation>;

    Examples

    -- Create a database with the default character set.
CREATE DATABASE app_db;

-- Create a database with utf8mb4. Use utf8mb4 for most applications —
-- it supports a wider range of characters, including supplementary Unicode characters.
CREATE DATABASE app_db
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Create a database with the GBK character set for Chinese-language content.
CREATE DATABASE cn_db
  DEFAULT CHARACTER SET gbk
  COLLATE gbk_chinese_ci;

    Delete a database

    DROP DATABASE <database_name>;
    Important

    This operation is irreversible. Back up your data before proceeding.

    Example

    DROP DATABASE app_db;

    Account management

    Create an account

    CREATE USER 'username'@'host' IDENTIFIED BY 'password';

    The host value controls which hosts the account can connect from:

    host valueEffect
    %Allows connections from any host. This matches how the ApsaraDB RDS console creates accounts, and the account can be deleted from the console later.
    192.168.1.%Restricts connections to a specific CIDR block. Accounts with a specific host value cannot be deleted from the console — use DROP USER instead.

    Examples

    -- Allow connections from any host.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';

-- Restrict connections to a specific CIDR block.
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

    Grant privileges

    GRANT <privilege> ON <level> TO 'username'@'host';
    ParameterDescriptionExamples
    <privilege>The privilege to grantSELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES
    <level>The scope of the privilegedb_name.* (database), db_name.table_name (table), *.* (global)
    You cannot grant ALL PRIVILEGES ON *.*. Global full control is not available on ApsaraDB RDS for MySQL.

    Examples

    -- Grant SELECT on all tables in app_db.
GRANT SELECT ON app_db.* TO 'app_user'@'%';

-- Grant read/write privileges on a specific table.
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.users TO 'app_user'@'%';

-- Grant all privileges on app_db to clients from the 192.168.1.x CIDR block.
-- Use with caution — this grants broad access.
GRANT ALL PRIVILEGES ON app_db.* TO 'admin_user'@'192.168.1.%';

    View account privileges

    -- List all accounts and their allowed hosts.
SELECT user, host FROM mysql.user;

-- Show the privileges granted to a specific account.
SHOW GRANTS FOR 'username'@'host';
    On earlier RDS versions, if querying mysql.user returns an error, query the mysql.user_view view instead.

    Example

    -- Show all privileges granted to app_user when connecting from 192.168.1.x.
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

    Revoke privileges

    Use REVOKE to remove privileges without deleting the account. Note that REVOKE uses FROM, while GRANT uses TO.

    Revoke all privileges

    -- Remove all operation privileges and the ability to grant privileges to others.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

    Revoke a specific privilege

    -- Revoke a specific privilege at any scope (global, database, or table).
REVOKE <privilege> ON <level> FROM 'username'@'host';
    Revoking privileges does not delete the account. To delete the account, run DROP USER.

    Examples

    -- Revoke all privileges from app_user (connecting from 192.168.1.x).
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'192.168.1.%';

-- Revoke UPDATE on a specific table from app_user (connecting from 10.10.0.x).
REVOKE UPDATE ON shop_db.users FROM 'app_user'@'10.10.0.%';

    Delete an account

    DROP USER 'username'@'host';
    Important

    Do not use DELETE FROM mysql.user WHERE User = '<username>' AND Host = '<host>'; to delete accounts. This method can leave residual privileges and cause metadata inconsistencies.

    To safely delete an account, revoke its privileges first, then drop it:

    -- Step 1: Revoke all privileges.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

-- Step 2: Delete the account.
DROP USER 'username'@'host';

    Example

    -- Delete the account that can only connect from the 192.168.1.x CIDR block.
DROP USER 'app_user'@'192.168.1.%';

    Troubleshooting

    Can't delete an account from the ApsaraDB RDS console

    Problem description

    Symptom

    The Account Management page shows the following error when you try to delete an account:

    Failed to delete the account. Check the request or the input parameters. Other threads in the instance may be waiting for the lock or the host value of the current database account is not set to % (allows logins from all hosts).

    Solution

    Cause

    The console can only delete accounts where host is set to %. If you created the account with SQL and specified a particular IP address or CIDR block — such as 'username'@'10.10.10.1' or 'username'@'10.%.%.%' — the console cannot delete it.

    Fix

    Check the host value of the account:

    SELECT user, host FROM mysql.user WHERE user = 'username_to_delete';

    If host is not %, delete the account with DROP USER:

    DROP USER 'username_to_delete'@'target_host';

    Related topics