book

MySQL® Administrator's Bible

Name: MySQL® Administrator's Bible
ISBN: 9780470416914

by Sheeri K. Cabral, Keith Murphy

May 2009

Intermediate to advanced

888 pages

20h 33m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Sheeri CabralKeith Murphy
Introduction
Who Should Read This BookHow This Book Is OrganizedConventions and FeaturesTips, Notes, and CautionsWhat's on the Companion WebsiteWhere To Go From Here
I. First Steps with MySQL
1. Introduction to MySQL
1.1. MySQL Mission—Speed, Reliability, and Ease of Use1.1.1. Company background1.1.2. Community and Enterprise server versions1.2. The MySQL Community1.2.1. How to contribute1.2.2. Reasons to contribute1.3. Summary
2. Installing and Upgrading MySQL Server
2.1. Before Installation2.1.1. Choosing the MySQL version2.1.2. MySQL support2.1.3. Downloads2.2. Installation2.2.1. MySQL Server installations on Unix2.2.1.1. Installing from an rpm file on GNU/Linux2.2.1.2. Installing from a pkg file on Solaris2.2.1.3. Installing from an archive package on Unix2.2.2. MySQL Server Installation on Windows2.2.2.1. Installing MySQL from the installation packages on windows2.2.2.2. Initial configuration of MySQL on windows2.2.2.2.1. Service Options Screen2.2.2.2.2. Security Options Screen2.2.2.2.3. Confirmation Screen2.2.3. Installing MySQL from a Noinstall Zip Archive2.2.4. Starting and stopping MySQL from the Windows command line2.2.5. Starting and stopping MySQL as a Windows service2.3. Initial Configuration2.3.1. Unix configuration file2.3.2. Windows configuration file2.4. MySQL Configuration Wizard on Windows2.4.1. Detailed Configuration2.4.2. The Server Type screen2.4.3. Database Usage screen2.4.4. InnoDB Tablespace screen2.4.5. Concurrent Connections screen2.4.6. Networking Options and Strict Mode Options screen2.4.7. Character Set screen2.4.8. Service Options screen2.4.9. Security Options screen2.4.10. Confirmation screen2.5. MySQL Post-Install Configuration on Unix2.5.1. Initializing the system tables2.5.2. Setting initial passwords2.5.3. Root user password assignment2.5.4. Anonymous users2.6. Securing Your System2.7. Windows PATH Variable Configuration2.7.1. Automated startup2.7.2. Starting and stopping mysqld on System V-based Unix2.7.3. System V run levels2.8. Upgrading mysqld2.8.1. The MySQL changelog2.8.2. Upgrading MySQL on Windows2.9. Troubleshooting2.10. Summary
3. Accessing MySQL
3.1. Accessing mysqld with Command-Line Tools3.1.1. Frequently used options3.1.2. Using the command-line mysql client3.1.2.1. Non-interactive modes3.1.2.2. Useful features of mysql3.1.3. mysqladmin—Client for administering a server3.2. GUI Tools3.2.1. SQLyog3.2.1.1. Server connections3.2.1.2. SQL query creation3.2.1.3. Database management3.2.1.4. Database synchronization3.2.2. phpMyAdmin3.2.2.1. Creating a database3.2.2.2. Creating a table in your database3.2.2.3. Data insertion into a table3.2.2.4. Database backup3.2.3. MySQL Query Browser3.2.4. MySQL Administrator3.2.4.1. User administration3.2.4.2. Health monitoring3.2.4.3. Backup and recovery3.2.4.4. Server information3.2.4.5. Replication status3.2.4.6. Server log management3.2.4.7. Service control3.2.4.8. Catalogs3.2.5. MySQL Workbench3.3. Summary
II. Developing with MySQL

