Setting up a connection to the database
At this point, I assume you already have your MySQL server installed, set up, and running as well as Navicat, so that we can get our hands dirty with Navicat right away.
To launch Navicat in Windows 7 and earlier, go to Start menu | All Programs | PremiumSoft and click on the version of Navicat you have installed on your PC.
If you're using a Mac, Navicat should simply be in your Applications folder unless you dragged and dropped it elsewhere from the installer window.
In order to define a new connection, go to the File menu or the Connection button, which is the first icon in the Navicat's main toolbar (or ribbon, as we might call it) and select File | New Connection | MySQL to open up the connection profile window titled MySQL - New Connection, where we can specify the settings for the connection we want to establish.
You can refer to the following screenshot:
As you can see in the second part of the screenshot, the MySQL - New Connection window is where you can specify the settings to define a connection. It has five tabs; the first of which is where you set the basic connection properties and it is sufficient in most cases, which are as follows:
- Connection Name: It is totally up to you, so you can enter any name to describe your connection.
- Host Name/IP Address: It is exceedingly intuitive, where you can either enter the domain name of your database server or its IP address.
- Port: This field includes the TCP/IP port number of the MySQL server which in most cases is
3306. - User Name: This field includes the database username (I'm going with
roothere which is the default admin user for my newly installed local server). - Password: This field includes password for the above entered username. In fresh MySQL installations,
rootcomes with a blank password, so if this is your first time connecting to the database server you just installed, you might want to leave this blank at this time as I will guide you how to modify all these settings at the end of the chapter.
If you are connecting to a remote MySQL server, you must make sure that remote access privileges are granted for the username you will be using. In some cases where the MySQL service provider does not provide direct access to the server remotely, connecting via Secure Shell (SSH) or an HTTP tunnel might be an alternative solution. We'll see how to set up these kinds of connections respectively in the following sections.
Connecting via Secure Shell (SSH)
SSH is a command line tool to log into a server or another computer over a network in a secure manner to run commands on the remote machine or to transfer data. For increased security, SSH provides a strong authentication mechanism either by using a password or a public/private key pair also known simply as a public key.
In order to set up your connection to the MySQL server via SSH, first enter the basic connection settings as described in the previous section, then go to the SSH tab in the connection settings window, click on the checkbox labeled Use SSH Tunnel,and then enter the following information:
- Host Name/IP Address: This field includes the address or the IP of the SSH server.
- Port: This field includes the port number of the SSH server (the default is
22). - User Name: This field includes the user of the SSH server, which is usually a UNIX machine and not a username of the database.
- Authentication method: This field allows you to choose between Password authentication and Public Key authentication, whichever's applicable.
- Password (if applicable): This field includes the password of the SSH user (not the database).
- Private Key (if applicable): This field appears if you choose the Public Key authentication, in which you need to specify the path to your private key file by clicking on the small rectangular button proceeding it.
- Passphrase (if applicable): This field also shows up in the case of Public key authentication and is used in conjunction with the Private Key. It's basically like a password, but it applies to your key and not an account.
Connecting via an HTTP tunnel
In some cases, it is not possible to connect to a server through any protocol but HTTP, especially when one party is behind a firewall. Some companies, for example, want to limit the Internet access of its users so that they are only able to browse the web, and do nothing else; no FTP, no instant messaging, and so on. This is where the HTTP tunneling comes in handy. It allows you to connect to a server (in this case MySQL) through the port 80 (the HTTP default) instead of 3306 or any other port.
To set up an HTTP connection, go through the following steps:
- Upload the HTTP tunneling script which came with the Navicat installer to the web server where the MySQL server is located. It's a file called
ntunnel_mysql.php. - Go to the tab named HTTP in the MySQL - New Connection window of Navicat.
- Enable the checkbox Use HTTP Tunnel.
- Enter the URL of the tunneling script where you uploaded it (for example, http://www.ozar.net/mysql/ntunnel_mysql.php).
- You can check the Encode outgoing query with base64 option if you know that the web server you uploaded the script has ModSecurity installed.
- If the tunneling script is on a password protected server or your internet connection is over a proxy, you can provide the required authentication details under the Authentication or Proxy tab.