-MySQLDatabaseUsageandAdministration-McGraw-Hill2009.pdf

MySQL Database Usage
& Administration

Vikram Vaswani

New York Chicago San Francisco
Lisbon London Madrid Mexico City

Milan New Delhi San Juan
Seoul Singapore Sydney Toronto

Copyright © 2010 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States Copyright Act of 1976,
no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without
the prior written permission of the publisher.

ISBN: 978-0-07-160550-2

MHID: 0-07-160550-9

The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-160549-6, MHID: 0-07-160549-5.

All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name,
we use names in an editorial fashion only, and to the πbenefit of the trademark owner, with no intention of infringement of the trademark. Where
such designations appear in this book, they have been printed with initial caps.

McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training pro-
grams. To contact a representative please e-mail us at [email protected].

Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechan-
ical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of any informa-
tion and is not responsible for any errors or omissions or the results obtained from the use of such information.

TERMS OF USE

This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use
of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the
work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, dis-
seminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own non-
commercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to com-
ply with these terms.

THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE
ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY
INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DIS-
CLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MER-
CHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the func-
tions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its
licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages result-
ing therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall
McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the
use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall
apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.

For Farah and Tonka:
I couldn’t have got this far without you!

About the Author

Vikram Vaswani is the founder and CEO of Melonfire
(www.melonfire.com), a consultancy firm with special
expertise in open-source tools and technologies. He is a
passionate proponent of the open-source movement and
frequently contributes articles and tutorials on open-source
technologies—including Perl, Python, PHP, MySQL, and
Linux—to the community at large. His previous books
include MySQL: The Complete Reference (www.mysql-tcr.com),
PHP: A Beginner’s Guide (www.php-beginners-guide.com),
and PHP Programming Solutions (www.php-programming-
solutions.com).

Vikram has more than eight years’ experience interacting with the MySQL RDBMS
as a user, administrator, and application developer. He has deployed MySQL in a variety
of different environments, including corporate intranets, high-traffic websites, and
mission-critical thin client applications, and is a vocal advocate of MySQL in his role as
a software consultant.

A Felix Scholar at the University of Oxford, England, Vikram combines his interest in
Web application development with various other activities. When not dreaming up plans
for world domination, he amuses himself by reading crime fiction, watching old movies,
playing squash, blogging, and keeping an eye out for unfriendly agents. Read more
about him and MySQL Database Usage & Administration at www.mysql-usage.com.

About the Technical Editor

Chris Cornutt has been involved in the PHP community for more than eight years. Soon
after discovering the language, he started up his news site, www.PHPDeveloper.org, to
share the latest happenings and opinions from other PHPers from around the world.
Chris has written for publications such as php|architect and the international PHP
magazines on topics ranging from geocoding to trackbacks. He is also a coauthor of
PHP String Handling (Wrox Press, 2003). Chris lives in Frisco, Texas, with his wife and
son, where he works for a large natural-gas distributor maintaining their website and
developing PHP-based applications.

Contents at a Glance

Part I Usage
1 An Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 Understanding Basic Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
3 Making Design Decisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
4 Using Joins, Subqueries, and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
5 Using Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
6 Using Stored Procedures and Functions . . . . . . . . . . . . . . . . . . . . . . . . 133
7 Using Triggers and Scheduled Events . . . . . . . . . . . . . . . . . . . . . . . . . . 167
8 Working with Data in Different Formats . . . . . . . . . . . . . . . . . . . . . . . . 189
9 Optimizing Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

Part II Administration
10 Performing Basic Server Administration . . . . . . . . . . . . . . . . . . . . . . . . 241
11 Managing Users and Controlling Access . . . . . . . . . . . . . . . . . . . . . . . . 263
12 Performing Maintenance, Backup, and Recovery . . . . . . . . . . . . . . . . 287
13 Replicating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
A Installing MySQL and the Sample Database . . . . . . . . . . . . . . . . . . . . . 319

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

v

This page intentionally left blank

Contents

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Part I Usage

1 An Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Unique Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Speed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Reliability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Ease of Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Portability and Standards Compliance . . . . . . . . . . . . . . . . . . . . 7
Multiuser Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Internationalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Wide Application Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Open-Source Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Product Family . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
MySQL Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
MySQL Proxy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
MySQL Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Query Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Workbench . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Migration Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Embedded Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Drivers and Connectors . . . . . . . . . . . . . . . . . . . . . . . . . 10

Technical Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Subsystems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Standards Compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

vii

v i i i M y S Q L D a t a b a s e U s a g e & A d m i n i s t r a t i o n

Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Query Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Extensibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Symmetric Multiprocessing Support . . . . . . . . . . . . . . . . . . . . . 13
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Application Programming Interfaces . . . . . . . . . . . . . . . . . . . . . 14

Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Web Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Data Warehouses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Business Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

2 Understanding Basic Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Understanding Basic Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Databases, Tables, and Records . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Structured Query Language (SQL) . . . . . . . . . . . . . . . . . . . . . . . 23
Database Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Working with Databases and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Using the MySQL Command-Line Client . . . . . . . . . . . . . . . . . 25
Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Removing Tables and Databases . . . . . . . . . . . . . . . . . . . . . . . . . 32

Working with Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Creating Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Removing and Modifying Records . . . . . . . . . . . . . . . . . . . . . . . 34
Retrieving Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Viewing Database, Table, and Field Information . . . . . . . . . . . . . . . . . 47
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

3 Making Design Decisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Selecting Field Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Character and String Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Text and Binary Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Date and Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Enumerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Data Type Selection Checklist . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Selecting Table Storage Engines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
The MyISAM Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
The InnoDB Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
The Archive Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
The Federated Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
The Memory Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
The CSV Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

