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

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Boom 2 1.0.1 - System-wide pro audio app...
Boom 2 is a system-wide volume booster and equalizer app that is designed especially for OS X 10.10 Yosemite. It comes with a smart interface, self-calibrates itself according to your Mac, offers... Read more
Apple Security Update 2015-001 - For OS...
Apple Security Update 2015-001 is recommended for all users and improves the security of OS X. For detailed information about the security content of this update, please visit: http://support.apple.... Read more
Drive Genius 4.0.1 - Powerful system uti...
Drive Genius 4 gives you faster performance from your Mac while also protecting it. The award-winning and improved DrivePulse feature alerts you to hard drive issues before they become major problems... Read more
Yosemite Cache Cleaner 9.0.3 - Clear cac...
Yosemite Cache Cleaner is an award-winning general purpose tool for OS X. YCC makes system maintenance simple with an easy point-and-click interface to many OS X functions. Novice and expert users... Read more
xScope 4.1.1 - Onscreen graphic measurem...
xScope is powerful set of tools that are ideal for measuring, inspecting, and testing on-screen graphics and layouts. Its tools float above your desktop windows and can be accessed via a toolbar,... Read more
OS X Yosemite 10.10.2 - Apple's lat...
OS X Yosemite is Apple's newest operating system for Mac. An elegant design that feels entirely fresh, yet inherently familiar. The apps you use every day, enhanced with new features. And a... Read more
Apple iOS 8.1.3 - The latest version of...
The latest version of iOS can be downloaded through iTunes. Apple iOS 8 comes with big updates to apps you use every day, like Messages and Photos. A whole new way to share content with your family.... Read more
SpamSieve 2.9.19 - Robust spam filter fo...
SpamSieve is a robust spam filter for major email clients that uses powerful Bayesian spam filtering. SpamSieve understands what your spam looks like in order to block it all, but also learns what... Read more
RapidWeaver 6.0.5 - Create template-base...
RapidWeaver is a next-generation Web design application to help you easily create professional-looking Web sites in minutes. No knowledge of complex code is required, RapidWeaver will take care of... Read more
Duet 1.2.2 - Use your iPad as an externa...
Duet is the first app that allows you to use your iDevice as an extra display for your Mac using the Lightning or 30-pin cable. Note: This app requires a $14.99 iOS companion app. Version 1.2.2:... Read more

Storm & Skye – Magical Adventure Sto...
Storm & Skye – Magical Adventure Story For Kids Review By Amy Solomon on January 28th, 2015 Our Rating: iPhone App - Designed for the iPhone, compatible with the iPad The first book in the Storm & Skye series includes... | Read more »
New WOTA: U-Boat Compass ‘Wets’ Your App...
New WOTA: U-Boat Compass ‘Wets’ Your Appetite for WOTA: Wolves of the Atlantic. Posted by Jessica Fisher on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Warship Solitaire Review
Warship Solitaire Review By Nadia Oxford on January 27th, 2015 Our Rating: :: A WAR OF NUMBERSUniversal App - Designed for iPhone and iPad Warship Solitaire might lack frills and looks, but it offers some thought provoking... | Read more »
Keep Track of Your Hectic Work Schedule...
Keep Track of Your Hectic Work Schedule With Shifts Posted by Jessica Fisher on January 27th, 2015 [ permalink ] iPhone App - Designed for the iPhone, compatible with the iPad | Read more »
All Star Quarterback Review
All Star Quarterback Review By Campbell Bird on January 27th, 2015 Our Rating: :: PLAY YOUR PARTUniversal App - Designed for iPhone and iPad Train your way to the Super Bowl in this free-to-play sports management game.   | Read more »
Sentinel 4: Dark Star Adds New Campaign...
Sentinel 4: Dark Star Adds New Campaign and More Posted by Ellis Spice on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Wheel & Deal Review
Wheel & Deal Review By Tre Lawrence on January 27th, 2015 Our Rating: :: SIMPLE FUNUniversal App - Designed for iPhone and iPad A simple arcade shooter that has a retro feel.   | Read more »
Join the NAVY in a Big New Update for WW...
Join the NAVY in a Big New Update for WW2: Sandbox. Strategy & Tactics Posted by Jessica Fisher on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Earthworm Alchemy Review
Earthworm Alchemy Review By Campbell Bird on January 27th, 2015 Our Rating: :: FEED THE WORMUniversal App - Designed for iPhone and iPad Feed this funny looking worm and dodge bombs in this charming, free-to-play arcade game.   | Read more »
Graphic Novel App netwars – The Butterfl...
Graphic Novel App netwars – The Butterfly Attack has Received Some New Episodes Posted by Jessica Fisher on January 27th, 2015 [ permalink ] | Read more »

