How to Connect to a Database with MySQL Workbench

Download MySQL Workbench

One tool you can use to connect to remotely connect to databases on your shared, VPS, or Dedicated hosting account is MySQL Workbench. MySQL Workbench is a free tool you can use to connect to your server databases from your personal computer. MySQL Workbench can be Downloaded for free at the following link:

http://dev.mysql.com/downloads/workbench/

You will want to use the Windows (x86, 32-bit), MSI Installer if you are on Windows. The file we are installing in this tutorial is the mysql-workbench-gpl-5.2.47-win32.msi file.

Once you have downloaded MySQL Workbench, you can install it onto your computer. After you have the program installed, follow the steps below to set up a remote connection.

Add IP to your Remote MySQL

In order for you to be able to connect to your databases remotely you will need to add your local computer IP address to the Remote MySQL in cPanel. You can get your IP address by going to the following link:

What’s My IP address?

Steps to connect to your database remotely

  1. Open MySQL Workbench.
  2. New Connection MySQL Workbench

    Click New Connection towards the bottom left of MySQL Workbench.

  3. Test Connection MySQL Workbench

    In the “Set up a New Connection Dialogue” box, Type your Database connection credentials. The credentials will be like the following:

    Connection Name: You can name this whatever you like.
    Connection Method: Standard (TCP/IP).
    Hostname: You can use your domain our your cPanel IP address.
    Port: 3306
    Username: Your cPanel username or the user you created for the database.
    Password: cPanel password or the password for the database user that was created.
    Default Schema: This can be left blank.

    Click Test Connection.


  4. Enter password MySQL Workbench

    Type your password and click the “Save Password in Vault” check box.

    Click Ok.


  5. Correect connetion MySQL Workbench

    MySQL Workbench should say “Connection parameters are correct“. Click Ok.

  6. Click ok MySQL Workbench

    Click Ok again to accept the connection settings.

  7. Open Connetion MySQL Workbench

    Now under the SQL Development section you will see your connection listed in the Open Connection to Start Querying box. Click your newly created account.

    View Databases in MySQL Workbench

    Now you will see the databases list in the area on the left.

