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

MacCleanse 9.0 - $29.95
MacCleanse is the product of thousands of hours of intense research and development. It meticulously scans all of the nooks and crannies of a computer for unnecessary junk that can take up huge... Read more
MacPilot 12.0 - $15.96
MacPilot gives you the power of UNIX and the simplicity of Macintosh, which means a phenomenal amount of untapped power in your hands! Use MacPilot to unlock over 1,200 features, and access them all... Read more
NetNewsWire 5.1 - RSS and Atom news read...
NetNewsWire is the best way to keep up with the sites and authors you read most regularly. Let NetNewsWire pull down the latest articles, and read them in a distraction-free and Mac-like way. Native... Read more
FontExplorer X Pro 7.1.3 - Font manageme...
FontExplorer X Pro is optimized for professional use; it's the solution that gives you the power you need to manage all your fonts. Now you can more easily manage, activate and organize your... Read more
DiskCatalogMaker 8.2.5 - Catalog your di...
DiskCatalogMaker is a simple disk management tool which catalogs disks. Simple, light-weight, and fast Finder-like intuitive look and feel Super-fast search algorithm Can compress catalog data for... Read more
Skim 1.5.12 - PDF reader and note-taker...
Skim is a PDF reader and note-taker for OS X. It is designed to help you read and annotate scientific papers in PDF, but is also great for viewing any PDF file. Skim includes many features and has a... Read more
rekordbox 6.1.0.0030 - Professional DJ m...
rekordbox is the best way of preparing and managing your tracks, be it at home, in the studio, or even on the plane! It allows you to import music from other music-management software using the... Read more
iExplorer 4.4.0 - View and transfer file...
iExplorer is an iPhone browser for Mac lets you view the files on your iOS device. By using a drag and drop interface, you can quickly copy files and folders between your Mac and your iPhone or... Read more
OmniGraffle 7.17.5 - Create diagrams, fl...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
Apple Configurator 2.13.1 - Configure an...
Apple Configurator makes it easy to deploy iPad, iPhone, iPod touch, and Apple TV devices in your school or business. Use Apple Configurator to quickly configure large numbers of devices connected to... Read more

Latest Forum Discussions

See All

