MySQL Management and Administration with Navicat

134 Pages·2012·4.65 MB·English

by  Gökhan Ozar

MySQL Management and
Administration with Navicat
Master the tools you thought you knew and discover
the features you never knew existed
Gökhan Ozar
professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
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 ([email protected])
Credits
Author Project Coordinator
Gökhan Ozar Abhishek Kori
Reviewers Proofreader
Nick Au Maria Gould
Matthew Yau
Indexer
Monica Ajmera Mehta
Acquisition Editor
Alex Newbury
Production Coordinator
Nitesh Thakur
Lead Technical Editor
Alex Newbury
Cover Work
Nitesh Thakur
Technical Editor
Kaustubh S. Mayekar
Copy Editor
Insiya Morbiwala
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 [email protected] 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.
Table of Contents
Preface 1
Chapter 1: Getting Started 7
Enter Navicat 7
Setting up a connection to the database 8
Connecting via Secure Shell (SSH) 10
Connecting via an HTTP tunnel 11
Setting up Secure Sockets Layer (SSL) 11
Advanced settings 12
Testing and saving your settings 13
Summary 13
Chapter 2: Working with Databases 15
Managing database objects with Navicat 15
Creating a database from scratch 16
Creating tables 18
Defining foreign keys 20
Defining triggers 22
Entering data in tables 24
Creating views 26
Working with functions and procedures 29
Using Navicat’s event designer for MySQL 33
Working with queries in Navicat 35
Designing the Query 35
Building further queries 38
Summary 42
Table of Contents
Chapter 3: Data Management with Navicat 43
Working with an existing database 44
Creating the database schema from a SQL dump file 45
Importing and exporting data 49
Exporting in detail 50
Importing a file into a table 55
Direct data transfer between two databases 57
Data and structure synchronization 58
Backup and restore 60
Creating and scheduling batch jobs 62
Summary 64
Chapter 4: Data Modeling with Navicat 65
Working with Navicat's model designer 66
Creating our first model 68
Defining the relationships 71
Adding some pizzazz to the model with notes and images 74
Working with layers 75
Deleting unwanted objects 76
Working with multiple diagrams 76
Exporting the model diagram to SQL 77
Reverse-engineering a database into a model 78
Summary 80
Chapter 5: Database Maintenance and Security Management 81
User and privilege management with Navicat 82
How MySQL deals with access privileges 84
Diving deep into creating and editing a user in Navicat 85
Performing maintenance tasks with Navicat 89
Analyzing a MySQL table or view with Navicat 90
Checking a table or view 90
Optimization made easy 91
Repairing a table 91
Summary 91
Chapter 6: Designing Reports with Navicat 93
First contact with the tool(s) 94
Preparing the data with Query Wizard 95
Designing the report 101
Switching to the Preview tab 105
Summary 108
[ ii ]
Table of Contents
Appendix: Additional Tips and Tricks 109
Transferring your settings from one computer to another 109
Transferring settings on the Mac 109
Transferring settings on Windows 110
Monitoring the MySQL Server 111
The process list 112
Variables 113
Status 113
Revealing a hidden search feature of Navicat 113
Summary 114
Index 115
[ iii ]