MySQL Management and Administration with Navicat

By Gokhan Ozar

Standard Mini. This book is written for MySQL Users, Developers, and admins who want to discover just how easy Navicat makes even normally arduous tasks.

• Language: English
• Publisher: Packt Publishing
• Release date: Sep 13, 2012
• ISBN: 9781849687478

Book preview

Table of Contents

MySQL Management and Administration with Navicat

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Getting Started

Enter Navicat

Setting up a connection to the database

Connecting via Secure Shell (SSH)

Connecting via an HTTP tunnel

Setting up Secure Sockets Layer (SSL)

Advanced settings

Testing and saving your settings

Summary

2. Working with Databases

Managing database objects with Navicat

Creating a database from scratch

Creating tables

Defining foreign keys

Defining triggers

Entering data in tables

Creating views

Working with functions and procedures

Using Navicat’s event designer for MySQL

Working with queries in Navicat

Designing the Query

Building further queries

Summary

3. Data Management with Navicat

Working with an existing database

Creating the database schema from a SQL dump file

Importing and exporting data

Exporting in detail

Importing a file into a table

Direct data transfer between two databases

Data and structure synchronization

Backup and restore

Creating and scheduling batch jobs

Summary

4. Data Modeling with Navicat

Working with Navicat's model designer

Creating our first model

Defining the relationships

Adding some pizzazz to the model with notes and images

Working with layers

Deleting unwanted objects

Working with multiple diagrams

Exporting the model diagram to SQL

Reverse-engineering a database into a model

Summary

5. Database Maintenance and Security Management

User and privilege management with Navicat

How MySQL deals with access privileges

Diving deep into creating and editing a user in Navicat

Performing maintenance tasks with Navicat

Analyzing a MySQL table or view with Navicat

Checking a table or view

Optimization made easy

Repairing a table

Summary

6. Designing Reports with Navicat

First contact with the tool(s)

Preparing the data with Query Wizard

Designing the report

Switching to the Preview tab

Summary

A. Additional Tips and Tricks

Transferring your settings from one computer to another

Transferring settings on the Mac

Transferring settings on Windows

Monitoring the MySQL Server

The process list

Variables

Status

Revealing a hidden search feature of Navicat

Summary

Index

MySQL Management and Administration with Navicat

---

MySQL Management and Administration with Navicat

Copyright © 2012 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: September 2012

Production Reference: 1060912

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 9781849687-46-1

www.packtpub.com

Cover Image by Artie Ng (<[e-mail]>)

Credits

Author

Gökhan Ozar

Reviewers

Nick Au

Matthew Yau

Acquisition Editor

Alex Newbury

Lead Technical Editor

Alex Newbury

Technical Editor

Kaustubh S. Mayekar

Copy Editor

Insiya Morbiwala

Project Coordinator

Abhishek Kori

Proofreader

Maria Gould

Indexer

Monica Ajmera Mehta

Production Coordinator

Nitesh Thakur

Cover Work

Nitesh Thakur

About the Author

Gökhan Ozar is an IT professional with both hands-on and outsourcing expertise in the areas of application development, database design, data analysis, project management, systems integration, training, support, and delegation of support.

A graduate in 1999 of Bilkent University in Ankara, Turkey, he started his career as a Web Designer and Developer, making database-driven web applications on a variety of platforms.

During his high school years at the age of 16, he was known within the Mac user communities in Turkey as the maker of an adventure game called The Journey, made exclusively for older Macs running on Mac OS versions prior to OS X.

He has had experience in various domains of IT, such as business intelligence, data warehousing, and quality assurance, besides software development mainly on Java EE and .NET platforms. He went on to build his career working with the Business Process Management/Electronic Document Workflow software.

He also runs several blogs, which are accessible from his personal website at http://gokhan.ozar.net, and also welcomes new followers on Twitter (twitter.com/skyhan).

About the Reviewers

Nick Au, who graduated from the Hong Kong University of Science and Technology, has been working as a Software Developer at PremiumSoft for over 10 years. Now he is the lead developer for the Windows version of Navicat, leading a team of over 10 programmers.

Matthew Yau joined PremiumSoft after he graduated from the Hong Kong Polytechnic University in 2000. After taking up a position in web programming for the first 2 years, he has focused on developing the Navicat series since 2002. Now, he is working at the managerial level for the development of Navicat and other softwares for the Company.

www.PacktPub.com

Support files, eBooks, discount offers, and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books. 

Why Subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

Instant Updates on New Packt Books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.

Preface

Navicat is a GUI tool used for managing every aspect of a MySQL Server, such as managing visual tools as well as an intelligent code editor for handcoding SQL and stored procedures. While some of its features are fairly intuitive, some of them require guidance to be discovered and learned.

The book starts with creating basic server connection setups, designing databases from scratch, or importing existing data. Then it continues with using advanced features, such as designing functions and stored procedures, creating event triggers, and creating and scheduling batch jobs.

The chapters are ordered in a logical progression, where the user starts from simple structures to complex design, and is gradually introduced to advanced features. By the end of the last chapter, the reader should be able to handle every aspect of database administration as well as how to master the intelligent code editor, in the case of a development need, such as functions and procedures.

For intermediate and advanced level MySQL users and administrators, the book could be used as a reference guide, and chapters need not be followed in any order.

What this book covers

Chapter 1, Getting Started, gives an introduction to the Navicat Database Administration tool with a GUI and describes how to set up different kinds of connections, from basic settings to advanced configurations.

Chapter 2, Working with Databases, discusses the fundamentals of working with database objects, such as tables, views, functions, and events, along with designing queries using Navicat's visual tools.

Chapter 3, Data Management with Navicat, takes you through the process of data management. The topics covered are import and export of data in a variety of formats, direct data transfer between different databases, data and structure synchronization, backup/restore operations, and creating and scheduling of batch jobs.

Chapter 4, Data Modeling with Navicat, guides you through the steps involved in visual data modeling, so as to help us learn how to design