# How to Manage MySQL Users and Databases in a Database Cluster MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality. MySQL database clusters come configured with a default database (`defaultdb`) and a default administrative user (`doadmin`). These defaults are necessary for cluster replication and administration, so you can’t delete them, but you can add additional users and databases. By default, every database cluster is publicly accessible. To limit access, you can [add trusted sources](https://docs.digitalocean.com/products/databases/mysql/how-to/secure/index.html.md#firewalls) or [manage user permissions](https://docs.digitalocean.com/products/databases/mysql/how-to/modify-user-privileges/index.html.md). **Tip**: If you connect to the database cluster with preconfigured connection details from the cluster’s **Overview** page, you can edit them to use a different user and password or a different database. ## Create or Delete a Database User Using the CLI ## How to Create a Database User Using the DigitalOcean CLI 1. [Install `doctl`](https://docs.digitalocean.com/reference/doctl/how-to/install/index.html.md), the official DigitalOcean CLI. 2. [Create a personal access token](https://docs.digitalocean.com/reference/api/create-personal-access-token/index.html.md) and save it for use with `doctl`. 3. Use the token to grant `doctl` access to your DigitalOcean account. ```shell doctl auth init ``` 4. Finally, run `doctl databases user create`. Basic usage looks like this, but you can [read the usage docs](https://docs.digitalocean.com/reference/doctl/reference/databases/user/create/index.html.md) for more details: ```shell doctl databases user create [flags] ``` The following example creates a new user with the username `example-user` for a database cluster with the ID `ca9f591d-f38h-5555-a0ef-1c02d1d1e35`: ```shell doctl databases user create ca9f591d-f38h-5555-a0ef-1c02d1d1e35 example-user ``` ## How to Delete a Database User Using the DigitalOcean CLI 1. [Install `doctl`](https://docs.digitalocean.com/reference/doctl/how-to/install/index.html.md), the official DigitalOcean CLI. 2. [Create a personal access token](https://docs.digitalocean.com/reference/api/create-personal-access-token/index.html.md) and save it for use with `doctl`. 3. Use the token to grant `doctl` access to your DigitalOcean account. ```shell doctl auth init ``` 4. Finally, run `doctl databases user delete`. Basic usage looks like this, but you can [read the usage docs](https://docs.digitalocean.com/reference/doctl/reference/databases/user/delete/index.html.md) for more details: ```shell doctl databases user delete [flags] ``` The following example deletes the user with the username `example-user` for a database cluster with the ID `ca9f591d-f38h-5555-a0ef-1c02d1d1e35`: ```shell doctl databases user delete ca9f591d-f38h-5555-a0ef-1c02d1d1e35 example-user ``` ## Create, Update, or Delete a Database User Using the API ## How to Create a Database User Using the DigitalOcean API 1. [Create a personal access token](https://docs.digitalocean.com/reference/api/create-personal-access-token/index.html.md) and save it for use with the API. 2. Send a POST request to [`https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/users`](https://docs.digitalocean.com/reference/api/digitalocean//index.html.md#operation/databases_add_user). ### cURL Using cURL: ```shell curl -X POST \ -H "Content-Type: application/json" \ -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \ -d '{"name": "app-01"}' \ "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users" ``` ### Go Using [Godo](https://github.com/digitalocean/godo), the official DigitalOcean API client for Go: ```go import ( "context" "os" "github.com/digitalocean/godo" ) func main() { token := os.Getenv("DIGITALOCEAN_TOKEN") client := godo.NewFromToken(token) ctx := context.TODO() addUserRequest := &godo.DatabaseCreateUserRequest{ Name: "app-01", } user, _, err := client.Databases.CreateUser(ctx, "88055188-9e54-4f21-ab11-8a918ed79ee2", addUserRequest) } ``` ### Python Using [PyDo](https://github.com/digitalocean/pydo), the official DigitalOcean API client for Python: ```python import os from pydo import Client client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN")) add_user_resp = client.databases.add_user(database_cluster_uuid="ab7bb7a", body={"name": "app-01"}) ``` ## How to Update a Database User Using the DigitalOcean API 1. [Create a personal access token](https://docs.digitalocean.com/reference/api/create-personal-access-token/index.html.md) and save it for use with the API. 2. Send a PUT request to [`https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/users/{username}`](https://docs.digitalocean.com/reference/api/digitalocean//index.html.md#operation/databases_update_user). ### cURL Using cURL: ```shell curl -X PUT \ -H "Content-Type: application/json" \ -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \ -d '{"settings": {"acl": [{"topic": "events", "permission": "produce"}]}}' \ "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users" ``` ### Go Using [Godo](https://github.com/digitalocean/godo), the official DigitalOcean API client for Go: ```go import ( "context" "os" "github.com/digitalocean/godo" ) func main() { token := os.Getenv("DIGITALOCEAN_TOKEN") client := godo.NewFromToken(token) ctx := context.TODO() userName := "test-user" updateUserRequest := &godo.DatabaseUpdateUserRequest{ Settings: { ACL: [ { Permssion: "consume", Topic: "events", } { Permission: "produce", Topic: "metrics", } ] } } user, _, err := client.Databases.UpdateUser(ctx, "88055188-9e54-4f21-ab11-8a918ed79ee2", userName, updateUserRequest) } ``` ## How to Delete a Database User Using the DigitalOcean API 1. [Create a personal access token](https://docs.digitalocean.com/reference/api/create-personal-access-token/index.html.md) and save it for use with the API. 2. Send a DELETE request to [`https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/users/{username}`](https://docs.digitalocean.com/reference/api/digitalocean//index.html.md#operation/databases_delete_user). ### cURL Using cURL: ```shell curl -X DELETE \ -H "Content-Type: application/json" \ -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \ "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/users/app-01" ``` ### Go Using [Godo](https://github.com/digitalocean/godo), the official DigitalOcean API client for Go: ```go import ( "context" "os" "github.com/digitalocean/godo" ) func main() { token := os.Getenv("DIGITALOCEAN_TOKEN") client := godo.NewFromToken(token) ctx := context.TODO() _, err := client.Databases.DeleteUser(ctx, "9cc10173-e9ea-4176-9dbc-a4cee4c4ff30", "app-01") } ``` ### Python Using [PyDo](https://github.com/digitalocean/pydo), the official DigitalOcean API client for Python: ```python import os from pydo import Client client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN")) delete_resp = client.databases.delete_user(database_cluster_uuid="aba134a", username="backend_user1") ``` ## Add or Delete a Database User Using the Control Panel To add or delete users or databases to a database cluster, click the name of the database to go to its **Overview** page, then select the **Users & Databases** tab. ![Screenshot of Users and Databases screen](https://docs.digitalocean.com/screenshots/databases/mysql-users-and-databases.fdff93cb1da64baaa57417855f5c134a3ef8eca3dde6ae16f7680837e89bcf51.png) Create a new database in the **Databases** section by entering a name in the **Add new database** field and clicking **Save**. You can also delete a database here by opening its **More** menu, clicking **Delete**, and then confirming the deletion. Create new users in the **Users** section by entering a name in the **Add new user** field, selecting a [**Password encryption** option](#password-encryption), and clicking **Save**. New users will receive the permissions of the `doadmin` user by default; these can be changed by following the instructions at [How to Modify User Privileges in MySQL Databases](https://docs.digitalocean.com/products/databases/mysql/how-to/modify-user-privileges/index.html.md). You can also delete a user here by opening the user’s **More** menu, clicking **Delete**, and then confirming the deletion. Similarly, to reset a user’s password, open the user’s **More** menu and select **Reset password**. If you have any services that depend on that user’s credentials, such as log forwarding, resetting the password may break that service. ## Password Encryption DigitalOcean Managed Databases using MySQL 8+ are automatically configured to use `caching_sha2_password` authentication by default. `caching_sha2_password` uses a stronger password encryption than prior versions of MySQL and some applications (such as PHP based applications using PHP 7.1 or older) have trouble connecting to MySQL 8+ databases. You can use the **Password Encryption** option to set a user’s password encryption to the legacy version (`mysql_native_password`) if your applications are experiencing authentication issues. ![Select password encryption](https://docs.digitalocean.com/screenshots/databases/mysql_select-pass-encryption.abc011a77915a67d396668d300f119f19b03332bc87e9d85a365a8680593225e.png) To change a user’s password encryption on a database using the control panel, click the name of the database to go to its **Overview** page, then select the **Users & Databases** tab. Beside the user you want to change, click the **More** menu and select **Edit Password Encryption**. In the **Password encryption** menu, select the desired encryption type, then click **Save**. The database automatically updates with the new encryption preference. ![Edit password encryption](https://docs.digitalocean.com/screenshots/databases/mysql_edit-pass-encryption.7b0413e68a8ccd00423cb59d81a9a0fa0ded3ad21f46af07356729236628886d.png)