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.

Conclusion

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: http://www.navicat.com/


MacTech Staff

 
AAPL
$101.70
Apple Inc.
+0.84
MSFT
$46.56
Microsoft Corpora
-0.20
GOOG
$586.04
Google Inc.
+6.09

MacTech Search:
Community Search:

Software Updates via MacUpdate

Apple Digital Camera RAW Compatibility 5...
Apple Digital Camera RAW Compatibility update adds RAW image compatibility to Aperture 3 and iPhoto '11. For more information on supported RAW formats, see here.Version 5.07: Adds RAW camera... Read more
Transmit 4.4.7 - Excellent FTP/SFTP clie...
Transmit is an excellent FTP (file transfer protocol), SFTP, S3 (Amazon.com file hosting) and iDisk/WebDAV client that allows you to upload, download, and delete files over the internet. With the... Read more
Macgo Blu-ray Player 2.10.8.1715 - Blu-r...
Macgo Mac Blu-ray Player can bring you the most unforgettable Blu-ray experience on your Mac. Overview Macgo Mac Blu-ray Player can satisfy just about every need you could possibly have in a Blu-ray... Read more
Capture One Pro 8.0.0.433 - RAW workflow...
Capture One Pro 8 is a professional RAW converter offering you ultimate image quality with accurate colors and incredible detail from more than 300 high-end cameras -- straight out of the box. It... Read more
Adobe Acrobat Pro 11.0.09 - Powerful PDF...
Adobe Acrobat allows users to communicate and collaborate more effectively and securely. Unify a wide range of content in a single organized PDF Portfolio. Collaborate through electronic document... Read more
Adobe Reader 11.0.09 - View PDF document...
Adobe Reader allows users to view PDF documents. You may not know what a PDF file is, but you've probably come across one at some point. PDF files are used by companies and even the IRS to... Read more
iFFmpeg 4.6.1 - Convert multimedia files...
iFFmpeg is a graphical front-end for FFmpeg, a command-line tool used to convert multimedia files between formats. The command line instructions can be very hard to master/understand, so iFFmpeg does... Read more
NTFS 11.3.62 - Provides full read and wr...
Paragon NTFS breaks down the barriers between Windows and OS X. Paragon NTFS effectively solves the communication problems between the Mac system and NTFS, providing full read and write access to... Read more
OS X Yosemite 10.10 DP8 - Developer Prev...
Note: This is a Developer Preview. You must be a registered Apple Mac Developer to download this update. You can also sign up for the free OS X Beta Program to download and preview public beta... Read more
FotoMagico 4.5 - Powerful slideshow crea...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more

Latest Forum Discussions

See All

Little World Escape Review
Little World Escape Review By Jordan Minor on September 17th, 2014 Our Rating: :: EARTHBOUNDUniversal App - Designed for iPhone and iPad Little World Escape draws players in with captivating concepts before pushing them away with... | Read more »
Light in the Dark Review
Light in the Dark Review By Nadia Oxford on September 17th, 2014 Our Rating: :: LIGHT 'EM UP UP UPUniversal App - Designed for iPhone and iPad Light in the Dark is an interesting and challenging puzzle game with some amusing bits... | Read more »
ShareFile for iPad is iOS 8-Ready with N...
ShareFile for iPad is iOS 8-Ready with New File-Editi​ng Feature Posted by Jessica Fisher on September 17th, 2014 [ permalink ] | Read more »
Pizo Animals
Pizo Animals By Amy Solomon on September 17th, 2014 Our Rating: :: DELIGHTFUL PUZZLESUniversal App - Designed for iPhone and iPad Pizo Animals is a puzzle app that includes many options adults can choose to customize the experience... | Read more »
ETA for iOS 8 Introduces Today View Exte...
ETA for iOS 8 Introduces Today View Extension and is Having a 50% Off Sale Posted by Jessica Fisher on September 17th, 2014 [ permalink ] | Read more »
Get Ready to Hit the Ice – NHL 2K is Com...
Get Ready to Hit the Ice – NHL 2K is Coming to the App Store Soon Posted by Jessica Fisher on September 17th, 2014 [ permalink ] 2K announced today that they are bringing NHL 2K< | Read more »
Readdle Adds Tons of iOS 8 Updates for A...
Readdle Adds Tons of iOS 8 Updates for All Readdle Apps Posted by Jessica Fisher on September 17th, 2014 [ permalink ] With the iOS 8 dropping today, Readdle has dramatically | Read more »
Let it Rainbow Review
Let it Rainbow Review By Jennifer Allen on September 17th, 2014 Our Rating: :: SIMPLE COLORSiPhone App - Designed for the iPhone, compatible with the iPad A little too simple for extended sessions, Let It Rainbow is still a cute... | Read more »
Goat Simulator (Games)
Goat Simulator 1.0.1 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0.1 (iTunes) Description: Goat Simulator is the latest in goat simulation technology, bringing next-gen goat simulation to YOU. You no longer have to... | Read more »
Almightree: The Last Dreamer Review
Almightree: The Last Dreamer Review By Blake Grundman on September 17th, 2014 Our Rating: :: CLIMBING HIGHERUniversal App - Designed for iPhone and iPad Can this Zelda-inspired platformer reach new heights of success?   | Read more »

Price Scanner via MacPrices.net

Logitech Bluetooth Multi-Device Cross-Platfor...
Logitech has an enviable track record of making some of the best computer keyboards and mice. At least in my estimation, the best freestanding keyboards I’ve ever used have been Logitech units,... Read more
Roundup of Apple refurbished iPad Airs and iP...
Apple is offering Certified Refurbished iPad Airs for up to $140 off MSRP. Apple’s one-year warranty is included with each model, and shipping is free. Stock tends to come and go with some of these... Read more
Sprint offers 16GB iPad mini for $199.99 with...
Sprint is offering 1st generation 16GB iPad minis for $199.99 with a 2-year service agreement. Standard MSRP for this iPad is $429. Their price is the lowest available for this model. Read more
2.5GHz Mac mini remains on sale for $549, sav...
B&H Photo has the 2.5GHz Mac mini on sale for $549.99 including free shipping. That’s $50 off MSRP, and B&H will also include a free copy of Parallels Desktop software. NY sales tax only. Read more
Apple refurbished iMacs available for up to $...
The Apple Store has Apple Certified Refurbished iMacs available for up to $300 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free. These are the best prices on... Read more
13″ 2.5GHz MacBook Pro offered for $100 off M...
B&H Photo has the 13″ 2.5GHz MacBook Pro on sale for $999.99 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
Free GIMP Professional Grade Graphics App Ver...
The latest 2.8.14 version of the oddly-named GIMP (acronym for: GNU Image Manipulation Program) open source, high-end image editing and creation alternative to Adobe’s Photoshop and refuge from... Read more
Apple Announces Record Pre-orders for iPhone...
Apple has released metrics showing a record number of first day pre-orders of iPhone 6 and iPhone 6 Plus, with over four million sold in the first 24 hours. Demand for the new iPhones exceeds the... Read more
10% off iPhone 6 and 6 Plus Otterbox cases
Get 10% off on popular Otterbox iPhone 6 and iPhone 6 Plus cases at MacMall through September 19th. Use code OTTERBOX10 to see the discount. Read more
15-inch MacBook Pros on sale for up to $125 o...
Amazon has the new 2014 15″ Retina MacBook Pros on sale for up to $125 off MSRP including free shipping: - 15″ 2.2GHz Retina MacBook Pro: $1899.99 save $100 - 15″ 2.5GHz Retina MacBook Pro: $2374... Read more

Jobs Board

*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.