Thoughts on “How to Connect to a Database with MySQL Workbench

  • HI I have a question is that for the server only ?

    if YES then what step am i going to set for the other pc  that is connected with the same hotspot with the server?

  • Hi

    Thanks for the detailed explanation.

    When I try to connect to MySQL I’m getting below error.

    “Connect failed: Access denied for user ‘root’@’localhost’ (using password: YES)”

    I downloaded the latest version of both Mysql server and Workbench which is 8.0

    Please let me know what wrong I am doing here.

    • It looks like a typical user/password combination error. Are you certain you’re using the correct password? Remember the MySQL root password may be different from the server root user password. I’d advise resetting the password to be sure.

  • 2- I am getting an error when connecting to an existing remote database (I think it is rev 4.5): “Failed to Connect to MySQL at ASHSxxxxx:3306 with user root. Malformed packet”.

    What can I do to make it work? Other than upgrading the DB (I cannot do that at this time).

  • hi

    i had a problem in making database .it needs connection and when i try to make connection then it ask for host name i dont under stand what to write in host name . kinly tell me the solution so i make database.

  • I am able to connect, but I get a connection warning. It says:

     

    Incompatible/nonstandard server version or connection protocol detected (10.1.32)

     

    A connection to this database can be established but some MySQL Workbench features may not work properly since the database is not fully compatible with the supported versions of MySQL.

    MySQL Workbench is developed and tested for MySQL server versions 5.6, 5.7, and 8.0

    Is there a way to update the SQL version on my inmotion server account?

  • I hvae a database on my local PC that I would like to move to a network drive/folder and access it there from my PC.  How do I get the database to the network folder so it’s usable from my PC?

    Thanks.

    • Depending on the type of database you’re using, you should be able to export the database. If you want to use the database server available with our hosting services, then you will need to use our MySQL server. If you can export the database into a base format such as CSV or XML, then you should able to move your database from your local PC to the server database. The application you would use to import the CSV file into the database would phpMyAdmin. Please see this article on how to use phpMyAdmin to import a database.

  • Hello – Am not able to connect Workbench to the SQLExpress db engine installed on my laptop’s Windows 7 Professional hard drive.

    Have successfully connected to the db engine with SQL Server Mgmt Studio, run queries, etc.

    In Workbench, I do not know which Connection Method to use, and have tried filling in variations of SSMS’s connection properties with no luck. (They are Server Name: DACERP645MG\SQLEXPRESS; Authentication: Windows; User Name: DACERP645MG\Deirdre; Connect to database: <default>….)

    Windows 7 has no password. I have tried creating the Workbench connection with Mgmt Studio running / connected, and without.

    All help greatly appreciated!  Thanks, Dee

    BEGINNING: On Sept 7, 2017, I installed both MS SQLServer Express, Mgmt Studio and prerequisite tools – to their default paths. Seven days later I installed Workbench. Windows User Profiles (all Type Local):

    DACERP645MG\Deirdre – Administrator
    NTSERVICE/MSSQL$SQLEXPRESS
    NTSERVICE/MSSQL$SQLSRVEXPR
    NTSERVICE/MSSQLFDLauncher$SQLSRVEXPR
    NTSERVICE/ReportServer$SQLSRVEXPR

    TESTING

    Have tried different online suggestions:  With my service provider’s IP address (Host 127.00.01; Port 3306), Workbench loads with Service Status: Stopped. Start Sever brings log message f Cannot connect to MySQL server on ‘127.00.01’ (10061) (2003).  Connection properties that display are – Host: n/a; Socket: n/a; Port: n/a; Version: n/a; Compiled For: n/a (n/a); Configuration File:  C:\Program Data\MySQL\MySQL Server 5.5\my.ini – which does not exist.

    Have searched for SQL Server-related *.ini files I both C:\Program Files\, C:\Program Files (x86)\. Also searched C:\ for *.ini, by Date Modified and found only long file names in C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn and C:\Windows\inf\MSSQL$SQLEXPRESS\0009.

     

    SERVICES:  Both services SQL Server (SQLEXPRESS) and SQL Server (SQLSRVEXPR) are Started and Automatic. When I try to start SQL Server Agent (SQLEXPRESS) and SQL Server Agent (SQLSRVEXPR), I get the message that “… service on Local Comptuer started and then stopped. Some services stop automatically if they are no in use by other services or programs.”

    • It is difficult to troubleshoot your local environment since we do not have access or know how it was configured. But, I recommend checking your server’s logs for records of the connection. Also, check your firewall and port settings to ensure they allow incoming MySQL connections.

      Thank you,
      John-Paul

  • Nice post! This is really helpful. Its nice information for WordPress users. This is really helpful to used my business site developement.

    Thanks.

  • Hi,

    I was working on a website and want to get the PHP portion of it working. Does Mysql Workbench serve as a web hosting server as well? If so, what is the general directory where I would have to say my files. Also if there are any special configurations I would need setup prior.

    Thanks!

    • Unfortunately, no, MySQL Workbench is just a database client. It’s basically an application that connects to a database and provides some helpful management tools. You will still need a web server or hosting account with PHP and database software like MySQL or MariaDB installed.

    • Hello Louis,

      Thanks for using our tutorial! If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

  • I have been able to link to my database and seel all my tables, but I am getting an error when I try to creat or edit a table. “ERROR 1044: Access denied for user …” Im not sure where im missing allowing access to my database?

  • One question , and it is one that I cannot seem to get a clear answer to. If I install MySql workbench on a windows client can I do a remote connection to a backend MySql database sitting on a Linux RedHat Server ??

    • Hello Joseph,

      I can answer this question in respect to our hosting servers which use a flavor of Linux called CENTOS. As per the article above, you can create a remote connection to allow the database client to connect to the database. I’m not sure if it would be any different with your Linux Redhat server.

      If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

  • I was looking for the database server hostname, excecuting this from the myPhpAdmin solved the problem:

    SHOW VARIABLES WHERE Variable_name = 'hostname'
  • Hi excuse me I created a php file for working with MySQL Workbench. I searched in internet that when one is using XAMPP for example you will need to place the file in the “C:\xampp\htdocs” directory (or if your XAMPP install directory is different use that directory\htdocs). For MySQL Workbench where do I need to place my php file?

    • Hello Sebastian,

      Apologies for the confusion with MySQL workbench. We can provide support for the software as it applies to hosted servers. However, your issue has to do with placement or location of a file on a local computer hard drive. You will need to review the MySQL Workbench documentation for details on this issue.

      If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

    • Hello Matthew,

      Your application would need to be able to use a remote MYSQL connection with port 3306. Please use the directions above for creating the connection. If you are the creator of the application, then you would need to construct the connection in your application. If you’re not the developer, then you need to speak with the developer of your application for details on how you can get the application to connect to MySQL. We do not provide coding support for you as it is beyond the scope of support.

      If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

  • i cant connet to mysql database even after adding my machine ip address on the list of allowed ips in cpanel

    please assiste

    • Hello ceasar,

      It should work fine assuming all credentials are correct. What is the specific error message you are getting?

      Kindest Regards,
      Scott M

    • Hello Amy,

      It seems that it is not possible to load a DB2 database into workbench as IBM has made the software proprietary.

      Best Regards,
      TJ Edens

  • HI ALL,

     

    I want send an automatic email  in mysql as backend using scheduler….

    can let me know how to send an email ..with steps..

    kindly help me out in this…

    Thanks ,

    Nagendra.

    • Hello Nagnedra,

      Apologies but that is unfortunately beyond the scope of our support. We do not provide coding support. You may want to study this post that describes a person approaching the same issue.

      Regards,
      Arnel C.

  • I dont have a dedicate IP and it is dynamically allocated by ISP so does it mean i have to change IP on cpanel everytime i refresh the router. (mysql remote connecting to workbench)

    • Hello Zam,

      Yes, you will need to enter the current IP every time you want to connect.

      Kindest Regards,
      Scott M

  • Sorry, did not realize I was on a hosting site. Not having heard of Workbench, I thought I was on a site for support for workbench. Excuse my question please, I am not a customer of yours. Will seek support elsewhere.

    • I’m happy to help where I can.

      Regarding how GravityForms stores it’s data, it is a WordPress standard and nearly all plugins store data in the same way keep things organized. GravityForms is by far the best form handling plugin available for WordPress and is highly respected by WordPress developers, including myself.

      If you’re trying to just work with a database, PHPMyAdmin or MySQL Workbench are your best options.

  • Hi …. before I get into learning your interface with MySql I have a simple question. but first here is my background. I used to build online Apps for websites with MSSQL, and mainly ASP/Javascript etc. I retired 7 years ago, and now find myself on a Linux server with access to MySQL. I have a project I want to do that is very simple, but I don’t know PHP nor MySQL. However, there is plenty of sample coding available onlone that i can edit for the simple things I need to do. I used to use the free MS tool for remote access to the MSSQL server I hosted with a provider, in order to create tables, fields, import/export data, edit field data when needed quickly for a client, do certain queries for maintenance or correcting mistakes made (made plenty of those). Having the visual aid with handson for repair and maintenance type queries was great. Of course most of my work was done from ASP based code. And so my question is, should I expect that Workbench will basically give me the same type of tools that I am used to. I am using WordPress and I already bought Gravity Forms, that was a mistake, they integrate the form data somehow into the WordPress table. I can only find one plugin that creates separate tables, but it is very limited and not well supported. So it looks like my dreaeded concern has raised its head, I have to work from scratch. I desparately need a tool like the one I am used to, because my memory is failing and learning lotsa new coding is only a limited option. At least the project is simple. Finding that your tool might be the trick for me, is great. What can I expect to be able to do out of the things I listed above. They are the only tools I need for a remote connection, create tables, create fields, export/import data, backup, edit fields/rows when I made an error or need a change, and move data from one field to another through query. 

  • can any one help me on this:

    I have installed mysql, i clicked on new connection and entered username and password

    it says Connection parameters are correct SSL not enabled.

    how can i enable SSL.

  • Hello Scott M,

    Thanks for your reply.

    SSL connection is very important???Can I let it go and then still continue to use the software?
    I would like to create a database to connect the php file, then doing the mail merge function to send a mass email to many recipients.

    I am a new user. It is my school project. Could you mind to help me?

    Thanks

    Regards,

    Sally

     

    • Hello Sally Mak,

      If you are still needing to connect to the database via SSL then you will need to log into the cPanel and use PHPMyAdmin. You can still use the software without the SSL option as the information is still secure.

      Kindest Regards,
      TJ Edens.

  • There is actually no requirement for the site certificate.  The SSH tunnel is established separately from SSL/TLS.  Although SSH may utilize SSL/TLS, in the case of MySQL Workbench, only the SSH connection information is required.

    Reply

  • Hello. I tried it but there are some problems.

    “Connection parameters are correct 
    SSL not enabled”

    How to enable the SSL?
    Why it cannot enable the SSL?

    >< Could you comment the problems?

    HELP!!!

    • Hello Sally,

      Port 3306 is the port used to connect to MySQL, but there is not an SSL connection involved.

      Kindest Regards,
      Scott M

  • If your site has a certificate installed, you can use SSH to secure you connection to MySQL for MySQL Workbench.  In MySQL Workbench, setup the connection as follows:

    • SSH Hostname: <yourdomain.com>:2222
    • SSH Username: <yourSSHuser>
    • SSH Password: <sshpasword>
    • SSH Key File: <path to your SSH private keyfile>
    • MySQL Hostname:  127.0.0.1
    • MYSQL Server Port: 3306
    • Username: <DBUserAccount>
    • Default Schema:  <leave blank>

    Click Test Connection and you will be prompted for the <DBUserAccount> password which you can store in your keychain.

    • Hello Shubam,

      What is the error message you are receiving on step 5? Be sure that your connection parameters are correct for the server you are trying to connect to.

      Kindest Regards,
      Scott M

  • hi

    i install workbench 6.1 and tried to connect it showing following error

    Failed to connect to mysql at ridgesushi.com:3306 with user ******

    can’t connect to MYSQL on ‘ridgesushi.com'(10060)

     

    but when i tried to connect with localhost it’s working well

     

    may i know what is the issue ??

     

  • Thank for replying Arn,

     I’ve currently installed mysql workbench 5.2ce.. Also I check that have an update available for 6.0. So my question is it ok to update newer version? Do I get lost my current database and tables?

    • Hello Dushyant,

      Thanks for the question. Actually, the .SQL file is a text file. If you’re going to be importing data, it would need to be formatted in some type of table format such as what’s found in .csv files in order to be of some use. You can typically use a SQL client such as phpMyAdmin to import the file. If, as per the posts above, you’re trying to programmatically update the database, you would need to open the table and match the data you’re importing per the database constraints. I hope that helps to answer your question. Please let us know if you need any further information. If you’re interested, check out our article on Importing a Database using phpMyAdmin.

      Regards,

      Arnel C.

    • Hello Dushyant,

      I’m not sure what you have loaded, but here is a link for MySQL Workbench version 6.0. The link will also lead to the documentation on this tool. You can also do it via shell (Plugins > Start Shell for MySQL Utilities):

      $ mysqldbimport –server=user:pass@host:port:socket db1.csv db2.sql db3.gri
      You can type –help after each command for a listing of the flags.

      $ mysqldbexport –server=user:pass@host:port:socket db1, db2, db3

      (note: this requires either a VPS/Dedicated server account and the appropriate access level).

      Regards,
      Arnel c.

    • Hello Dushyant,

      Yes you can import data using MySQL Workbench, but you don’t have separate MySQL Workbench databases. It is simply a tool that you use to manage MySQL databases, much like you could use phpMyAdmin on the server itself.

      Once you’ve created a connection to a database on the main Home screen in MySQL Workbench, and then connected to that database, you should see the Navigator panel on the left-hand side.

      There should be a Data Import/Restore button a bit further down that you can click on. Then on the next page, you’d more than likely want to select Import from Self-Contained File and then select the location of the .sql backup that you’d like to import into the currently selected database.

      – Jacob

  • Thanks for your reply.  

    I’m not that good to do it… any way is there any screen shot to show me to set up job? 

    Or do you have any pdf to take me start working on it..

    • Hello Dushyant,

      Again, are you sure that what you are trying to setup requires a MySQL job? And what are you referring to when you are talking about a job, or job schedule?

      If you’re talking about using the MySQL Event Scheduler, then I’d recommend reading that documentation from MySQL to help understand the concepts of MySQL events, and see if that is indeed something you need to use.

      I don’t see a way to directly add MySQL events from MySQL Workbench, so you would just need to use the standard method of doing a normal query for these based on the Event syntax. But again this is only going to be available on a server in which that is turned on server-wide.

      – Jacob

  • Jacob, Thanks for replying me back,

    I’m planning to set up job in mysql  workbench. This job is for the one of the function is referencing the three tables to populate the data in mysql workbench database. That data is comming from SQL database. So do I also need to set up alert too to get the data from sql server? 

    Please let me know how to do it?

    • Hello Dushyant, no problem at all.

      Are you sure that what you’re trying to accomplish is best served by a remote MySQL interface? It sounds like you might be trying to programmatically alter the data in your database, and this is typically done with a scripting language such as PHP interacting with the MySQL database.

      Typically you’d have your PHP script connect to your database you need to pull data from, then connect to the database you’d like to put that information into and do an INSERT query.

      Doing it this way, you could then just setup a normal cron job on the server to run your PHP script to keep updating your databases.

      Alternatively this probably could be done with MySQL Workbench, but you would need to turn on the MySQL Event Scheduler to automate things and I’m not sure of the exact process for using jobs in this fashion with a remote MySQL interface.

      – Jacob

    • Hello Dushyant,

      It depends what you mean by being able to set up a job schedule. For instance you can use MySQL Workbench in order to schedule a backup of your database.

      Or are you instead referring to the MySQL Event Scheduler? If that is the case, then yes you can create your Events from within MySQL Workbench using the standard syntax for doing so:

      CREATE EVENT hit_update
      ON SCHEDULE EVERY 1 MINUTE
      DO 
      UPDATE user_hits SET hits = hits + 1;

      Although for this to work, your server’s /etc/my.cnf configuration file must contain the line turning on the Event Scheduler:

      event_scheduler=ON

      Please let us know if you had any further questions at all.

      – Jacob

Leave a Reply