How to Use MySQL Workbench for Your Website
Although phpMyAdmin and other web hosting tools for managing databases are good, there may be times when you want something more powerful. MySQL Workbench is a good source in that instance.
MySQL Workbench is a free database design and administration tool made by Oracle that joins together two previous products: MySQL Query Browser and MySQL Administrator.
How to install MySQL Workbench
To download and install the Workbench for most operating systems, use the following steps:
Scroll down the screen to the download section, and then select your operating system (OS) from the drop-down box.
Click the Download button for the appropriate installer.
Install the software as appropriate for your OS.
Open MySQL Workbench and you are presented with three columns:
SQL Development: This section gives you an SQL window where you can run any SQL commands you need on your database. This is useful for manually running maintenance commands, querying data, and editing data.
Data Modeling: Useful for more advanced database users, this section helps you design and analyze databases. This is a design tool rather than a maintenance tool, but it can be very handy when you’re creating and dealing with complex database structures.
Server Administration: Some of the facilities in this section require remote administration to be switched on at the server, and many hosts won’t allow that. Even without that, though, the Server Administration section shows you some useful information about the health of the server and provides an essential database backup facility.
How to configure MySQL Workbench
After you open MySQL Workbench on your computer, the next thing you need to do is set up a connection to your server. Follow these steps:
Click New Connection in the left column.
Give your connection a name and select your connection method.
You can find out the correct connection method for your server from your host.
Enter your hostname, port, and username.
The port is usually 3306, unless your host advises you otherwise.
Choose to store your password in the vault if you want your computer to remember it for you (or in Keychain if you’re on a Mac).
If you don’t choose to store the password, you are asked for it every time you connect to the server.
Click Test Connection.
The software attempts to connect to the server using the details you entered. If it is successful, you see a message telling you that the connection was successful and the connection parameters are correct. You can then click OK to complete the setup.
If the connection is not successful and the error message says that Host xxxxxxx is not allowed to connect to this MySQL server, move on to Step 6.
Make a note of the name of the host it says is not allowed to connect.
Log in to your hosting control panel.
Look for an option labeled Remote MySQL and select it.
On the Remote Database Access Hosts page, enter the hostname exactly as you copied it down from the error message.
Click Add Host.
Return to MySQL Workbench and test the connection again.
If it still fails, check with your host to confirm that you are allowed remote connections.
Next, use the following steps to set up a connection to the server in the Server Administration section:
Click New Server Instance in the right-hand column of the Workbook home page.
Select Take Parameters from Existing Database Connection, and select the connection you just created from the drop-down box.
When the connection check is complete, click Continue again.
Select whether to use remote management and click Continue.
Enter a name for the Server Instance and click Finish.
Return to the home page, and your connections will appear in the boxes on the screen. To use the SQL Development or Server Administration tools, click the connection you just created.
If you have more than one server, you can create multiple connections. Just make sure to give them obvious names so you can tell which is which.