MySQL Administrator's Bible by Keith Murphy (English) Paperback Book

Description: MySQL Administrator's Bible by Keith Murphy, Sheeri K. Cabral 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. FORMAT Paperback LANGUAGE English CONDITION Brand New Publisher Description 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 MySQLs 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. Back Cover Whether youre a novice or experienced DBA, this book has all you need to learn MySQL MySQL Administrators Bible is designed to provide a solid framework for a beginning MySQL DBA or an experienced DBA transitioning from another database platform. The book starts with the fundamentals of MySQL database management, including MySQLs unique approach to SQL queries, data and index types, stored procedures and functions, triggers and views, and transactions. The authors continue with comprehensive coverage of major MySQL Server components and processes for which DBAs are responsible--these include: MySQL Server tuning Query optimization and index tuning Managing storage engines Caching Authentication and managing users Partitioning; Replication; Logging Database and performance monitoring Security; Backup and Recovery Managing availability and scaling Fully covers MySQL 5.1 Companion Web Site The books Companion Web Site includes a tutorial on foundational SQL programming that all MySQL DBAs need to understand, plus code examples, scripts, and data schemas used in the book. Youll also learn about the latest changes in MySQL and find helpful links. Visit the site at Flap Whether youre a novice or experienced DBA, this book has all you need to learn MySQL MySQL Administrators Bible is designed to provide a solid framework for a beginning MySQL DBA or an experienced DBA transitioning from another database platform. The book starts with the fundamentals of MySQL database management, including MySQLs unique approach to SQL queries, data and index types, stored procedures and functions, triggers and views, and transactions. The authors continue with comprehensive coverage of major MySQL Server components and processes for which DBAs are responsible--these include: MySQL Server tuning Query optimization and index tuning Managing storage engines Caching Authentication and managing users Partitioning; Replication; Logging Database and performance monitoring Security; Backup and Recovery Managing availability and scaling Fully covers MySQL 5.1 Companion Web Site The books Companion Web Site includes a tutorial on foundational SQL programming that all MySQL DBAs need to understand, plus code examples, scripts, and data schemas used in the book. Youll also learn about the latest changes in MySQL and find helpful links. Visit the site at Author Biography Sheeri K. Cabral won the MySQL Community Member of the Year award in 2007 and 2008. She organizes the Boston, Massachusetts, USA, MySQL User Group -- which she founded in November 2005 -- and produces freely available presentation videos as well as OurSQL: The MySQL Database Podcast for the Community, by the Community. You can check out her prolific blog postings at Keith Murphy is a MySQL database administrator who has been using MySQL server since 1998. He recently formed Paragon Consulting Services (www.paragon-cs.com) to provide consulting services for companies seeking MySQL training and help with MySQL solutions ranging from everyday database administration tasks to utilizing "cloud" computing services, performance tuning and scaling. Keith blogs at blog.paragon-cs.com in addition he is the editor of MySQL Magazine . Readers are invited to contact Keith by email at . Table of Contents Introduction xxvii Part I First Steps with MySQL Chapter 1: Introduction to MySQL 3 MySQL Mission — Speed, Reliability, and Ease of Use 3 Company background 4 Community and Enterprise server versions 5 The MySQL Community 6 How to contribute 6 Reasons to contribute 7 Summary 7 Chapter 2: Installing and Upgrading MySQL Server 9 Before Installation 9 Choosing the MySQL version 11 MySQL support 12 Downloads 12 Installation 12 MySQL Server installations on Unix 13 MySQL Server Installation on Windows 20 Installing MySQL from a Noinstall Zip Archive 24 Starting and stopping MySQL from the Windows command line 25 Starting and stopping MySQL as a Windows service 26 Initial Configuration 29 Unix configuration file 31 Windows configuration file 31 MySQL Configuration Wizard on Windows 31 Detailed Configuration 32 The Server Type screen 33 Database Usage screen 33 InnoDB Tablespace screen 34 Concurrent Connections screen 34 Networking Options and Strict Mode Options screen 34 Character Set screen 35 Service Options screen 35 Security Options screen 35 Confirmation screen 36 MySQL Post-Install Configuration on Unix 36 Initializing the system tables 36 Setting initial passwords 37 Root user password assignment 37 Anonymous users 39 Securing Your System 40 Windows PATH Variable Configuration 42 Automated startup 42 Starting and stopping mysqld on System V-based Unix 42 System V run levels 43 Upgrading mysqld 45 The MySQL changelog 45 Upgrading MySQL on Windows 46 Troubleshooting 47 Summary 48 Chapter 3: Accessing MySQL 49 Accessing mysqld with Command-Line Tools 49 Frequently used options 50 Using the command-line mysql client 52 mysqladmin — Client for administering a server 62 GUI Tools 66 SQLyog 66 phpMyAdmin 69 MySQL Query Browser 71 MySQL Administrator 74 MySQL Workbench 80 Summary 83 Part II Developing with MySQL Chapter 4: How MySQL Extends and Deviates from SQL 87 Learning MySQL Language Structure 88 Comments and portability 88 Case-sensitivity 90 Escape characters 91 Naming limitations and quoting 93 Dot notation 95 Time zones 97 Character sets and collations 98 Understanding MySQL Deviations 105 Privileges and permissions 110 Transaction management 110 Check constraints 111 Upsert statements 112 Using MySQL Extensions 114 Aliases 115 Alter Table extensions 115 Create Extensions 118 DML Extensions 119 Drop Extensions 124 The LIMIT Extension 125 SELECT Extensions 126 Select Into Outfile/Select Into Dumpfile 126 Sql_Small_Result/Sql_Big_Result 127 Union Order By 127 Select For Update 127 Select Lock In Share Mode 128 Distinctrow 128 Sql_Buffer_Result 129 High_Priority/Low_Priority 129 Server maintenance extensions 129 The Set extension and user-defined variables 131 The Show extension 135 Table definition extensions 147 Table maintenance extensions 150 Transactional statement extensions 156 Summary 158 Chapter 5: MySQL Data Types 159 Looking at MySQL Data Types 159 Character String Types 160 Length 162 Character string type attributes 164 National Character String Types 166 Binary Large Object String Types 168 Blob values 169 Binary values 169 Binary length 169 Varbinary length 170 Numeric Types 170 Numeric data sizes and ranges 172 Numeric data type attributes 177 Boolean Types 180 Datetime Types 183 Allowed input values 185 Microsecond input 186 Automatic updates 187 Conversion issues 188 Numeric functions and Datetime types 188 Other conversion issues 190 Datetime data type attributes 191 The effect of time zones 192 Interval Types 193 ENUM and SET Types 195 Enumerations 195 ENUM and SET data type attributes 198 Choosing SQL Modes 201 Invalid data 201 SQL modes 203 Using NULL Values 211 Finding an Optimal Data Type for Existing Data 212 Small data samples and Procedure Analyse() 215 Summary 217 Chapter 6: MySQL Index Types 219 Looking at Keys and Indexes 219 Using Indexes to Speed Up Lookups 221 Creating and dropping indexes 223 Index order 225 Index length 226 Index types 228 Redundant indexes 230 Creating and Dropping Key Constraints 231 Creating and dropping unique key constraints 231 Creating and dropping foreign key constraints 232 Foreign key constraints and data changes 234 Requirements for foreign key constraints 235 Using FULLTEXT Indexes 237 Summary 239 Chapter 7: Stored Routines, Triggers, and Events 241 Comparing Stored Routines, Triggers, and Events 241 Using Triggers 242 Creating a trigger 243 Dropping a trigger 244 Multiple SQL statements in triggers 245 Changing a trigger 246 Triggers on views and temporary tables 247 Trigger runtime behavior 248 Finding all triggers 252 Trigger storage and backup 252 Triggers and replication 254 Trigger limitations 254 Using Stored Routines 255 Performance implications of stored routines 256 Stored procedures vs stored functions 256 Creating a stored routine 256 Invoking a stored procedure 259 Dropping a stored routine 261 Multiple SQL statements in stored routines 261 INOUT arguments to a stored procedure 261 Local variables 262 Stored routine runtime behavior 264 Options when creating routines 265 Creating a basic stored function 268 Full Create Function syntax 269 Invoking a stored function 269 Changing a stored routine 270 Naming: stored routines 271 Stored procedure result sets 273 Stored routine errors and warnings 274 Conditions and handlers 275 Stored routine flow control 282 Recursion 284 Stored routines and replication 285 Stored function limitations 285 Stored routine backup and storage 286 Using Cursors 287 Using Events 289 Turning on the event scheduler 289 Creating an event 291 Dropping an event 292 Multiple SQL statements in events 293 Start and end times for periodic events 293 Event status 294 Finding all events 295 Changing an event 295 After the last execution of an event 296 Event logging 297 Event runtime behavior 298 Event limitations 299 Event backup and storage 300 Summary 300 Chapter 8: MySQL Views 301 Defining Views 302 View definition limitations and unexpected behavior 304 Security and privacy 305 Specify a views definer 306 Abstraction and simplification 307 Performance 308 Updatable views 313 Changing a View Definition 317 Replication and Views 317 Summary 318 Chapter 9: Transactions in MySQL 319 Understanding ACID Compliance 320 Atomicity 321 Consistency 321 Isolation 321 Durability 321 Using Transactional Statements 322 Begin, Begin Work, and Start Transaction 322 Commit 322 Rollback 322 Savepoints 323 Autocommit 324 Using Isolation Levels 325 Read Uncommited 329 Read Committed 331 Repeatable Read 332 Serializable 334 Multi-version concurrency control 335 Explaining Locking and Deadlocks 336 Table-level locks 338 Page-level locks 341 Row-level locks 341 Recovering MySQL Transactions 343 Summary 344 Part III Core MySQL Administration Chapter 10: MySQL Server Tuning 349 Choosing Optimal Hardware 349 Tuning the Operating System 352 Operating system architecture 352 File systems and partitions 353 Buffers 356 Kernel parameters 357 Linux 357 Other daemons 360 Tuning MySQL Server 360 Status variables 360 System variables 361 Option file 361 Dynamic variables 371 Summary 373 Chapter 11: Storage Engines 375 Understanding Storage Engines 375 Storage engines as plugins 376 Storage engine comparison 376 Using Different Storage Engines 378 MyISAM storage engine 378 InnoDB storage engine 384 Memory storage engine 394 Maria storage engine 396 Falcon storage engine 401 PBXT storage engine 410 Federated storage engine 415 NDB storage engine 417 Archive storage engine 417 Blackhole storage engine 419 CSV storage engine 420 Working with Storage Engines 421 Create Table 421 Alter Table 421 Drop Table 422 Summary 422 Chapter 12: Caching with MySQL 423 Implementing Cache Tables 424 Working with the Query Cache 427 What gets stored in the query cache? 427 Query cache memory usage and tuning 429 Query cache fragmentation 433 Utilizing memcached 434 Summary 438 Chapter 13: Backups and Recovery 439 Backing Up MySQL 439 Uses for backups 441 Backup frequency 443 What to back up 445 Backup locations 445 Backup methods 445 Online backup 460 mysqlhotcopy 462 Commercial options 464 Copying Databases to Another Machine 467 Recovering from Crashes 468 Planning for Disasters 471 Summary 472 Chapter 14: User Management 473 Learning about MySQL Users 473 Access Control Lists 474 Wildcards 475 System tables 476 Managing User Accounts 478 Grant and Revoke commands 481 Show Grants and mk-show-grants 485 Resetting the Root Password 487 Windows server 488 Unix-based server 489 Debugging User Account Problems 490 Bad password 490 Access issues 491 Client does not support authentication protocol 491 Cant connect to local mysqld through socket /path/to/mysqld.sock 492 I do not have the right permissions! 493 Summary 494 Chapter 15: Partitioning 495 Learning about Partitioning 495 Partitioning Tables 496 RANGE partitioning 497 LIST partitioning 502 HASH partitioning 503 KEY partitioning 504 Composite partitioning 504 Partition management commands 507 Restrictions of partitioning 510 Merge Tables 510 Creating a Merge table 511 Changing a Merge table 512 Advantages of Merge tables 513 Partitioning with MySQL Cluster 513 Programmatic Partitioning 514 Summary 514 Chapter 16: Logging and Replication 517 Log Files 517 Error log 517 Binary logs 518 Relay logs 520 General and slow query logs 520 Rotating logs 522 Other methods of rotating 523 Replication 524 Setting up semisynchronous replication 525 Statement-based, row-based, and mixed-based replication 527 Replication Configurations 529 Simple replication 529 Change Master statement 534 More complex setups 534 Additional replication configuration options 539 Correcting Data Drift 540 mk-table-checksum overview 540 mk-table-sync overview 542 Putting this together 542 Summary 543 Chapter 17: Measuring Performance 545 Benchmarking 546 mysqlslap 547 SysBench 552 Benchmarking recommendations 565 Profiling 566 Show Global Status 566 mysqltuner 568 mysqlreport 572 mk-query-profiler 580 mysqldumpslow 583 Capacity Planning 585 Summary 585 Part IV Extending Your Skills Chapter 18: Query Analysis and Index Tuning 589 Using Explain 590 Explain plan basics 590 Data access strategy 596 Explain plan indexes 606 Rows 607 Extra 608 Subqueries and Explain 611 Explain Extended 612 Explain on Non-Select Statements 614 Other Query Analysis Tools 614 Optimizing Queries 615 Factors affecting key usage 615 Optimizer hints 616 Adding an Index 616 Optimizing away Using temporary 620 Using an index by eliminating functions 623 Non-index schema changes 626 Batching expensive operations 628 Optimizing frequent operations 629 Summary 631 Chapter 19: Monitoring Your Systems 633 Deciding What to Monitor 634 Examining Open Source Monitoring 636 Nagios 636 Cacti 637 Hyperic HQ 638 OpenNMS 640 Zenoss Core 641 Munin 642 Monit 643 Examining Commercial Monitoring 644 MySQL enterprise monitor 644 MONyog 645 Summary 646 Chapter 20: Securing MySQL 649 Access Control Lists 649 Wildcards and blank values 650 Privilege and privilege levels 651 Accessing the Operating System 654 Database access 654 Changing MySQL connectivity defaults 654 Operating system login 654 Securing Backups and Logs 656 Data Security 656 Data flow 657 Encrypted connectivity 659 Data security using MySQL objects 664 Creating Security Policies 665 Summary 666 Chapter 21: The MySQL Data Dictionary 667 Object Catalog 668 Schemata 668 Tables 670 Views 674 Columns 676 Statistics 679 Table_Constraints 681 Key_Column_Usage 682 Referential_Constraints 684 Triggers 685 Routines 686 Parameters 690 Events 691 Partitions 693 System Information 695 Character_Sets 695 Collations 696 Collation_Character_Set_Applicability 696 Engines 697 Plugins 697 Processlist 698 Profiling 709 Global_Variables 710 Session_Variables 710 Global_Status 711 Session_Status 711 Displaying Permissions 711 Column_Privileges 712 Table_Privileges 713 Schema_Privileges 714 User_Privileges 715 Storage Engine-Specific Metadata 716 Custom Metadata 716 Defining the plugin 716 Compiling the plugin 722 Installing the plugin 724 Summary 725 Chapter 22: Scaling and High Availability Architectures 727 Replication 728 One read slave 729 Promoting a new master 729 Many read slaves 734 Master/master replication 735 Circular replication 736 SAN 737 DRBD 738 MySQL and DRBD setup 738 MySQL Proxy 739 Scaling read queries 740 Automated failover 740 Read/write splitting 742 Sharding 742 Linux-HA Heartbeat 742 MySQL Cluster 744 Connection Pooling 746 memcached 747 Summary 748 Appendix A: MySQL Proxy 749 Appendix B: Functions and Operators 783 Appendix C: Resources 813 Index 821 Long Description Whether youre a novice or experienced DBA, this book has all you need to learn MySQL MySQL Administrators Bible is designed to provide a solid framework for a beginning MySQL DBA or an experienced DBA transitioning from another database platform. The book starts with the fundamentals of MySQL database management, including MySQLs unique approach to SQL queries, data and index types, stored procedures and functions, triggers and views, and transactions. The authors continue with comprehensive coverage of major MySQL Server components and processes for which DBAs are responsible--these include: MySQL Server tuning Query optimization and index tuning Managing storage engines Caching Authentication and managing users Partitioning; Replication; Logging Database and performance monitoring Security; Backup and Recovery Managing availability and scaling Fully covers MySQL 5.1 Companion Web Site The books Companion Web Site includes a tutorial on foundational SQL programming that all MySQL DBAs need to understand, plus code examples, scripts, and data schemas used in the book. Youll also learn about the latest changes in MySQL and find helpful links. Visit the site at Details ISBN0470416912 Author Sheeri K. Cabral Short Title MYSQL ADMINISTRATORS BIBLE Language English ISBN-10 0470416912 ISBN-13 9780470416914 Media Book Format Paperback Year 2009 Illustrations Illustrations Edition 1st Imprint John Wiley & Sons Inc Place of Publication New York Country of Publication United States UK Release Date 2009-05-08 AU Release Date 2009-05-08 NZ Release Date 2009-05-08 US Release Date 2009-05-08 Pages 896 Publisher John Wiley & Sons Inc Series Bible Publication Date 2009-05-08 DEWEY 005.7585 Audience Professional & Vocational We've got this At The Nile, if you're looking for it, we've got it. With fast shipping, low prices, friendly service and well over a million items - you're bound to find what you want, at a price you'll love! TheNile_Item_ID:102470499;

Price: 108.98 AUD

Location: Melbourne

End Time: 2024-11-21T03:02:14.000Z

Shipping Cost: 20.82 AUD