4. How MySQL Extends and Deviates from SQL
4.1. Learning MySQL Language Structure4.1.1. Comments and portability4.1.2. Case-sensitivity4.1.3. Escape characters4.1.4. Naming limitations and quoting4.1.5. Dot notation4.1.6. Time zones4.1.7. Character sets and collations4.2. Understanding MySQL Deviations4.2.1. Privileges and permissions4.2.2. Transaction management4.2.3. Check constraints4.2.4. Upsert statements4.3. Using MySQL Extensions4.3.1. Aliases4.3.2. ALTER TABLE extensions4.3.3. CREATE extensions4.3.4. DML extensions4.3.5. DROP extensions4.3.6. The LIMIT extension4.3.7. SELECT extensions4.3.8. SELECT ... INTO OUTFILE/SELECT ... INTO DUMPFILE4.3.9. SQL_SMALL_RESULT/SQL_BIG_RESULT4.3.10. UNION ... ORDER BY4.3.11. SELECT ... FOR UPDATE4.3.12. SELECT ... LOCK IN SHARE MODE4.3.13. DISTINCTROW4.3.13.1. SQL_CALC_FOUND_ROWS4.3.14. SQL_BUFFER_RESULT4.3.15. HIGH_PRIORITY/LOW_PRIORITY4.3.16. Server maintenance extensions4.3.17. The SET extension and user-defined variables4.3.17.1. Local variables in stored code4.3.17.2. Assigning values to dynamic server variables4.3.18. The SHOW extension4.3.19. Table definition extensions4.3.20. Table maintenance extensions4.3.20.1. Index and data corruption4.3.20.2. Fragmentation4.3.20.3. Reclaiming disk space4.3.20.4. Maintaining table statistics4.3.20.5. Getting a table checksum4.3.21. Transactional statement extensions4.4. Summary
5. MySQL Data Types
5.1. Looking at MySQL Data Types5.2. Character String Types5.2.1. Length5.2.1.1. CHAR Length5.2.1.2. VARCHAR length5.2.1.3. TEXT sizes5.2.2. Character string type attributes5.3. National Character String Types5.4. Binary Large Object String Types5.4.1. BLOB values5.4.2. BINARY values5.4.3. BINARY length5.4.4. VARBINARY length5.5. Numeric Types5.5.1. Numeric data sizes and ranges5.5.1.1. DECIMAL size and range5.5.1.2. Integer sizes and ranges5.5.1.3. BIT size and range5.5.1.4. FLOAT size and range5.5.1.5. DOUBLE size and range5.5.2. Numeric data type attributes5.6. Boolean Types5.7. Datetime Types5.7.1. Allowed input values5.7.2. Microsecond input5.7.3. Automatic updates5.7.4. Conversion issues5.7.5. Numeric functions and DATETIME types5.7.6. Other conversion issues5.7.7. Datetime data type attributes5.7.8. The effect of time zones5.8. Interval Types5.9. ENUM and SET Types5.9.1. Enumerations5.9.2. ENUM and SET data type attributes5.10. Choosing SQL Modes5.10.1. Invalid Data5.10.2. SQL modes5.10.2.1. SQL mode functionality5.10.2.2. SQL mode definitions5.11. Using NULL Values5.12. Finding an Optimal Data Type for Existing Data5.12.1. Small data samples and PROCEDURE ANALYSE()5.13. Summary
6. MySQL Index Types
6.1. Looking at Keys and Indexes6.2. Using Indexes to Speed Up Lookups6.2.1. Creating and dropping indexes6.2.2. Index order6.2.3. Index length6.2.4. Index types6.2.5. Redundant indexes6.3. Creating and Dropping Key Constraints6.3.1. Creating and dropping unique key constraints6.3.2. Creating and dropping foreign key constraints6.3.3. Foreign key constraints and data changes6.3.4. Requirements for foreign key constraints6.4. Using FULLTEXT Indexes6.5. Summary
7. Stored Routines, Triggers, and Events
7.1. Comparing Stored Routines, Triggers, and Events7.2. Using Triggers7.2.1. Creating a trigger7.2.2. Dropping a trigger7.2.3. Multiple SQL statements in triggers7.2.4. Changing a trigger7.2.5. Triggers on views and temporary tables7.2.6. Trigger runtime behavior7.2.6.1. Changing the sql_mode of a trigger7.2.6.2. Character set and collation7.2.6.3. Changing whom a trigger is invoked as7.2.7. Finding all triggers7.2.8. Trigger storage and backup7.2.9. Triggers and replication7.2.10. Trigger limitations7.3. Using Stored Routines7.3.1. Performance implications of stored routines7.3.2. Stored procedures vs. stored functions7.3.3. Creating a stored routine7.3.4. Invoking a stored procedure7.3.5. Dropping a stored routine7.3.6. Multiple SQL statements in stored routines7.3.7. INOUT arguments to a stored procedure7.3.8. Local variables7.3.9. Stored routine runtime behavior7.3.9.1. sql_mode7.3.9.2. Character set and collation7.3.9.3. How the stored routine runs7.3.10. Options when creating routines7.3.10.1. Comments7.3.10.2. Language7.3.10.3. Determinism7.3.10.4. SQL usage7.3.10.5. Full CREATE PROCEDURE Syntax7.3.11. Creating a basic stored function7.3.12. Full CREATE FUNCTION syntax7.3.13. Invoking a stored function7.3.14. Changing a stored routine7.3.15. Naming: stored routines7.3.16. Stored procedure result sets7.3.17. Stored routine errors and warnings7.3.18. Conditions and handlers7.3.18.1. Handlers7.3.18.2. Conditions7.3.19. Stored routine flow control7.3.19.1. IF7.3.19.2. CASE7.3.19.3. WHILE7.3.19.4. REPEAT7.3.19.5. LOOP7.3.19.5.1. ITERATE7.3.19.5.2. LEAVE7.3.20. Recursion7.3.21. Stored routines and replication7.3.22. Stored function limitations7.3.23. Stored routine backup and storage7.4. Using Cursors7.5. Using Events7.5.1. Turning on the event scheduler7.5.2. Creating an event7.5.3. Dropping an event7.5.4. Multiple SQL statements in events7.5.5. Start and end times for periodic events7.5.6. Event status7.5.7. Finding all events7.5.8. Changing an event7.5.9. After the last execution of an event7.5.10. Event logging7.5.11. Event runtime behavior7.5.11.1. sql_mode7.5.11.2. Character set and collation7.5.11.3. Changing whom an event is invoked as7.5.12. Event limitations7.5.13. Event backup and storage7.6. Summary
8. MySQL Views
8.1. Defining Views8.1.1. View definition limitations and unexpected behavior8.1.2. Security and privacy8.1.3. Specify a view's definer8.1.4. Abstraction and simplification8.1.5. Performance8.1.5.1. View algorithm8.1.5.2. Materialized views and view indexes8.1.5.3. Simulating check constraints8.1.5.4. WITH CHECK OPTION on views that reference other views8.1.6. Updatable views8.1.6.1. Updatable view limitations8.1.6.2. Updatable view problems8.2. Changing a View Definition8.3. Replication and Views8.4. Summary
9. Transactions in MySQL
9.1. Understanding ACID Compliance9.1.1. Atomicity9.1.2. Consistency9.1.3. Isolation9.1.4. Durability9.2. Using Transactional Statements9.2.1. BEGIN, BEGIN WORK, and START TRANSACTION9.2.2. COMMIT9.2.3. ROLLBACK9.2.4. Savepoints9.2.5. AUTOCOMMIT9.3. Using Isolation Levels9.3.1. READ UNCOMMITED9.3.2. READ COMMITTED9.3.3. REPEATABLE READ9.3.4. SERIALIZABLE9.3.5. Multi-version concurrency control9.4. Explaining Locking and Deadlocks9.4.1. Table-level locks9.4.2. Page-level locks9.4.3. Row-level locks9.5. Recovering MySQL Transactions9.6. Summary
III. Core MySQL Administration
10. MySQL Server Tuning
10.1. Choosing Optimal Hardware10.2. Tuning the Operating System10.2.1. Operating system architecture10.2.2. File systems and partitions10.2.3. Buffers10.2.4. Kernel parameters10.2.5. Linux10.2.5.1. Open files limit10.2.5.2. I/O schedulers10.2.5.3. Swap memory10.2.6. Other daemons10.3. Tuning MySQL Server10.3.1. Status variables10.3.2. System variables10.3.3. Option file10.3.3.1. Overall mysqld options10.3.3.2. MyISAM storage engine options10.3.3.3. InnoDB storage engine options10.3.3.4. Falcon storage engine options10.3.3.5. Maria storage engine options10.3.3.6. Query cache options10.3.4. Dynamic variables10.4. SUMMARY
11. Storage Engines
11.1. Understanding Storage Engines11.1.1. Storage engines as plugins11.1.2. Storage engine comparison11.2. Using Different Storage Engines11.2.1. MyISAM storage engine11.2.1.1. MyISAM configuration options11.2.1.2. MyISAM utilities11.2.1.3. myisamchk11.2.1.4. myisampack11.2.1.5. myisam_ftdump11.2.1.6. Merge storage engine11.2.2. InnoDB storage engine11.2.2.1. Tablespace configuration variables11.2.2.2. Performance configuration variables11.2.2.3. SHOW ENGINE InnoDB STATUS11.2.2.4. InnoDB tablespace management11.2.2.5. Working with ibdata Files11.2.2.6. Adding an additional ibdata file to a shared tablespace11.2.2.7. Removing or reorganizing shared tablespace files11.2.2.8. Moving from a shared tablespace file to per-table tablespace11.2.2.9. InnoDB log files and crash recovery11.2.3. MEMORY storage engine11.2.3.1. MEMORY configuration options11.2.4. Maria storage engine11.2.4.1. Maria configuration options11.2.4.2. Log configuration parameters11.2.4.3. Page cache configuration11.2.4.4. Maria table options11.2.4.5. Miscellaneous configuration11.2.4.6. Maria command-line tools11.2.5. Falcon storage engine11.2.5.1. Architectural overview11.2.5.2. Falcon configuration options11.2.5.3. Performance optimization configuration11.2.5.4. Transactional support configuration11.2.5.5. Record cache configuration11.2.5.6. Serial log configuration11.2.5.7. Falcon tablespace management11.2.5.8. Creating a new tablespace11.2.5.9. Creating tables and indexes11.2.6. PBXT storage engine11.2.6.1. Architectural overview11.2.6.2. PBXT configuration options11.2.6.3. Transaction configuration11.2.6.4. Data log configuration11.2.6.5. File growth variables11.2.6.6. SHOW ENGINE PBXT STATUS11.2.6.7. Installing the PBXT binary plugin11.2.7. FEDERATED storage engine11.2.7.1. Limitations of FEDERATED tables11.2.8. NDB storage engine11.2.9. Archive storage engine11.2.10. Blackhole storage engine11.2.11. CSV storage engine11.3. Working with Storage Engines11.3.1. CREATE TABLE11.3.2. ALTER TABLE11.3.3. DROP TABLE11.4. Summary
12. Caching with MySQL
12.1. Implementing Cache Tables12.2. Working with the Query Cache12.2.1. What gets stored in the query cache?12.2.2. Query cache memory usage and tuning12.2.3. Query cache fragmentation12.3. Utilizing Memcached12.4. Summary
13. Backups and Recovery
13.1. Backing Up MySQL13.1.1. Uses for backups13.1.1.1. Disaster recovery13.1.1.2. Data recovery13.1.1.3. Easy test server creation13.1.1.4. Easy slave creation13.1.2. Backup frequency13.1.3. What to back up13.1.4. Backup locations13.1.5. Backup methods13.1.5.1. mysqldump13.1.5.2. SELECT INTO OUTFILE13.1.5.3. maatkit parallel dump/restore13.1.5.4. File system snapshot13.1.5.5. ZFS13.1.5.6. LVM13.1.5.7. SAN-based13.1.5.8. Microsoft VSS13.1.6. Online backup13.1.7. mysqlhotcopy13.1.8. Commercial options13.1.8.1. InnoDB hot backup13.1.8.2. Zmanda13.1.8.3. R1Soft13.2. Copying Databases to Another Machine13.3. Recovering from Crashes13.4. Planning for Disasters13.5. Summary
14. User Management
14.1. Learning about MySQL Users14.1.1. Access control lists14.1.2. Wildcards14.1.3. System tables14.2. Managing User Accounts14.2.1. GRANT and REVOKE commands14.2.1.1. Global14.2.1.2. Database14.2.1.3. Table14.2.1.4. Column14.2.1.5. Routine14.2.1.6. REVOKE14.2.2. SHOW GRANTS and mk-show-grants14.3. Resetting the Root Password14.3.1. Windows server14.3.2. Unix-Based server14.4. Debugging User Account Problems14.4.1. Bad password14.4.2. Access issues14.4.3. Client does not support authentication protocol14.4.4. Can't connect to local mysqld through socket '/path/to/mysqld.sock'14.4.5. I do not have the right permissions!14.5. Summary
15. Partitioning
15.1. Learning About Partitioning15.2. Partitioning Tables15.2.1. RANGE partitioning15.2.2. LIST partitioning15.2.3. HASH partitioning15.2.4. KEY partitioning15.2.5. Composite partitioning15.2.6. Partition management commands15.2.6.1. Adding and dropping partitions15.2.6.2. Partition table maintenance15.2.7. Restrictions of partitioning15.3. MERGE Tables15.3.1. Creating a MERGE table15.3.2. Changing a MERGE table15.3.3. Advantages of MERGE tables15.4. Partitioning with MySQL Cluster15.5. Programmatic Partitioning15.6. Summary
16. Logging and Replication
16.1. Log Files16.1.1. Error log16.1.2. Binary logs16.1.3. Relay logs16.1.4. General and slow query logs16.1.5. Rotating logs16.1.5.1. flush logs16.1.5.2. max_binlog_size16.1.5.3. purge binary logs16.1.5.4. expire_logs_days16.1.6. Other methods of rotating16.2. Replication16.2.1. Setting up semisynchronous replication16.2.2. Statement-based, row-based, and mixed-based replication16.3. Replication Configurations16.3.1. Simple replication16.3.2. CHANGE MASTER statement16.3.3. More complex setups16.3.3.1. Single Master and Multiple Slaves16.3.3.2. Master and relay slave16.3.3.3. Master-Master Replication16.3.3.4. Circular replication16.3.4. Additional replication configuration options16.4. Correcting Data Drift16.4.1. mk-table-checksum overview16.4.2. mk-table-sync overview16.4.3. Putting this together16.5. Summary
17. Measuring Performance
17.1. Benchmarking17.1.1. mysqlslap17.1.2. SysBench17.1.2.1. CPU Test Mode17.1.2.2. I/O Test Mode17.1.2.3. mutex contention test mode17.1.2.4. Memory test mode17.1.2.5. OLTP test mode17.1.2.6. Compiling SysBench17.1.3. Benchmarking recommendations17.2. Profiling17.2.1. SHOW GLOBAL STATUS17.2.2. mysqltuner17.2.2.1. General statistics17.2.2.2. Storage engine statistics17.2.2.3. Performance metrics17.2.3. mysqlreport17.2.4. mk-query-profiler17.2.5. mysqldumpslow17.3. Capacity Planning17.4. Summary
IV. Extending Your Skills
18. Query Analysis and Index Tuning
18.1. Using EXPLAIN18.1.1. EXPLAIN plan basics18.1.2. Data access strategy18.1.2.1. Full table scan18.1.2.2. Full index scan18.1.2.3. Partial index scan18.1.2.4. Using more than one index18.1.2.5. Looking up nonunique, nullable index values18.1.2.6. Data access strategy for fulltext searching18.1.2.7. Joining and looking up nonunique index values18.1.2.8. Joins and unique index values18.1.2.9. Looking up unique index values18.1.2.10. Constant propagation18.1.2.11. Retrieve at most one record from a system table18.1.2.12. No Data accesss strategy18.1.3. EXPLAIN plan indexes18.1.4. Rows18.1.5. Extra18.1.6. Subqueries and EXPLAIN18.1.7. EXPLAIN EXTENDED18.2. EXPLAIN on Non-SELECT Statements18.3. Other Query Analysis Tools18.4. Optimizing Queries18.4.1. Factors affecting key usage18.4.2. Optimizer hints18.4.3. Adding an Index18.4.3.1. Optimizing away using filesort18.4.3.2. Optimizing away range checked for each record18.4.4. Optimizing away Using temporary18.4.5. Using an index by eliminating functions18.4.6. Non-index schema changes18.4.7. Batching expensive operations18.4.8. Optimizing frequent operations18.5. Summary
19. Monitoring Your Systems
19.1. Deciding What to Monitor19.2. Examining Open Source Monitoring19.2.1. Nagios19.2.2. Cacti19.2.3. Hyperic HQ19.2.4. OpenNMS19.2.5. Zenoss Core19.2.6. Munin19.2.7. Monit19.3. Examining Commercial Monitoring19.3.1. MySQL enterprise monitor19.3.2. MONyog19.4. Summary
20. Securing MySQL
20.1. Access Control Lists20.1.1. Wildcards and blank values20.1.2. Privilege and privilege levels20.2. Accessing the Operating System20.2.1. Database access20.2.2. Changing MySQL connectivity defaults20.2.3. Operating system login20.3. Securing Backups and Logs20.4. Data Security20.4.1. Data flow20.4.2. Encrypted connectivity20.4.2.1. Confirming encrypted connectivity support20.4.2.2. Creating the mysqld certificate20.4.2.3. Configure mysqld to enable encrypted connections20.4.2.4. Requiring encrypted connections20.4.3. Data security using mysql objects20.5. Creating Security Policies20.6. Summary
21. The MySQL Data Dictionary
21.1. Object Catalog21.1.1. SCHEMATA21.1.2. TABLES21.1.3. VIEWS21.1.4. COLUMNS21.1.5. STATISTICS21.1.6. TABLE_CONSTRAINTS21.1.7. KEY_COLUMN_USAGE21.1.8. REFERENTIAL_CONSTRAINTS21.1.9. TRIGGERS21.1.10. ROUTINES21.1.11. PARAMETERS21.1.12. EVENTS21.1.13. PARTITIONS21.2. System Information21.2.1. CHARACTER_SETS21.2.2. COLLATIONS21.2.3. COLLATION_CHARACTER_SET_APPLICABILITY21.2.4. ENGINES21.2.5. PLUGINS21.2.6. PROCESSLIST21.2.7. PROFILING21.2.8. GLOBAL_VARIABLES21.2.9. SESSION_VARIABLES21.2.10. GLOBAL_STATUS21.2.11. SESSION_STATUS21.3. Displaying Permissions21.3.1. COLUMN_PRIVILEGES21.3.2. TABLE_PRIVILEGES21.3.3. SCHEMA_PRIVILEGES21.3.4. USER_PRIVILEGES21.4. Storage Engine-Specific Metadata21.5. Custom Metadata21.5.1. Defining the plugin21.5.2. Compiling the plugin21.5.3. Installing the plugin21.6. Summary
22. Scaling and High Availability Architectures
22.1. Replication22.1.1. One read slave22.1.2. Promoting a new master22.1.2.1. Gather information22.1.2.2. Make sure the slave is caught up applying its relay logs22.1.2.3. Stop and reset the slave process22.1.2.4. Change configuration parameters22.1.2.5. Restart22.1.2.6. RESET MASTER22.1.2.7. Change any remaining slaves to replicate off the new master22.1.2.8. Slave promotion summary22.1.3. Many read slaves22.1.4. Master/master replication22.1.5. Circular replication22.2. SAN22.3. DRBD22.3.1. MySQL and DRBD setup22.4. MySQL Proxy22.4.1. Scaling read queries22.4.2. Automated failover22.4.3. Read/write splitting22.4.4. Sharding22.5. Linux-HA Heartbeat22.6. MySQL Cluster22.7. Connection Pooling22.8. memcached22.9. Summary
A. MySQL Proxy
A.1. Understanding MySQL ProxyA.1.1. Installing mysql-proxyA.1.2. Proxy backendsA.1.3. ScriptingA.1.4. Running MySQL proxyA.2. Learning Lua BasicsA.2.1. Basic script functionalityA.2.2. The proxy tokenizerA.3. Changing the Query BackendA.4. Changing and Injecting QueriesA.5. Understanding MySQL Proxy InternalsA.5.1. MySQL proxy tokensA.6. Summary
B. Functions and Operators
B.1. Using Aggregation FunctionsB.2. Using Bitwise OperatorsB.2.1. Combining multiple statementsB.3. Compressing and Encrypting DataB.3.1. CryptographicB.4. Testing, Logic, and Control FlowB.4.1. Control flowB.4.2. LogicalB.5. Using Server-Level FunctionsB.5.1. Keyword lockingB.6. Working with Data TypesB.6.1. Mathematical functions and numbersB.6.2. Date and time functionsB.6.3. String functions on TEXT typesB.6.3.1. Character sets and collationsB.6.3.2. CoercibilityB.6.4. Data type conversionsB.6.5. Working with XML
C. Resources
C.1. Finding Paid SupportC.2. Asking QuestionsC.2.1. MySQL forumsC.2.2. MySQL listsC.2.3. Bug reportsC.3. Sharing SolutionsC.4. Getting DocumentationC.4.1. Cheat sheets and quick referencesC.5. Learning MoreC.5.1. ArticlesC.5.2. PresentationsC.5.2.1. User Group MeetingsC.5.2.2. ConferencesC.5.3. Training/certification

