TweetFollow Us on Twitter

Navicat: More Than Just Backing up MySQL

Volume Number: 22 (2006)
Issue Number: 4
Column Tag: Review

Navicat: More Than Just Backing up MySQL

by MacTech Staff

MySQL Management Tools

There are a number of ways to manage and even backup your MySQL databases. Some people prefer the command line. Others decide that they like a web interface like phpMyAdmin. Finally, you may want to use a standalone application that's a GUI like CocoaMySQL, MySQL Administrator or Navicat.

Because of the issues of backing up a live MySQL database, and even more the case with Retrospect with MySQL databases (see the article on Retrospect in this issue for more information), we needed to find another way to backup our MySQL databases.

If you decide to do this at the command line, you can use 'mysqldump' in a cron job:

mysqldump -A -a --add-drop-table -u username -password "thepassword" > backup.sql

You can restore it with:

mysql -u username -p < backup.sql

The benefit, of course, is that this doesn't require any additional product, it's reliable and it's secure. You can drop it into a script, if you'd like, but if you do, make sure it's owned by root and can only be accessed by root (0700). And, if you do this, you can add in a "logger" statement so that you can monitor things, or ask cron to mail you the output by default. Then again, you may want a GUI, and want to be able to touch the results a bit more easily than what cron does for you.

Why Navicat?

We chose Navicat specifically because of its easy to use scheduling features to backup SQL databases, restore them easily, and even replicate data between databases for nightly updates.

While we started to use Navicat specifically for backups of the MySQL databases, we found a variety of other uses. Specifically, if you aren't fluent with MySQL queries, Navicat helps by allowing you to define a query through the GUI and then shows you what the resulting query would look like.

About Navicat

Navicat is a MySQL database administration and development tool that allows professional developers as well as new users to create, organize, access, and share information in a secure and convenient way. Navicat supports multiple MySQL server connections which can be connected locally or remotely (remote MySQL server can be running on Mac OS X, Linux, UNIX, or Windows platform). Navicat requires Mac OS X 10.1 or later.

Some of the important features of Navicat for Mac OS X include SSH Tunnel, data and structure synchronization, SQL console, views, stored procedure, trigger creation, support for all MySQL server versions above 3.21, creation and deletion of databases, tables, indexes, and users, stored procedures, foreign keys, Unicode and character set support, import / export data in up to 4 most popular formats (CSV, TXT, DBF, and XML), creation / execution of SQL queries, printing table structure, creation of schedule for backup, backup / restore of database, saved queries, support for transferring databases from one MySQL server to another MySQL server, and managing user privileges.

Navicat: Main Interface

Navicat's main interface comprises Server Navigator and Database Window. Server Navigator displays a list of server connections assigned in Navicat. These connections can be set by Navicat to open automatically on start up. Navicat provides 10 views: Connection, Table, View, Stored Proc., Query, Backup, Schedule, Data Transfer, Data Sync, and Structure Sync. Database Window shows the content of the chosen view.

Navicat's SSH tunneling feature can be used to encrypt communications between Navicat and a remote MySQL server. Many MySQL hosting providers block access to the MySQL server from outside hosting company's network, and only grant access to users connecting locally. SSH tunneling allows connecting to a MySQL server from behind a firewall when the MySQL server port is blocked. Navicat's port forwarding function can be used for connecting provided SSH access is available from the hosting company. Navicat communicates with the remote MySQL server in plain text (with the exception of the user password). Navicat's port forwarding function provides security of communications with the MySQL server and it protects MySQL sessions from various attacks.

Typical operations that can be carried out on databases by using Navicat are creating database, opening database, renaming database, deleting database, copying database, moving database, and maintaining a database. Maintenance options for a database include optimize, analyze, check, and repair.

Navicat allows the following operations to be performed with tables: creating a table (from scratch/from an existing one in the database), open a table, design a table, empty a table, duplicate a table, and change table type. In addition to these functions, Navicat also offers functions to maintain a table. These functions include optimize table, analyze table, check table, and repair table. Navicat also features a Filter Wizard that can be utilized to filter records in Navicat's Table Window. Filters can be saved for fast retrieval and they can also be saved to a profile for retrieval when table is opened again.

The options available with Navicat to work with queries are creation of query, opening or running a query, deleting a query, saving a query, and loading SQL file. All the query actions can be stored in a log file for tracking. Navicat also features its "Visual Query Builder" that can be used to create join queries with options to specify selection, grouping, sorting, and projection criterion. SQL Console can be used to run SQL queries. It simulates the command line environment of MySQL.

