MySQL administrator's guide and language reference

Cover......Page 1
Table of Contents......Page 10
1 General Information......Page 28
 1.2 Conventions Used in This Manual......Page 29
 1.3 Overview of MySQL AB......Page 31
 1.4 Overview of the MySQL Database Management System......Page 32
  1.4.1 History of MySQL......Page 33
  1.4.2 The Main Features of MySQL......Page 34
  1.4.3 MySQL Stability......Page 37
  1.4.4 How Large MySQL Tables Can Be......Page 38
  1.4.5 Year 2000 Compliance......Page 39
  1.5.1 What Is MaxDB?......Page 41
  1.5.3 Features of MaxDB......Page 42
  1.5.5 Feature Differences Between MaxDB and MySQL......Page 43
  1.5.6 Interoperability Features Between MaxDB and MySQL......Page 44
 1.6 MySQL Development Roadmap......Page 45
  1.6.1 What\'s New in MySQL 5.0......Page 46
  1.7.1 MySQL Mailing Lists......Page 49
  1.7.2 MySQL Community Support at the MySQL Forums......Page 51
 1.8 How to Report Bugs or Problems......Page 52
 1.9 MySQL Standards Compliance......Page 57
  1.9.2 Selecting SQL Modes......Page 58
  1.9.4 MySQL Extensions to Standard SQL......Page 59
  1.9.5 MySQL Differences from Standard SQL......Page 62
  1.9.6 How MySQL Deals with Constraints......Page 69