Content preview from MySQL® Administrator's Bible

Chapter 21. The MySQL Data Dictionary

MySQL stores information about the data in the databases; this is called metadata. Much of this information is stored in the INFORMATION_SCHEMA database, following the SQL 2003 standard.

Tables in the INFORMATION_SCHEMA database are read-only, in-memory, and show data from various sources.

SHOW CREATE TABLE will show the tables as TEMPORARY, because they reside in memory and do not persist between mysqld restarts. INFORMATION_SCHEMA tables are called system views and they may be of different storage engine types. At the time of this writing all the system views are either the MEMORY, MyISAM, or Maria storage engine.

Regular SQL statements can be used to query them, though they have some special properties that other views do not have:

mysqldump will not export any information (data, schema) from INFORMATION_SCHEMA system views
There is no data directory for the INFORMATION_SCHEMA database
There is no .frm file associated with the INFORMATION_SCHEMA views. The definitions are hard-coded into the database.

The table definitions for the data dictionary are hard-coded into the source code, and loaded when mysqld starts. Unlike other databases, there is no directory in the datadir for the INFORMATION_SCHEMA database. All users have permission to see the INFORMATION_SCHEMA database; however, they can only see the objects they have permission to see. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470416914Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

MySQL® Administrator's Bible

by Sheeri K. Cabral, Keith Murphy

Chapter 21. The MySQL Data Dictionary

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.