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.
The Overview tab contains several sections:
Use the Connection Details section to locate your database connection information. It looks like this:
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.
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.
The General Information section of the Overview tab has two user-editable fields and other key information about your cluster. It looks like this:
To rename the cluster, click the Label and type a new name.
Click Tag to add a new tag to the cluster.
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.
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:
Click Edit.
Enter an IPv4 address. IPv6 is not supported.
Click Save.
You can add multiple IP addresses at a time, separated by commas. When complete, your trusted sources list looks similar to:
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.
This section has links to documentation and support.
Monitor the cluster's resource use in this tab.
Use this tab to monitor the running queries, database statistics, and recent log entries.
Use this tab to manage the cluster's users, passwords, and databases. When you first deploy a cluster, it looks like this:
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.
See the Managed Database Migration guide for more information about this section.
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.
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
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:
Business node plan:
The node plan determines what backup and recovery options are available.
Support for Vultr API and vultr-cli is coming soon. Today, you can deploy managed databases from the Vultr customer portal.
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.
You cannot create superuser accounts. To create a standard user account, use the Vultr customer portal.
A cluster can only have one primary node. It can have multiple replica (read-only) nodes.
Yes, you must use primary keys for all database tables, which is enforced through the database configuration.
MySQL databases use the InnoDB storage engine. We do not support other storage engines.
Vultr's managed database clusters use the latest version of 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.
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.
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;
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.