Raziel: Dungeon Arena is a hack 'n...
Raziel: Dungeon Arena is available now on mobile and will appeal to fans of both comic books and old school dungeon crawlers. Not only will you hack 'n' slash your way through mobs of enemies but there's also fully-narrated animated comic to enjoy... | Read more »
Steam Link Spotlight - Hades
Steam Link Spotlight is a feature where we look at PC games that play exceptionally well using the Steam Link app. Our last entry was on Disco Elysium. Read about how it plays using Steam Link over here. | Read more »
Microsoft has acquired ZeniMax Media and...
In the latest of a series of blockbuster moves, Microsoft has now acquired Zenimax Media and its subsidiary, Bethesda Softworks, for $7.5 billion. [Read more] | Read more »
Infinity Mechs is an upcoming idle game...
Indie developer SkullStar studio has announced an upcoming idle mech game called Infinity Mechs. It draws inspiration from the mobile game Iron Saga and has been officially licensed by Game Duchy. It's set to launch for both iOS and Android on... | Read more »
PUBG Mobile Lite's latest update se...
PUBG Mobile Lite, the streamlined version of the popular battle royale that's designed to work on less powerful devices, sees the return of a popular game variant today, Survive Till Dawn mode. It arrives as part of the 0.19.0 content update. [... | Read more »
Matchy Catch, Jyamma Games’ new hyper-ca...
Matchy Catch is a new hyper-casual puzzler from Jyamma Games, the Italian studio behind the Pong-inspired puzzle-adventure Hi-Ball Rush. It’s only the developer’s second game for iOS and Android devices, but it promises to be every bit as fun and... | Read more »
Among Us! Imposter Guide - How to be a s...
Among Us! continues to be getting a lot of play in these parts, and since our first guide we've learned a thing or two about the game. This is especially true regarding the imposter role, as its a relatively rare opportunity that we've now put... | Read more »
Paladin's Story is an upcoming fant...
Paladin's Story is an upcoming fantasy RPG with an off-kilter sense of humour that's heading for iOS and Android. It will officially launch for both on September 16th though the game is already available on Google Play in Early Access. [Read more... | Read more »
Among Us! Guide - Tips for the uninitiat...
| Read more »
A Pretty Odd Bunny is a stealth-platform...
A Pretty Odd Bunny is a stealth-platformer from two-man team AJ Ordaz and René Rivera. It follows the story of a red-eyed rabbit who is allergic to carrots and instead has a penchant for devouring pigs. It's available now for Android devices. [... | Read more »

Price Scanner via MacPrices.net

The cheapest Macs are back in stock today at...
Apple has restocked clearance, previous-generation, Certified Refurbished Mac minis starting at only $599. Each mini comes with free shipping plus Apple’s standard one-year warranty. These are the... Read more
Sale! Amazon has 2020 13″ 2.0GHz MacBook Pros...
Amazon has 2020 13″ MacBook Pros with 10th generation Intel CPUs back in stock on sale again today for $150-$200 off Apple’s MSRP. Shipping is free. Be sure to purchase the MacBook Pro from Amazon,... Read more
Base 13″ 1.4GHz Apple MacBook Pros on sale fo...
Apple reseller Expercom is offering a $65-$75 discount on new 2020 13″ 1.4GHz MacBook Pros, depending on configuration. Shipping is free. Expercom estimates shipping in 3-5 days, as stock of Apple’s... Read more
Price drop! Get a 44mm Apple Watch Series 5 G...
Amazon has dropped their price on the 44mm Apple Watch Series 5 GPS + Cellular by $100 to $429 shipped. That’s $100 off Apple’s original MSRP for this model. For the latest prices and sales, see our... Read more
Verizon offers $200 discount on new Apple Wat...
Verizon will take up to $200 off the purchase of a new GPS + Cellular Apple Watch Series 6 or Apple Watch SE with select trade-in and the purchase of a new iPhone with service. The fine print: “Get... Read more
Verizon offers $250 discount on new 8th gener...
Verizon will take up to $250 off the price of an 8th generation 2020 Apple Cellular iPad with select trade-ins and a new iPhone purchase. Plus get Apple News+ free for 6 months. The fine print: “Save... Read more
Apple’s Implementation Of COVID-19 Exposure...
NEWS: 09.18.20 – The latest effort by Apple to embed exposure notifications for COVID-19 contact tracing right into its mobile operating system has some iPhone users weary of being exposed to... Read more
Here’s how to get a 16″ MacBook Pro for $300...
B&H Photo has new 16″ MacBook Pros on sale today for $250-$300 off Apple’s MSRP, starting at $2099. Expedited shipping is free to many addresses in the US: – 2019 16″ 2.6GHz 6-Core MacBook Pro... Read more
Apple has Certified Refurbished 16″ MacBook P...
Apple has Certified Refurbished 2019 16″ MacBook Pros available for up to $420 off the cost of new models, starting at $2039. Each model features a new outer case, shipping is free, and an Apple 1-... Read more
Price drops! Apple reseller B&H drops App...
B&H Photo has dropped prices on Apple Watch Series 5 models by $50-$70 off Apple’s original MSRP. Shipping is free. These are the same Apple Watch models sold by Apple in their retail and online... Read more

Jobs Board

Security Officer ($23.00/Hourly) - *Apple*...
**Security Officer \($23\.00/Hourly\) \- Apple Store** **Description** About NMS Built on a culture of safety and integrity, NMSdelivers award\-winning, integrated Read more
Security Officer ($23.00/Hourly) - *Apple*...
**Security Officer \($23\.00/Hourly\) \- Apple Store** **Description** About NMS Built on a culture of safety and integrity, NMSdelivers award\-winning, integrated Read more
Platform - Workplace Eng - *Apple* Enterpri...
MORE ABOUT THIS JOB We are looking for an Apple Platform Engineer who will bring a unique engineering skill set, support, clarity, organization and above all else, Read more
*Apple* Certified Repair Technician - Utah S...
…selected candidate will work in the USU Campus Store Tech Department as an Apple Certified Repair Technician and floor associate. This position is for both summer Read more
Senior Data Engineer - *Apple* - Theorem, L...
Job Summary Apple is seeking an experienced, detail-minded data engineeringconsultant to join our worldwide business development and strategy team. If you are Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.