MySQL

Creating a MySQL database

mStudio UI

To create a MySQL database in the mStudio, follow these steps:

1. Navigate to the project that you want to create the database in.
2. Select the "Databases" menu item in the sidebar.
3. Click the "Create Database" button and select "MySQL".
4. Enter a description for the database, select the desired version and choose a password for the database user.

After the installation has completed, observe the host and port in the details under connection information. You need both for the configuration of your system.

CLI

To create a MySQL database from the CLI, run the following commands:

$ mw database mysql create --description "My MySQL database" --version 8.0

This command will prompt you to enter a password for the database user. To skip the prompt, use the --password option (be careful, because this will store the password in your shell history), or set the MYSQL_PWD environment variable, first:

$ read -s MYSQL_PWD
$ export MYSQL_PWD
$ mw database mysql create ...

There are additional flags that you can provide to set additional options:

• --character-set and --collation to set the character set and collation for the database. These default to utf8mb4 and utf8mb4_unicode_ci, respectively. Usually, there is little reason to change these, except when migrating from an existing database.
• --user-access-level allows you to restrict the permissions of the database user. The default is full, which grants all permissions. You can also choose readonly.
• --user-external allows you to create a user that can connect from outside the mittwald cloud platform. This is disabled by default.

After the installation has completed, use the mw database mysql get command to observe the connection information.

API

For details please refer to the endpoint documentation for the following endpoint:

POST

Create a MySQLDatabase with a MySQLUser.

/v2/projects/{projectId}/mysql-databases/

Operational concerns

Managed backup and recovery

The mittwald cloud platform automatically creates backups of your projects and their databases at regular intervals. You can inspect the available backups as any time using the mStudio UI or the mw CLI, using the mw backup list command. You can also create a backup manually using the mw backup create command:

Local shell session

$ mw backup create --wait --expires 30d

To recover the contents of a single MySQL database, you can follow the following steps:

1. Download a backup to your local machine:

   Local shell session

   $ mw backup download <backup-id> --format tar --output backup.tar.gz

2. After downloading the backup, you can extract the database contents from the downloaded archive:

   Local shell session

   $ database_id=<database-id>
   $ tar xfz backup.tar.gz dbdump-${database_id/_/-}

   note

   Note that in the archive files, the database ID is spelled differently. Whereas it usually follows the format mysql_XXXXXX, in the archive, it is spelled with dashes instead of underscores, like mysql-XXXXXX. :::

3. Recover the backup using the mw database mysql import command. For this, you need to have an SSH key set up on your local machine, which is also linked to your mStudio user account.

   Local shell session

   $ database_id=<database-id>
   $ mw database mysql import ${database_id} --input dbdump-${database_id/_/-}/*.sql.gz --gzip

Manual backup and recovery

You can use the mw CLI to manually create (and restore) backups of your MySQL database. As a prerequisite, you need to have an SSH key set up on your local machine, which is also linked to your mStudio user account.

To create a backup, run the following command:

Local shell session

$ mw database mysql dump <database-id> --output dump.sql.gz --gzip

In this command, replace <database-id> with the ID of the MySQL database you want to back up. The --gzip flag compresses the backup file, which can save disk space and reduce transfer times.

tip

If you want to create the backup with a different user than the one you are currently logged in with, you can use the --ssh-user flag to specify a different user for the SSH connection. However, you still need to make sure that an SSH key is set up on your local machine for that user.

To restore a backup, run the following command:

Local shell session

$ mw database mysql import <database-id> --input dump.sql.gz --gzip