Stored procedures and functions were introduced in MySQL in Version 5.0. Navicat supports following operations related to stored procedures and functions: create stored procedure/function, design stored procedure/function, and delete stored procedure/function. The feature of views was also added to MySQL Version 5.0. Navicat allows creation, design, and deletion of views.

Navicat's Table Window enables editing, adding, deleting, and sorting data in a table. It also features a blob edit control that facilitates editing of binary data. Navicat can be used to manage user and host privileges. Privilege management operations provided by Navicat include user privilege operations such as creating new user, deleting existing user, and setting privileges of a user, and host privilege operations such as creating new host, removing existing host, and setting privileges of a host.

Navicat's Backup Tools

Navicat's backup feature allows backup of all tables and records of a MySQL database. Backup can be restored by using Restore Backup function. Backup operations allowed by Navicat are creation of new backup, restoration of a backup, deletion of a backup, and extraction of SQL from a database. Navicat's data transfer feature allows transfer of tables/records from one MySQL database to another MySQL database or a plain text file. The target database can be on the same server as the source database or on another MySQL server. Navicat allows saving of a profile for easy retrieval and running of data transfer between databases. Data transfer operations offered by Navicat are creating a new data transfer profile, deleting a data transfer profile, and setting up general and advanced settings.

Navicat can be used to create batch jobs for automating functions such as backup, restore backup, data transfer, import and export. Batch job can be used to execute a series of actions one by one by just executing the batch job. Batch job can also be scheduled to execute at a specified time.

What is MySQL Doing?

One of the cool features of Navicat is that you can see what your MySQL database is doing. There's obviously a variety of other ways you can do that. In the case of Navicat, the GUI has a server monitor that can show MySQL Server Process List, MySQL Server Variables, and MySQL Server Status properties from the selection of MySQL servers. The data synchronization functionality of Navicat transfers data from one database to another with detailed analytical process. The user is authorized to rollback the transferring process, in addition to inserting, deleting and updating records to the destination. The synchronization profile can also be saved for setting a schedule. Navicat also provides structure synchronization to compare and modify the structure of tables with a detailed analytical process.


One of the open source alternatives or the command line may work great for you. In the end, it all depends on what your needs or desires are. If you are looking for a commercial application that can help you reliably with backups and data transfers, we found Navicat did the job well.

CD Rom Version: $109

Download Version $99

Available from:

MacTech Staff


Community Search:
MacTech Search:

Software Updates via MacUpdate

