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

Vienna 3.1.9 :e81515b: - RSS and Atom ne...
Vienna is a freeware and Open-Source RSS/Atom newsreader with article storage and management via a SQLite database, written in Objective-C and Cocoa, for the OS X operating system. It provides... Read more
iExplorer 4.0.12.0 - View and transfer f...
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
OpenEmu 2.0.5 - Open Source game-emulati...
OpenEmu is about to change the world of video game emulation, one console at a time... For the first time, the 'It just works' philosophy now extends to open source video game emulation on the Mac.... Read more
TextSoap 8.3.3 - Automate tedious text d...
TextSoap can automatically remove unwanted characters, fix up messed up carriage returns, and do pretty much anything else that we can think of to text. Save time and effort. Be more productive. Stop... Read more
Apple iTunes 12.6 - Play Apple Music and...
Apple iTunes lets you organize and stream Apple Music, download and watch video and listen to Podcasts. It can automatically download new music, app, and book purchases across all your devices and... Read more
Airmail 3.2.4 - Powerful, minimal email...
Airmail is an mail client with fast performance and intuitive interaction. Support for iCloud, MS Exchange, Gmail, Google Apps, IMAP, POP3, Yahoo!, AOL, Outlook.com, Live.com. Airmail was designed... Read more
MacPilot 9.0.6 - Enable over 1,200 hidde...
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
Jamf Pro 9.98 - Powerful sysadmin/enterp...
Jamf Pro (formerly Casper Suite) is the EMM tool that delights IT pros and the users they support by delivering on the promise of unified endpoint management for Apple devices. At Jamf, connecting... Read more
PopChar 7.7 - $16.99 (51% off)
PopChar helps you get the most out of your font collection. With its crystal-clear interface, PopChar provides a frustration-free way to access any font's special characters. Features Expanded... Read more
RapidWeaver 7.3.1 - 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

Leap to victory in Nexx Studios new plat...
You’re always a hop, skip, and a jump away from a fiery death in Temple Jump, a new platformer-cum-endless runner from Nexx Studio. It’s out now on both iOS and Android if you’re an adventurer seeking treasure in a crumbling, pixel-laden temple. | Read more »
Failbetter Games details changes coming...
Sunless Sea, Failbetter Games' dark and gloomy sea explorer, sets sail for the iPad tomorrow. Ahead of the game's launch, Failbetter took to Twitter to discuss what will be different in the mobile version of the game. Many of the changes make... | Read more »
Splish, splash! The Pokémon GO Water Fes...
Niantic is back with a new festival for dedicated Pokémon GO collectors. The Water Festival officially kicks off today at 1 P.M. PDT and runs through March 29. Magikarp, Squirtle, Totodile, and their assorted evolved forms will be appearing at... | Read more »
Death Road to Canada (Games)
Death Road to Canada 1.0 Device: iOS Universal Category: Games Price: $7.99, Version: 1.0 (iTunes) Description: Get it now at the low launch price! Price will go up a dollar every major update. Update news at the bottom of this... | Read more »
Bean's Quest Beginner's Guide:...
Bean's Quest is a new take on both the classic platformer and the endless runner, and it's free on the App Store for the time being. Instead of running constantly, you can't stop jumping. That adds a surprising new level of challenge to the game... | Read more »
How to rake in the cash in Bit City
Our last Bit City guide covered the basics. Now it's time to get into some of the more advanced techniques. In the later cities, cash flow becomes much more difficult, so you'll want to develop some strategies if you want to complete each level.... | Read more »
PixelTerra (Games)
PixelTerra 1.1.1 Device: iOS Universal Category: Games Price: $.99, Version: 1.1.1 (iTunes) Description: The world of PixelTerra is quite dangerous so you need to build a shelter, find some food supply and get ready to protect... | Read more »
Tokaido™ (Games)
Tokaido™ 1.0 Device: iOS Universal Category: Games Price: $6.99, Version: 1.0 (iTunes) Description: Discover the digital adaptation of Tokaido, the boardgame phenomenon that has already sold more than 250,000 copies worldwide, and... | Read more »
Card Thief (Games)
Card Thief 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Card Thief is a solitaire style stealth game played with a deck of cards. In Card Thief you move through a deck of cards as a... | Read more »
Smilegate’s crafting battler Super Tank...
Super Tank Rumbleputs you in the seat of your very own, handcrafted tank. You can choose from over 100 different parts to create your Super Tank before taking it out to wreak havoc on your opponents in glorious PVP combat. Now, Smilegate is upping... | Read more »