C o n t e n t s ix

The MERGE Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
The ISAM Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
The NDB Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Storage Engine Selection Checklist . . . . . . . . . . . . . . . . . . . . . . . 56

Using Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Using Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
The UNIQUE Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
The FULLTEXT Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

4 Using Joins, Subqueries, and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Using Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

A Simple Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Types of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Using Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
A Simple Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Types of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Using Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
A Simple View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
View Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Multitable Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Nested Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Updatable Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

5 Using Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Understanding Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

The ACID Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
MySQL and the ACID Properties . . . . . . . . . . . . . . . . . . . . . . . . 114
A Simple Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

Controlling Transactional Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Automatic Commits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Transaction Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Pseudo-Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Table Locks as a Substitute for Transactions . . . . . . . . . . . . . . . 127
Implementing a Pseudo-Transaction with Table Locks . . . . . . 130

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

6 Using Stored Procedures and Functions . . . . . . . . . . . . . . . . . . . . . . . 133
Understanding Stored Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134

Creating and Using Stored Procedures . . . . . . . . . . . . . . . . . . . 135
Creating and Using Stored Functions . . . . . . . . . . . . . . . . . . . . . 142
Setting Routine Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . 146

x M y S Q L D a t a b a s e U s a g e & A d m i n i s t r a t i o n

Doing More with Stored Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Conditional Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Handlers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

7 Using Triggers and Scheduled Events . . . . . . . . . . . . . . . . . . . . . . . . . 167
Understanding Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168

A Simple Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Triggers and Old/New Values . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Triggers and More Complex Applications . . . . . . . . . . . . . . . . . 173
Triggers and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

Understanding Scheduled Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
A Simple Scheduled Event . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Recurring Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
One-Off Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

8 Working with Data in Different Formats . . . . . . . . . . . . . . . . . . . . . . . 189
Importing Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Exporting Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Working with XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Obtaining Results in XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Using XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Importing XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Exporting XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211

9 Optimizing Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Optimizing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Query Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Query Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218

Optimizing Joins and Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Use Joins Instead of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Use Session Variables and Temporary Tables for

Transient Data and Calculations . . . . . . . . . . . . . . . . . . . . . . . 223
Explicitly Name Output Fields . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Index Join Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Rewrite Correlated Subqueries as Joins . . . . . . . . . . . . . . . . . . . 225
Replace Materialized Subqueries with Temporary Tables . . . 225

Optimizing Transactional Performance . . . . . . . . . . . . . . . . . . . . . . . . . 226
Use Small Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Select an Appropriate Isolation Level . . . . . . . . . . . . . . . . . . . . . 227
Avoid Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228

C o n t e n t s xi

Optimizing Stored Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Follow the KISS Principle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Optimize SQL Statements Within Routines . . . . . . . . . . . . . . . . 230

Optimizing Table Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Optimizing Server Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Benchmarking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Part II Administration

10 Performing Basic Server Administration . . . . . . . . . . . . . . . . . . . . . . . 241
Database Administration and MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 242

Uptime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Data Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Security and Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Performance Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

Understanding Basic Server Administration . . . . . . . . . . . . . . . . . . . . 244
Starting and Stopping the Server . . . . . . . . . . . . . . . . . . . . . . . . 245
Checking MySQL Server Status . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Managing MySQL Client Processes . . . . . . . . . . . . . . . . . . . . . . 248
Altering the Server Configuration . . . . . . . . . . . . . . . . . . . . . . . 249
Setting the Server’s SQL Mode . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Troubleshooting with the Error Log . . . . . . . . . . . . . . . . . . . . . . 255

Obtaining Database Meta-Information . . . . . . . . . . . . . . . . . . . . . . . . . 256
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

11 Managing Users and Controlling Access . . . . . . . . . . . . . . . . . . . . . . . 263
Understanding the Access Control System . . . . . . . . . . . . . . . . . . . . . . 264

The user Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
The db and host Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
The tables_priv and columns_priv Tables . . . . . . . . . . . . . . . . . 272
The procs_priv Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Interaction Between the Grant Tables . . . . . . . . . . . . . . . . . . . . . 276

Managing User Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Granting and Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . 277
Viewing Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Restoring Default Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Working with User Accounts and Passwords . . . . . . . . . . . . . . . . . . . . 282
The Administrator Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

12 Performing Maintenance, Backup, and Recovery . . . . . . . . . . . . . . . 287
Using Database Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

The Error Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
The General Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289

xi i M y S Q L D a t a b a s e U s a g e & A d m i n i s t r a t i o n

The Slow Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
The Binary Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290

Checking and Repairing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Checking Tables for Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Repairing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Optimizing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

Backing Up and Restoring Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Backing Up Databases and Tables . . . . . . . . . . . . . . . . . . . . . . . . 295
Restoring Databases and Tables from Backup . . . . . . . . . . . . . 298

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299

13 Replicating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Understanding Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

The Master-Slave Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Replication Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Replication Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Configuring Master-Slave Replication . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Configuring Master-Master Replication . . . . . . . . . . . . . . . . . . . . . . . . . 308
Managing the Replication Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

Changing Replication Parameters . . . . . . . . . . . . . . . . . . . . . . . . 312
Starting and Stopping Slave Servers . . . . . . . . . . . . . . . . . . . . . . 312
Checking Replication Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Working with Master Server Binary Logs . . . . . . . . . . . . . . . . . 316

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317

A Installing MySQL …

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your paper today and save 30% with the discount code HAPPY

X
Open chat
1
You can contact our live agent via WhatsApp! Via + 1 323 412 5597

Feel free to ask questions, clarifications, or discounts available when placing an order.

Order your essay today and save 30% with the discount code HAPPY