Warhammer 40,000: Freeblade guide - How...
Warhammer 40,000: Freebladejust launched in the App Store and it lets you live your childhood dream of blowing up and slashing a bunch of enemies as a massive, hulking Space Marine. It's not easy being a Space Marine though - and particularly if... | Read more »
Gopogo guide - How to bounce like the be...
Nitrome just launched a new game and, as to be expected, it's a lot of addictive fun. It's called Gopogo, and it challenges you to hoparound a bunch of platforms, avoiding enemies and picking up shiny stuff. It's not easy though - just like the... | Read more »
Sago Mini Superhero (Education)
Sago Mini Superhero 1.0 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0 (iTunes) Description: KAPOW! Jack the rabbit bursts into the sky as the Sago Mini Superhero! Fly with Jack as he lifts impossible weights,... | Read more »
Star Wars: Galaxy of Heroes guide - How...
Star Wars: Galaxy of Heroes is all about collecting heroes, powering them up, and using them together to defeat your foes. It's pretty straightforward stuff for the most part, but increasing your characters' stats can be a bit confusing because it... | Read more »
The best cooking apps (just in time for...
It’s that time of year again, where you’ll be gathering around the dinner table with your family and a huge feast in front of you. [Read more] | Read more »
Square Rave guide - How to grab those te...
Square Rave is an awesome little music-oriented puzzle game that smacks of games like Lumines, but with its own unique sense of gameplay. To help wrap your head around the game, keep the following tips and tricks in mind. [Read more] | Read more »
Snowboard Party 2 (Games)
Snowboard Party 2 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Crowned the best snowboarding game available on the market, Snowboard Party is back to fulfill all your adrenaline needs in... | Read more »
One Button Travel (Games)
One Button Travel 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: “To cut a long story short, If you like interactive fiction, just go buy this one.” - “Oozes the polish that... | Read more »
Light Apprentice Volume 1 (Games)
Light Apprentice Volume 1 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Light Apprentice Volume 1 includes Chapters 1 to 4, all gathered in a new exclusive game. When life in the world of... | Read more »
The best games like Animal Crossing on m...
Animal Crossing amiibo Festival is out right now for the Wii U, reminding us of just how much fun that world can be. Or at least to go back and check in on our villages once in a while. [Read more] | Read more »

Price Scanner via

iMobie Releases its Ace iOS Cleaner PhoneClea...
iMobie Inc. has announced the new update of PhoneClean 4, its iOS cleaner designed to reclaim wasted space on iPhone/iPad for use and keep the device fast. Alongside, iMobie hosts a 3-day giveaway of... Read more
U.S. Cellular Offering iPad Pro
U.S. Cellular today announced that it is offering the new iPad Pro with Wi-Fi + Cellular, featuring a 12.9-inch Retina display with 5.6 million pixels — the most ever in an iOS device. U.S. Cellular... Read more
Newegg Canada Unveils Black Friday Deals for...
Newegg Canada is offering more than 1,000 deep discounts to Canadian customers this Black Friday, available now through Cyber Monday, with new deals posted throughout the week. “Black Friday is... Read more
Black Friday: Macs on sale for up to $500 off...
BLACK FRIDAY B&H Photo has all new Macs on sale for up to $500 off MSRP as part of their early Black Friday sale including free shipping plus NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $... Read more
Black Friday: Up to $125 off iPad Air 2s at B...
BLACK FRIDAY Walmart has the 16GB iPad Air 2 WiFi on sale for $100 off MSRP on their online store. Choose free shipping or free local store pickup (if available): - 16GB iPad Air 2 WiFi: $399, save $... Read more
Black Friday: iPad mini 4s on sale for $100 o...
BLACK FRIDAY Best Buy has iPad mini 4s on sale for $100 off MSRP on their online store for Black Friday. Choose free shipping or free local store pickup (if available): - 16GB iPad mini 4 WiFi: $299.... Read more
Black Friday: Apple Watch for up to $100 off...
BLACK FRIDAY Apple resellers are offering discounts and bundles with the purchase of an Apple Watch this Black Friday. Below is a roundup of the deals being offered by authorized Watch resellers:... Read more
Black Friday: Target offers 6th Generation iP...
BLACK FRIDAY Save $40 to $60 on a 6th generation iPod touch at Target with free shipping or free local store pickup (if available). Sale prices for online orders only, in-store prices may vary: -... Read more
Black Friday: Walmart and Target offer iPod n...
BLACK FRIDAY Walmart has the 16GB iPod nano (various colors) on sale for $119.20 on their online store for a limited time. That’s $30 off MSRP. Choose free shipping or free local store pickup (if... Read more
Black Friday deals on the Apple Watch and App...
Apple resellers are offering discounts and bundles with the purchase of an Apple Watch this Black Friday weekend. Below is a roundup of the deals being offered by authorized Watch resellers: Apple... Read more

Jobs Board

Storefront Operations Coordinator, *Apple* -...
# Storefront Operations Coordinator, Apple -Latin America Job Number: 43587750 Miami, Florida, United States Posted: Oct. 16, 2015 Weekly Hours: 40.00 **Job Summary** The Read more
*Apple* Enterprise / Government Professional...
# Apple Enterprise / Gove ment Professional Services Engineer Job Number: 42292976 Reston, Virginia, United States Posted: Aug. 18, 2015 Weekly Hours: 40.00 **Job Read more
iOS Wallet & *Apple* Pay Engineer - App...
# iOS Wallet & Apple Pay Engineer Job Number: 40586801 Santa Clara Valley, Califo ia, United States Posted: Nov. 16, 2015 Weekly Hours: 40.00 **Job Summary** The iOS Read more
Software Engineer, *Apple* Watch - Clock Fa...
# Software Engineer, Apple Watch - Clock Face Team Job Number: 44368761 Santa Clara Valley, Califo ia, United States Posted: Nov. 14, 2015 Weekly Hours: 40.00 **Job Read more
Administrative Assistant, *Apple* Online St...
# Administrative Assistant, Apple Online Store Job Number: 43992352 Santa Clara Valley, Califo ia, United States Posted: Nov. 9, 2015 Weekly Hours: 40.00 **Job Summary** Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.