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
$97.67
Apple Inc.
+2.95
MSFT
$45.38
Microsoft Corpora
+0.55
GOOG
$597.41
Google Inc.
+2.67

MacTech Search:
Community Search:

Software Updates via MacUpdate

Firefox 31.0 - Fast, safe Web browser. (...
Firefox for Mac offers a fast, safe Web browsing experience. Browse quickly, securely, and effortlessly. With its industry-leading features, Firefox is the choice of Web development professionals... Read more
Little Snitch 3.3.3 - Alerts you to outg...
Little Snitch gives you control over your private outgoing data. Track background activityAs soon as your computer connects to the Internet, applications often have permission to send any... Read more
Thunderbird 31.0 - Email client from Moz...
As of July 2012, Thunderbird has transitioned to a new governance model, with new features being developed by the broader free software and open source community, and security fixes and improvements... Read more
Together 3.2 - Store and organize all of...
Together helps you organize your Mac, giving you the ability to store, edit and preview your files in a single clean, uncluttered interface. Smart storage. With simple drag-and-drop functionality,... Read more
Cyberduck 4.5 - FTP and SFTP browser. (F...
Cyberduck is a robust FTP/FTP-TLS/SFTP browser for the Mac whose lack of visual clutter and cleverly intuitive features make it easy to use. Support for external editors and system technologies such... Read more
Airmail 1.4 - Powerful, minimal email cl...
Airmail is a powerful, minimal mail client.It was designed to retain the same experience with a single or multiple accounts and provide a quick, modern and easy-to-use user experience. Airmail... Read more
Macs Fan Control 1.1.12 - Monitor and co...
Macs Fan Control allows you to monitor and control almost any aspect of your computer's fans, with support for controlling fan speed, temperature sensors pane, menu-bar icon, and autostart with... Read more
A Better Finder Rename 9.37 - File, phot...
A Better Finder Rename is the most complete renaming solution available on the market today. That's why, since 1996, tens of thousands of hobbyists, professionals and businesses depend on A Better... Read more
MacBook Air EFI Firmware Update 2.9 - Fo...
MacBook Air EFI Firmware Update is recommended for MacBook Air (Mid 2011) models. This update addresses an issue where systems may take longer to wake from sleep than expected and fixes a rare issue... Read more
FileZilla 3.9.0.1 - Fast and reliable FT...
FileZilla (ported from Windows) is a fast and reliable FTP client and server with lots of useful features and an intuitive interface.Version 3.9.0.1: MSW: Fix installation issue with locked DLLs... Read more

Latest Forum Discussions

See All

Stronghold 3: The Campaigns Review
Stronghold 3: The Campaigns Review By Jennifer Allen on July 23rd, 2014 Our Rating: :: DULL STRATEGIZINGiPad Only App - Designed for the iPad A cumbersome strategy game, Stronghold 3: The Campaigns has a few too many issues to... | Read more »
Table Tennis Touch on Sale for a Limited...
Table Tennis Touch on Sale for a Limited Time Posted by Jessica Fisher on July 23rd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Secret Files Tunguska Review
Secret Files Tunguska Review By Jennifer Allen on July 23rd, 2014 Our Rating: :: CONSPIRACY-LITTERED ADVENTURINGUniversal App - Designed for iPhone and iPad Offering traditional adventuring with no fear of in-app purchases, Secret... | Read more »
Celebrate Summer With a Cat in the Hat L...
Celebrate Summer With a Cat in the Hat Learning Library Sale Posted by Ellis Spice on July 22nd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Dragon Raiders Review
Dragon Raiders Review By Nadia Oxford on July 22nd, 2014 Our Rating: :: RUN, DRAGON, RUNUniversal App - Designed for iPhone and iPad Dragon Raiders is rough and scaly in some parts, but overall it’s an enjoyable level-based running... | Read more »
MyTaskList Review
MyTaskList Review By Jennifer Allen on July 22nd, 2014 Our Rating: :: EFFECTIVE IF PLAINUniversal App - Designed for iPhone and iPad It’s not the most stylish of task management apps, but MyTaskList has all the features you could... | Read more »
FlyCraft Herbie: Crazy Machines Review
FlyCraft Herbie: Crazy Machines Review By Jennifer Allen on July 22nd, 2014 Our Rating: :: TRICKY FLYINGUniversal App - Designed for iPhone and iPad A tough game of careful thrusting and navigation, FlyCraft Herbie: Crazy Machines... | Read more »
MTN Review
MTN Review By Jessica Fisher on July 22nd, 2014 Our Rating: :: ADORABLE, SERENE, AND AMUSINGUniversal App - Designed for iPhone and iPad MTN is an adorable, talking pet mountain that is less game and more zen garden.   | Read more »
Fly High with Ninja UP! Now Available o...
Fly High with Ninja UP! Now Available on the App Store Posted by Jessica Fisher on July 22nd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Bio Inc. Review
Bio Inc. Review By Nadia Oxford on July 22nd, 2014 Our Rating: :: SICKENING - IN A COMPELLING WAYUniversal App - Designed for iPhone and iPad Bio Inc is about orchestrating the medical destruction of a single person. If that doesn’... | Read more »

Price Scanner via MacPrices.net

MacBook Airs on sale starting at $799, free s...
B&H Photo has the new 2014 MacBook Airs on sale for up to $100 off MSRP for a limited time. Shipping is free, and B&H charges NY sales tax only. They also include free copies of Parallels... Read more
Apple 27″ Thunderbolt Display (refurbished) a...
The Apple Store has Apple Certified Refurbished 27″ Thunderbolt Displays available for $799 including free shipping. That’s $200 off the cost of new models. Read more
WaterField Designs Unveils Cycling Ride Pouch...
High end computer case and bag maker WaterField Designs of San Francisco now enters the cycling market with the introduction of the Cycling Ride Pouch – an upscale toolkit with a scratch-free iPhone... Read more
Kingston Digital Ships Large Capacity Near 1T...
Kingston Digital, Inc., the Flash memory affiliate of Kingston Technology Company, Inc.,has announced its latest addition to the SSDNow V300 series, the V310. The Kingston SSDNow V310 solid-state... Read more
Apple’s Fiscal Third Quarter Results; Record...
Apple has announced financial results for its fiscal 2014 third quarter ended June 28, 2014, racking up quarterly revenue of $37.4 billion and quarterly net profit of $7.7 billion, or $1.28 per... Read more
15-inch 2.0GHz MacBook Pro Retina on sale for...
B&H Photo has the 15″ 2.0GHz Retina MacBook Pro on sale for $1829 including free shipping plus NY sales tax only. Their price is $170 off MSRP. B&H will also include free copies of Parallels... Read more
Apple restocks refurbished Mac minis for up t...
The Apple Store has restocked Apple Certified Refurbished Mac minis for up to $150 off the cost of new models. Apple’s one-year warranty is included with each mini, and shipping is free: - 2.5GHz Mac... Read more
Twelve South HiRise For MacBook – Height-Adju...
If you use your MacBook as a workhorse desktop substitute, as many of us do, a laptop stand combined with an external keyboard and pointing device are pretty much obligatory if you want to avoid... Read more
Why The Mac Was Not Included In The Apple/IBM...
TUAW’s Yoni Heisler cites Fredrick Paul of Network World whoi blogged last week that the Mac’s conspicuous absence from Apple and IBM’s landmark partnership agreement represents a huge squandered... Read more
Save $100 on 13-inch Retina MacBook Pros, plu...
Adorama has 13″ Retina MacBook Pros on sale for $100 off MSRP. Shipping is free, and Adorama charges sales tax in NY & NJ only: - 13″ 2.4GHz/128GB MacBook Pro with Retina Display: $1199 - 13″ 2.... Read more

Jobs Board

Sr Software Lead Engineer, *Apple* Online S...
Sr Software Lead Engineer, Apple Online Store Publishing Systems Keywords: Company: Apple Job Code: E3PCAK8MgYYkw Location (City or ZIP): Santa Clara Status: Full 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
Sr *Apple* Engineer - IT - Requisition #: -...
For more information about TIAA-CREF, visit our website . The Apple Engineer will provide engineering and third-level incident support for 300- 500 MacOS desktop/laptop Read more
*Apple* Systems Administrator - DISH (United...
…satellite service provider, and Dish is currently looking for an experienced Apple /Mac Systems Administrator. Apple systems administrator will be responsible for Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.