2 Installing and Upgrading MySQL......Page 74
  2.1.1 Operating Systems Supported by MySQL......Page 75
  2.1.2 Choosing Which MySQL Distribution to Install......Page 77
  2.1.3 How to Get MySQL......Page 83
  2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG......Page 84
  2.1.5 Installation Layouts......Page 87
 2.3 Installing MySQL on Windows......Page 89
  2.3.1 Choosing an Installation Package......Page 90
  2.3.3 Using the MySQL Installation Wizard......Page 91
  2.3.4 Using the Configuration Wizard......Page 94
  2.3.6 Extracting the Install Archive......Page 100
  2.3.7 Creating an Option File......Page 101
  2.3.8 Selecting a MySQL Server Type......Page 102
  2.3.9 Starting the Server for the First Time......Page 103
  2.3.10 Starting MySQL from the Windows Command Line......Page 104
  2.3.11 Starting MySQL as a Windows Service......Page 105
  2.3.12 Testing the MySQL Installation......Page 108
  2.3.13 Troubleshooting a MySQL Installation Under Windows......Page 109
  2.3.14 Upgrading MySQL on Windows......Page 110
  2.3.15 MySQL on Windows Compared to MySQL on Unix......Page 111
 2.4 Installing MySQL on Linux......Page 114
 2.5 Installing MySQL on Mac OS X......Page 117
 2.6 Installing MySQL on NetWare......Page 120
 2.7 Installing MySQL on Other Unix-Like Systems......Page 122
 2.8 MySQL Installation Using a Source Distribution......Page 125
  2.8.1 Source Installation Overview......Page 126
  2.8.2 Typical configure Options......Page 129
  2.8.3 Installing from the Development Source Tree......Page 132
  2.8.4 Dealing with Problems Compiling MySQL......Page 135
  2.8.5 MIT-pthreads Notes......Page 138
  2.8.6 Installing MySQL from Source on Windows......Page 139
  2.8.7 Compiling MySQL Clients on Windows......Page 143
  2.9.1 Windows Post-Installation Procedures......Page 144
  2.9.2 Unix Post-Installation Procedures......Page 145
  2.9.3 Securing the Initial MySQL Accounts......Page 158
 2.10 Upgrading MySQL......Page 161
  2.10.1 Upgrading from MySQL 5.0 to 5.1......Page 162
  2.10.2 Upgrading from MySQL 4.1 to 5.0......Page 164
  2.10.3 Copying MySQL Databases to Another Machine......Page 170
 2.11 Downgrading MySQL......Page 171
 2.13 Perl Installation Notes......Page 172
  2.13.1 Installing Perl on Unix......Page 173
  2.13.3 Problems Using the Perl DBI/DBD Interface......Page 174
 3.1 Overview of MySQL Programs......Page 178
 3.2 Invoking MySQL Programs......Page 179
 3.3 Specifying Program Options......Page 180
  3.3.1 Using Options on the Command Line......Page 181
  3.3.2 Using Option Files......Page 183
  3.3.3 Using Environment Variables to Specify Options......Page 188
  3.3.4 Using Options to Set Program Variables......Page 189
 4.1 Overview of Server-Side Programs......Page 190
  4.2.1 mysqld Command Options......Page 192
  4.2.2 Server System Variables......Page 203
  4.2.3 Using System Variables......Page 231
  4.2.4 Server Status Variables......Page 241
  4.2.5 The Server SQL Mode......Page 251
  4.2.6 The MySQL Server Shutdown Process......Page 256
 4.3 The mysqld-max Extended MySQL Server......Page 257
  4.4.1 mysqld_safe—MySQL Server Startup Script......Page 261
  4.4.2 mysql.server—MySQL Server Startup Script......Page 264
  4.4.3 mysqld_multi—Manage Multiple MySQL Servers......Page 265
 4.5 mysqlmanager—The MySQL Instance Manager......Page 269
  4.5.1 Starting the MySQL Server with MySQL Instance Manager......Page 270
  4.5.2 Connecting to the MySQL Instance Manager and Creating User Accounts......Page 271
  4.5.3 MySQL Instance Manager Command Options......Page 272
  4.5.4 MySQL Instance Manager Configuration Files......Page 273
  4.5.5 Commands Recognized by the MySQL Instance Manager......Page 275
  4.6.1 mysql_fix_privilege_tables—Upgrade MySQL System Tables......Page 277
  4.6.2 mysql_upgrade—Check Tables for MySQL Upgrade......Page 278
 4.7 General Security Issues......Page 279
  4.7.1 General Security Guidelines......Page 280
  4.7.2 Making MySQL Secure Against Attackers......Page 283
  4.7.3 Security-Related mysqld Options......Page 284
  4.7.4 Security Issues with LOAD DATA LOCAL......Page 286
  4.7.5 How to Run MySQL As a Normal User......Page 287
  4.8.2 How the Privilege System Works......Page 288
  4.8.3 Privileges Provided by MySQL......Page 293
  4.8.4 Connecting to the MySQL Server......Page 297
  4.8.5 Access Control, Stage 1: Connection Verification......Page 298
  4.8.6 Access Control, Stage 2: Request Verification......Page 302
  4.8.8 Causes of Access denied Errors......Page 305
  4.8.9 Password Hashing as of MySQL 4.1......Page 311
 4.9 MySQL User Account Management......Page 316
  4.9.1 MySQL Usernames and Passwords......Page 317
  4.9.2 Adding New User Accounts to MySQL......Page 318
  4.9.4 Limiting Account Resources......Page 322
  4.9.5 Assigning Account Passwords......Page 324
  4.9.6 Keeping Your Password Secure......Page 325
  4.9.7 Using Secure Connections......Page 327
  4.10.1 Database Backups......Page 335
  4.10.2 Example Backup and Recovery Strategy......Page 337
  4.10.3 Point-in-Time Recovery......Page 341
  4.10.4 Table Maintenance and Crash Recovery......Page 343
  4.11.1 The Character Set Used for Data and Sorting......Page 356
  4.11.3 Adding a New Character Set......Page 358
  4.11.4 The Character Definition Arrays......Page 360
  4.11.7 Problems with Character Sets......Page 361
  4.11.8 MySQL Server Time Zone Support......Page 362
 4.12 MySQL Server Logs......Page 363
  4.12.2 The General Query Log......Page 364
  4.12.3 The Binary Log......Page 365
  4.12.4 The Slow Query Log......Page 369
  4.12.5 Server Log Maintenance......Page 370
 4.13 Running Multiple MySQL Servers on the Same Machine......Page 371
  4.13.1 Running Multiple Servers on Windows......Page 373
  4.13.2 Running Multiple Servers on Unix......Page 376
  4.13.3 Using Client Programs in a Multiple-Server Environment......Page 378
  4.14.1 How the Query Cache Operates......Page 379
  4.14.3 Query Cache Configuration......Page 381
  4.14.4 Query Cache Status and Maintenance......Page 383
 5.1 Introduction to Replication......Page 386
 5.2 Replication Implementation Overview......Page 387
 5.3 Row-Based Replication......Page 388
 5.4 Replication Implementation Details......Page 389
  5.4.1 Replication Master Thread States......Page 390
  5.4.2 Replication Slave I/O Thread States......Page 391
  5.4.4 Replication Relay and Status Files......Page 392
 5.5 How to Set Up Replication......Page 394
 5.6 Replication Compatibility Between MySQL Versions......Page 399
 5.8 Replication Features and Known Problems......Page 400
 5.9 Replication Startup Options......Page 406
 5.10 How Servers Evaluate Replication Rules......Page 413
 5.11 Replication FAQ......Page 417
 5.12 Comparison of Statement-Based Versus Row-Based Replication......Page 423
 5.13 Troubleshooting Replication......Page 425
 5.14 How to Report Replication Bugs or Problems......Page 427
 5.15 Auto-Increment in Multiple-Master Replication......Page 428
 6.1 Optimization Overview......Page 430
  6.1.2 Designing Applications for Portability......Page 431
  6.1.3 What We Have Used MySQL For......Page 432
  6.1.4 The MySQL Benchmark Suite......Page 433
  6.1.5 Using Your Own Benchmarks......Page 434
  6.2.1 Optimizing Queries with EXPLAIN......Page 435
  6.2.2 Estimating Query Performance......Page 445
  6.2.4 WHERE Clause Optimization......Page 446
  6.2.5 Range Optimization......Page 448
  6.2.6 Index Merge Optimization......Page 452
  6.2.7 IS NULL Optimization......Page 455
  6.2.8 DISTINCT Optimization......Page 456
  6.2.9 LEFT JOIN and RIGHT JOIN Optimization......Page 457
  6.2.10 Nested Join Optimization......Page 458
  6.2.11 Outer Join Simplification......Page 465
  6.2.12 ORDER BY Optimization......Page 467
  6.2.13 GROUP BY Optimization......Page 469
  6.2.14 LIMIT Optimization......Page 471
  6.2.15 How to Avoid Table Scans......Page 472
  6.2.16 Speed of INSERT Statements......Page 473
  6.2.19 Other Optimization Tips......Page 475
  6.3.1 Locking Methods......Page 478
  6.3.2 Table Locking Issues......Page 481
  6.3.3 Concurrent Inserts......Page 482
  6.4.2 Make Your Data As Small As Possible......Page 483
  6.4.4 Multiple-Column Indexes......Page 485
  6.4.5 How MySQL Uses Indexes......Page 486
  6.4.6 The MyISAM Key Cache......Page 489
  6.4.7 MyISAM Index Statistics Collection......Page 495
  6.4.8 How MySQL Opens and Closes Tables......Page 496
  6.5.1 System Factors and Startup Parameter Tuning......Page 498
  6.5.2 Tuning Server Parameters......Page 499
  6.5.3 Controlling Query Optimizer Performance......Page 503
  6.5.4 How Compiling and Linking Affects the Speed of MySQL......Page 504
  6.5.5 How MySQL Uses Memory......Page 505
 6.6 Disk Issues......Page 507
  6.6.1 Using Symbolic Links......Page 509
 7.1 Overview of Client and Utility Programs......Page 512
 7.2 myisamchk—MyISAM Table-Maintenance Utility......Page 514
  7.2.1 myisamchk General Options......Page 515
  7.2.3 myisamchk Repair Options......Page 518
  7.2.4 Other myisamchk Options......Page 520
  7.2.5 myisamchk Memory Usage......Page 521
 7.3 myisamlog—Display MyISAM Log File Contents......Page 522
 7.4 myisampack—Generate Compressed, Read-Only MyISAM Tables......Page 523
  7.5.1 mysql Options......Page 530
  7.5.2 mysql Commands......Page 535
  7.5.4 mysql Tips......Page 539
 7.6 mysqlaccess—Client for Checking Access Privileges......Page 541
 7.7 mysqladmin—Client for Administering a MySQL Server......Page 543
 7.8 mysqlbinlog—Utility for Processing Binary Log Files......Page 548
 7.9 mysqlcheck—A Table Maintenance and Repair Program......Page 554
 7.10 mysqldump—A Database Backup Program......Page 557
 7.11 mysqlhotcopy—A Database Backup Program......Page 565
 7.12 mysqlimport—A Data Import Program......Page 568
 7.13 mysqlshow—Display Database, Table, and Column Information......Page 570
 7.14 mysql_zap—Kill Processes That Match a Pattern......Page 572
 7.15 perror—Explain Error Codes......Page 573
 7.16 replace—A String-Replacement Utility......Page 574
