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

NetShade 8.3 - Browse privately using an...
NetShade is an anonymous proxy and VPN app+service for Mac. Unblock your Internet through NetShade's high-speed proxy and VPN servers spanning 17 countries. NetShade masks your IP address as you... Read more
Adobe Animate CC 2020 20.0.1 - Animation...
Animate CC 2020 is available as part of Adobe Creative Cloud for as little as $20.99/month (or $9.99/month if you're a previous Flash Professional customer). Animate CC 2020 (was Flash CC) lets you... Read more
Adobe Acrobat DC 19.021.20058 - Powerful...
Acrobat DC is available only as a part of Adobe Creative Cloud, and can only be installed and/or updated through Adobe's Creative Cloud app. Adobe Acrobat DC with Adobe Document Cloud services is... Read more
Adobe Acrobat Reader 19.021.20058 - View...
Adobe Acrobat Reader allows users to view PDF documents. You may not know what a PDF file is, but you've probably come across one at some point. PDF files are used by companies and even the IRS to... Read more
Adobe Flash Player 32.0.0.303 - Plug-in...
Adobe Flash Player is a cross-platform, browser-based application runtime that provides uncompromised viewing of expressive applications, content, and videos across browsers and operating systems.... Read more
Adobe InDesign CC 2019 15.0.1 - Professi...
InDesign CC 2019 is available as part of Adobe Creative Cloud for as little as $20.99/month (or $9.99/month if you're a previous InDesign customer). Adobe InDesign CC 2019 is part of Creative Cloud.... Read more
Adobe Lightroom Classic 9.1 - Import, de...
You can download Lightroom for Mac as a part of Creative Cloud for only $9.99/month with Photoshop, included as part of the photography package. The latest version of Lightroom gives you all of the... Read more
Shredo 1.2.7 - $6.99
Shredo is a beautiful, functional file-shredding and privacy scan utility. It permanently shreds files, folders, and external volumes' contents to keep information secure and impossible for anyone to... Read more
Visual Studio Code 1.41.0 - Cross-platfo...
Visual Studio Code provides developers with a new choice of developer tool that combines the simplicity and streamlined experience of a code editor with the best of what developers need for their... Read more
calibre 4.6.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

Latest Forum Discussions

See All

King's Throne, the hugely ambitious...
King's Throne: Game of Lust is a deeply immersive medieval-set idle RPG which sees you playing as an ambitious prince, and sole heir to your father's kingdom. On a seemingly ordinary night whilst wandering the king's castle, you make the shocking... | Read more »
Abyssrium Pole is an upcoming aquarium b...
FleroGames' upcoming Abyssrium Pole has recently hit one million pre-registers, which is very impressive, particularly for a fairly casual looking game. Those who have pre-registered will receive 1000 Pearl when the game launches on 8th January... | Read more »
Two Spies is pretty fun, but it's h...
Two Spies just dropped on the App Store this week, and it looks pretty neat. The game has two players capturing various cities across Europe, with the goal of eventually spotting and striking the other spy down. It may be simple-looking, but after... | Read more »
Two Spies is a turn-based game for iOS t...
There aren't too many games that feature pass and play multiplayer and there are even less where you can only play against people you know, even when playing online. But Two Spies does both of those things and you can get it for iOS right now. [... | Read more »
Solve your way through new low-poly puzz...
The best escape-the-room games don’t just test your creative problem-solving skills – they look great, too. Released in October this year by Antler (the developer of the succesful VR puzzle SVRVIVE: The Deus Helix), Krystopia offers everything you... | Read more »
Get ready for an epic adventure with Pea...
Following a hugely successful pre-registration campaign, Pearl Abyss' much-hyped MMORPG, Black Desert Mobile, has finally arrived for iOS and Android. With some of the most impressive visuals on mobile, a vast open world to explore, an in-depth... | Read more »
Elder Scrolls: Blades has ditched chest...
Elder Scrolls: Blades started out as one of the most hyped mobile games of 2019, boasting some impressive visuals and no shortage of promise. Our hopes were somewhat dashed when it eventually launched and we all became privy to its mishandled... | Read more »
Hands-On with the Pocket City December U...
At the end of last month, Codebrew Games announced an update coming to their popular city-builder, Pocket City some time this month. In this update is the promise of expanding your city out into other regions, enacting policies, and more. The full... | Read more »
Black Desert Mobile is available for pre...
Pearl Abyss' stunning open-world MMORPG, Black Desert Mobile, is set to launch for iOS and Android on December 11th at 12 AM PST (8 AM UTC). However, those looking to get in early and test out the in-depth character customisation will be able to... | Read more »
Extraordinary Ones, NetEase's innov...
NetEase's inventive 5v5 anime MOBA, Extraordinary Ones, has now opened for pre-registration ahead of its global launch in early 2020. The game seems to have received a fairly warm reception from fans after its soft-launch earlier in the year,... | Read more »

Price Scanner via MacPrices.net

Apple Watch Series 3 models on sale at Amazon...
Amazon has Apple Watch Series 3 GPS models on sale for $20 off MSRP, starting at only $179. Their prices are the lowest available for these models from any Apple reseller. Choose Amazon as the seller... Read more
Sunday AirPods Sale: Amazon drops prices to a...
Amazon has new 2019 Apple AirPods on sale today ranging up to $30 off MSRP, starting at $139. Shipping is free: – AirPods Pro: $249 $0 off MSRP – AirPods with Wireless Charging Case: $168.95 $30 off... Read more
Holiday 2019 sale: 11″ iPad Pros for up to $2...
Amazon has new Apple 11″ iPad Pros in stock today and on sale for up to $200 off Apple’s MSRP as part of their Holiday 2019 sale. These are the same iPad Pros sold by Apple in its retail and online... Read more
B&H has 12.9″ WiFi iPad Pros on sale for...
B&H Photo has new 12.9″ WiFi iPad Pros on sale for up to $150 off Apple’s MSRP as part of their Holiday 2019 sale. Overnight shipping is free to many addresses in the US: – 12.9″ 64GB WiFi iPad... Read more
Find the best Holiday 2019 prices on Apple’s...
Our Apple award-winning price trackers are the best place to look for the best deals and lowest prices on Apple gear this 2019 Holiday shopping season. Scan our price trackers for the latest... Read more
13″ 2.4GHz/256GB Silver MacBook Pro on sale f...
Amazon has the Silver 13″ 2.4GHz/256GB 4-Core Touch Bar MacBook Pro on sale for $1499.99 shipped. Their price is $300 off Apple’s MSRP, and it’s the lowest price currently available for a 13″ 2.4GHz... Read more
Sams Club one day sales event December 14th:...
Through midnight Saturday night (December 14th), Sams Club online has several Apple Watch Series 5 models on sale for $40 off MSRP as part of their One Day sales event. Choose free shipping or free... Read more
Total Wireless offers iPhone 6S models for as...
Total Wireless has Apple 32GB iPhone 6S models available starting at $99: – 32GB iPhone 6S: $99.99 – 32GB iPhone 6S Plus: $149.99 A no-contract Total Wireless prepaid plan is required with your... Read more
Get a 4 or 6-core Mac Mini for up to $170 off...
B&H Photo has 4-Core and 6-Core Mac minis on sale for up to $170 off Apple’s standard MSRP as part of their Holiday 2019 sale. Overnight shipping is free to many US addresses: – 3.6GHz Quad-Core... Read more
Amazon restocks base 13″ 1.4GHz MacBook Pro f...
Amazon has restocked the base 13″ 1.4GHz/128GB Space Gray MacBook Pro for $1099.99 shipped. Their price is $200 off Apple’s MSRP, and it’s the cheapest price available for a new MacBook Pro. Amazon... Read more

Jobs Board

*Apple* Mobility Sales Professional - Best B...
**750138BR** **Job Title:** Apple Mobility Sales Professional **Job Category:** Store Associates **Store NUmber or Department:** 000471-Mt Vernon-Store **Job Read more
*Apple* Engineering Specialist (ITC ) - Gene...
…Suitability clearance, per contract requirements. Currently, we are seeking an Apple Engineering Specialist in Washington, DC The responsibilities for candidates in Read more
Senior *Apple* Endpoint Engineer - Leidos (...
…Medicaid Service (CMS) End User environment. Perform specific duties as an Apple Endpoint Engineer in support of the infrastructure operations, hardware, software Read more
Perioperative - RN - ( *Apple* Hill Surgical...
Perioperative - RN - ( Apple Hill Surgical Center) Tracking Code 59281 Job Description Monday - Friday - Part Time - Days Possible Saturdays General Summary: Under Read more
Lead DevOps Engineer - *Apple* - Theorem, L...
Job Summary Apple is looking for a seasoned Lead DevOps Engineer that can lead multiple projects and teams while delivering high quality and performant solutions in Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.