Whether you develop and maintain a large number of custom built web sites, or if you just need to perform some specific queries on the database for your lone site, there is a good chance you need to access, manage, and/or query a MySQL database. And if so, you want a tool that can give you these abilities easily and powerfully. At Web Site Optimizers, our development team must maintain a large number of MySQL databases. To do that, we turn to Navicat by PremiumSoft.
To be clear, this is not a paid endorsement or advertorial. We did not consult with Navicat in any way in writing this. It is simply the first of a series of reviews of products and services we use or have tried at Web Site Optimizers.
We’ve been using Navicat almost exclusively since 2004. Here are the reasons why.
Simple, fast, and intuitive access to all of your databases from the desktop – Connections to all of the databases we manage are listed right there on the left hand side. To open one up, we just click it to see a list of all the tables. The login information is set up in the connection, so getting to the data is fast—no time taken up with tedious logins.
Review and modify data quickly and easily – Just double-click on a table to open it up in Browse mode. If the table is small and you’re looking for a specific record, sort on any one or more fields by selecting it, and clicking one button. Scroll through or page through the records to get to the one you’re looking for. If you need to change some field values, just make the change right there and move to a different record to commit the change. This is so much easier and faster than having to click an Edit icon by a record to then pull up a new form.
Similarly, if you need to change the structure of a table, select the table and click on “Design Table” to bring the table up in Design mode. Make your changes right there, again without having to pull up additional forms, and save your changes.
One feature we find ourselves using is the Duplicate Table function. Just right-click on any table and choose “Duplicate Table” and you instantly have a copy of it right there in the database. Excellent for making a backup before testing some new code. Similarly, you can rename a table as easily as renaming a file. Just click it and change the name.
SQL tool to write and save MySQL scripts – If your table is not so small, Navicat has an excellent SQL tool in which you can write a simple or complicated query to pull up your data. The structure of the query is color coded too, with MySQL keywords in blue and literal strings in red, so that you can more easily recognize syntax errors in complicated queries.
The SQL tool is great for debugging your PHP code. We often echo an SQL query to the screen so we can copy and paste it into Navicat to make sure it is returning the record set we are expecting. You can also save queries that you run frequently. We have some basic two-line SQL queries saved that archive old records out of large databases. This saves us time and errors each month for several customers.
Differentiators
Okay, you might be reading this post, comparing what we’re saying to another SQL tool and thinking that there are a lot that can boast of the same thing. But those benefits listed above are critical—they’re needed every day and the fact is that we’ve been forced to use some popular tools that cannot deliver on those points. But there are also more advanced features we’ve been thankful Navicat offers on many occasions.
Export to Excel – We often get ad hoc requests from customers to either dump the contents of a table (for example, an opt-in list from customers who have placed an order), or possibly a simple subset of a table that can be collected quickly with a simple SELECT statement. With Navicat, whether you are in Browse mode for a specific table, or you run a specific query with the SQL tool, there is a simple “Export Wizard” that allows you to export the contents of a record set to Excel with ease, and save it right to your local disk. There is also a similar Import Wizard to allow you to take a local Excel or dBase or Access file (or other formats) and upload it into a table on your MySQL server.
Data Transfer – This is probably the most critical feature for us at Web Site Optimizers. It allows you to transfer one or more tables from one database to another, whether they are on the same server or different servers. With many tools, your only means of doing this is to download a set of SQL code to your local computer that represents a picture of your data. You then must either upload or paste that code into the SQL tool on your destination server and run it – often with errors. With Navicat’s Data Transfer tool, you transmit the data directly to the destination server.
As I say, we use this feature all the time. If we are building a site on a development server and then need to move the code and data to a production server, we do the entire database design and population on the beta server and then just use Data Transfer to move it to the production server. If we have a customer in a shared hosting environment and need to change hosts, the Data Transfer tool makes that so much easier. Most importantly, we can create true mirror images of all our customers’ databases, regardless of where they are hosted. We have a server that we only use for backups and every night do a Data Transfer of all our active databases to it to create fully accessible offsite backups. This is an automated process (see below), making it all the more reliable.
Stored Procedures & Scheduling – Two other advanced features of Navicat are Stored Procedures and Scheduled Tasks. A Stored Procedure is any combination of procedures you can do in Navicat, such as saved SQL Queries, Backups, Data Transfers, etc. These can be stored to streamline any regular or semi-regular maintenance tasks you must perform such as table optimizations, archiving, backups, etc. These can be further streamlined by using the Scheduled Tasks feature, which works with the Scheduled Tasks function of your Control Panel to set up automatic running of your Stored Procedures at any schedule you choose. As I noted above, we use these to automate the backups of our databases each night. Logs are created for each task so that we can review each morning to ensure that the tasks were completed successfully.
Summary
I’m sure that there are a lot of excellent MySQL database tools out there at all price ranges. But we’ve been extremely pleased with what we have gotten with Navicat for the past eight years now. If you’re looking for something that is reliable and full-featured, and if your current tools are requiring too much time to use, I’d suggest you take a look at Navicat.
