Creating and Managing MySQL Users

This page describes how to configure the default user account and create, delete, and update other user accounts for Cloud SQL instances.

For information about how users work with Cloud SQL, see Users.

Before you begin

Before completing the tasks on this page, you must have created the Cloud SQL instance. For more information, see Creating instances.

If you plan to use your database's administrative client to manage your users, you must have configured:

Configuring the default user account

When you create a new Cloud SQL instance, you must configure the default user account before you can connect to the instance.

For Cloud SQL for MySQL, the default user is root.

To configure the default user:

Console (2nd Gen)

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Click the instance to open its Overview page.
  3. Select the Users tab.
  4. Find the root user with a Host value of % (any host) and select Change password from the more actions menu More actions icon..
  5. Provide a strong password that you can remember and click Ok.

Console (1st Gen)

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Click the First Generation instance to open its Overview page.
  3. Select the Users tab.
  4. If a user with a User name of root and a Host name of %(any host) is already shown in the user list, you do not need to create the root user.

    To reset the password, select Change password from the more actions menu More actions icon..

  5. Otherwise, click Create user account.
  6. In the Create user account dialog, enter root for the User name and a strong root password that you can remember. Leave the Host name set to Allow any host(%).
  7. Click Create.

gcloud

Set the password for the default user:

gcloud sql users set-password root % \
   --instance [INSTANCE_NAME] --password [PASSWORD]

cURL

The following request uses the users:update method to update the root user account ('root'@'%').

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "root", "password": "[PASSWORD]"}' \
     -X PUT \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?name=root&host=%25'

Creating a user

To create a user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select the Users tab.
  4. Click Create user account.
  5. In the Create user account dialog, specify:
    • A User name.
    • A Password.
    • Optionally, a Host name.
      The default is '%', which means that the host list is unrestricted.
  6. Click Create.

Users created using Cloud SQL have all privileges except FILE and SUPER.

gcloud

Create the user:

gcloud sql users create [USER_NAME] [HOST] \
   --instance=[INSTANCE_NAME] --password=[PASSWORD]

Users created using Cloud SQL have all privileges except FILE and SUPER.

cURL

The following request uses the users:insert method to create a user account 'user_name'@'%'.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"host": "%", "name": "[USER_NAME]", "password": "[PASSWORD]"}' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users

Users created using Cloud SQL have all privileges except FILE and SUPER.

MySQL Client

  1. At the mysql prompt, create the user:
    CREATE USER '[USER_NAME]'@'%' IDENTIFIED BY '[PASSWORD]';
    
  2. You can confirm the user creation by displaying the user table:
    SELECT user, host FROM mysql.user;
    
    For a Second Generation instance, the output looks similar to this example:
    +----------+-----------+
    | user     | host      |
    +----------+-----------+
    | root     | %         |
    | newuser  | %         |
    +----------+-----------+
    1 row in set (0.01 sec)
    
  3. Give the user privileges with the GRANT statement. For more information, see Privileges Provided by MySQL.
  4. Flush the mysql.user table to ensure that the change persists:
    FLUSH TABLES mysql.user;
    

Changing a user password

To change a user password:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.
  4. Click more actions More actions icon. for the user you want to update.
  5. Select Change password, specify a new password, and click OK.

gcloud

Update the password:

gcloud sql users set-password [USER_NAME] [HOST] \
   --instance=[INSTANCE_NAME] --password=[PASSWORD]

cURL

The following request uses the users:update method to update the password for the user account 'user_name'@'%'. If your user has a different host, you must modify the call with the correct host.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "[USER_NAME]", "host": "%", "password": "[PASSWORD]"}' \
     -X PUT \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?name=[USER_NAME]&host=%25'

MySQL Client

  1. At the mysql prompt, change the password:
    SET PASSWORD FOR [USER] = PASSWORD('auth_string');
    
  2. Flush the mysql.user table to ensure that the change persists:
    FLUSH TABLES mysql.user;
    

Listing users

To list users:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.

gcloud

List the users for this instance:

gcloud sql users list --instance=[INSTANCE_NAME]

For a complete list of parameters for this command, see the gcloud sql users list reference page.

cURL

The following request uses users:list method to list the users defined for an instance.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     -X GET \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users

MySQL Client

At the mysql prompt, list the MySQL users:

SELECT user, host FROM mysql.user;

For a Second Generation instance with only the root user account configured, the output looks similar to this example:

+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
+------+-----------+
1 row in set (0.01 sec)

This example shows the users for an instance where the user'root'@'%' was added. The password field shows the hash of the password.

Deleting users

To delete a user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Access Control > Users.
  4. Click more actions More actions icon. for the user you want to delete.
  5. Select Delete and click OK.

gcloud

Delete the user:

gcloud sql users delete [USER_NAME] [HOST] --instance=[INSTANCE_NAME]

cURL

The following request uses the users:delete method to delete the user account 'user_name'@'%'.

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name": "[USER_NAME]", "host": "%"}' \
     -X DELETE \
     'https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/users?host=%25&name=[USER_NAME]'

MySQL Client

  1. At the mysql prompt, delete the user:
    DROP USER '[USER_NAME]'@'[HOST_NAME]';
    

    For more information about the DROP USER statement, see the MySQL documentation.

  2. Flush the mysql.user table to ensure that the change persists:
    FLUSH TABLES mysql.user;
    

What's next

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Cloud SQL for MySQL