8 Storage Engines and Table Types......Page 576
 8.1 The MyISAM Storage Engine......Page 578
  8.1.1 MyISAM Startup Options......Page 580
  8.1.3 MyISAM Table Storage Formats......Page 582
  8.1.4 MyISAM Table Problems......Page 585
  8.2.1 InnoDB Overview......Page 586
  8.2.3 InnoDB Configuration......Page 587
  8.2.4 InnoDB Startup Options and System Variables......Page 594
  8.2.5 Creating the InnoDB Tablespace......Page 602
  8.2.6 Creating and Using InnoDB Tables......Page 604
  8.2.7 Adding and Removing InnoDB Data and Log Files......Page 612
  8.2.8 Backing Up and Recovering an InnoDB Database......Page 613
  8.2.9 Moving an InnoDB Database to Another Machine......Page 616
  8.2.10 InnoDB Transaction Model and Locking......Page 617
  8.2.11 InnoDB Performance Tuning Tips......Page 628
  8.2.12 Implementation of Multi-Versioning......Page 636
  8.2.13 InnoDB Table and Index Structures......Page 637
  8.2.14 InnoDB File Space Management and Disk I/O......Page 639
  8.2.15 InnoDB Error Handling......Page 641
  8.2.16 Restrictions on InnoDB Tables......Page 645
  8.2.17 InnoDB Troubleshooting......Page 647
 8.3 The MERGE Storage Engine......Page 649
  8.3.1 MERGE Table Problems......Page 652
 8.4 The MEMORY (HEAP) Storage Engine......Page 653
  8.5.1 Operating Systems Supported by BDB......Page 655
  8.5.3 BDB Startup Options......Page 656
  8.5.4 Characteristics of BDB Tables......Page 658
  8.5.5 Things We Need to Fix for BDB......Page 659
  8.5.6 Restrictions on BDB Tables......Page 660
 8.6 The EXAMPLE Storage Engine......Page 661
  8.7.1 Description of the FEDERATED Storage Engine......Page 662
  8.7.2 How to Use FEDERATED Tables......Page 663
  8.7.3 Limitations of the FEDERATED Storage Engine......Page 664
 8.8 The ARCHIVE Storage Engine......Page 665
 8.10 The BLACKHOLE Storage Engine......Page 666
 9.1 MySQL Cluster Overview......Page 670
 9.2 Basic MySQL Cluster Concepts......Page 672
  9.2.1 MySQL Cluster Nodes, Node Groups, Replicas, and Partitions......Page 673
 9.3 Simple Multi-Computer How-To......Page 676
  9.3.1 Hardware, Software, and Networking......Page 678
  9.3.2 Multi-Computer Installation......Page 679
  9.3.3 Multi-Computer Configuration......Page 681
  9.3.4 Initial Startup......Page 683
  9.3.5 Loading Sample Data and Performing Queries......Page 684
  9.3.6 Safe Shutdown and Restart......Page 688
  9.4.1 Building MySQL Cluster from Source Code......Page 689
  9.4.3 Quick Test Setup of MySQL Cluster......Page 690
  9.4.4 Configuration File......Page 693
  9.5.1 MySQL Server Process Usage for MySQL Cluster......Page 721
  9.5.2 ndbd, the Storage Engine Node Process......Page 722
  9.5.4 ndb_mgm, the Management Client Process......Page 724
  9.5.5 Command Options for MySQL Cluster Processes......Page 725
  9.6.1 MySQL Cluster Startup Phases......Page 727
  9.6.2 Commands in the Management Client......Page 729
  9.6.3 Event Reports Generated in MySQL Cluster......Page 730
  9.6.5 Online Backup of MySQL Cluster......Page 736
 9.7 MySQL Cluster Replication......Page 739
  9.7.1 Abbreviations and Symbols......Page 740
  9.7.3 Known Issues......Page 741
  9.7.4 Replication Schema and Tables......Page 742
  9.7.5 Preparing the Cluster for Replication......Page 744
  9.7.6 Starting Replication (Single Replication Channel)......Page 746
  9.7.8 Implementing Failover with MySQL Cluster......Page 747
  9.7.9 MySQL Cluster Backups with Replication......Page 749
  9.8.1 Configuring MySQL Cluster to Use SCI Sockets......Page 757
  9.8.2 Understanding the Impact of Cluster Interconnects......Page 762
 9.9 Known Limitations of MySQL Cluster......Page 764
  9.10.1 MySQL Cluster Changes in MySQL 5.0......Page 767
  9.10.2 MySQL 5.1 Development Roadmap for MySQL Cluster......Page 768
 9.11 MySQL Cluster FAQ......Page 769
 9.12 MySQL Cluster Glossary......Page 777
 A......Page 782
 B......Page 784
 C......Page 786
 D......Page 796
 E......Page 799
 F......Page 801
 H......Page 804
 I......Page 806
 K......Page 813
 L......Page 814
 M......Page 817
 N......Page 832
 O......Page 835
 P......Page 850
 Q......Page 855
 R......Page 856
 S......Page 860
 T......Page 874
 U......Page 879
 V......Page 880
 W......Page 885
 X-Z......Page 887