Managing Your Scalingo for MySQL® Addon

Scaling (Changing Plan)

You can change your database plan whenever you want. This operation happens instantly, no manual input is required. When you change the plan, the instances of your database are stopped and then restarted with the characteristics of the chosen plan. According to the type of plan you are originally using, the impact on your application differs:

Starter Plans: Since these plans are not highly available, expect some unavailability during which the scale operations are conducted. The duration highly depends on the database size (from seconds to several hours).
Business Plans: Thanks to high availability, only a failover occurs during the operation. Current connections are stopped and the application has to reconnect. It can be transparent or lead to a ~2-5 seconds interruption, depending on the driver used by your application and its configuration.

In both cases, once the operation is finished, the application is restarted to ensure it creates new healthy connections to the database.

Using the Dashboard

From your web browser, open your dashboard
Click on the application for which you want to scale the Scalingo for MySQL® addon
Click on the Resources tab
Locate the Addons block and click on the “…“ button
From the dropdown menu, select Change plan
Select the new plan
Click the Finish button
Validate by clicking the Confirm plan change button

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In General, select Change plan
Select the new plan
Click the Finish button
Validate by clicking the Confirm plan change button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

From the command line, list the plans available for mysql:

scalingo addons-plans mysql

The output should look like this:

+-----------------------+---------------+
|          ID           |     NAME      |
+-----------------------+---------------+
| mysql-sandbox         | Sandbox       |
| mysql-starter-512     | Starter 512M  |
| mysql-starter-1024    | Starter 1G    |
...

Locate the ID corresponding to the plan you want to scale to (for example mysql-business-1024)

List the addons attached to your application:

scalingo --app my-app addons

The output should look like this:

+-------+-----------------------------------------+-------------------+---------+
| ADDON |                   ID                    |       PLAN        | STATUS  |
+-------+-----------------------------------------+-------------------+---------+
| MySQL | ad-871546ad-943a-4929-9770-ec7c839d65f5 | mysql-starter-512 | running |
...

Locate the ID corresponding to the addon you want to scale

Change plan using the addons-upgrade sub-command:

scalingo --app my-app addons-upgrade <addon_ID> <plan_ID>

The output should look like this:

-----> Addon ad-d0aa540a-5ed2-41f8-8bbe-91e3aff6623b has been upgraded
       Message from addon provider: Database plan is being changed

Using the Terraform Provider

Update the plan property of the corresponding Resource block in your Terraform file to scale the addon:
```
resource "scalingo_addon" "my-db" {
  provider_id = "mysql"
  plan = "mysql-business-1024"
  app = "${scalingo_app.my-app.id}"
}
```
In this example, we switch the my-db resource attached to the my-app application to a MySQL® Business 1024 addon.
Run terraform plan and check if the result looks good
If so, run terraform apply

Upgrading

Upgrading your MySQL® addon consists in changing the database version for a newer one.

When the database vendor releases a new version of your database engine, we take some time to study it and test it thoroughly before making it available. Upgrading to this new version is still your choice. We don’t do it automatically.

Your database needs to be upgraded to the latest minor version before having access to the next major version. For instance, let’s imagine that your version is 2.3.x and you want to upgrade to 3.1.x. If there is a 2.5.x version available, you first need to upgrade your database to the 2.5.x version before upgrading to the 3.1.x version.

During the upgrade, a downtime can unfortunately happen, depending on the Plan you are using and the upgrade:

Starter Plans: In all cases, we have to stop the node to upgrade it, causing an inevitable downtime.
Business Plans: We are able to achieve zero-downtime upgrade of minor version. In the case of major version upgrade, we need to completely stop the nodes, hence causing an inevitable downtime.

In both cases, once the operation is finished, the application is restarted.

Using the Database Dashboard

From your web browser, open your database dashboard
Select the Settings tab
In the Settings submenu, select the General tab
Locate the Database Version block
If an upgrade is available, a button allows you to trigger the upgrade
Click the button to launch the upgrade process

Managing Users

By default, Scalingo creates a user with read and write permissions on your database with the following queries:

GRANT ALL PRIVILEGES ON <username>.* TO '<username>'@'%'

Listing Users

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
The list of available users is displayed in the User Management block

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

From the command line, run the following command to list the users:

scalingo --app my-app --addon mysql database-users-list

