MySQL Administrator’s Bible- P18

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:41

Thêm vào BST

Báo xấu

82
lượt xem 10
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator’s Bible- P18: 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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator’s Bible- P18

Resources C ■ Wiki (http://forge.mysql.com/wiki/Main Page) — Anyone can create and edit pages to share knowledge. Current information on the wiki includes conference presenta- tions, user-contributed documentation, articles, tutorials, and speciﬁcations. Getting Documentation The ofﬁcial documentation for MySQL is online at http://dev.mysql.com/doc. It includes links to: ■ The comprehensive MySQL Reference Manual, for downloading or online viewing. The reference manuals for MySQL 5.0 and 5.1 have been translated into several different for- mats and languages. Visit http://dev.mysql.com/doc/#refman to see a matrix of the documentation available. ■ MySQL GUI Tools documentation ■ Expert Guides for internals, the Cluster API, and the test framework ■ The sakila, world, employee, and menagerie example databases ■ Community contributed documentation ■ Documentation about the MySQL documentation At the time of this writing, the ofﬁcial reference manual for MySQL 6.0 is only available in English, and can be viewed online at http://dev.mysql.com/ doc/refman/6.0/en/index.html or downloaded from the main documentation site at http://dev.mysql.com/doc/#refman. Cheat sheets and quick references ■ MySQL 6.0 function and statement cheat sheets (www.visibone.com/sql/) — From VisiBone, full-color quick-reference guides to all of the MySQL functions and state- ments. Available for free download or purchase laminated pages or posters. Indicates whether a function or statement is part of the ISO/ANSI SQL:2003 standard. ■ EXPLAIN cheat sheet (http://www.pythian.com/blogs/960/explain- cheatsheet) — Developed by author Sheeri K. Cabral with formatting help from Dave Edwards, the EXPLAIN cheat sheet is a freely downloadable reference for quick or ofﬂine lookup of what the different ﬁelds of output from EXPLAIN means ■ Common DBA tasks cheat sheet (http://en.wikibooks.org/wiki/MySQL/ CheatSheet) — From Wikibooks, a cheat sheet containing commands for sample queries, metadata access, privilege administration, data types, resetting the root password, and more. ■ Metadata access comparison (http://sqlzoo.net/howto/source/z.dir/ i12meta.xml) — Shows how to access metadata such as table names, column names, and the software version in many vendors’ DBMS, including MySQL. A similar cheat 817
C Resources sheet, formatted for printing, is available at http://mentalaxis.com/ref/mysql cheatsheet.pdf. Learning More There are many resources for further education about MySQL. The most recent and up-to-date learning opportunities can be found online or in person, in the form of articles and presenta- tions. Articles ■ Planet MySQL (www.planetmysql.org) — An aggregated feed of blog posts from the MySQL Community. The top 30 most active contributors are highlighted, so if you prefer you can follow the links and choose to follow a few individual blogs. ■ MySQL Magazine (www.mysqlzine.net) — Edited by Keith Murphy, MySQL Maga- zine’s inaugural issue appeared in the summer of 2007. MySQL Magazine is produced four times annually, with current and back issues freely available online as printable PDFs. ■ MySQL Developer Zone articles (http://dev.mysql.com/tech-resources/ articles) and news (http://dev.mysql.com/tech-resources/news) — Collec- tions of tech news and articles from and for MySQL developers ■ MySQL Newsletter (www.mysql.com/news-and-events/newsletter) — The monthly newsletter ‘‘with articles about new products, new features, training, security issues, known bugs, and events of interest to the MySQL community.’’ Current and back issues are freely available online. ■ MySQL White Papers (www.mysql.com/why-mysql/white-papers) — White papers about MySQL. ■ Sun’s list of MySQL Resources for System Administrators (www.sun.com/ bigadmin/topics/mysql) — Sun’s BigAdmin System Administration Portal contains a section on MySQL resources for system administrators. ■ HowToForge articles (www.howtoforge.com/howtos/mysql) — How To articles for MySQL tasks. Presentations There are many resources for seeing presentation slides and videos available: ■ MySQL presentations from before 2007 (http://dev.mysql.com/tech- resources/presentations/) — Presentations from conferences before 2007 ■ Presentations from the 2007 MySQL User Conference and Expo (www. technocation.org/content/2007-mysql-user-conference-and-expo- presentations-and-videos) — A comprehensive list of presentations, slides, notes, and videos from the 2007 MySQL User Conference and Expo 818
Resources C ■ Presentations from the 2008 MySQL User Conference and Expo (http:// forge.mysql.com/wiki/MySQLConf2008Notes) — A comprehensive list of presentations, slides, notes, and videos from the 2008 MySQL User Conference and Expo ■ Presentations from the 2009 MySQL User Conference and Expo (http://forge. mysql.com/wiki/MySQLConf2009Notes) — A comprehensive list of presentations, slides, notes, and videos from the 2009 MySQL User Conference and Expo The MySQL Forge Wiki (http://forge.mysql.com/wiki) will link to future con- ferences, so make sure to look there. ■ MySQL live webinars (www.mysql.com/news-and-events/web-seminars) — MySQL offers free live Webex webinars containing technology overviews ■ MySQL on-demand webinars (www.mysql.com/news-and-events/on-demand- webinars/) — MySQL makes the most popular live webinars available for playback any time ■ MySQL University (http://forge.mysql.com/wiki/MySQL University) — In- depth technical sessions given by leading MySQL developers and community members ■ MySQL presentations at Technocation (http://technocation.org/category/ areas/presentationsvideos) — Technocation, Inc. is a not-for-proﬁt organization providing educational resources for IT professionals. They host many video presentations and provide slides that can be freely downloaded or streamed for viewing online. ■ Popular video hosting sites — YouTube and Google Video are the most popular video sharing sites in the United States. However, there are many more video-sharing sites, and most have search capabilities. Simply searching for ‘‘mysql’’ on a video-sharing site can turn up presentations and video tutorials about MySQL. ■ YouTube MySQL videos (www.youtube.com/results?search query=mysql) — YouTube limits video uploads to 10 minutes, so the videos returned by this search are usually short. The exception is videos on YouTube that come from Google Video. ■ Google Video MySQL presentations (http://video.google.com/video search?q=mysql) — Do a basic search for videos containing the keyword ‘‘mysql’’. User group meetings There are MySQL user groups throughout the world. Information about starting a user group as well as what user groups currently exist can be found at http://forge.mysql.com/wiki/ Category:MySQLUserGroups. Conferences Conferences are an excellent place to see live presentations about MySQL and provide opportu- nities to meet other MySQL DBAs while learning. 819
C Resources ■ MySQL Conference and Expo (www.mysqlconf.com) — Traditionally held in April in Santa Clara, California, the MySQL Conference and Expo is the premier conference for learning about MySQL and networking with other MySQL users. ■ OSCon (http://conferences.oreillynet.com/oscon/) — OSCon, or the Open Source Convention, is traditionally held during July in Portland, Oregon. There are usu- ally several MySQL-related presentations. ■ Various camps — MySQL Camp is a free unconference for MySQL, held annually. As MySQL Camp is organized by the MySQL Community and is not a commercial event, the date and location changes often. MySQL Camp dates will be announced in many places, including Planet MySQL blogs (http://planetmysql.org). OpenSQL Camp (www.opensqlcamp.org) is a free unconference for open source databases, including MySQL. Training/certiﬁcation The training and certiﬁcation opportunities that MySQL offers are listed at www.mysql.com/ training. MySQL maintains a list of third-party partners offering training at http:// solutions.mysql.com/solutions/partners/training. 820
Symbols mysqladmin, 62–66 phpMyAdmin, 69–71 -- (double dash), 88 Query Browser, 71–74 # (pound), 89–90 SQLyog, 66–69 % (modulus operator), 503–504 summary, 83–84 || (string concatenation) Workbench, 80–83 MySQL support, 107 accounts, user SQL mode deﬁnitions, 210 debugging problems, 490–494 \ (backslash) managing, 478–487 escape characters, 91–93 ACID (atomicity, consistency, isolation and durability) naming limitations and quoting, 93–94 compliance . (dot), 95–97 deﬁned, 319–320 ” (double quotation mark) PBXT, 410 naming limitations and, 93 understanding, 320–322 SQL mode deﬁnitions, 204 ACLs (Access Control Lists) ! (exclamation point), 89 deﬁned, 474–475 ? (question mark), 49 securing MySQL, 649–653 ; (semi-colons), 60 security with stored routines, 256 ’ (single quotation mark), 93 activity logging, 520–522 \. (source), 54–55 administration 32-bit systems Administrator tool, 74–80 vs. 64-bit systems, 12 log. See logs choosing hardware, 349–350 measuring performance. See performance measurement \! command, 62 with mysqladmin, 62–66 64-bit systems server tuning. See MySQL server tuning vs. 32-bit systems, 12 storage engine. See storage engines choosing hardware, 349–350 user management. See user management OS architecture, 352 agent-based systems, 635–636 agentless systems, 635–636 aggregate functions A NULL values and, 211 abstraction, view, 307–308 overview, 783–784 access. See also security alerts, monitoring. See monitoring systems data access strategy, 596–606 algorithms database, 654 MyISAM index buffer, 366 user account problems, 491 partitioning, 496–497 Access Control Lists (ACLs). See ACLs (Access Control view, 309–310 Lists) aliases accessing MySQL creating trigger, 244 Administrator, 74–80 extension, 115 command-line client tool, 52–62 table, 592–594 with command-line tools, 49–52 ALLOW_INVALID_DATES, 204 823
A Index alpha releases, 11–12 atomicity, consistency, isolation and durability (ACID) ALTER EVENT, 295–296 compliance. See ACID (atomicity, consistency, ALTER ROUTINE, 270–271 isolation and durability) compliance ALTER TABLE attributes adding/dropping indexes, 223–225 binary data types, 171 adding/dropping partitions, 507–509 changing events, 296 creating/dropping key constraints, 231–237 character string type, 164–166 deﬁned, 421 datetime type, 191–192 extensions, 115–118 ENUM and SET data types, 198–200 ALTER VIEW, 316–317 national character string types, 167 ANALYZE TABLE numeric type, 177–180, 181–182 deﬁned, 156 auditing, trigger-based, 664–665 authentication query analysis, 615 data security, 656–665 analyzing queries. See query analysis lack of client support, 491–492 anonymous users managing user. See user management creating, 35–36 privileges. See privileges removing, 39 AUTO_INCREMENT ANSI, 204 master/master replication, 537, 735 ANSI QUOTES, 204 numeric type attributes, 177–178 Apache, 360 table deﬁnition extensions, 147 API (Application Programming Interface) autocommit mode, 157, 324–325 memcached programming libraries, 436 auto-completion security with stored routines, 256 mysql commands, 58–59 Apple OS. See Mac OS SQLyog, 67 Application Programming Interface (API) auto-generated data, 551 memcached programming libraries, 436 automated failover security with stored routines, 256 deﬁned, 729 application-level partitioning, 514 with Linux-HA heartbeat, 742–744 architecture with MySQL Proxy, 740–741 Falcon, 402–403 automated monitoring. See monitoring systems high availability. See high availability architectures automated startup, 42 operating system, 352–353 automatic updates, 187–188 PBXT, 410–411 Axmark, David, 4 Archive backing up, 462–463 understanding, 417–419 B backends archive packages, 18–20 changing query, 763–764 arguments MySQL Proxy, 751–755 bitwise operators, 784–787 backslash (\) INOUT to stored procedure, 261–262 escape characters, 91–93 Lua basics, 758–759 naming limitations and quoting, 93–94 mysqladmin, 62–66 backup stored routines, events and triggers, 242 commercial options, 464–467 arithmetic MySQL Proxy tokens, 773 comparing storage engines, 377–378 articles on MySQL, 818 copying databases, 467 assertions, 108 event, 300 asynchronous replication ﬁle system snapshot, 451–452 deﬁned, 524–525 frequency, 443–444 multi-master, 538–539 LVM, 455–460 atomicity, 321 maatkit parallel dump/restore, 450–451 824
Index C methods, 445–446 bitwise operators, 784–787 MySQL, 439–441 black box queries, 255–256 mysqldump, 446–450 Blackhole mysqlhotcopy, 462–463 complex replication, 536 online, 460–462 replication support, 527 phpMyAdmin, 70 understanding, 419–420 SAN and Microsoft VSS, 460 blank values, 650 security, 656 BLOB SELECT INTO OUTFILE, 450 data types, 168–170 simple replication, 530–531 indexing, 226–227 SQL Administrator, 79 block devices, 738–739 stored routine, 286–287 blogging, 6 summary, 472 bogging down, 585 trigger, 252–254 Boolean types, 180–183 uses for, 441–443 Bouman, Roland, 716, 744 what to and where to, 445 BTREE indexes ZFS, 453–454 deﬁned, 221–222 bad passwords, 490–491 order, 225–226 batching redundant, 231 executing non-interactively, 54–55 types, 228–229 expensive operations, 628–629 buffer pools, 579 frequent operations, 629–631 buffers battery-backed write cache, 351 deﬁned, 356–357 BEGIN, 322 InnoDB conﬁguration, 385 BEGIN WORK, 322 InnoDB status, 389 benchmarking InnoDB storage engine options, 367–369 deﬁned, 545–546 MyISAM, 365–366 with mysqlslap, 547–552 proﬁling, 574 overview, 546–547 storage engine support, 377–378 recommendations, 565–566 bugs. See also debugging with SysBench. See SysBench BIT_AND NULL, 784 Best Support For Multilingualism, 35 reports, 816 beta releases, 11–12 building queries, 67–69 binary collations character string type attributes and, 165 deﬁned, 98 C binary ﬁle vs. source code, 10 CA (Certiﬁcate Authority) binary large object string types, 168–170 command-line tool options, 53 binary logs encrypted connectivity, 662 backing up, 445 cache hit ratio, 365–366 crash recovery, 468–471 caching deﬁned, 518–519 implementing tables, 424–427 master/master replication and, 736 memcached, 434–438 rotating, 522–524 overview, 423–424 binary MySQL Proxy distributions, 750–751 PBXT conﬁguration, 412 binary packages, 20–24 proﬁling, 576–577 binary plug-ins, 415 query cache, 427–434 bin-log, 732 scaling with memcached, 747 BIT summary, 438 NULL and, 784 Cacti, 637–638 sizes and ranges, 173–174 CALL, 259–260 825
C Index Callaghan, Mark, 525 SHOW commands, 136–137 canceling queries, 59–60 table deﬁnition extensions, 147 capacity planning, 585 CHARACTER_SETS, 695–696 cardinality, index, 615 characters CASCADE, 234 bit functions, 786 cascading replication, 535–536 case-sensitivity, 90–91 CASE, 282–283 escape, 91–93 case-sensitivity index length, 226–228 binary collations, 98 Lua basics, 757 escape characters, 92 national string types, 166–168 Falcon tablespace, 409 sets and collations, 98–105 language structure, 90–91 string types, 160–166 query cache, 428 wildcards, 475–476 CAST cheat sheets, 817–818 data types for, 811 check constraints deﬁned, 102–105 MySQL deviations, 111–112 MySQL support, 107 simulating in views, 310–312 catalogs CHECK TABLE, 150–153 managing with Administrator, checksums 80 correcting data drift, 540–542 MySQL support, 107 table deﬁnition extensions, 147–148 object. See object catalog table maintenance extensions, 156 catch-all partitions, 499 child tables, 234 Certiﬁcate Authority (CA) chilling, 406 command-line tool options, 53 ciphers, 53 encrypted connectivity, 662 circular replication certiﬁcate ﬁles, 661–662 deﬁned, 537–539 certiﬁcation, MySQL, 820 scaling with, 736–737 CHANGE MASTER, 534 cleanup changelogs, 45–46 I/O test mode, 556–557 changing OLTP test mode, 563–565 events, 295–296 SysBench commands, 553 injecting queries and, 764–766 clear command MERGE tables, 512–513 deﬁned, 59 query backend, 763–764 mysqladmin, 63 stored routines, 270–271 client tool triggers, 246–247 MySQL access, 52–62 views, 316–317 setting characters sets and collation, 103 CHAR, 160–162 closing cursors, 287 character objects Cluster, MySQL. See MySQL Cluster character string types, 160 COALESCE PARTITION, 508–509 national character string types, 166 code Character Set screen, 35 language structure. See language structure character sets in MySQL community, 7 ALTER TABLE extensions, 116 source, 4 changing event, 299 source vs. binary ﬁle, 10 changing stored routine, 264 coercibility, 810–811 changing trigger, 249–250 cold backups character string type attributes and, 165–166 deﬁned, 441 deﬁned, 98–105 location, 445 MySQL support, 108 tools, 446 826
Index C COLLATION_CHARACTER_ transactional statement extensions, 156–158 SET_APPLICABILITY, 696–697 triggers. See triggers COLLATIONS, 696 user management, 478–487 collations comments changing event, 299 applying to events, 295 changing stored routine, 264 language structure, 88–90 changing trigger, 249–250 Lua, 757 character string type attributes and, 165–166 stored routine options, 266 language structure, 98–105 table deﬁnition extensions, 148 MySQL support, 108 commercial backup options, 464–467 SHOW commands, 137 commercial monitoring systems, 644–646 table deﬁnition extensions, 148 COMMIT COLUMN_PRIVILEGES, 712–713 deﬁned, 322 COLUMNS, 676–679 extensions, 156–158 columns community, 6–7 ALTER TABLE extensions, 118 compactor threads, 411 privileges, 482 company background, 4–5 SHOW commands, 137 comparison operators stored routine warning, 257–259 deﬁned, 789–791 Comma Separated Value (CSV), 420–421 MySQL support, 106–107 COMMAND in PROCESSLIST, 699–700 compilation command-line tools plugin, 722–723 accessing MySQL with, 49–52 SysBench, 565 binary log, 519 Complete installation, 21–22 client tool, 52–62 Complete package, 21 Maria, 401 complex replication, 534–539 memcached, 435 composite INTERVAL units, 801–802 mysqladmin, 62–66 composite keys, 220 proxy script behavior, 759 composite partitioning starting and stopping MySQL as service, 27 deﬁned, 497 starting and stopping MySQL from Windows, 25–26 working with, 504–507 table maintenance, 154 compound foreign key constraints, 232 using with Query Browser, 73 compression commands Archive, 417–418 events. See events command-line tool options, 53 FLUSH TABLES WITH READ LOCK, 452 commercial backup options, 466–467 INFORMATION_SCHEMA database system view. See functions, 788–789 MySQL Data Dictionary ibbackup, 464 LVM, 455–460 with myisampack, 381–382 MySQL deviations. See deviations concatenation, 107 mysqlhotcopy, 462–463 concurrency vs. options, 56 Concurrent Connections screen, 34 partitioning, 507–510 multi-version control, 335 replication. See replication MyISAM, 378–379 rotating logs, 522–523 MyISAM inserts, 379–380 SHOW, 136–147 conditions, stored routine, 275–281 SHOW ENGINE InnoDB STATUS, 385–390 conferences, 819–820 SHOW ENGINE PBXT STATUS, 414–415 conﬁguration storage engine, 421–422 Archive, 417–419 SysBench, 553 Conﬁguration Wizard on Windows, 31–36 table maintenance extensions, 150–156 CSV, 420–421 827
C Index conﬁguration (continued) control ﬂow encrypted connectivity, 663 functions, 791 Falcon, 403–409 stored routine, 282–284 FEDERATED storage engine, 415–417 conversion initial MySQL, 29–31 datetime types, 188, 190–191 InnoDB variables, 384–386 functions, 810–812 Maria, 396–401 CONVERT memcached, 435 data types for, 811 MEMORY, 395–396 deﬁned, 102 MyISAM, 380 MySQL support, 107 MySQL Proxy backends, 751–755 copying databases, 467 PBXT, 411–414 corruption post-install on Unix, 36–39 hardware, 352 query cache, 429–433 table maintenance, 150–154 replication, 529–539 count caching, 424–427 securing, 40–41 CPU test mode, 554 slave promotion, 732 crash recovery. See recovery Windows PATH variable, 42–44 CREATE, 63 conﬁguration ﬁles. See option ﬁles CREATE FUNCTION, 269 Conﬁrmation screen CREATE PROCEDURE, 268 conﬁguration options, 36 CREATE TABLE deﬁned, 24 deﬁned, 421 connection pooling, 746–747 RANGE partitioning, 497–502 connections creating changing query backend, 763–764 basic stored functions, 268–269 command-line tool options, 51–52 events, 291–292 debugging account problems, 492 extensions, 118–119 encrypted, 659–664 Falcon tablespaces, tables and indexes, 409 indexes, 223–225 health monitoring, 78 key constraints, 231–237 memcached, 437 MERGE tables, 511–512 MySQL deviations, 109 stored routines, 256–257 MySQL Proxy backends, 751–755 triggers, 243–244 mysqladmin commands, 63–64 users, 478–479 proﬁling, 577–578 views, 302–303 Query Browser, 72–73 cryptographic functions, 788–789 securing operating systems, 654–655 CSV (Comma Separated Value), 420–421 server maintenance extensions, 130 Ctrl functions, 58 SQL Administrator, 76 cursors SQLyog, 66–67 mysql commands, 58 table deﬁnition extensions, 148 updatable, 109 transaction. See transactions using, 287–289 consistency, 321 custom installation, 22 consistent backups, 440–441 custom queries, 551–552 consistent snapshot drivers, 461–462 custom tables, 551–552 constant propagation, 603–605 constants, MySQL Proxy, 766–771 constraints D key. See key constraints daemons MySQL deviations, 111–112 Linux-HA heartbeat, 742–744 security with stored routines, 256 memcached, 434–438 simulating check in views, 310–312 MySQL Proxy. See MySQL Proxy 828
Index D running multiple on same server, 360 databases security, 654–656 access, 654 daily backups, 444 changing with use, 57 data client tool options, 52–53 access strategies, 596–606 copying to backup, 467 backing up. See backup links, 416 changes and foreign key constraints, 234 management tools. See GUI (Graphical User Interface) constraints. See key constraints tools converting character sets, 104 monitoring. See monitoring systems drift, 540–543 partitioning. See partitioning exchange, 420 privileges, 481–482 insertion with phpMyAdmin, 70 setting characters sets and collation, 101 logs, 412–414 SHOW CREATE commands, 137–139 nodes, 744–746 dates querying. See queries DATE data types, 183–192 recovery. See recovery SQL mode deﬁnitions, 204, 207–209 security, 656–665 datetime types synchronization. See synchronization deﬁned, 183–193 table maintenance extensions, 150–156 functions, 797–806 transfer, 354 DAY_TIME data types, 193–194 DDL (Data Deﬁnition Language) statements, 322 Data Deﬁnition Language (DDL) statements, 322 deadlocks Data Dictionary, MySQL. See MySQL Data Dictionary Falcon parameters, 406 Data Manipulation Language (DML). See DML (Data InnoDB status, 389 Manipulation Language) page-level and row-level, 341 Data Manipulation Statements (DMS), 575 transaction, 336–343 data types Debian binary large object string, 168–170 rotating logs, 524 Boolean, 180–183 tuning, 353 character string, 160–166 debugging choosing SQL modes, 201–211 Falcon parameters, 403–404 conversions, 810–812 mysqladmin commands, 63 date and time functions, 797–806 with SHOW ENGINE InnoDB STATUS, 389–390 datetime, 183–193 stored routine conditions and handlers, 275–281 ENUM and SET, 195–200 with system command, 62 FULLTEXT index support, 238 user account problems, 490–494 indexing, 226–227 DECIMAL, 172 interval, 193–194 Decision Support, 34 mathematical functions and numbers, 795–797 DECLARE MySQL deviations, 106 stored routine conditions, 281 MySQL Proxy tokens, 773 using cursors, 287–288 national character string, 166–168 Dedicated MySQL server machine, NULL values, 211–212 32 numeric, 170–180 default backup drivers, 461–462 optimal for existing data, 212–216 default behavior, 203 overview, 159 DEFAULT values string functions on TEXT types, 806–810 character string type attributes, 165 summary, 217 datetime type attributes, 191–192 XML functions, 812 ENUM and SET attributes, 198 Database Explorer, 72 numeric type attributes, 177 Database Usage screen, 33 defense in depth, 649 829
D Index deﬁners diagnostics management, 110 changing trigger, 250–252 diagrams, Workbench, 82–83 specifying view, 306–307 Dictionary, MySQL Data. See MySQL Data Dictionary deﬁning views directives abstraction and simpliﬁcation, 307–308 conﬁguration ﬁle, 29 changing deﬁnitions, 316–317 in server option ﬁle, 362 deﬁner speciﬁcation, 306–307 directories limitations and unexpected behavior, 304–305 copying databases to backup, 467 overview, 302–303 creating LVM, 458–459 performance, 308–313 ibbackup, 464–465 security and privacy, 305–306 MySQL server archive package, 19 updatable, 313–316 partitioning, 354 defragmentation SQL mode deﬁnitions, 206 InnoDB tablespace, 390 table deﬁnition extensions, 148 partition table, 509 trigger storage, 252–254 query cache, 433–434 dirty reads table maintenance extensions, 154–155 deﬁned, 326 DELAY_KEY_WRITE, 148 READ UNCOMMITTED, 329–330 delaying inserts, 121–122 disabled event schedulers, 290 DELETE disabled events, 294–295 batching, 628–629 disaster planning, 471–472 DML extensions, 120–121 disaster recovery. See recovery EXPLAIN on, 614 disk space, 155 foreign key constraints and, 234 displaying permissions. See permissions display invoking triggers, 242–243 DISTINCTROW, 128 mysql commands, 58 distributed caching, 434 rows from MEMORY tables, 395 distributed replicated block devices (DRBDs), 738–739 slave servers and recovery, 443 DML (Data Manipulation Language) updatable views, 313–316 EXPLAIN on non-SELECT statements, 614 updating indexes, 223 extensions, 119–124 delimiters mysql commands, 60 using to update views, 313–314 stored procedure, 257 DMS (Data Manipulation Statements), 575 trigger, 245 documentation DES keys, 130 MySQL, 817–818 DESC keyword, 226 in MySQL community, 6 DESCRIBE, 109 domain support, 108 descriptor areas, 109 dot notation, 95–97 design with MySQL Workbench, 80–83 DOUBLE, 174–176 destination hosts, 467 double dash (--), 88 Detailed Conﬁguration, 31–32 double quotation mark (”) determinism naming limitations and, 93 caching SELECT statements, 427–428 SQL mode deﬁnitions, 204 stored routine options, 266–267 double-buffering, 357 Developer machine, 32 downloading MySQL, 12 deviations DRAM-based drives, 351–352 check constraints, 111–112 DRBDs (distributed replicated block devices), privileges and permissions, 110 738–739 transaction management, 110–111 drivers, backup, 461–462 understanding, 105–110 DROP, 63 upsert statements, 112–114 DROP TABLE, 422 830
Index E dropping event, 297–298 events, 291–292 installation problems, 26 extensions, 124–125 troubleshooting MySQL servers, 47–48 indexes, 223–225 ERROR_FOR_DIVISION_BY_ZERO, 204–205 key constraints, 231–237 errors MERGE tables, 513 creating events, 292 partitions, 507–509 determinism and stored routines, 267 stored routines, 261 foreign key constraint requirements, 235–237 triggers, 244–245 InnoDB status, 389 users, 478–479 myisam_ftdump, 383 views, 303 MySQL deviations, 105 dual licensing model, 4 partition table restrictions, 510 dual-master replication, 735–736 SHOW commands, 137, 140 dumps source command, 55 deﬁned, 446–450 SQL modes, 201–203 maatkit parallel restore and, 450–451 stored routine, 274–275 mysqldumpslow, 583–584 stored routine conditions and handlers, 275–281 duplicate key errors, 117–118 stored routine warning, 257–259 durability, 321–322 escape characters, 91–93 dynamic shared objects, 722–723 escape sequences, 91–93 dynamic variables Essentials package, 21 assigning values to, 134–135 EVENTS, 691–693 server tuning, 371–372 events slow query log management, 521 after last execution, 296–297 backup and storage, 300 caching with Event Scheduler, 424–426 E changing, 295–296 editing with Query Browser, 71 creating and dropping, 291–292 ego command, 61 Event Scheduler, 708 embedded declarations, 108–109 ﬁnding all, 295 enabled events, 294–295 limitations, 299 encryption logging, 297–298. See also logs backup, 466–467 runtime behavior, 298–299 connectivity, 659–664 SHOW commands, 140–141 data ﬂow security, 658 SHOW CREATE commands, 138 functions, 788–789 start and end times for periodic, 293–294 end of life versions, 569 status, 294–295 end times, 293–294 stored routines, triggers and, 241–242 endian formats, 379 turning on scheduler, 289–290 ENGINES, 697 exclamation point (!), 89 engines execution SHOW commands, 139–140 after event, 296–297 SQL mode deﬁnitions, 206 MySQL deviations, 110 storage. See storage engines plans, 595 table deﬁnition extensions, 148 SysBench, 552 Enterprise, MySQL, 5–6 exiting mysql, 56 Enterprise Monitoring, MySQL, 644–645 expensive operation batching, 628–629 Entity Relationship (ER) diagram, 82–83 expire_logs_days, 523 ENUM, 195–200 EXPLAIN error logs data access strategy, 596–606 deﬁned, 517–518 EXTENDED, 612–613 831
E Index EXPLAIN (continued) Falcon extra, 608–611 replication support, 527 on non-SELECT statements, 614 storage engine options, 370–371 overview, 590–595 understanding, 401–409 plan indexes, 606–607 FALSE, 94–95 rows, 607–608 FEDERATED storage engine, 415–417 subqueries and, 611–612 FETCH, 288–289 EXPLAIN PARTITIONS, 501–502 ﬁelds exports ALTER TABLE extensions, 116 logical backups, 439–440 ENUM and SET, 197 with mysqldump, 449–450 EXPLAIN. See EXPLAIN with SELECT... INTO OUTFILE, 126–127 INFORMATION_SCHEMA database system view. See ext2, 355–356 MySQL Data Dictionary ext3, 355 key constraints, 220–221 EXTENDED EXPLAIN, 612–613 master-master replication and auto increment, 537 extended INSERT, 448 optimal data type for existing data, 211–216 extended status, 63 redundant indexes, 230–231 eXtensible Markup Language (XML) setting characters sets and collation, 101–102 functions, 812 SHOW SLAVE STATUS, 532–533 LOAD XML INFILE, 123–124 SQL mode deﬁnitions, 206 extensions triggers aliases, 244 additional, 127–129 ﬁle systems aliases, 115 commercial backup options, 464–467 ALTER TABLE, 115–118 LVM, 455–460 CREATE, 118–119 SAN and Microsoft VSS, 460 DML, 119–124 DROP, 124–125 setting characters sets and collation, 103 LIMIT, 125–126 snapshots, 451–452 PLUGINS system view, 697–698 testing with SysBench, 555–557 SELECT, 126 ZFS, 453–454 SELECT...INTO OUTFILE, 126–127 ﬁles server maintenance, 129–131 comparing storage engines, 377–378 SET and user-deﬁned variables, 131–135 conﬁguration, 29–31 SHOW, 135–147 copying databases to backup, 467 SHOW GLOBAL STATUS, 566–568 CSV, 420–421 summary, 158 ibdata, 390–392 table deﬁnition, 147–150 InnoDB status, 389 table maintenance, 150–156 limit of open, 357–358 transactional statement, 156–158 option. See option ﬁles using, 114–115 PBXT growth variables, 414 Extra shared tablespace, 392–393 EXPLAIN, 608–611 systems and partitions, 353–356 optimizing away Using temporary, 620–623 tests, 556 Using filesort, 618–620 trigger storage, 252–254 ﬁnding events, 295 F triggers, 252 failover ﬁxed-width strings deﬁned, 729 binary data types, 171 master/master replication and, 736 character string types, 160 with MySQL Proxy, 740–741 national character string types, 166 832
Index G FLOAT FULLTEXT sizes and ranges, 174–176 ALTER TABLE extensions, 116 SQL mode deﬁnitions, 210 index types, 229–230, 237–239 ﬂoating point, 174 myisam_ftdump and, 382–383 ﬂow, data, 657–658 searching, 601 ﬂow control speeding lookups with, 222 functions, 791 functional partitioning, 514 stored routine, 282–284 functions aggregation, 783–784 FLUSH PRIVILEGES, 39 ALTER TABLE, 115–118 FLUSH QUERY CACHE, 434 BENCHMARK, 546–547 FLUSH TABLES WITH READ LOCK, 452 bitwise operators, 784–787 ﬂushing compression and encryption, 788–789 commands, 63 CREATE FUNCTION syntax, deﬁned, 129–131 269 Falcon parameters, 406 creating basic stored, 268–269 InnoDB storage engine options, 367–369 data type conversions, 810–812 logs, 522–523 date and time, 797–806 FOR UPDATE, 127 datetime type, 188–190 foreign key constraints DML extensions, 119–124 creating and dropping, 232–234 invoking stored functions, 269–270 deﬁned, 219–231 mathematical numbers and, 795–797 InnoDB status, 389 MySQL deviations, 105–110 InnoDB support, 384 MySQL Proxy. See MySQL Proxy KEY_COLUMN_USAGE, 682–684 MySQL Proxy tokens, 774 MySQL support, 107 proxy tokenizer, 760–763 partition table restrictions, 510 server-level, 792–794 SHOW commands, 141 REFERENTIAL_CONSTRAINTS, 684 SHOW CREATE commands, storage engine support, 377–378 138 TABLE_CONSTRAINTS, 681–682 SQL modes, 203–204 formats stored. See stored routines installation, 9–11 string on TEXT types, 806–810 interval types, 194 testing, logic and control ﬂow, 789–792 row, 149 using index by eliminating, 623–626 forums, MySQL, 814–815 XML, 812 fragmentation in ﬁle system partitions, 354 partition table, 509 G query cache, 433–434 g++ plugin compilation, 723 GA (generally available) releases, 5, 11–12 reducing with batching, 628–629 general query logs, 520–522 table maintenance extensions, 154–155 General Statistics, 569 FreeBSD 7.0, 453–454 generally available (GA) releases, 5, 11–12 frequency, backup, 443–444 global privileges, 481 full backup GLOBAL tables, 106 deﬁned, 440 GLOBAL_STATUS, 711 frequency, 443–444 GLOBAL_VARIABLES, 710 full index scans, 596–598 GNU/Linux, 13–16 full table scans go command, 60–61 deﬁned, 596 gopher threads, 403 optimization, 616–618 grandfather-father-son backup rotation, 444 833
G Index GRANT help deﬁned, 110 command-line tools, 49 privileges and privilege levels, 651–653 resources, 813–820 SHOW commands, 141 SysBench commands, 553 SQL mode deﬁnitions, 206 high availability architectures user management, 481–487 deﬁned, 442 grant tables DRBDs, 738–739 deﬁned, 474–475 Linux-HA heartbeat, 742–744 initializing, 36–37 MySQL Proxy, 739–742 system tables, 476–478 overview, 727–728 Graphical User Interface (GUI) tools. See GUI (Graphical with replication, 728–737 User Interface) tools SANs, 737–738 graphing summary, 748 with Cacti, 637–638 HIGH_NOT_PRECEDENCE, 205 Hyperic HQ, 638–640 hit ratio, 431 monitoring systems, 635 holes, 433 with MONyog, 645–646 horizontal partitioning with Munin, 642–643 deﬁned, 495–496 MySQL Enterprise Monitoring, 644–645 programmatic, 514 OpenNMS, 640–641 horizontal scaling, 727 GROUP BY, 620–621 host strings grouping deﬁned, 473–474 MySQL support, 108 wildcards in, 475–476, 650 SQL mode deﬁnitions, hosts 209–210 command-line tool options, 51 GUI (Graphical User Interface) tools ﬂushing, 63 Administrator, 74–80 server maintenance extensions, 130 phpMyAdmin, 69–71 hot backups Query Browser, 71–74 deﬁned, 441 SQLyog, 66–69 ﬁle system snapshots, 451–452 Workbench, 80–83 InnoDB, 464–466 location, 445 online, 460–462 H tools, 446 Hyperic HQ, 638–640 handlers, stored routine, 275–281 hard drives choosing, 349–352 failure and backup, 441 I ﬁle system partitions, 353–356 ibbackup, 464–466 hardware, 349–352 ibdata ﬁles, 390–392 Harrison, Guy, 282 IDENTIFIED BY, 479 HASH partitioning identiﬁers deﬁned, 497 dot notation, 95–97 working with, 503–504 naming limitations and quoting, 93–95 hashes IF, 282 deﬁned, 221–222 IF EXISTS, 124–125 index types, 228–229 IF NOT EXISTS, 118–119 one-way, 656–657 IGNORE query cache, 428 ALTER TABLE extensions, 117–118 redundant indexes, 230 DML extensions, 119 health monitoring. See monitoring systems IGNORE_SPACE, 206 834
Index I implicit COMMIT, 322 Tablespace screen, 34 implicit transactions, 156 understanding, 384–394 inconsistent reads, 331 INOUT arguments, 261–262 incremental backups inputs deﬁned, 440 datetime types, 185–187 frequency, 443–444 stored routine options, 266–267 what to back up, 445 INSERT index types batching, 628–629 creating and dropping key constraints, 231–237 DML extensions, 121–122 FULLTEXT, 237–239 EXPLAIN on, 614 keys and, 219–221 extended, 448 MySQL deviations, 106 foreign key constraints and, 234 speeding up lookups, 221–231 invoking triggers, 242–243 summary, 239–240 keys and indexes, 220 index_merge, 599–600 with phpMyAdmin, 70 indexes table deﬁnition extensions, 148–149 adding to queries, 616–620 updatable views, 313–316 ALTER TABLE extensions, 116–117 updating indexes, 223 binary log ﬁles, 518 upsert statements, 112–114 INSERT DELAYED, 704–705 caching, 410–411 insert ratio, 432 CREATE extensions, 119 installation creating Falcon, 409 LVM, 456 enumerations, 195–197 MySQL Proxy, 750–751 EXPLAIN and, 606–607 PBXT binary plug-in, 415 factors affecting key usage, 615 plugin, 724–725 Falcon parameters, 405, 406 semisynchronous replication plug-in, 525–526 Lua, 758 installation, MySQL server MyISAM buffer, 366 before, 9–12 preﬁxes, 226 Conﬁguration Wizard on Windows, 31–36 relay log ﬁles, 520 initial conﬁguration, 29–31 scans, 596–606 from noinstall zip archive, 24–25 SHOW commands, 141–142 overview, 12–13 storage engine implementation, 377–378 post-install conﬁguration on Unix, 36–39 table deﬁnition extensions, 148 securing, 40–41 table maintenance extensions, 150–156 starting and stopping as Windows service, 26–29 using by eliminating functions, 623–626 starting and stopping from Windows command line, view, 310 25–26 INFORMATION_SCHEMA database system views. summary, 48 See MySQL Data Dictionary troubleshooting, 47–48 initial conﬁguration on Unix, 13–20 MySQL, 29–31 upgrading mysqld, 45–47 of MySQL on Windows, 23–24 on Windows, 20–24 injecting queries, 764–766 Windows PATH variable conﬁguration, 42–44 injection, SQL, 657–658 integer data types in-memory caches, 434 attributes, 178–180 InnoDB sizes and ranges, 172–173 backing up, 445 interrupted transactions, 343–344 copying databases to backup, 467 interval types, 193–194 hot backup program, 464–466 INTERVAL units, 800–802 storage engine options, 367–370 INTO OUTFILE, 450 835
I Index introducers REFERENTIAL_CONSTRAINTS, 684 character sets and, 102 TABLE_CONSTRAINTS, 681–682 language structure, 88–89 upsert statements, 112–114 invalid data, 201–203 key ﬁles, 661–662 invalidation, query cache KEY_COLUMN_USAGE, 682–684 deﬁned, 428 keys, partition minimizing, 433–434 deﬁned, 497 invoking LIST, 503 changing event runtime behavior, 299 partition table restrictions, 510 changing trigger runtime behavior, 250–252 RANGE, 500–502 stored functions, 269–270 working with, 504 stored routines, 259–260 keywords triggers, 242–243 character string types, 160–161 I/O schedulers, 358–359 datetime types, 183 I/O test mode, 555–557 locking, 792–794 I/O threads MySQL Proxy tokens, 774–782 deﬁned, 525 national character string types, 166 InnoDB status, 389 numeric types, 170–171 semisynchronous replication, 527 KILL slave promotion and, 730–731 deﬁned, 63 IRC channels, 6 server maintenance extensions, 130 isolation levels Kneschke, Jan, 739 deﬁned, 321 Kruckenberg, Mike, 744 Falcon parameters, 406 transaction, 325–335 ITERATE, 284 iterations, 552 L language structure case-sensitivity, 90–91 J character sets and collations, 98–105 comments and portability, 88–90 Java Runtime Environment (JRE), 639 JFS (Journaled File System), 355 dot notation, 95–97 joins escape characters, 91–93 nonunique index values, 601–602 naming limitations and quoting, 93–95 optimizing away Using temporary, 620–623 stored routine ﬂow control, 282–285 unique index values and, 602 stored routines, events and triggers, 242 Journaled File System (JFS), 355 time zones, 97–98 trigger, 245–246 languages K Lua, 741 kernel Lua basics, 757–763 parameters, 357 memcached programming API libraries, 436 tuning Linux, 357–359 stored routine options, 266 key cache, 131 Larsson, Allan, 4 key columns, 497 latin1 character set, 100 key constraints LEAVE, 284 creating and dropping, 231–237 length in EXPLAIN plan indexes, 606–607 binary data types, 171 factors affecting usage, 615 binary large object string types, 169–170 index types and, 219–221 character string types, 162–164 KEY_COLUMN_USAGE, 682–684 datetime type, 184 MySQL support, 111–112 index, 226–228 836