Vultr DocsLatest Docs

Vultr Managed Databases for MySQL Reference Guide

MySQL and MariaDBManaged Databases
Updated on November 17, 2022
Vultr Managed Databases for MySQL Reference Guide header image

This reference guide for managed MySQL covers all the essential information you need to administer and use your database. This guide assumes you've already deployed a MySQL managed database by following our Quickstart guide and want to learn more.

To get started, navigate to the Databases section of my.vultr.com. Then, click your deployed database to open the administration area containing several tabs.

Overview Tab

The Overview tab contains several sections:

Connection Details

Use the Connection Details section to locate your database connection information. It looks like this:

Connection Details

Here, you'll find the username, password, host, and port number. The password is hidden by default, but you can click the eye icon to reveal it.

Your other options are:

  • Databases: If you have multiple databases, choose one from the Databases drop-down.

  • Users: If you have multiple users, choose the user account from the Users drop-down.

  • Copy Connection String: Use this button to copy a connection string for mysql to your clipboard. Here's an example:

      mysql --host="[YOUR_HOST]" --port=18140 --user="vultradmin" --password="[YOUR_PASSWORD]"

  • Copy MySQL URL: Copies a MySQL URL to your clipboard. It looks like this:

      mysql://vultradmin:[YOUR_PASSWORD]@[YOUR_HOST]:18140/defaultdb

  • Download Signed Certificate: If you need to download a signed certificate for your client, use this button.

Your connection string connects to the primary node and will automatically switch to the newly-elected primary node in case of a database failover. You can use these connection details in web applications, programming languages, or GUI tools like MySQL Workbench, dbForge Studio, HeidiSQL, DataGrip, DBeaver, SQuirreL SQL, and more. Please consult your application's documentation to learn how to use the connection details.

How to Enable SSL Connections

To encrypt your connection with SSL, add --ssl-mode=required to your connection string like this:

$ mysql \
    --host="example.vultrdb.com" \
    --port=16751 \
    --user="vultradmin" \
    --password="example_password" \
    --ssl-mode=required

See the available connection options in the MySQL manual for more information.

General Information

The General Information section of the Overview tab has two user-editable fields and other key information about your cluster. It looks like this:

General Information

  • To rename the cluster, click the Label and type a new name.
  • Click Tag to add a new tag to the cluster.

Trusted Sources

When you deploy a database cluster, it's open to all internet traffic. Use the Trusted Sources section to restrict access to your database cluster.

Trusted Sources

You should list allowed IP addresses in the Trusted Sources section. After you set a trusted IP address, all other traffic is blocked, similar to a firewall. Setting the trusted sources is a good security practice before you create your databases and add users.

To add a trusted source:

  1. Click Edit.
  2. Enter an IPv4 address. IPv6 is not supported.
  3. Click Save.

You can add multiple IP addresses at a time, separated by commas. When complete, your trusted sources list looks similar to:

Trusted Sources 2

Actions

In the Actions section of the Overview tab, you'll find:

  • Fork Database Cluster: When you fork a cluster, you create a second cluster from a snapshot of the original. You can change the number of nodes, the type, and the location of the new cluster.
  • Restore From Backup: Restoring from backup creates a new cluster without standby or read-only nodes.

Resources

This section has links to documentation and support.

Usage Graphs Tab

Monitor the cluster's resource use in this tab.

Queries & Logs Tab

Use this tab to monitor the running queries, database statistics, and recent log entries.

Users & Databases Tab

Use this tab to manage the cluster's users, passwords, and databases. When you first deploy a cluster, it looks like this:

Users

  • The eye icon reveals the user's password.
  • The copy icon copies the password to your clipboard.
  • The Add New User button creates a new database user. Enter a username and password in the pop-up window. The system automatically generates a strong password if you leave the password blank.
  • Use the Reset Password button to reset a user's password.
  • The Add New Database button allows you to create a new database in the cluster.

Migration Tab