The output should look like this:

+----------------+-----------+-----------+
|    USERNAME    | READ-ONLY | PROTECTED |
+----------------+-----------+-----------+
| my_app_4553    | false     | true      |
| my_app_4553_rw | false     | false     |
| my_app_4553_ro | true      | false     |
+----------------+-----------+-----------+

In this example, we can see that the database has 3 users available. One has been created along with the addon and is protected, which means it can’t be removed. Among the two others, one can only read data from the database.

Creating a New User

The following restrictions apply when creating a new user, regardless of the method you use:

Username:
- Must be between 6 and 16 characters long
- Can only contain alphanumerical characters and underscores (_)
- Must start with a letter
Password:
- Must be between 24 and 64 characters long.
- Must not contain the character " or '

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
Fill the Add a user form:
- Fill a username
- To grant write abilities to this user, make sure to check the Write (optional) checkbox
Validate by clicking the Add this user button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool
From the command line, run the following command to create a new user:
```
scalingo --app my-app --addon mysql database-users-create <username>
```
Optionally, if you want to restrict this user to read only abilities, use the --read-only flag:
```
scalingo --app my-app --addon mysql database-users-create --read-only <username>
```
Set the user password:
- Either chose a password and confirm it
- Or let the platform generate a password by leaving the field empty
The output should look like this:
- If you set a password:
```
User "my_user" created.
```
- If you let the platform generate a password:
```
User "my_user" created with password "YANs3y07m5_KJC2MSDGebh8tx1lliFWh2Yb239zVqGQvbElWDjIN7QWspVH92Ul8".
```

Updating a User Password

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
Locate the user you want the password to be updated
Click the “…“ button next to the user
From the popup menu, select Reset password
In the popup window, confirm the reset by typing the name of the user
Validate by clicking the Confirm button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

Update the user password with the following command:

scalingo --app my-app --addon mysql database-users-update-password <username>

An interactive prompt asks you for a password you want to attribute to your user:
- Either choose a password and confirm it
- Or let the platform generate a password by leaving the field empty The output should look like this:
- If you set a password:
```
User "my_user" created.
```
- If you let the platform generate a password:
```
User "my_user" created with password "YANs3y07m5_KJC2MSDGebh8tx1lliFWh2Yb239zVqGQvbElWDjIN7QWspVH92Ul8".
```

Deleting an Existing User

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
Locate the user you want to remove
Click the “…“ button next to the user
From the popup menu, select Delete user
In the popup window, confirm the deletion by typing the name of the user
Validate by clicking the Confirm button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

Remove the user with the following command:

scalingo --app my-app --addon mysql database-users-delete <username>

Using Multiple Databases

Each Scalingo for MySQL® addon comes with a default database. You can, however, create multiple databases on the same Scalingo for MySQL® instance. In this case:

The memory and disk storage allocated in the plan are shared between all databases
By default, the database users (the default one and the ones you could have created) are shared between databases, even for existing ones
All databases are backed up in the same backup file
The platform doesn’t provide any environment variable for this new database. You should, however, be able to generate one from the original SCALINGO_MYSQL_URL.

Listing Existing Databases

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Databases

Creating a New Database

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Databases
Fill the Add a database form by specifying a name for the new database
Validate the form by clicking the Create this database button

Deleting a Database

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Databases
Locate the database you want to remove
Click the “…“ button next to the database
From the popup menu, select Drop
In the popup window, confirm the deletion by typing the name of the database
Validate by clicking the Confirm button

Working with SQL Modes

SQL modes allow you to configure MySQL® to behave according to specific standards, adjusting how it processes queries and verifies data validity. This includes settings that can make MySQL® more strict or more flexible regarding SQL syntax and data constraints, providing developers the ability to optimize database performances based on their application’s requirements.

Scalingo doesn’t moderate the modes available, which means all modes supported by your addon are indeed available. The exhaustive list may vary depending on your MySQL® version. Descriptions of the modes can be found in the official MySQL® documentation:

Currently, the default SQL mode includes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

Setting SQL Modes

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Configuration
Click the Manage SQL modes button
Select the mode(s) you are interested in
Validate by clicking the Update configuration button
The changes are done at runtime, which means they immediately apply to new queries

Last update: 24 Oct 2024 Suggest edits