This article provides you with an overview of MySQL Workbench and how to use MySQL Workbench to connect to your database remotely.
In cases where you don't want to give the developer too much access to the Nexcess Client Portal, you can install a remote MySQL client like MySQL Workbench to connect to the database remotely.
Note that by default, the Nexcess Client Portal gives you access to your databases using phpMyAdmin.
Prerequisites
The following items are the prerequisites needed:
- You will want MySQL Workbench installed on your local machine. Next, visit the MySQL Workbench Downloads page to download the installer for your operating system. It's available for Windows, macOS, Ubuntu Linux, RedHat Linux, and Fedora.
- You will need the Public IP address of the MySQL Server, which is also the Hosting IP of the website.
- You will need the database username and password for the MySQL account that you wish to connect.
- You should safelist (also called "whitelisting") your local machine's IP address against the MySQL Port.
What Is MySQL Workbench?
The MySQL Workbench provides a graphical user interface for working with SQL servers and databases. You can use it to create entity relation diagrams, manage servers, and run SQL queries.
In addition to being developed and maintained by Oracle, MySQL Workbench is cross-platform, meaning it operates on Windows, Linux, and Mac OS X.
MySQL Workbench has the following functionalities as described in the following sections:
- SQL Development
- Data Modeling (Design)
- Server Administration
- Data Migration
- Visual Performance Dashboard
SQL Development
This feature allows you to connect to a database, run and edit SQL queries and SQL scripts using a built-in SQL editor, and manage connections and data objects.
Data Modeling (Design)
Data modeling lets you visually construct models of your database schema, reverse and forward engineer between a schema and a live database, and use a comprehensive Table Editor to edit all aspects of your database.
The Table Editor lets you easily edit Tables, Columns, Indexes, Triggers, Partitions, Options, Inserts and Privileges, Routines, and Views.
Server Administration
MySQL Workbench provides a visual interface to administer MySQL environments and better understand databases easily. You can administer users, perform backup and recovery, view database health, configure servers, and inspect audit data.
Data Migration
You can migrate tables, data, and objects from Microsoft SQL Server, Microsoft Access, Sybase ASE, PostgreSQL, and other RDBMS.
Visual Performance Dashboard
MySQL Workbench provides a set of tools for improving the performance of MySQL applications.
For example, the Performance Dashboard provides quick access to different performance metrics, while Performance Reports display performance hotspots and high-cost SQL statements.
Using MySQL Workbench to Connect to a Database Remotely
Here’s a step-by-step guide on how to use MySQL Workbench:
1. Install MySQL Workbench
If you do not have it, you can download it here. Select the version that fits your operating system.
Generally, MySQL Workbench is available in three editions:
- Community Edition (Open Source, GPL)
- Standard Edition (Commercial)
- Enterprise Edition (Commercial)
You can visit the MySQL official page to know more about these various MySQL editions.
2. Get Database Credentials Information
To connect to the MySQL Workbench, you will need the Hostname/IP and the Database Username connected to the MySQL database and its password. You can find database credentials in your Nexcess Client Portal under the MySQL section of your plan.
3. Safelisting/Whitelisting Your IP Address
You will need to safelist your local machine IP on the Nexcess Firewall against the MySQL Port. Visit these links to learn how to do it on Shared Servers or Cloud Servers.
If you are on a WordPress hosting/WooCommerce plan, don't hesitate to contact the Nexcess Support Team to safelist your IP for remote MySQL connections. Also, if you have a dynamic IP, you will need to safelist your IP address each time it changes.
4. Open MySQL Workbench
To start a new connection to a database, click on the + button.
5. New Connection Setup
A new window named Setup New Connection will appear. Then, you can fill in the necessary account information.
- Connection Name – It can be something similar to "database_for_example.com" or something more descriptive to help you identify the connection you're making later.
- Connection Method – Leave it as the default, Standard (TCP/IP).
- Hostname – Enter your website's Hosting IP address here.
- Port – Leave it as the default — 3306.
- Username – Database Username.
- Password – Click on Store in Keychain and add your database user's password.
- Default Schema – Can be left blank.
6. Testing Your Connection
Verify your settings by choosing Test Connection.
7. Create Connection
Choose OK to create the connection.
Once you’ve successfully connected to the database, you can view and edit the MySQL Database Instance details under the Administration area to the left of the navigation bar:
- Database Configuration
- Database Status
- Server Status
- Performance Dashboard
- Current Connections
It also allows you to view users and their permissions. In addition to backing up and restoring databases, MySQL Workbench supports importing and exporting MySQL dump files.
Let’s also have a look at the Performance Dashboard.
The Schemas area on the left navigation bar is where you can find the databases listed. With a dropdown arrow next to each database, you can expand your database tables and objects.
From this area of MySQL Workbench, you can view table data, run complex queries, and edit data.
With MySQL Remote Access Enabled, What Are My Next Steps?
Remotely accessing MySQL requires a third-party MySQL client application. Some of these applications are free, while others require purchasing.
Instructions on using the third-party MySQL client application should be provided by the company's website where you downloaded it.
Here are a few of the industry-leading MySQL client applications:
MySQL with Remote Connections: Keep It Secure
Despite being able to open MySQL to remote connections and to prevent possible security breaches, you should grant privileges to selected users only. Additionally, ensure the users are using very strong passwords. Keeping the MySQL server software up-to-date should help too.
MySQL with Remote Connections: Connection Tips
Common connection tips are:
- Keep in mind that SSL is required if you're trying to connect to a cluster environment, like AWS.
- If the connection keeps failing, have the Nexcess Support Team check whether your IP address has been allowed and ensure your credentials are correct.
- Alternatively, you can use a SSH Gateway to securely and efficiently run MySQL commands on your website if you still experience connection issues.
Final Thoughts: MySQL Workbench & Nexcess Web Hosting Database Management
We hope this article was helpful. Now you should be able to connect your database using MySQL Workbench easily without any hassle. Kindly remember that you can always reach out to our support team by email or via Client Portal for 24/7/365 assistance.
Not a Nexcess Customer Yet?
Check out our fully managed hosting plans to get started.
MySQL With Remote Connections FAQs
Some common questions that frequently come up include the following.
Why is Remote Access to the MySQL Database Server Disabled By Default?
The remote access to the MySQL database server is disabled due to security reasons.
Can I Test My MySQL Database Remotely and Verify Database Access?
Yes, you can test the remote MySQL connection using MySQL client applications like MySQL Workbench. Otherwise, you can run the following command from a remote machine with a MySQL client:
$ MySQL -u <local database username> -h <database server IP address> -pIf My IP Constantly Changes, What Should I Do?
If you have a dynamic IP address, the connecting IP address will keep changing periodically, depending on the Internet Service Provider (ISP). To use the Remote MySQL Functionality, you must update the IP address each time it changes.
There are a few solutions you can try if you are having trouble getting through the firewall with a dynamic IP address:
- You can contact Nexcess Support to get your IP address range added to the MySQL Whitelist IP table.
- Otherwise, get a static IP addressing instead for your client computers, and then add the IP addresses as firewall rules.
When Connecting to MySQL, Why Do I Get a Connection Refused Error?
Our servers block Port 3306 with regard to inbound traffic. Contact us via live chat or a phone call to request that Port 3306 be opened for your IP address to connect remotely.
Is MySQL Workbench Free?
Yes, the MySQL Workbench Community Edition is free and available for multiple platforms, including Microsoft Windows, macOS, and Linux. There are other commercial editions that users may buy with additional features:
- MySQL Workbench Community Edition — Open Source (GPL License)
- MySQL Workbench Standard Edition — Commercial
- MySQL Workbench Enterprise Edition — Commercial
Is MySQL Free?
Yes, the MySQL Community Edition is free and available for multiple platforms, including Microsoft Windows, macOS, and Linux. There are other commercial editions that users may buy with additional features:
- MySQL Community Edition — Open Source (GPL License)
- MySQL Standard Edition — Commercial
- MySQL Enterprise Edition — Commercial
- MySQL Cluster Carrier Grade Edition — Commercial
