Skip to main content

MySQL

On-Demand databases can be managed via Cockpit, our command-line tool nctl or by directly using the Nine Self Service API.

Billing is automated based on the resources used. Nine's automated management ensures the smooth operation of the service.

On-Demand databases offer limited configuration options. If more extensive settings are required, use our Managed Service.

Updates and maintenance windows

Security and software updates are generally performed during the maintenance window.

Therefore, short service interruptions may occur during this maintenance window without prior notice.

Monitoring

Nine monitors the instance with a monitoring system 24x7. In the event of a malfunction, an (on-call) technician from Nine is automatically alerted and restores proper operation as quickly as possible.

Full utilization of resources is not assessed as a malfunction, as the size of the instance can be increased or decreased at any time.

Pricing

Our Pricing for relational databases such as MySQL and PostgreSQL starts at CHF 49 for a nine-db-s (1vCPU/2GB Ram) sizing. Additional charges are based on the resources used by NKE. Check the Machine Types for more details.

Configuration options

On-Demand Services come with a sensible base configuration that is suitable for most setups. They also provide the ability to change certain configuration options. Adjusting these configuration options can result in a restart of the instance.

The instance can be customized with the following configuration options:

Name

The name of the instance can be freely chosen, but must be unique. Once created, the name cannot be changed.

Location

Depending on available resources, instances can be created in two locations within the "ColoZüri" data center or the "NTT" data center in Rümlang. The location cannot be changed later.

Allowed IP addresses

IPv4 addresses and address ranges from which connections to the service can be established. Access from our Kubernetes products NKE and GKE as well as from deplo.io is already enabled.

The access restriction can be adjusted at any time. Adjustments are made non-disruptively moments after the form is submitted.

Backup retention

The backup retention period in days can be selected between 0 and 365 days.

If 0 days is selected, the backup routine will be disabled and all existing backups will be deleted.

Please note that the storage space requirement increases if the local retention period is long. This may result in higher instance costs. We have summarized information about automatic disk space expansion in the section Automatic Storage Space Expansion.

For more information about backing up your databases on a daily basis, accessing the backups, and how to create your own backups if needed, see the section about backups.

Machine type

We offer the following optimized machine types for on-demand databases:

note

We recommend nine-db-s or larger sizings for production workloads.

nine-db-xsnine-db-snine-db-mnine-db-lnine-db-xlnine-db-xl
Virtual CPU (VCPU)2446810
RAM4 GB8 GB12 GB16 GB24 GB32 GB
Storage space20 GB20 GB20 GB20 GB20 GB20 GB
Monthly fees

Additional storage space per 10 GB:

Machine types can be changed after creation. After an adjustment, the database instance will be restarted and will be unavailable for a few minutes. The storage space is automatically extended if required. For more information, see the section Automatic Storage Space Expansion.

SSH Public-Keys

Configure the public keys to access the database backups via SSH. The keys can be adjusted at any time.

Version

Nine currently provides On-Demand MySQL environments with MySQL 8 only.

Long Query Time

The "Long Query Time" specifies the time in seconds after which the MySQL service considers the execution of a query to be slow and logs the query.

Min Word Length

This value configures the minimum length of a word that MySQL will use for full text search.

Nine sets the value chosen here for both ft_min_word_len (MyISAM Storage Engine, Legacy) and innodb_ft_min_token_size (InnoDB Storage Engine).

Character Set

The charset is customizable. From experience, the default values utf8mb4_unicode_ci / utf8mb4 cover most needs.

Before considering customizing these values, please consult the MySQL documentation: Character Sets and Collations in MySQL

Transaction Isolation

Nine recommends not making any adjustment to the selected default value unless absolutely necessary due to application requirements.

Be sure to consult the MySQL documentation in advance and familiarize yourself with the related implications: Transaction Isolation Levels.

SQL Modes

The SQL Mode should also only be adjusted if the application absolutely requires it. Nine uses the default values set by Oracle for MySQL 8.

Oracle provides documentation and FAQ about SQL Modes in the following articles:

Backups

Nine backs up the databases daily between 01:00 and 02:00. These backups are kept locally for 10 days (configurable) and on a remote backup system for seven days.

Backups are stored in the /home/dbadmin/backup directory. All backups are versioned in directories with the following time scheme (example, exact timestamp will vary): 2022-11-18-0134

/home/dbadmin/backup/latest always points to the latest backup.

Backups are stored in the customer directory. The database schema can be found in the structure directory.

Create additional backups

Additional backups can be created by running:

dbadmin@managedvirtualmachine-xxxxxxx:~ $ sudo nine-mysql-backup 2022-11-18T09:54:19+01:00 Dumped and compressed database 'frontend_production' in 53 seconds 2022-11-18T09:55:04+01:00 Dumped and compressed database 'frontend_staging' in 45 seconds

Storage requirements of the backups

The backup routine creates compressed backups. Depending on the size of the database, this may still result in backups that require a lot of disk space.

To ensure that sufficient disk space is always available, the On Demand database environments have a mechanism that automatically monitors and performs a disk space expansion if required.

Number of backups kept

The number of backups kept can be adjusted via Cockpit. The duration of the retention period can be freely selected between one and 365 days.

Please note that a long retention period requires more storage space, which may result in additional costs.

Disabling backups

To disable backups, the retention time can be adjusted to 0.

In this case, the creation of further backups is deactivated. All backups already created will be deleted shortly after the adjustment.

Access to the created backups

Using the system user dbadmin you can access the created backups via an SSH connection.

SSH access for the user is controlled by storing an SSH key in Cockpit.

Restoring and working with the created backups

The backup routine used is the same as the one we use for our managed servers. We have described how to work with the backups as well as more information about restoring backups in the following support articles:

Automatic storage space expansion

To provide the most robust environment possible, the available storage space is monitored at 5 minute intervals. If our monitoring detects that the available storage space falls below a threshold, an expansion of the storage quota is automatically performed.

Thresholds

For a total storage size below 50 GB, the threshold is 5 GB of free storage space.

For a total storage size above 50 GB, the threshold is 10% free storage space.

Expansion of the storage space

The expansion of the storage space is done automatically in steps of 25 GB.

Reduction of storage space

It is not currently possible to reduce the disk size of database instances. The only way to reduce disk usage is to download a backup of the current instance and restore it to a new instance.

Billing of the storage space expansion

The additional storage space is charged automatically.

Interacting with databases

Connecting

The connection information (FQDN, user, and password) can be found in Cockpit under Access Information. The database servers are accessible via their standard ports.

The database server only accepts TLS connections. Depending on your The instance will only accept TLS connections. Depending on the client or library, you may need to explicitly enable TLS.

mysql -h FQDN -u dbadmin -p

Basic commands

Connecting to your Database:

mysql -h FQDN -u dbadmin -p

Creating a new database named app_prod:

mysql> CREATE DATABASE app_prod;

Creating a new user named app_prod:

mysql> CREATE USER 'app_prod' IDENTIFIED BY 'strongpassword';

Granting the user app_prod privileges to the database app_prod:

mysql> GRANT ALL ON app_prod.* TO 'app_prod'@'%';

For granting more specified privileges, find the details in the official MySQL documentation: Summary of Available Privileges

Changing the user app_prod's password:

mysql> ALTER USER app_prod IDENTIFIED BY 'newstrongpassword';

Deleting the database app_prod:

mysql> DROP DATABASE app_prod;

Deleting the user app_prod:

mysql> DROP USER app_prod;

Use the official MySQL documentation for additional info about user and database management.