Price Scanner via MacPrices.net

13-inch Touch Bar MacBook Pros on sale for up...
B&H Photo has the Apple 13″ Touch Bar MacBook Pros in stock today and on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.9GHz/512GB Touch Bar... Read more
Today only! 15-inch 2.7GHz Space Gray Touch B...
B&H Photo has the new 2016 15″ 2.7GHz Space Gray Apple Touch Bar MacBook Pro in stock today and on sale for $300 off MSRP for today only. Shipping is free, and B&H charges NY sales tax only... Read more
New $329 iPad A Fabulous Value; 10.5-Inch iPa...
Part of the iPad upgrade/new model puzzle is now in place. Yesterday, as KGI Securities financial services group analyst Ming-Chi Kuo last summer predicted they would, Apple released a new low-cost 9... Read more
New 9.7-Inch iPad Features All Of The Fun...
Apple today updated its most popular-sized iPad, featuring a brighter 9.7-inch Retina display and best-in-class performance at its most affordable price ever, starting at $329 (US) with 32GB of... Read more
Apple Introduces iPhone 7 and iPhone 7 Plus (...
Apple today announced iPhone 7 and iPhone 7 Plus (PRODUCT)RED Special Edition in a vibrant red matte aluminum finish, in recognition of more than 10 years of partnership between Apple and (RED). This... Read more
Apple now offering Certified Refurbished 15-i...
Apple is now offering Certified Refurbished 2016 15″ Touch Bar MacBook Pros for $360-$420 off original MSRP. An Apple one-year warranty is included with each model, and shipping is free: - 15″ 2.6GHz... Read more
Apple Introduces Clips: A Free Innovative Way...
Apple today introduced Clips, a new app that makes it quick and fun for anyone to create expressive videos on iPhone and iPad. The app features a unique design for combining video clips, photos and... Read more
Urban Armor Gear Unveils Case For 4th Generat...
Orange County, California based Urban Armor Gear (UAG), designers of rugged, lightweight protective cases for phones, tablets and laptops, has released its latest drop-tested cases for Apple’s 4th... Read more
Most Users Continue To Prefer 5.0 to 5.3 Inch...
In the first half of 2016, US and UK smartphone users were most likely to be interested in purchasing a device with a 5.3″ or 5.0″ display. Findings in a new report from the User Experience... Read more
12-inch 1.2GHz Retina MacBooks on sale for up...
B&H has 12″ 1.2GHz Retina MacBooks on sale for up to $200 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 12″ 1.2GHz Space Gray Retina MacBook: $1439.99 $160 off MSRP - 12″ 1... Read more

Jobs Board

*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Fulltime aan de slag als shopmanager in een h...
Ben jij helemaal gek van Apple -producten en vind je het helemaal super om fulltime shopmanager te zijn in een jonge en hippe elektronicazaak? Wil jij werken in Read more
Starte Dein Karriere-Abenteuer in den Hauptst...
…mehrsprachigen Teams betreust Du Kunden von bekannten globale Marken wie Apple , Mercedes, Facebook, Expedia, und vielen anderen! Funktion Du wolltest schon Read more
Starte Dein Karriere-Abenteuer in den Hauptst...
…mehrsprachigen Teams betreust Du Kunden von bekannten globale Marken wie Apple , Mercedes, Facebook, Expedia, und vielen anderen! Funktion Du wolltest schon Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.