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

calibre 2.69.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Evernote 6.9.1 - Create searchable notes...
Evernote allows you to easily capture information in any environment using whatever device or platform you find most convenient, and makes this information accessible and searchable at anytime, from... Read more
jAlbum Pro 13.5 - Organize your digital...
jAlbum Pro has all the features you love in jAlbum, but comes with a commercial license. You can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly... Read more
jAlbum 13.5 - Create custom photo galler...
With jAlbum, you can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly, with pro results - Simply drag and drop photos into groups, choose a design... Read more
Google Chrome 53.0.2785.143 - Modern and...
Google Chrome is a Web browser by Google, created to be a modern platform for Web pages and applications. It utilizes very fast loading of Web pages and has a V8 engine, which is a custom built... Read more
Chromium 53.0.2785.143 - Fast and stable...
Chromium is an open-source browser project that aims to build a safer, faster, and more stable way for all Internet users to experience the web. Version 53.0.2785.143: [Security Fix] High CVE-2016-... Read more
QuickBooks 2015 16.1.7.1524 R8 - Financi...
Save 20% on QuickBooks Pro for Mac today through this special discount link QuickBooks 2015 helps you manage your business easily and efficiently. Organize your finances all in one place, track... Read more
Sierra Cache Cleaner 11.0.1 - Clear cach...
Sierra Cache Cleaner is an award-winning general purpose tool for macOS X. SCC makes system maintenance simple with an easy point-and-click interface to many macOS X functions. Novice and expert... Read more
Default Folder X 5.0.7 - Enhances Open a...
Default Folder X attaches a toolbar to the right side of the Open and Save dialogs in any OS X-native application. The toolbar gives you fast access to various folders and commands. You just click on... Read more
Safari Technology Preview 10.1 - The new...
Safari Technology Preview contains the most recent additions and improvements to WebKit and the latest advances in Safari web technologies. And once installed, you will receive notifications of... Read more

Pumped BMX 3: Beginner tips and tricks
There’s a whole lot more to Pumped BMX 3 than meets the eye. Your goal is to perform a wide array of sweet flips and tricks, but that’s easier said than done. It takes well practiced timing and coordination, and the game doesn’t really explain that... | Read more »
Cybird’s latest release - BFB Champions...
Launched in the UK in early September, BFB Champions’ newest update is loaded with great new features, and looks set to outshine the original version by taking it out of soft launch and giving it a new lease of life. | Read more »
3 apps to boost your focus
As someone who works from home, my workspace is a minefield of distraction. Cats, tasty snacks, the wind blowing past my window, that cleaning that I suddenly can’t put off any longer. If I let distraction takes its course, I find that soon half... | Read more »
Pumped BMX 3 (Games)
Pumped BMX 3 1.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0 (iTunes) Description: The final instalment of the smash hit #1 rated BMX game is here! Following on from the insane success of Pumped BMX 2, Pumped 3... | Read more »
4 games like Burly Men at Sea to inspire...
Burly Men at Sea is out today and it looks a treat. It tells the tale of three Scandinavian fishermen who leave the humdrum of their daily lives to go exploring. It’s a beautiful folksy story that unfurls as you interact with the environment... | Read more »
3 reasons you need to play Kingdom: New...
Developed by a tag team of indie developers - Thomas "Noio" van den Berg and Marco "Licorice" Bancale - Kingdom is a vibrant medieval fantasy adventure that casts players as a king or queen who must expand their empire by exploring the vasts lands... | Read more »
JoyCity have launched a brand new King o...
Great news for all of you Game of Dice fans out there - JoyCity have just released a brand new limited edition pack with a really cool twist. The premise of Game of Dice is fairly straightforward, asking you to roll dice to navigate your way around... | Read more »
Burly Men at Sea (Games)
Burly Men at Sea 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Burly Men at Sea is a folktale about a trio of large, bearded fishermen who step away from the ordinary to seek adventure. | Read more »
3 tips for catching the gnarliest waves...
Like a wave breaking on the shore, Tidal Rider swept its way onto the App Store charts this week settling firmly in the top 10. It’s a one-touch high score-chaser in which you pull surfing stunts while dodging seagulls and collecting coins. The... | Read more »
The beginner's guide to destroying...
Age of Heroes: Conquest is 5th Planet Games’ all new turn-based multiplayer RPG, full of fantasy exploration, guild building, and treasure hunting. It’s pretty user-friendly as far as these games go, but when you really get down to it, you’ll find... | Read more »

Price Scanner via MacPrices.net

CAZE Annouces New Zero 5 Case for Jet Black i...
Hong Kong basd CAZE has announced Zero 5 case for iPhone 7/ 7 Plus, one of the world’s thinnest clear hard cases, measuring just 0.5 millimeters. CAZE has been producing and improving the Zero 5... Read more
Nest Egg Inventory App for iOS Offers Conven...
Campbell, California based Winprogger LLC has announced the release and immediate availability of Nest Egg – Inventory 4.1.22, an important update to their easy-to-use, yet comprehensive inventory... Read more
Factor4, LLC Launches Apple iOS and Android G...
Factor4, LLC, which offers gift and loyalty services to the SMB marketplace, has released free mobile applications that enable merchants to process via all Apple and Android devices. The Apple and... Read more
15-inch Retina MacBook Pros on sale for $200...
B&H Photo has 15″ Retina Apple MacBook Pros on sale for $200 off MSRP. Shipping is free, and B&H charges NY tax only: - 15″ 2.2GHz Retina MacBook Pro: $1799 $200 off MSRP - 15″ 2.5GHz Retina... Read more
Apple refurbished iMacs available for up to $...
Apple has Certified Refurbished 2015 21″ & 27″ iMacs available for up to $350 off MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: - 21″ 3.... Read more
Check Apple prices on any device with the iTr...
MacPrices is proud to offer readers a free iOS app (iPhones, iPads, & iPod touch) and Android app (Google Play and Amazon App Store) called iTracx, which allows you to glance at today’s lowest... Read more
Apple price trackers, updated continuously
Scan our Apple Price Trackers for the latest information on sales, bundles, and availability on systems from Apple’s authorized internet/catalog resellers. We update the trackers continuously: - 15″... Read more
Apple refurbished 2016 13-inch MacBook Airs a...
Apple has Certified Refurbished 2016 13″ MacBook Airs available starting at $849. An Apple one-year warranty is included with each MacBook, and shipping is free: - 2016 13″ 1.6GHz/8GB/128GB MacBook... Read more
1.4GHz Mac mini on sale for $449, save $50
Adorama has the 1.4GHz Mac mini on sale for $50 off MSRP including free shipping plus NY & NJ sales tax only: - 1.4GHz Mac mini (Apple sku# MGEM2LL/A): $449 $50 off MSRP To purchase a mini at... Read more
Apple refurbished 2015 13-inch MacBook Airs a...
Apple has Certified Refurbished 2015 13″ MacBook Airs available starting at $759. An Apple one-year warranty is included with each MacBook, and shipping is free: - 2015 13″ 1.6GHz/4GB/128GB MacBook... Read more

Jobs Board

*Apple* Retail - Multiple Positions- Akron,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Hardware Design Validation Engineer - *Apple...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more Read more
Systems Architecture Prototyping - *Apple*...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more Read more
*Apple* Retail - Multiple Positions- South B...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Restaurant Manager (Neighborhood Captain) - A...
…in every aspect of daily operation. WHY YOU'LL LIKE IT: You'll be the Big Apple . You'll solve problems. You'll get to show your ability to handle the stress and Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.