Price Scanner via MacPrices.net

New Good Management Suite Simplifies Enterpri...
Good Technology has announced the availability of the Good Management Suite, a comprehensive cross-platform solution for organizations getting started with mobile business initiatives. Built on the... Read more
15-inch 2.0GHz Retina MacBook Pro (refurbishe...
The Apple Store has Apple Certified Refurbished previous-generation 15″ 2.0GHz Retina MacBook Pros available for $1489 including free shipping plus Apple’s standard one-year warranty. Their price is... Read more
15-inch 2.5GHz Retina MacBook Pro on sale for...
Amazon has the 15″ 2.5GHz Retina MacBook Pro on sale for $2319.99 including free shipping. Their price is $180 off MSRP, and it’s the lowest price available for this model from any reseller. Read more
iPad Turns Five – The ”Book Mystique
Five years ago this week, the late Steve Jobs took the stage at an Apple special press event to unveil the first Apple iPad — a slab-shaped, hand-holdable device that was to shake the personal... Read more
Sale! 15-inch Retina MacBook Pros for up to $...
B&H Photo has the new 2014 15″ Retina MacBook Pros on sale for $150 off MSRP for a limited time. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1889.99... Read more
Apple refurbished Mac minis for up to $150 of...
The Apple Store has restocked Apple Certified Refurbished 2014 Mac minis, with models available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz... Read more
Apple Releases New Tranche Of OS Updates For...
Apple has released incremental bugfix and security updates for its most recent Mac and iOS versions. OS X Yosemite 10.10.2 Update Apple says this update includes the following improvements: Resolves... Read more
Samsung To Be Main Supplier Of A9 Chips For i...
Korea’s Maeil Business Newspaper reports that Samsung has beat out its chipmaking rivals, Taiwan’s Qualcomm and TSMC, as supplier of chipsets for the next generations of both its own Galaxy S6 and... Read more
Save up to $340 on iMacs with Apple Certified...
The Apple Store has Apple Certified Refurbished iMacs available for up to $340 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
AppleCare Protection Plans on sale for up to...
B&H Photo has 3-Year AppleCare Warranties on sale for up to $105 off MSRP including free shipping plus NY sales tax only: - Mac Laptops 15″ and Above: $244 $105 off MSRP - Mac Laptops 13″ and... Read more

Jobs Board

*Apple* Solutions Consultant - Retail Sales...
As an ambassador of the Apple brand, the ASC is accountable for driving sales performance by: Connecting with customers. Discovering customers' needs and values. Showing Read more
*Apple* Solutions Consultant - Retail Sales...
As an ambassador of the Apple brand, the ASC is accountable for driving sales performance by: Connecting with customers. Discovering customers' needs and values. Showing Read more
*Apple* Tree Children's Center is hirin...
Apple Tree Learning Center is Hiring Preschool Teachers! Phoenix Childrens Academy is a national leader in the early childhood education industry and we are currently Read more
*Apple* Solutions Consultant - Retail Sales...
**Job Summary** As an Apple Solutions Consultant (ASC) you are the link between our customers and our products. Your role is to drive the Apple business in a retail Read more
*Apple* Solutions Consultant - Retail Sales...
As an ambassador of the Apple brand, the ASC is accountable for driving sales performance by: Connecting with customers. Discovering customers' needs and values. Showing Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.