46,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Serie: Bible
- Sprache: Englisch
With special focus on the next major release of MySQL, this resource provides a solid framework for anyone new to MySQL or transitioning from another database platform, as well as experience MySQL administrators. The high-profile author duo provides essential coverage of the fundamentals of MySQL database management--including MySQL's unique approach to basic database features and functions--as well as coverage of SQL queries, data and index types, stores procedure and functions, triggers and views, and transactions. They also present comprehensive coverage of such topics as MySQL server tuning, managing storage engines, caching, backup and recovery, managing users, index tuning, database and performance monitoring, security, and more.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1190
Veröffentlichungsjahr: 2011
Ähnliche
Table of Contents
Cover
Title
Copyright
Dedication
About the Authors
Credits
Acknowledgments
Sheeri Cabral
Keith Murphy
Introduction
Who Should Read This Book
How This Book Is Organized
What's on the Companion Website
Where To Go From Here
Part I: First Steps with MySQL
Chapter 1: Introduction to MySQL
MySQL Mission—Speed, Reliability, and Ease of Use
The MySQL Community
Summary
Chapter 2: Installing and Upgrading MySQL Server
Before Installation
Installation
Initial Configuration
MySQL Configuration Wizard on Windows
MySQL Post-Install Configuration on Unix
Securing Your System
Windows PATH Variable Configuration
Upgrading mysqld
Troubleshooting
Summary
Chapter 3: Accessing MySQL
Accessing mysqld with Command-Line Tools
GUI Tools
Summary
Part II: Developing with MySQL
Chapter 4: How MySQL Extends and Deviates from SQL
Learning MySQL Language Structure
Understanding MySQL Deviations
Using MySQL Extensions
Summary
Chapter 5: MySQL Data Types
Looking at MySQL Data Types
Character String Types
National Character String Types
Binary Large Object String Types
Numeric Types
Boolean Types
Datetime Types
Interval Types
ENUM and SET Types
Choosing SQL Modes
Using NULL Values
Finding an Optimal Data Type for Existing Data
Summary
Chapter 6: MySQL Index Types
Looking at Keys and Indexes
Using Indexes to Speed Up Lookups
Creating and Dropping Key Constraints
Using FULLTEXT Indexes
Summary
Chapter 7: Stored Routines, Triggers, and Events
Comparing Stored Routines, Triggers, and Events
Using Triggers
Using Stored Routines
Using Cursors
Using Events
Summary
Chapter 8: MySQL Views
Defining Views
Changing a View Definition
Replication and Views
Summary
Chapter 9: Transactions in MySQL
Understanding ACID Compliance
Using Transactional Statements
Using Isolation Levels
Explaining Locking and Deadlocks
Recovering MySQL Transactions
Summary
Part III: Core MySQL Administration
Chapter 10: MySQL Server Tuning
Choosing Optimal Hardware
Tuning the Operating System
Tuning MySQL Server
Summary
Chapter 11: Storage Engines
Understanding Storage Engines
Using Different Storage Engines
Working with Storage Engines
Summary
Chapter 12: Caching with MySQL
Implementing Cache Tables
Working with the Query Cache
Utilizing Memcached
Summary
Chapter 13: Backups and Recovery
Backing Up MySQL
Copying Databases to Another Machine
Recovering from Crashes
Planning for Disasters
Summary
Chapter 14: User Management
Learning about MySQL Users
Managing User Accounts
Resetting the Root Password
Debugging User Account Problems
Summary
Chapter 15: Partitioning
Learning About Partitioning
Partitioning Tables
MERGE Tables
Partitioning with MySQL Cluster
Programmatic Partitioning
Summary
Chapter 16: Logging and Replication
Log Files
Replication
Replication Configurations
Correcting Data Drift
Summary
Chapter 17: Measuring Performance
Benchmarking
Profiling
Capacity Planning
Summary
Part IV: Extending Your Skills
Chapter 18: Query Analysis and Index Tuning
Using EXPLAIN
EXPLAIN on Non-SELECT Statements
Other Query Analysis Tools
Optimizing Queries
Summary
Chapter 19: Monitoring Your Systems
Deciding What to Monitor
Examining Open Source Monitoring
Examining Commercial Monitoring
Summary
Chapter 20: Securing MySQL
Access Control Lists
Accessing the Operating System
Securing Backups and Logs
Data Security
Creating Security Policies
Summary
Chapter 21: The MySQL Data Dictionary
Object Catalog
System Information
Displaying Permissions
Storage Engine-Specific Metadata
Custom Metadata
Summary
Chapter 22: Scaling and High Availability Architectures
Replication
SAN
DRBD
MySQL Proxy
Linux-HA Heartbeat
MySQL Cluster
Connection Pooling
memcached
Summary
Appendix A: MySQL Proxy
Understanding MySQL Proxy
Learning Lua Basics
Changing the Query Backend
Changing and Injecting Queries
Understanding MySQL Proxy Internals
Summary
Appendix B: Functions and Operators
Using Aggregation Functions
Using Bitwise Operators
Compressing and Encrypting Data
Testing, Logic, and Control Flow
Using Server-Level Functions
Working with Data Types
Appendix C: Resources
Finding Paid Support
Asking Questions
Sharing Solutions
Getting Documentation
Learning More
Index
Advertisement
End User License Agreement
List of Tables
Chapter 1: Introduction to MySQL
Table 1.1: MySQL Server Timeline
Chapter 2: Installing and Upgrading MySQL Server
Table 2.1: MySQL RPM Packages
Table 2.2: MySQL Server Archive Package Directories
Table 2.3: Red Hat System Run Levels
Chapter 3: Accessing MySQL
Table 3.1: Other Common Options for Command-Line Tools
Table 3.2: mysqladmin Command Parameters
Chapter 4: How MySQL Extends and Deviates from SQL
Table 4.1: Translating DESCRIBE into SHOW COLUMNS
Table 4.2: Setting Transaction Modes in MySQL
Table 4.3: Data to Upsert to Store
Table 4.4: SHOW PROFILE Extra Field Information
Chapter 5: MySQL Data Types
Table 5.1: Summary of MySQL Character String Types
Table 5.2: Summary of MySQL Binary Data Types
Table 5.3: Data Sizes and Ranges for Integer Data Types
Table 5.4: Summary of MySQL Numeric Data Types
Table 5.5: Datetime Data Types
Table 5.6: Interval Data Types
Table 5.7: Summary of MySQL ENUM and SET Data Types
Chapter 7: Stored Routines, Triggers, and Events
Table 7.1: Comparison of Stored Routines and UDFs
Table 7.2: Trigger Alias Usage
Chapter 8: MySQL Views
Table 8.1: Addresses Before Any Update
Table 8.2: INCORRECT: Addresses After Updating the View
Table 8.3: CORRECT: Addresses After Updating the Base Table
Chapter 10: MySQL Server Tuning
Table 10.1: RAID Levels
Table 10.2: Overall MySQL Server Options
Table 10.3: MyISAM Configuration Options
Table 10.4: InnoDB Configuration Options
Table 10.5: Falcon Configuration Options
Table 10.6: Maria Configuration Options
Chapter 11: Storage Engines
Table 11.1: MySQL Server Storage Engine Overview
Table 11.2: MyISAM Configuration Options
Table 11.3: Tablespace Configuration Variables
Table 11.4: InnoDB Performance Configuration Variables
Table 11.5: InnoDB Status Sections
Table 11.6: MEMORY Storage Engine Configuration
Table 11.7: Maria Log File Configuration Variables
Table 11.8: Maria Page Cache Configuration Variables
Table 11.9: Maria Table Creation Options
Table 11.10: Maria Miscellaneous Options
Table 11.11: Maria Command-Line Tools
Table 11.12: Miscellaneous Falcon Parameters
Table 11.13: falcon_debug_mask Values
Table 11.14: Performance Optimization Parameters
Table 11.15: Falcon Page Size and Index Length
Table 11.16: Transactional Support Parameters
Table 11.17: Falcon Record Cache Configuration Parameters
Table 11.18: Falcon Serial Log Parameters
Table 11.19: PBXT Cache Configuration Variables
Table 11.20: Transaction Log Variables
Table 11.21: Data Log Variables
Table 11.22: File Growth Variables
Chapter 12: Caching with MySQL
Table 12.1: Query Cache System Variables
Table 12.2: Query Cache Status Variables
Table 12.3: memcached Command-Line Options
Table 12.4: memcached Programming API Libraries
Chapter 13: Backups and Recovery
Table 13.1: Sample Grandfather-Father-Son Backups Available as of June 15, 2009
Table 13.2: Backup Tools Overview
Table 13.3: Frequently Used Options for mysqldump
Table 13.4: Frequently Used mysqlhotcopy Options
Table 13.5: ibbackup Options
Table 13.6: mysqlbinlog Options
Chapter 14: User Management
Table 14.1: MySQL User Privileges
Chapter 16: Logging and Replication
Table 16.1: Frequently used mysqlbinlog Options
Table 16.2: Server Variables Used to Manage General and Slow Query Logs
Table 16.3: SHOW SLAVE STATUS Field Descriptions
Chapter 17: Measuring Performance
Table 17.1: mysqlslap Options
Table 17.2: SysBench Common Options
Table 17.3: CPU Test Mode Options
Table 17.4: I/O Test Mode Options
Table 17.5: File Tests
Table 17.6: mutext Test Mode Options
Table 17.7: memory Test Mode Options
Table 17.8: thread Test Mode Options
Table 17.9: OLTP Test Mode Options
Table 17.10: Relationships between Variables in Performance Metrics and SHOW GLOBAL STATUS
Table 17.11: Available Options For mysqlreport
Table 17.12: mk-query-profiler Options
Table 17.13: mysqldumpslow Options
Chapter 18: Query Analysis and Index Tuning
Table 18.1: Values for
type
Table 18.2: EXPLAIN Plan Extra Values
Table 18.3: Subquery Values for select_type
Table 18.4: Common Ways to Optimize by Eliminating Functions
Chapter 20: Securing MySQL
Table 20.1: Use of Wildcards in Host Strings
Table 20.2: Privilege and Privilege Levels
Table 20.3: Sample Permissions Based on User Role
Table 20.4: Connectivity Options
Chapter 21: The MySQL Data Dictionary
Table 21.1: Values and Meanings of COMMAND in the PROCESSLIST System View
Table 21.2: Values and Meanings of STATE in the PROCESSLIST System View
Table 21.3: ST_FIELD_INFO Structure
Table 21.4: g++ Compile Flags for Compiling a Plugin as a Dynamic Shared Object
Appendix A: MySQL Proxy
Table A.1: Simple Round-Robin Behavior of mysql-proxy with Multiple Backends
Table A.2: More Complex Round-robin Behavior of
mysql-proxy
with Multiple Backends
Table A.3: Basic Proxy Script Behavior
Table A.4: Sample Tokenized Query
Table A.5: MySQL Proxy Constants
Table A.6: MySQL Proxy Internal Structures
Table A.7: MySQL Proxy Tokens
Table A.8: MySQL Proxy SQL Keyword Tokens
Appendix B: Functions and Operators
Table B.1: Aggregation Functions
Table B.2: Bit Functions
Table B.3: Bit Operator Precedence
Table B.4: Compression Functions
Table B.5: Encryption Functions
Table B.6: Comparison Functions
Table B.7: Control Flow Functions
Table B.8: Logical Functions
Table B.9: Server Functions
Table B.10: Locking Functions
Table B.11: Numeric Functions and Operators
Table B.12: DATE_FORMAT() Specifiers
Table B.13: Simple INTERVAL Units
Table B.14: Composite INTERVAL Units
Table B.15: Date and Time Functions
Table B.16: String Functions
Table B.17: Coercibility values
Table B.18: Data Types for the CAST() and CONVERT() Functions
Table B.19: Type Conversion Functions
Table B.20: XML Functions
List of Illustrations
Chapter 2: Installing and Upgrading MySQL Server
Figure 2.1
Figure 2.2
Chapter 3: Accessing MySQL
Figure 3.1
Figure 3.2
Figure 3.3
Figure 3.4
Figure 3.5
Figure 3.6
Figure 3.7
Figure 3.8
Figure 3.9
Figure 3.10
Figure 3.11
Figure 3.12
Figure 3.13
Figure 3.14
Figure 3.15
Figure 3.16
Figure 3.17
Chapter 9: Transactions in MySQL
Figure 9.1
Chapter 10: MySQL Server Tuning
Figure 10.1
Chapter 11: Storage Engines
Figure 11.1
Figure 11.2
Chapter 12: Caching with MySQL
Figure 12.1
Chapter 13: Backups and Recovery
Figure 13.1
Chapter 15: Partitioning
Figure 15.1 shows the final layout of the subpartitions on the hard drives.
Chapter 16: Logging and Replication
Figure 16.1
Figure 16.2
Figure 16.3
Figure 16.4
Figure 16.5
Chapter 22: Scaling and High Availability Architectures
Figure 22.1
Figure 22.2
Figure 22.3
Figure 22.4
Figure 22.5
Figure 22.6
Figure 22.7
Figure 22.8
Figure 22.9
Guide
Cover
Table of Contents
Begin Reading
Pages
C1
iii
iv
v
vii
ix
xi
xii
xiii
xxvii
xxviii
xxix
1
3
4
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
347
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
MySQL® Administrator’s Bible
Sheeri Cabral
Keith Murphy
MySQL® Administrator's Bible
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-41691-4
Library of Congress Cataloging-in-Publication Data:
Cabral, Sheeri, 1978-
MySQL administrator's bible / Sheeri Cabral, Keith Murphy.
p. cm.
Includes index.
ISBN 978-0-470-41691-4 (paper/website)
1. MySQL (Electronic resource) 2. Database management. 3. Relational databases. I. Cabral, Sheeri, 1970- II. Title.
QA76.9.D3C3178 2009
005.75′65—dc22
2009005633
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. MySQL is a registered trademark of MySQL AB Company. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
To Mitch Cherniack, who taught me databases and inspired my love of them. In memory of Zvi Kramer and Yehoshua Kritzer—may I be able to echo their strength and spirit.—Sheeri Cabral
For Rachel, the love of my life.—Keith Murphy
About the Authors
Sheeri K. Cabral is a Database Team Lead at the Pythian Group, a remote database administration solution. She has a master's degree in computer science specializing in databases from Brandeis University and a career background in systems administration. Unstoppable as a volunteer and activist since she was 14, Sheeri founded and organizes the Boston MySQL User Group. She also produces OurSQL: The MySQL Database Podcast for the Community, By the Community and many workshop videos. These resources are freely available at www.technocation.org, the website of Technocation, Inc. Sheeri is the Treasurer of Technocation, Inc, a not-for-profit organization providing resources and educational grants for IT professionals. Sheeri currently lives in the Boston area with her husband Tony Cabral and a myriad assortment of Muppets™. When she's not working or volunteering, she reads, knits, listens to podcasts, and enjoys time with her nieces and nephews.
Keith Murphy is a MySQL database administrator who has been using MySQL server since 1998. A long-time Linux system administrator, he worked with a number of Internet companies solving systems issues. More recently he formed Paragon Consulting Services (http://www.paragon-cs.com) to provide consulting services for companies seeking training for MySQL and help with MySQL solutions ranging from everyday database administration tasks to utilizing “cloud” computing services, performance tuning, and scaling. In addition to information about the company, Keith blogs at http://blog.paragon-cs.com, which he updates as frequently as possible with tricks, tips, and general information about MySQL server.
In addition he is the editor of MySQL Magazine (http://www.mysqlzine.net), the MySQL community's digital magazine about all things MySQL. It is his pleasure to work with some of the finest people in the community in this collaborative effort to increase both the amount and quality of the available information on MySQL server.
Readers are invited to contact Keith by email at [email protected].
Credits
Executive Editor
Robert Elliott
Development Editor
Ed Connor
Technical Editors
Baron Schwartz
Morgan Tocker
Nicklas Westerlund
Production Editor
Liz Britten
Copy Editors
Kim Cofer
Foxxe Editorial Services
Editorial Manager
Mary Beth Wakefield
Production Manager
Tim Tate
Vice President and Executive Group Publisher
Richard Swadley
Vice President and Executive Publisher
Barry Pruett
Associate Publisher
Jim Minatel
Project Coordinator, Cover
Lynsey Stanford
Proofreader
Jen Larsen, Word One New York
Indexer
Jack Lewis
Acknowledgments
Sheeri Cabral
When I was four years old, I refused to let anyone else tie my shoes, because I wanted to do it myself. There was only one problem—I did not know how to tie my shoes yet. I went around for months with untied shoes because I made it very clear that I was going to do it. Eventually I learned to tie my shoes. I also learned that I could do anything I put my mind to.
I could not have accomplished the herculean task of writing a book had I not had the encouragement of my stubbornness. To Mom, Dad, Lymor, Noam, Josh, and Elinor, thanks for putting up with me all these years, letting me try my way so that my failures and successes were my own, and teaching me to always do a good job so I can be proud of my work. My love of databases was inspired by the man who taught me the subject at Brandeis University, Professor Mitch Cherniack. Professionally, I would not be the person I am today without the guidance and example of Tom Limoncelli, an inspirational role model, great mentor, and good friend; and Tracy Gangwer, who taught me to be a super DBA/sysadmin by confirming and triple-checking everything. A huge thank you goes to Keith for being the lead author on this project, and of course for asking me to become involved. Of course, none of this could have been done without the support of Wiley Publishing, particularly our editors Ed Connor and Bob Elliott.
I used many two-dimensional resources while writing this book, but there's a limit to how much information is in manuals and books. Special thanks are due to Brian Aker for being a constant fountain of readily available knowledge and Martin Garden for software assistance. Jay Pipes provided permission for material used in the Query Analysis chapter, Roland Bouman helped me get a firm grip on the data dictionary, and Mark Atwood answered my questions about how storage engines work. I was encouraged and supported by Carsten Turner, Ronald Bradford, Patrick Galbraith, and many fans of the OurSQL Podcast who wrote to me asking when it was coming back.
Keith Murphy
There are many people involved in any book project. Without the crew of people from Wiley we could never have finished this book. Thank you, Bob Elliott (our Executive Editor) for taking a chance on me. I hope you weren't disappointed. Without our Development Editor, Ed Connor, I suspect we would have ended up in the ditch—never to get out. And thanks to all the other people at Wiley who I didn't have the chance to meet who help make this happen.
Sheeri, I could not have done this alone. I am grateful for all the long hours and the meticulous attention to detail that you brought to the project. If I ever get the notion to write another book you will be the first person I call. Of course you don't have to answer!
And a big thanks for all those in the MySQL community. It is you who help power this crazy thing we call Open Source. Thank you for taking the time help others learn and become better.
Introduction
Welcome to the MySQL Administrator's Bible. Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical application information, as well as reference and background information that provide a context for what you are learning. This book is a comprehensive resource on MySQL database administration. It covers almost every conceivable topic of database administration from the very basics, including server installation and user management, to some fairly complex topics such as security and high availability. By the time you have completed the MySQL Administrator's Bible you will be well-prepared to perform as a database administrator.
This book fully covers MySQL 5.1 plus early features of the 6.0 release (as of the time of writing). All of the examples work in both MySQL 5.1 and MySQL 6.0, except where noted.
Who Should Read This Book
This book is designed for two groups of people. The first group of people is those who have never worked on a database server and want to learn the fundamentals of database management using the world's number one open source database server. The second group of people is those who are currently using another database server and are looking to make a transition to MySQL server. They may be experienced database administrators, but are not experienced with MySQL.
How This Book Is Organized
The MySQL Administrator's Bible is divided into four parts.
Part I makes no assumptions on the knowledge level of the reader. Chapter 1 is a brief introduction to MySQL's history. After this introduction Part I covers:
MySQL server installation (Chapter 2)
Accessing MySQL server (Chapter 3)
Part II covers how MySQL compares to standards and MySQL's SQL syntax:
How MySQL extends and deviates from standard SQL (Chapter 4)
MySQL data types (Chapter 5)
MySQL index types (Chapter 6)
Stored routines, triggers and events (Chapter 7)
MySQL Views (Chapter 8)
Transactions in MySQL (Chapter 9)
Part III is all about MySQL administration:
Tuning the server (Chapter 10)
MySQL storage engines (Chapter 11)
Caching with MySQL (Chapter 12)
Backups, recovery and disaster planning (Chapter 13)
Managing MySQL users (Chapter 14)
Partitioning in MySQL server (Chapter 15)
Logging and replication (Chapter 16)
Measuring performance (Chapter 17)
Part IV highlights intermediate topics ranging from further administrative tasks to performance tuning:
Query analysis and index tuning (Chapter 18)
Monitoring MySQL servers (Chapter 19)
Securing MySQL (Chapter 20)
The MySQL data dictionary (Chapter 21)
Scaling MySQL server and high availability (Chapter 22)
In addition to the two parts there are three appendixes of additional material:
Using the MySQL Proxy (Appendix A)
MySQL Functions reference (Appendix B)
Additional resources for MySQL (Appendix C)
Conventions and Features
There are many different organizational and typographical features throughout this book designed to help you get the most of the information.
Tips, Notes, and Cautions
Whenever the authors want to bring something important to your attention the information will appear in a Tip, Note, Caution or On the Website.
Caution
This information is important and is set off in a separate paragraph with a special icon. Cautions provide information about things to watch out for, whether simply inconvenient or potentially hazardous to your data or systems.
Tip
Tips generally are used to provide information that can make your work easier—special shortcuts or methods for doing something easier than the norm.
Note
Notes provide additional, ancillary information that is helpful, but somewhat outside of the current presentation of information.
ON the WEBSITE
On the Website points the reader to the companion website (www.wiley.com/go/mysqladminbible). The website contains new material and up to-date information about evolving MySQL features.
What's on the Companion Website
On the companion website (www.wiley.com/go/mysqladminbible), you will find the following:
Sample code—each chapter has its own subfolder on the website and you'll find all the code output that was discussed in each chapter organized accordingly.
Examples that work in both MySQL 5.1 and MySQL 6.0 (as released at the time of writing), except where noted.
Extra material such as a new SQL tutorial, information on spatial data and indexes, and up-to-date information on the new features in MySQL 6.0.
Errata updates.
Where To Go From Here
When you finish reading MySQL Administrator's Bible you will have a solid foundation for working as a MySQL database administrator. While it takes experience to produce a quality database administrator, knowledge and understanding of foundational principals is critical. Our goal in this book is to provide those two parts of the equation.
You will definitely want to check out the companion website (www.wiley.com/go/mysqladminbible). MySQL 6.0 is still new technology at the time of publication of this book and it will continue to evolve for some time. The world of MySQL changes quickly and the information in the book is up-to-date at time of publication. However, as more changes occur, we will write about the latest changes on the website.
In addition there are many other resources available as you grow in your knowledge of MySQL server. Many of these resources are listed in Appendix C of the book but we would point out in particular Planet MySQL (http://www.planetmysql.org), a blog aggregation site, and MySQL Magazine (http://www.mysqlzine.net), a free digital magazine with which the co-authors are involved.
Part IFirst Steps with MySQL
In this Part
Chapter 1
Introduction to MySQL
Chapter 2
Installing and Upgrading MySQL Server
Chapter 3
Accessing MySQL
Chapter 1Introduction to MySQL
In This Chapter
Learning MySQL's historyFinding the MySQL communityContributing to MySQLEveryone who has been involved with IT for more than a few months has at least heard of MySQL. The acquisition of MySQL AB by Sun Microsystems brought a great deal of additional attention to MySQL's database management system (DBMS). Even so, there is often more to MySQL than many people realize. They simply do not realize the full capabilities of MySQL.
Recent versions of MySQL have brought a large feature set that covers just about every imaginable need. This includes partitions, scheduled events, prepared statements, triggers, and views. MySQL has long been used in dynamic websites and applications. Whether you program in Perl, PHP, ASP, .NET or Ruby you can integrate MySQL into your environment.
MySQL Mission—Speed, Reliability, and Ease of Use
The driving force behind MySQL has been to provide a reliable, high-performance server that is easy to set up and use. These qualities are why many Internet companies in the late 1990s chose MySQL to power their websites. These same qualities are why MySQL is making strong inroads into the internal database servers of Fortune 1000 companies that have traditionally used commercial databases. MySQL did not corner the existing database market; instead, MySQL allowed the database market to expand and grow to include people all over the world. MySQL created a niche by creating the opportunity for almost anyone to be able use a database. Had there not been an easy-to-use, fast, reliable database server such as MySQL, there would be a dearth of the user-provided content and collaborative efforts that are what we expect from the Internet. Without MySQL, there would be far fewer articles, podcasts, online ’zines, tutorials, photos, forums, videos, collaborative reference material, and search engines. The World (Wide Web) as we know it would be completely different.
MySQL is not the only free database management system; it also is not the only open source database management system. One of the largest differences is the user friendliness that pervades MySQL. The friendliness, starting with the cost—free unless embedded in another product—shines through the quick installation and setup, and pleases the new database user with SQL language extensions that are nearly intuitive. For example, the SHOW DATABASES command shows a list of databases you have permission to see.
Experienced database administrators can install, configure, and bring a MySQL server online in less than fifteen minutes. If the installation process is packaged it can be done in five minutes. We look at the installation process in more detail in Chapter 2.
The reliability of MySQL played a role in MySQL's rise to become, as stated at www.mysql.com, “the world's most popular open source database.” In addition to this reliability, MySQL operates on a wide range of hardware and operating systems from laptops to multi-core servers. Though benchmark comparisons can be slanted to benefit one server or another, the various benchmarks available show that MySQL competes with and frequently beats competing database servers.
Company Background
MySQL server has been downloaded more than 100 million times. MySQL is open source software. An admittedly simple definition of open source software is software that is freely available (including source code) with free redistribution. Source code is the source of a program—the file(s) containing the original programming language code, which can be read, discussed, and learned from, just as the words of a book can. The roots of MySQL server are found in a database system called Unireg that was developed by Michael “Monty” Widenius for a Swedish company called TcX during the 1980s. The initial release of MySQL server was created in 1995 when Monty added an SQL interface to Unireg. Shortly after, David Axmark recommended MySQL server be released under a dual licensing model, where it would be available for widespread free use but could also be used in situations that require a more restrictive licensing use (such as in embedded devices). David and Monty, together with Allan Larsson, founded MySQL AB in 1995. MySQL AB was the company that, until its acquisition by Sun Microsystems in January 2008, provided support and service for the MySQL database in addition to developing most of the code.
In 2001 MySQL began supporting transactions with the integration of the BDB and InnoDB engines. This allowed for safer handling of concurrent write operations, which began the trend of adding features needed by enterprise environments.
Over the years the feature set of the MySQL server has grown to cover almost any feature needed in an enterprise database server. Some might even argue that it has too many features! Innovation has continued; within months of readily accessible cloud computing environments such as Amazon Web Services (http://aws.amzon.com), hobbyists and companies alike are deploying and using MySQL in creative environments.
Table 1.1 shows an overview of MySQL server's release history, including important pre-release (Alpha and Beta) unstable versions as well as public stable releases that were determined to be generally available (GA). In Table 1.1, only the first release notes the new features, though all features mentioned in the pre-release Beta version were also released in the GA version.
Table 1.1: MySQL Server Timeline
Community and Enterprise Server Versions
In August of 2007 MySQL AB began offering the MySQL server in two different versions: MySQL Community and MySQL Enterprise. Although the code base of the two servers is similar, there are different levels of support for the servers. With MySQL Enterprise you purchase one of four support packages in addition to receiving the MySQL server binaries and source code. Sun has a table of available options at www.mysql.com/products/enterprise/features.html.
MySQL Community offers freely downloadable binaries with no support guarantee. When a database experiences problems the user is on his or her own to resolve those problems.
MySQL Enterprise has more frequent releases than MySQL Community Server. Owners of the source code must be allowed to redistribute the source code in full—this is required under the provisions of the GPLv2 license that governs MySQL distributions.
The MySQL Community
The MySQL community is a diverse population of both users of and contributors to MySQL, spread across the world. The large user community brings many opinions about what features are needed and discussions about the best methods to implement those features. Though this can sometimes bring about somewhat heated discussions, it also allows for honest and direct feedback that many companies may not hear.
How to Contribute
You can contribute to MySQL server in a number of ways:
Blogging:
Many people who work on MySQL either full or part time write about their experiences online. These blogs can be valuable for learning more about the ins and outs of working with MySQL. Many of these blogs are aggregated at the Planet MySQL website (
www.planetmysql.org
).
Mailing Lists:
The official public MySQL mailing lists are available at
http://lists.mysql.com
. The mailing lists are a great way to both contribute your knowledge and learn something new. The General Discussion list, in particular, has a wide range of topics. Dive in and take a look.
IRC Channels:
Various IRC channels are available. These can often provide instant feedback and help with problems. One of the most popular is the
#mysql
channel on the Freenode IRC network (
irc.freenode.net
, or visit
http://freenode.net
for more information).
User Groups:
Many active user groups exist around the world. It is a great experience to get together with other people who share a similar passion. Each month the user groups will have someone present on a topic relating to MySQL. One month it might be backups and recovery. The next month it might be about how to scale an application effectively. The place to look for a user group near you is
www.meetup.com
. If there is not a user group near you, consider starting one!
Documentation:
MySQL has some of the best software documentation available, located at
http://dev.mysql.com/doc
. Working on the documentation is the easiest way to directly contribute to the MySQL project. As MySQL continues to change it is necessary to keep the documentation up to date, so this is always an ongoing project.
Code:
The MySQL server code is complex software. However, it is somewhat modular and there are areas where an experienced C or C++ coder can help out. For smaller projects you might want to investigate the MySQL forge (
http://forge.mysql.com
) where many projects find a home. These projects are in many coding languages including (among others) Perl, Python, PHP, bash, C, and even Ruby.
MySQL Magazine:
Do you like to write? Then MySQL Magazine (
www.mysqlzine.net
) might be your venue. Articles cover anything from coding to how-to articles on normal DBA activities. The audience is a large group of the best database administrators and developers in the world.
Reasons to Contribute
Many people never do anything other than download MySQL server, read a tutorial or two, and install the software and use it. They never contribute in any manner. That is fine, but we think that you can have a far more positive experience by becoming part of the community and contributing what you can while learning about this fascinating server software.
Though it is beyond the scope of this book to discuss the open source software philosophy in depth, openly and freely giving and receiving knowledge is the basis of this philosophy. Both code and knowledge are free to be shared. By doing so, everyone benefits from the exchange.
Summary
MySQL server has a long history of innovation. The community that has grown around MySQL provides learning experiences and teaching opportunities for database administrators old and young, veteran and newbie alike.
This chapter covered:
MySQL company history
The original and continuing mission of the MySQL database
MySQL server version and feature overview
Where to find more information
How you can contribute to MySQL server
The MySQL Administrator's Bible covers all the topics you need to understand as a beginning MySQL database administrator. If you are a more advanced administrator switching from another database system, this book offers a unique look at where MySQL is different from the ISO SQL:2003 standard. After advancing to an intermediate level of familiarity with MySQL, the material on scaling and high availability, replication, metadata, and server tuning will continue to provide guidance.
Chapter 2Installing and Upgrading MySQL Server
In This Chapter
Pre-installationInstallationInitial ConfigurationUpgradingTroubleshooting InstallationThe MySQL server has two installation formats, and the MySQL server itself runs on more than sixteen different operating system platforms. The top three operating system platforms for MySQL are covered in some depth: GNU/Linux, Microsoft Windows, and Sun Solaris.
Tip
For information on how to install the MySQL server on Mac OS X, see the MySQL manual page at http://dev.mysql.com/doc/refman/6.0/en/mac-os-x-installation.html.
This chapter takes you through the bare bones of what you need to get the MySQL server (also known as mysqld) up and running, including the initial configuration. For more complete configuration tuning, see Chapter 10. It then delves into upgrading mysqld, which is very similar to installation. The chapter ends with troubleshooting assistance.
Before Installation
Before installing, you must choose what to install. You have two basic installation formats to choose from when performing an installation—source code installation and binary file installation. A source code installation means that you download the actual source code and then compile the code on your server in order to actually install the software. With a binary installation the server software is precompiled and ready to install. Think of it as the difference between preparing a meal by combining ingredients and cooking them and getting takeout from the local Chinese restaurant. A binary installation is like getting takeout: when you receive the food it is already pre-cooked and ready for eating.
Source Code vs. Binary File
A binary file is a file that contains binary data. It is not readable by humans, but a machine can read binary data very efficiently. Binary data is more compact than regular text, and many programs are run in binary format.
A binary file does not start out that way, though. A software developer writes some code in a text file, and then generates a binary file from that text file. The code is called “source code” because it is the source of the binary file. Generating the binary file is called compiling.
To compile source code you need a special program called a compiler. If you are new to compiling files on your operating system, it is best to download the binary files and use them. Compiling MySQL from source code is an advanced technique not covered by this book.
When you cook your own food it takes longer than picking up the telephone and placing an order with a restaurant. However, you can make the food exactly as you want. The difference between source and binary installs is the same. With source code installations it will take longer and require more expertise. However, the benefit is that you can control every configuration option for the compilation process. With the binary installation you can be up and running much faster, but those compilation choices are made by someone else. This is not always optimal for your situation.
Most installations of MySQL Server are binary installations. This choice is made because it is often not worth the extra work that source code installation requires. Compiling a MySQL Server binary from source code does not necessarily result in a better, faster database. Upgrading takes more work, because compiling takes time and expertise, and you have to document how and why you compiled the binary a certain way.
If a database administrator chooses to use binary format installations there is another decision: whether to use the official binary from Sun Microsystems or a binary from the operating system vendor. Many operating systems, such as Debian and Red Hat, provide their own version of a binary for MySQL Server. Some of these packages consist of a few wrapper scripts around the Sun-provided package, whereas other packages are made from taking the source code, revising it, and compiling it. Because of this, we recommend using the binaries available for download from http://dev.mysql.com/downloads/mysql, unless you are specifically using a patched binary for the feature(s) it has.
Problems with Vendor-Supplied Packages
Many vendors supply MySQL packages. Some vendors simply place the official packages into their repositories. Others repackage the official binaries, and still others start with the official source code, make changes, and compile and produce their own, unique package.
Debian packages have many extras; in the version of MySQL packaged by Debian, the my.cnf file is placed in /etc/mysql/my.cnf, and a debian-sys-maint@localhost user is created with a password that is stored in /etc/mysql/debian.cnf. These types of enhancements can make administering databases on different operating systems difficult.
The documentation for specific MySQL Server versions is based on the complete source code. Some vendor-supplied packages may not include all the features and bug fixes, because the packaging process does not necessarily include all the documented changes. This can lead to behavior that is inconsistent with the expected behavior.
As an example, in May 2008, a serious bug was revealed in the Debian package for OpenSSL. The package, built in 2006, had been built without code that caused some security tools to generate warnings. This led to the side-effect that the ssl keys generated by the package could be easily compromised. This was a serious security flaw that was in the Debian package for two years without being discovered.
Though Debian was used as an example, any vendor supplying packages may be changing the expected behavior—including Red Hat, Solaris, BSD, and others. Therefore, we recommend using the official MySQL downloads from http://dev.mysql.com to ensure that you get the features you expect.
In addition, third-party packages are often out of date. If you are installing third-party packages, these instructions may not work; consult the third-party documentation. To ensure you have an official Sun package, download directly from http://dev.mysql.com/downloads.
Choosing the MySQL Version
Sun Microsystems has four release levels for the MySQL Server. These levels are GA (General Availability), RC (Release Candidate), beta, and alpha.
Software that is considered to be ready for production is labeled GA. The qualification for GA is that it has very few known bugs that cause severe production issues. It does not mean that the code is bug free. When a version of the server is declared GA it is typically quite solid.
A release candidate is high enough quality that Sun thinks it is a possibility for GA. There might be some serious, severe, or critical bugs left in the code, but they do not affect all users, just those using features in an obscure way, or using a few rare features.
Beta software is a step below a release candidate in terms of quality. Known issues (documented at http://bugs.mysql.com) exist with the server. Beta software should not be used in production. As a database administrator, however, it can be prudent to test the beta software and follow its development. It is a good idea to be familiar with the features and bug fixes in beta software, because after even more enhancements and corrections the beta software will become a release candidate and then GA.
Alpha software is where new features are added, and thus is very much experimental. Do not run alpha-level software in production. It is strictly a test bed of server features.
32-bit vs. 64-bit Systems
The official download page offers software for 32-bit systems and 64-bit systems. We recommend using a 64-bit operating system and a 64-bit MySQL installation. 32-bit systems can only use about 2.4 Gb of RAM per process, which means that the MySQL Server daemon will not be able to use more than 2.4 Gb of RAM. Most machines can handle a 64-bit operating system.
MySQL Support
Several types of support are available for MySQL Server. Many resources are freely available on the Internet including mailing lists, forums, online reference manuals from Sun/MySQL, and many websites with information. See Appendix C, “Resources,” for more information. Companies exist that provide one-time or ongoing training and consulting services for MySQL. Sun also provides support through its paid Enterprise program.
Downloads
The official download location is at http://dev.mysql.com/downloads. Here you will find current and past versions of MySQL Community server. In addition, there is information about the Enterprise version of the server. The Enterprise binary is a part of the MySQL Enterprise program, which includes monitoring and support software in addition to the database server. It is not necessary to purchase MySQL Enterprise to have a fully functioning, production-quality database. However, many useful tools such as the MySQL Monitor and the Query Analyzer can help DBAs do their job better in less time. MySQL Enterprise also comes with support, which is also a useful resource.
Installation
GNU/Linux platforms have several installation methods. There are distribution-specific packages (rpm, deb, tar.gz, and so on). Distribution-specific packages can be downloaded from the official download site listed in the previous section. As another installation method, some operating systems offer MySQL packages; this is not recommended for reasons discussed in the “Problems with Vendor-Supplied Packages” note.
The third installation method is using an official compressed archive. We cover all three installation methods in detail with an example of an rpm package installation on a CentOS distribution, an example of a pkg package installation on a Solaris distribution, and an example using the compressed archive.
On Windows are two installation packages that have wizards to assist with installation and configuration. There is also a compressed archive package available that has no wizards. We cover all three installation methods on Windows.
There is another option for installation: downloading the source code and compiling your own binary, which this book does not cover.
Regardless of operating system, mysqld is initialized with users who have insecure permissions. Make sure to refer to the installation instructions for your operating system as well as the post-install instructions to secure your database.
MySQL Server Installations on Unix
Unix-based servers with MySQL installations represent the majority of current installations of MySQL Server. Unlike Windows, Unix-based servers come in a number of packaging formats and configurations. We discuss three major packages: rpm-based packaging, the Solaris pkg package, and the binary archive package.
Installing from an rpm file on GNU/Linux
In most cases, you only need to install the server and client rpm packages (MySQL-server and MySQL-client) to get a functional MySQL installation. Other packages (see Table 2.1) may or may not be required for a standard installation but may have useful libraries. For example, you may get an error such as the following:
Error: removing these packages would break dependencies libmysqlclient.so.10 is needed by…
Installing the mysql-shared-compat package might fix this problem. The mysql-shared-compat package includes shared libraries for backward compatibility (libmysqlclient.so.12 for MySQL Server 4.0 and libmysqlclient.so.10 for MySQL Server 3.23).
Tip
Document which packages your environment needs, and why, to make upgrading and testing easier.
On Unix systems, the MySQL server binary is a file called mysqld. The recommended way to install mysqld on rpm-based GNU/Linux distributions is by using the rpm packages provided on the official download page at http://dev.mysql.com/downloads. These rpms should work on all versions of GNU/Linux that support rpm packages and use the glibc2.3 library. There are platform-specific and generic rpms; the difference is that a platform-specific rpm dynamically links to libraries found on a platform and a generic rpm is linked statically with LinuxThreads.
Whether to use a dynamically linked rpm or a statically linked rpm is up to your organization's policies and your own personal preference. Using dynamically linked libraries means that when the libraries are upgraded, the database automatically uses the libraries. However, this also means that an upgrade may change or break the way mysqld works. Using a statically linked library takes away this danger; however, if the library code requires upgrading, you have to upgrade the mysqld package, not just the library code itself.
The available rpm packages are shown in Table 2.1. The source code package has the form of MySQL-VERSION.platform.src.rpm, such as MySQL-community-5.1.25-0.rhel4.src.rpm. The other packages have a name as shown in Table 2.1, and a suffix with the version, platform (such as Red Hat Enterprise version 4), and the name of the dynamically linked library (such as glibc2.3). Finally, it will have an abbreviation for the processor type for which the rpm was compiled and packaged. An example package filename is MySQL-server-community-6.0.8-0.rhel4.x86_64.rpm.
Table 2.1: MySQL RPM Packages
Package Name
Description
MySQL-client
MySQL client package including the
mysql
command-line tool.
MySQL-debuginfo
Used for debugging problems with both the client and server programs. Can be used to generate extra information with MySQL Server crashes.
MySQL-devel
The libraries needed to compile additional MySQL clients.
MySQL-embedded
The MySQL embedded server. You only need this package if you are creating an application that has MySQL embedded in it.
MySQL-ndb-management
Files used by the MySQL Cluster server.
MySQL-server
The MySQL Server files, including the
mysqld
binary. This is required to run a MySQL Server.
MySQL-shared
Shared libraries used by various applications and languages to communicate with MySQL.
MySQL-shared-compat
This package is a replacement for MySQL-shared if your application requires libraries from older versions of MySQL but you need to upgrade
mysqld
to a newer version.
MySQL-test
The MySQL test suite.
MySQL-VERSION.PLATFORM.src.rpm
Source code for all the packages.
Executing rpm -qa will list all rpm files installed on your system. To see if you currently have any mysql packages installed:
shell> rpm -qa | grep -i mysql
MySQL-server-6.0.8-0.glibc23
MySQL-shared-6.0.8-0.glibc23
MySQL-client-6.0.8-0.glibc23
perl-DateTime-Format-MySQL-0.04-1.el5.rf
MySQL-devel-6.0.8-0.glibc23
perl-DBD-MySQL-3.0007-1.fc6
In this example, four MySQL packages and two Perl libraries are installed. The Perl libraries are third-party packages for being able to connect Perl with MySQL, and are not actually a part of a MySQL installation. If you see existing MySQL installations on your system, refer to the “Upgrading mysqld” section.
