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

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 control panel.

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.

Users and Privileges

In your control panel, you can manage PostgreSQL users and their privileges.

Creating Users

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

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

Changing User Password

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

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.

Changing 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. Adjust the parameters. To read more about each parameter, click Open description next to it.

5. Click Apply at the bottom of the page.

Available parameters depend on the selected MySQL version.

MySQL 8.0 Parameters List

MySQL 8.4 Parameters List

Resetting Parameters

You can easily restore the default 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. Scroll down to the bottom of the page and click Restore default settings.

Be careful: the parameters will be reset immediately, without additional confirmation.

Connecting to the Database

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

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. Сlick the Web Interface button and select the preferred option.

Terminal

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.

If you are using MariaDB 10.10 or higher as the client, use the option --skip_ssl when connecting to the cluster.

Creating a Dump

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

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.