See the Managed Database Migration guide for more information about this section.

Settings Tab

Use the Settings tab to manage the cluster options. Choose from the following menus.

  • Upgrade Window: Set the preferred time for system upgrades.
  • Time Zone: Set the default timezone for your cluster.
  • SQL Modes: Use SQL modes to define what validation checks MySQL should perform on data and what SQL syntax it should support. See Server SQL Modes in the MySQL documentation for details on each mode.
  • Change Plan: Scale up your database cluster.
  • Datacenter Location: Migrate your entire cluster to a new datacenter location.
  • Delete Managed Database:: Destroy the cluster and all backups.

Frequently Asked Questions

Is the database backed up?

Yes, all managed databases are backed up for disaster recovery purposes. In addition, all plans other than Hobbyist offer user-initiated recovery, forking, and point-in-time backups. You can use these backups to restore a cluster, which overwrites the current cluster's data. You can also fork a cluster from the backup, creating a new cluster with the backup data and not modifying the existing cluster. You'll find those options in the Actions section of your cluster's information page.

MySQL databases offer point-in-time recovery history; the duration available depends on your node plan.

  • Premium: 30 days
  • Business: 14 days
  • Startup: 2 days
  • Hobbyist: None

How do I find my Node Plan?

You can deploy managed databases in several node plans, which are a shorthand way of identifying the available size and number of nodes. We offer Hobbyist, Startup, Business, and Premium node plans.

After you deploy a managed database, look in the General Information section of your cluster's information page. The Node Plan appears below the Monthly Price. The plan name format is Vultr-Dbaas-[plan type]-[other internal information]. Here are two example node plans, with the important information underlined in red.

Premium node plan:

Premium Plan

Business node plan:

Business Plan

The node plan determines what backup and recovery options are available.

Can I deploy managed databases with the Vultr API or vultr-cli?

Support for Vultr API and vultr-cli is coming soon. Today, you can deploy managed databases from the Vultr customer portal.

How do I scale my database cluster?

  • Use the Change Plan menu on the Setting tab to scale your cluster up.
  • To scale a cluster down, migrate or fork it to a new, smaller cluster.

How do I create an admin (superuser) or root-level account?

You cannot create superuser accounts. To create a standard user account, use the Vultr customer portal.

Can I use multiple primary (write) nodes?

A cluster can only have one primary node. It can have multiple replica (read-only) nodes.

Do I need to use primary keys for my tables?

Yes, you must use primary keys for all database tables, which is enforced through the database configuration.

What MySQL storage engines are available?

MySQL databases use the InnoDB storage engine. We do not support other storage engines.

What versions of MySQL are available?

Vultr's managed database clusters use the latest version of MySQL.

Can I set the SQL Mode for MySQL?

Yes. MySQL can operate in different SQL modes. You can apply these modes in the database server's Settings tab through the customer portal. See the MySQL documentation to learn more about SQL modes.

Notes about MySQL Modes

  • The ANSI (Combination Mode) SQL mode includes the following SQL modes: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY.
  • Some modes can be toggled on individually, but if ANSI (Combination Mode) is enabled, that setting will take precedence.
    • For example, if you want to disable ONLY_FULL_GROUP_BY, you will also need to disable ANSI (Combination Mode), because it is a part of that mode bundle.
  • According to the MySQL documentation, TRADITIONAL (Combination Mode) includes the following SQL modes: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.
  • We do not support the NO_BACKSLASH_ESCAPES or PAD_CHAR_TO_FULL_LENGTH SQL modes at this time.

Some features that you cannot set globally can be enabled per session. For example, you cannot set binlog_row_value_options = partial_json globally, but you can set it per session like this:

 SET SESSION binlog_row_value_options = partial_json;

What MySQL database properties can I configure?

Users cannot change MySQL database properties or use the root user for cluster stability. However, you can view the database properties with the SHOW VARIABLES; SQL query.

More Resources