Take your MySQL skills to the top tier
Maximize every powerful feature available in MySQL 5.1 with hands-on instruction from a MySQL expert. This definitive guide shows you how to use MySQL's advanced suite of data management tools, optimize performance and reliability, and secure and administer a robust RDBMS. MySQL Database Usage & Administration includes detailed code examples in each chapter to highlight real-world applications of the material covered. If you want to get the most out of MySQL, you need this practical handbook.
- Understand MySQL's features, technical architecture, subsystems, and commands
- Make database design decisions that optimizre performance, storage, and reliability
- Write complex queries using joins, subqueries, and views
- Group SQL statements into transactions and execute them atomically
- Build and use sophisticated stored procedures and functions
- Automate database operations with triggers and scheduled events
- Import and export data in different formats, includiong SQL, CSV, and XML
- Optimize server and query performance
- Administer a secure, high availability MySQL RDBMS
- Manage users and control access
- Perform database maintenance, replication, backup, and recovery
Vikram Vaswani is the founder and CEO of Melonfire (www.melonfire.com), a consultancy firm with special expertise in open-source tools and technologies. His previous books include MySQL: The Complete Reference, PHP: A Beginner's Guide , and PHP Programming Solutions .
Author(s): Vikram Vaswani
Series: Advanced skills from the experts
Edition: 1
Publisher: McGraw-Hill
Year: 2010
Language: English
Pages: 369
City: New York
Contents......Page 8
Foreword......Page 14
Acknowledgments......Page 16
Introduction......Page 18
Part I: Usage......Page 22
1 An Introduction to MySQL......Page 24
History......Page 25
Unique Features......Page 26
Product Family......Page 29
Technical Architecture......Page 31
Applications......Page 35
Summary......Page 37
2 Understanding Basic Commands......Page 40
Understanding Basic Concepts......Page 41
Working with Databases and Tables......Page 46
Working with Records......Page 54
Viewing Database, Table, and Field Information......Page 68
Summary......Page 69
3 Making Design Decisions......Page 70
Selecting Field Data Types......Page 71
Selecting Table Storage Engines......Page 74
Using Primary and Foreign Keys......Page 78
Using Indexes......Page 84
Summary......Page 89
4 Using Joins, Subqueries, and Views......Page 90
Using Joins......Page 91
Using Subqueries......Page 104
Using Views......Page 116
Summary......Page 129
5 Using Transactions......Page 130
Understanding Transactions......Page 131
Controlling Transactional Behavior......Page 142
Pseudo-Transactions......Page 147
Summary......Page 152
6 Using Stored Procedures and Functions......Page 154
Understanding Stored Routines......Page 155
Doing More with Stored Routines......Page 169
Summary......Page 187
7 Using Triggers and Scheduled Events......Page 188
Understanding Triggers......Page 189
Understanding Scheduled Events......Page 202
Summary......Page 208
8 Working with Data in Different Formats......Page 210
Importing Records......Page 211
Exporting Records......Page 214
Working with XML Data......Page 217
Summary......Page 232
9 Optimizing Performance......Page 234
Optimizing Queries......Page 235
Optimizing Joins and Subqueries......Page 243
Optimizing Transactional Performance......Page 247
Optimizing Stored Routines......Page 250
Optimizing Table Design......Page 252
Optimizing Server Settings......Page 253
Benchmarking......Page 254
Summary......Page 258
Part II: Administration......Page 260
10 Performing Basic Server Administration......Page 262
Database Administration and MySQL......Page 263
Understanding Basic Server Administration......Page 265
Obtaining Database Meta-Information......Page 277
Summary......Page 281
11 Managing Users and Controlling Access......Page 284
Understanding the Access Control System......Page 285
Managing User Privileges......Page 298
Working with User Accounts and Passwords......Page 303
Summary......Page 306
12 Performing Maintenance, Backup, and Recovery......Page 308
Using Database Log Files......Page 309
Checking and Repairing Tables......Page 313
Backing Up and Restoring Data......Page 316
Summary......Page 320
13 Replicating Data......Page 322
Understanding Replication......Page 323
Configuring Master-Slave Replication......Page 327
Configuring Master-Master Replication......Page 329
Managing the Replication Process......Page 333
Summary......Page 338
A: Installing MySQL and the Sample Database......Page 340
Obtaining MySQL......Page 341
Installing and Configuring MySQL......Page 343
Testing MySQL......Page 350
Performing Post-Installation Steps......Page 351
Setting Up the Example Database......Page 352
Summary......Page 355
B......Page 356
C......Page 357
D......Page 358
F......Page 359
I......Page 360
M......Page 361
P......Page 363
R......Page 364
S......Page 365
T......Page 367
U......Page 368
Z......Page 369
