Key takeaways
- MySQL Workbench connects to native or distant databases utilizing the suitable methodology, host, port, username, and password.
- Most connections use Customary TCP/IP, whereas safe distant setups might require IP safelisting, SSH, or each.
- Server-side entry have to be configured earlier than you check the connection in Workbench.
- Affirm credentials, entry guidelines, and connection methodology first to keep away from frequent errors.
MySQL Workbench offers a desktop interface for connecting to MySQL databases, together with distant databases hosted exterior your native machine.
In circumstances the place you don’t need to give the developer an excessive amount of entry to the Nexcess Shopper Portal, you may set up a distant MySQL shopper like MySQL Workbench to connect with the database remotely.
Notice that by default, the Nexcess Shopper Portal offers you entry to your databases utilizing phpMyAdmin.
What you want earlier than you join
Earlier than you open MySQL Workbench, accumulate the main points your connection requires. This protects time and helps you keep away from failed connection makes an attempt brought on by lacking entry, incorrect credentials, or blocked ports.
The next objects are the conditions wanted:
- You will have MySQL Workbench put in in your native machine. Subsequent, go to the MySQL Workbench Downloads web page to obtain the installer on your working system. It’s obtainable for Home windows, macOS, Ubuntu Linux, RedHat Linux, and Fedora.
- You will have the Public IP deal with of the MySQL Server, which can also be the Internet hosting IP of the web site.
- You will have the database username and password for the MySQL account that you just want to join.
- You must safelist (additionally known as “whitelisting”) your native machine’s IP deal with in opposition to the MySQL Port.
You might also want:
- The MySQL port, normally 3306
- The database title or default schema
- Affirmation that distant MySQL entry is enabled
- Firewall entry on your present IP deal with
- SSH username, SSH password or key, SSH hostname, and SSH port if you happen to plan to attach by an SSH tunnel
What’s MySQL Workbench?
The MySQL Workbench offers a graphical consumer interface for working with SQL servers and databases. You need to use it to create entity relation diagrams, handle servers, and run SQL queries.
Along with being developed and maintained by Oracle, MySQL Workbench is cross-platform, which means it operates on Home windows, Linux, and Mac OS X.
MySQL Workbench helps customers connect with MySQL databases, browse schemas and tables, run SQL queries, handle customers, import and export information, and evaluate server efficiency from one desktop utility.
create a brand new MySQL Workbench connection
Right here’s a step-by-step information on how one can use MySQL Workbench:
1. Set up MySQL Workbench
For those who don’t have it, you may obtain it right here. Choose the model that matches your working system.
2. Get database credentials data
To connect with the MySQL Workbench, you will want the Hostname/IP and the Database Username related to the MySQL database and its password.
For those who host with Liquid Net or Nexcess, test your internet hosting management panel or Shopper Portal for the database hostname, IP deal with, username, and password. The precise location will depend on your internet hosting product and management panel.
3. Safelist your IP deal with
You will have to safelist your native machine IP on the Nexcess Firewall in opposition to the MySQL Port.
Safelisting permits your pc to succeed in the MySQL service out of your present public IP deal with.
In case you are on a WordPress internet hosting/WooCommerce plan, don’t hesitate to contact the Nexcess Assist Workforce to safelist your IP for distant MySQL connections. Additionally, when you have a dynamic IP, you will want to safelist your IP deal with every time it adjustments.
4. Open MySQL Workbench
Open MySQL Workbench in your pc.
To begin a brand new connection to a database, click on on the + button.
5. Enter a connection title
A brand new window named Setup New Connection will seem. Then, you may fill within the needed account data.
Connection Title – It may be one thing much like “database_for_example.com” or one thing extra descriptive that will help you determine the connection you’re making later.
Select a reputation that helps you acknowledge the location, setting, or venture later. For instance, use names like store-production, client-site-staging, or instance.com database.
6. Select the connection methodology
Most simple MySQL Workbench connections use Customary TCP/IP.
Connection Methodology – Go away it because the default, Customary (TCP/IP).
Use Customary TCP/IP over SSH when the database shouldn’t settle for direct distant MySQL connections or when your internet hosting setup requires an SSH tunnel for safer entry.
7. Enter the hostname, port, and username
Fill within the connection fields rigorously.
| Subject | What to enter |
| Hostname | Use localhost or 127.0.0.1 for an area database, or use the server IP deal with or hostname for a distant database |
| Port | Use 3306 except your server makes use of a customized MySQL port |
| Username | Enter the MySQL database username |
| Password | Retailer or enter the MySQL consumer’s password |
| Default schema | Go away clean except you need Workbench to open a particular database by default |
Hostname – Enter your web site’s Internet hosting IP deal with right here.
Port – Go away it because the default (3306).
Username – Database Username.
Password – Click on on Retailer in Keychain and add your database consumer’s password.
Default Schema – Could be left clean.
8. Save the password securely
MySQL Workbench can retailer credentials so that you don’t have to enter the password each time you join.
Use your system’s safe credential storage, equivalent to Keychain on macOS or the obtainable vault choice in Workbench, when applicable. Keep away from saving passwords on shared computer systems.
9. Check the connection
Confirm your settings by selecting Check Connection.
If MySQL Workbench returns an error, keep away from altering fields at random. Affirm the hostname, port, username, password, safelisted IP, firewall guidelines, and connection methodology.
10. Save and join
Select OK to create the connection.
After saving, click on the connection tile from the MySQL Workbench residence display to open the database connection.
Customary TCP/IP vs. Customary TCP/IP over SSH
MySQL Workbench helps a number of connection strategies. For many internet hosting customers, the choice normally comes right down to Customary TCP/IP or Customary TCP/IP over SSH.
| Connection methodology | Finest for | What you want |
| Customary TCP/IP | Native databases or distant databases that enable direct MySQL entry out of your IP | MySQL hostname, port, username, password, and allowed distant entry |
| Customary TCP/IP over SSH | Distant databases the place you need or want visitors to go by an SSH tunnel | SSH hostname, SSH username, SSH password or key, MySQL hostname, MySQL port, and MySQL credentials |
When Customary TCP/IP is sufficient
Customary TCP/IP may fit when your database server accepts distant MySQL connections and your native IP deal with has permission to attach by the firewall.
This setup requires cautious entry management. Safelist solely the IP addresses that want database entry, and take away entry when a developer, vendor, or short-term consumer now not wants it.
When to make use of Customary TCP/IP over SSH
Customary TCP/IP over SSH can add a safer connection path by sending database visitors by an SSH tunnel as an alternative of exposing MySQL entry broadly.
Use this methodology when your internet hosting setting requires SSH entry for distant database work, when direct MySQL entry stays blocked, or when your crew desires tighter management over how database connections attain the server.
How to connect with a distant database in MySQL Workbench
Distant database connections require settings in each locations: MySQL Workbench in your pc and the server or internet hosting account that controls entry to the database.
Earlier than testing the connection, verify that the database consumer can join remotely, the firewall permits entry out of your present IP deal with, and the MySQL service listens on the anticipated port.
In case your internet hosting setting separates the net server from the database server, use the database server hostname as an alternative of the web site hostname.
Frequent MySQL Workbench connection errors and fixes
Connection errors usually come from lacking conditions reasonably than an issue with MySQL Workbench itself.
| Error or symptom | Doubtless trigger | What to test |
| MySQL Workbench can not connect with server | MySQL service will not be working or the host is unreachable | Affirm the server is on-line and MySQL is working |
| Entry denied for consumer | Incorrect username, password, host, or consumer permissions | Recheck credentials and ensure the MySQL consumer has the suitable privileges |
| Host will not be allowed to attach | Your IP deal with or host has not been allowed for distant MySQL | Safelist your present public IP deal with |
| Connection timed out | Firewall, community, or port entry blocks the connection | Verify firewall guidelines, internet hosting entry guidelines, and port 3306 |
| Port 3306 blocked | Supplier blocks inbound MySQL visitors by default | Ask assist to permit entry out of your IP or use SSH if supported |
| SSH connection fails | Incorrect SSH hostname, username, password, key, or port | Recheck SSH credentials and ensure SSH entry |
| Password immediate retains showing | Password was not saved or the saved password is wrong | Replace the saved password in Workbench or enter it once more |
| SSL error | The server requires SSL or the SSL settings don’t match | Affirm whether or not SSL is required and add the proper SSL settings |
If the connection retains failing, have the Nexcess Assist Workforce test whether or not your IP deal with has been allowed and guarantee your credentials are right.
Alternatively, you should use a SSH Gateway to securely and effectively run MySQL instructions in your web site if you happen to nonetheless expertise connection points.
Safety suggestions for MySQL Workbench connections
Distant database entry could make improvement and administration simpler, nevertheless it additionally wants limits.
Regardless of with the ability to open MySQL to distant connections and to forestall attainable safety breaches, you need to grant privileges to chose customers solely. Moreover, make sure the customers are utilizing very sturdy passwords. Preserving the MySQL server software program up-to-date ought to assist too.
Use these safety fundamentals when connecting with MySQL Workbench:
- Restrict entry to chose customers solely
- Safelist solely the IP addresses that want entry
- Use sturdy, distinctive passwords
- Use SSH or SSL the place applicable
- Preserve MySQL and MySQL Workbench up to date
- Overview database consumer privileges usually
- Take away distant entry when a consumer or vendor now not wants it
What you are able to do after connecting
After you connect with the database, MySQL Workbench offers you instruments for improvement, administration, and information administration.
When you’ve efficiently related to the database, you may view and edit the MySQL Database Occasion particulars underneath the Administration space to the left of the navigation bar:
- Database Configuration
- Database Standing
- Server Standing
- Efficiency Dashboard
- Present Connections
It additionally means that you can view customers and their permissions. Along with backing up and restoring databases, MySQL Workbench helps importing and exporting MySQL dump information.
The Schemas space on the left navigation bar is the place you could find the databases listed. With a dropdown arrow subsequent to every database, you may develop your database tables and objects.
From this space of MySQL Workbench, you may view desk information, run advanced queries, and edit information.
join two databases in MySQL Workbench
MySQL Workbench can retailer a number of saved connections. If it is advisable work with two databases, the suitable setup will depend on the place these databases reside.
If each databases exist on the identical MySQL server, create one connection to that server and use the Schemas panel to open every database.
If the databases reside on totally different servers, create a separate saved connection for every server. Every connection can have its personal hostname, port, username, password, SSH settings, and default schema.
Connecting to a number of databases offers you entry to separate schemas or saved server connections primarily based in your consumer permissions. It doesn’t mix the databases into one setting.
MySQL Workbench FAQs
Getting began with MySQL Workbench
A profitable MySQL Workbench connection will depend on two issues: the suitable desktop settings and the suitable server-side entry setup.
Begin by gathering your host, port, username, password, connection methodology, and remote-access necessities earlier than opening Workbench.
Liquid Net managed internet hosting offers your crew the assist, safety steerage, and internet hosting infrastructure wanted to handle databases with out overexposing entry. Discover Liquid Net managed internet hosting to discover a internet hosting setting that helps safe database administration.