Manage your MySQL Database with Navicat

Navicat is a robust desktop application produced by Premium Soft. It provides an excellent GUI for managing several type of databases.  This article will illustrate how to use Navicat with your MySQL Server hosted on any of Hosting.com's Platform Services. We will discuss how to add your MySQL database servers, create tables, perform regular maintenance, work with importing and exporting data.

Download and install the latest version of Navicat for Windows or Mac.  The free non-commercial version, Navicat Lite comes with more than enough features to get started.  Upgrading to the full version will give you access to several advanced features. Some of the more notable items that come with Navicat Premium are GUI Query Builder, Backup / Restore, Batch Job Schedules and Reports.

Adding Connections

Once installed to add your database as a new connection mouse over the Connection button and choose MySQL.

Give your connection a name that makes sense to you. Unlike the other items the Connection Name value does not correspond directly to MySQL permissions.  

Enter your MySQL server's IP address or Host Name.

Keep the port set to 3306 unless you have changed what port your MySQL server is listening on. 

Enter an existing MySQL user and password. MySQL users should be created by your database administrator to prevent unauthorized access. Options in Navicat are directly related to the permission of the user provided.  If you are the database administrator you may wish to create a more privileged user that has access to server administrative tasks like user management.  If you enter a user that has access to multiple databases this connection will list them all.

Click the Test Connection button to verify Navicat can access your MySQL server. You should see Connection Successful if the information entered works properly.

 

Creating Tables

Once you have created a connection you're ready to get started designing your database. First you'll need to open your connection and an associated database. Simply double click on the connection name and again on the database name.

 

From here you can start creating tables by clicking on the New Table button.  

Here is an example of adding a "comments" table related to books and users.

 

Click the Save button, give the table a name and click OK.

Regular Maintenance

To perform regular maintenance on tables right click on the table and move your mouse over "Maintain Tables". From here  you can easily analyze, check, optimize and repair tables. 

 

Importing, Exporting and Dump Files

Navicat comes with some very nice features to get data in and out of your database. The Import and Export Wizards allow you to insert data or get information out using a variety of formats. Using the Lite version you may utilize .txt, .html, .xml and MS Windows Clipboard. 

Dump files are handled apart from the Import and Export wizard.  To create a dump file  for all tables right click on the database name an choose Dump SQL File...

 

To make a dump file of only specific tables hold down the Ctrl button on your keyboard and click on each table. Then right click on any highlighted table and choose Dump SQL File...

To execute (or import from) a dump file or any SQL file right click on the database name and choose Execute SQL File...

 

Browse to the dump file, check off any of the optional items you may want and click start.