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
$112.65
Apple Inc.
+3.24
MSFT
$47.52
Microsoft Corpora
+1.78
GOOG
$511.10
Google Inc.
+6.21

MacTech Search:
Community Search:

Software Updates via MacUpdate

BBEdit 11.0.2 - Powerful text and HTML e...
BBEdit is the leading professional HTML and text editor for the Mac. Specifically crafted in response to the needs of Web authors and software developers, this award-winning product provides a... Read more
ExpanDrive 4.2.1 - Access cloud storage...
ExpanDrive builds cloud storage in every application, acts just like a USB drive plugged into your Mac. With ExpanDrive, you can securely access any remote file server directly from the Finder or... Read more
Adobe After Effects CC 2014 13.2 - Creat...
After Effects CC 2014 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous After Effects customer). After Effects CS6 is still available... Read more
Command-C 1.1.7 - Clipboard sharing tool...
Command-C is a revolutionary app which makes easy to share your clipboard between iOS and OS X using your local WiFi network, even if the app is not currently opened. Copy anything (text, pictures,... Read more
Tidy Up 4.0.2 - Find duplicate files and...
Tidy Up is a complete duplicate finder and disk-tidiness utility. With Tidy Up you can search for duplicate files and packages by the owner application, content, type, creator, extension, time... Read more
Typinator 6.3 - Speedy and reliable text...
Typinator turbo-charges your typing productivity. Type a little. Typinator does the rest. We've all faced projects that require repetitive typing tasks. With Typinator, you can store commonly used... Read more
GraphicConverter 9.5 - Graphics editor w...
GraphicConverter is an all-purpose image-editing program that can import 200 different graphic-based formats, edit the image, and export it to any of 80 available file formats. The high-end editing... Read more
Toast Titanium 12.0.1 - The ultimate med...
Toast Titanium goes way beyond the very basic burning in the Mac OS and iLife software, and sets the standard for burning CDs, DVDs, and now Blu-ray discs on the Mac. Create superior sounding audio... Read more
QuickBooks 2015 16.0.2.1422 R3 - Financi...
Save 20% on QuickBooks Pro for Mac today through this special discount link QuickBooks Pro 2013 helps you manage your business easily and efficiently. Organize your finances all in one place, track... Read more
Remotix 3.0.6 - Access all your computer...
Remotix is a fast and powerful application to easily access multiple Macs (and PCs) from your own Mac. Features: Complete Apple Screen Sharing support - including Mac OS X login, clipboard... Read more

Latest Forum Discussions

See All

Mahjong Journey Review
Mahjong Journey Review By Jennifer Allen on December 18th, 2014 Our Rating: :: STEADY MATCHINGiPad Only App - Designed for the iPad Aimed at the more laid back gamer, Mahjong Journey isn’t for everyone, but those looking for some... | Read more »
Emoji Type - custom keyboard with predic...
Emoji Type - custom keyboard with predictive emojis 0.4.0 Device: iOS iPhone Category: Utilities Price: $.99, Version: 0.4.0 (iTunes) Description: Emoji Type is custom keyboard for iOS 8 that auto suggests emojis as you type. ABOUT... | Read more »
Game of the Year 2014 – 148Apps Staff Pi...
The end of 2014 is almost here, which can only mean one thing. Okay it can mean a lot of things, but in this specific context it means Game of the Year lists! Which is why the 148Apps staff have all picked their favorites from the past year. And why... | Read more »
UponPixels Review
UponPixels Review By Jennifer Allen on December 18th, 2014 Our Rating: :: CREATIVE TYPOGRAPHYUniversal App - Designed for iPhone and iPad Add cool typography and objects to your photos with the easy to use UponPixels.   | Read more »
The Vikings are Coming! CastleStorm’s Ne...
The Vikings are Coming! CastleStorm’s New Update Adds a Survival Mode Posted by Jessica Fisher on December 18th, 2014 [ permalink ] | Read more »
Duet Display (Productivity)
Duet Display 0.3.3 Device: iOS Universal Category: Productivity Price: $9.99, Version: 0.3.3 (iTunes) Description: Duet Display allows you to use your iPad or iPhone as an extra display. Developed by a team of ex-Apple engineers,... | Read more »
Dragon Quest III Review
Dragon Quest III Review By Jennifer Allen on December 18th, 2014 Our Rating: :: DATED BUT NOT WITHOUT MERITUniversal App - Designed for iPhone and iPad A walk down memory lane isn’t foolproof for Dragon Quest III, but it has its... | Read more »
8 KEMCO JRPGs Are Just $0.99 in Celebrat...
8 KEMCO JRPGs Are Just $0.99 in Celebration of the Holidays Posted by Jessica Fisher on December 18th, 2014 [ permalink ] KEMCO has announc | Read more »
Brothers in Arms 3: Sons of War Review
Brothers in Arms 3: Sons of War Review By Jennifer Allen on December 18th, 2014 Our Rating: :: FUN BUT PUSHYUniversal App - Designed for iPhone and iPad Brothers in Arms 3: Sons of War could be great fun, but its plethora of... | Read more »
The Snow is Falling on the French Countr...
The Snow is Falling on the French Countryside of Carcassonne’s Newest Update Posted by Jessica Fisher on December 18th, 2014 [ permalink ] < | Read more »

Price Scanner via MacPrices.net

Save up to $400 on MacBooks with Apple Certif...
The Apple Store has Apple Certified Refurbished 2014 MacBook Pros and MacBook Airs available for up to $400 off the cost of new models. An Apple one-year warranty is included with each model, and... Read more
Save up to $300 on Macs, $30 on iPads with Ap...
Purchase a new Mac or iPad at The Apple Store for Education and take up to $300 off MSRP. All teachers, students, and staff of any educational institution qualify for the discount. Shipping is free,... Read more
iOS and Android OS Targeted by Man-in-the-Mid...
Cloud services security provider Akamai Technologies, Inc. has released, through the company’s Prolexic Security Engineering & Research Team (PLXsert), a new cybersecurity threat advisory. The... Read more
KMI MIDI K-Board Great Gift for Amateur &...
The K-Board is a MIDI Nano keyboard for music creation for iPad, Android, And computers; the easiest way to make music with iPads & Android tablets, and Mac, Windows, or Linux computers. Ultra-... Read more
Amazon offers 15-inch 2.2GHz Retina MacBook P...
 Amazon.com has the 15″ 2.2GHz Retina MacBook Pro on sale for $1699 including free shipping. Their price is $300 off MSRP. Stock is limited, so act now if you’re interested. Read more
Holiday sales continue: MacBook Pros for up t...
 B&H Photo has new MacBook Pros on sale for up to $300 off MSRP as part of their Holiday pricing. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1699... Read more
Holiday sale: Mac minis available for up to $...
 B&H Photo has new 2014 Mac minis on sale for up to $80 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 1.4GHz Mac mini: $459 $40 off MSRP - 2.6GHz Mac mini: $629 $70 off... Read more
Google Search App For iOS Gets A Major Makeov...
Google has given iOS users an early Christmas present with a substantial update of it’s not-very-often-upgraded Google Search app. Google Search has been my go-to tool for Web searches since it was... Read more
ShopKeep Apple Pay And Chip Card Reader Avail...
ShopKeep, a cloud-based technology provider to more than 10,000 small business owners to manage retail shops and restaurants with iPads, has released its new Apple Pay and chip card reader. This... Read more
Holiday sale! 27-inch 5K iMac for $2299, save...
 B&H Photo has the 27″ 3.5GHz 5K iMac in stock today and on sale for $2299 including free shipping plus NY sales tax only. Their price is $200 off MSRP, and it’s the lowest price available for... 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...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, 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
Project Manager / Business Analyst, WW *Appl...
…a senior project manager / business analyst to work within our Worldwide Apple Fulfillment Operations and the Business Process Re-engineering team. This role will work Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.