MySQL Management

MySQL is a database management system used for storing and processing structured data. In a DBaaS environment, you get a managed database cluster without the need for manual configuration: everything is handled automatically.

When creating a cluster, you can choose the MySQL version: 8.0 or 8.4.

Creating Databases

Copy link

When you create a new cluster, a database named default_db is created automatically. 

You can create additional databases:

1. Go to the Databases section of your Hostman dashboard.

2. Click on the cluster.

3. In the Databases tab, click Add.

4. Provide a name for your database: 3–64 characters. Letters, numbers, and special characters are allowed.

5. Add a description, if necessary.

6. Click Create.

Database Import

Copy link

To import a database:

1. Go to the Databases section of your Hostman dashboard.
2. Click on the cluster.
3. In the Databases tab, click Import.
4. Enter the connection details for the database you want to import.
5. Click Start import. 

Requirements and Limitations

Copy link

• Network Access: The source database must be accessible for connection from the internet from any IP address. Without this, the import will not start.
• Unique Name: The target cluster must not contain a database with the same (identical) name.

What will be transferred:

• Table structures
• Data in tables

What will not be transferred:

• Users

You will need to create users manually in the new database, after the import.

Before running the import, we recommend temporarily reducing the innodb_buffer_pool_size parameter to 50% of the RAM allocated to the database.

Users and Privileges

Copy link

In your Hostman dashboard, you can manage MySQL users and their privileges.

Creating Users

Copy link

When a new cluster is provisioned, it automatically includes a database user named gen_user. You can add more users in the Users tab of the interface.

1. Go to the Databases section and click on the cluster.

2. Open the Users tab and click Add.

3. Set the username and password:

• Username: 3–32 characters. Letters, numbers, and special characters are allowed.

• Password: 8–30 characters. Letters, numbers, and special characters are allowed.

4. (Optional) Configure the user’s privileges. You can also leave the default parameters and change them later at any time.

5. Click Create.

Modifying Privileges

Copy link

To update the privileges of an existing user:

1. Go to the Databases section and click on the cluster.

2. Open the Users tab.

3. Click the three dots next to the user.

4. Select Privileges.

5. Choose the required privileges. You can grant access to a specific database or grant the same privileges for all databases by enabling Identical for all databases in the top right.

Available Privileges

Copy link

Changing User Password

Copy link

You can change the user password:

1. Go to the Database section and click on the cluster.
2. Go to the Users tab.
3. Click the three dots next to the user.
4. Select Change password.
5. Enter a new value and save changes.

Database Parameters

Copy link

You can change database settings to optimize its performance. 

By default, the database is created with the parameters that are suitable for most tasks and will ensure the performance of the selected configuration. However, if your project requires specific settings, you can change them as you wish. 

Be careful and change parameters only if you are sure of their purpose. Incorrect configuration can negatively affect the operation of the database and its performance.

Available parameters depend on the MySQL version.

MySQL 8.0 Parameters List

Copy link

MySQL 8.4 Parameters List

Copy link

Changing Parameters

Copy link

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify parameters next to Database parameters.

4. Adjust the parameters. To read more about each parameter, click Open description next to it.

5. Click Apply and Restart at the bottom of the page.

6. Confirm the action.

Resetting Parameters

Copy link

If needed, you can restore one or all parameters to their default values.

Resetting a Single Parameter

1. Go to the Databases section and click on the cluster.
2. Go to the Configuration tab.
3. Click Modify parameters next to Database parameters.
4. Click Reset to default next to the parameter you want to reset. 
5. Click Apply and Restart at the bottom of the page.
6. Confirm the action.

Resetting All Parameters

1. Go to the Databases section and click on the cluster.
2. Go to the Configuration tab.
3. Click Modify parameters next to Database parameters.
4. Click Restore default settings at the bottom of the page.
5. Click Continue to confirm. The cluster will be restarted.

Importing and Exporting Parameters

Copy link

MySQL parameters can be exported to a file and imported into another cluster.

Importing Parameters

1. Go to the Databases section and click on the cluster.
2. Go to the Configuration tab.
3. Click Modify parameters next to Database parameters.
4. Click Import / export → Import parameters.
5. Select a JSON file containing the parameters.
6. Click Apply.

The parameters file must contain an object with parameter names as keys and their values as strings:

{
  "parameter_name_1": "parameter_value_1",
  "parameter_name_2": "parameter_value_2"
}

If a parameter doesn't exist in the MySQL version you're using, or can't be modified, it will be ignored during import.

Exporting Parameters

1. Go to the Databases section and click on the cluster.
2. Go to the Configuration tab.
3. Click Modify parameters next to Database parameters.
4. Click Import / export → Export parameters.

Your browser will download a JSON file containing the current parameter values.

Connecting to the Database

Copy link

You can connect to your databases using a web interface or a terminal. Connecting via a web interface is possible only if the cluster has a public IP address.

Adminer and phpMyAdmin

Copy link

You can connect to the database via the built-in web interfaces: Adminer or phpMyAdmin.

1. Go to the Databases section and click on the cluster.

2. Click the Web Interface button and select the preferred option.

Terminal

Copy link

Install the MySQL client:

• Ubuntu:

sudo apt install mysql-client

• Debian:

sudo apt install mariadb-client

• Arch:

sudo pacman -S percona-server-clients

• Fedora:

sudo dnf install mysql

There are two ways to connect to a MySQL cluster:

• via public IP;
• via private IP.

You can copy the connection command from:

• the Dashboard tab;

• the Connection tab.

Creating a Dump

Copy link

To create a dump, run the following command:

mysqldump --set-gtid-purged=off -y -u gen_user -p'password' -h ip_dbaas default_db | gzip > default_db.sql.gz

Here:

• --set-gtid-purged=off excludes the SET @@GLOBAL.GTID_PURGED command, which may cause errors if privileges are insufficient.

• -y (--no-tablespaces) excludes tablespaces, preventing possible errors during dump creation.

Importing a Dump

Copy link

To import a dump, run:

cat default_db.sql.gz | gzip -d | mysql -u gen_user -p'password' -h ip_dbaas default_db

This command decompresses the dump and pipes it into the MySQL client for execution.

Basic Plan Limitations

Copy link

The basic database plan (1 CPU / 1 GB RAM / 20 GB NVMe) has the following technical limitations:

• Additional databases cannot be added
• Additional users cannot be added
• The following parameters cannot be configured:
  • innodb_buffer_pool_size
  • sort_buffer_size
  • max_connections
  • join_buffer_size
  • thread_cache_size