MySQL Administrator’s Bible- P18

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

0
49
lượt xem
6
download

MySQL Administrator’s Bible- P18

Mô tả tài liệu
  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ủ đề:
Lưu

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

  1. 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 specifications. Getting Documentation The official 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 official 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 offline lookup of what the different fields 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
  2. 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
  3. 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-profit 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
  4. 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/certification The training and certification 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
  5. 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 definitions, 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 defined, 319–320 ” (double quotation mark) PBXT, 410 naming limitations and, 93 understanding, 320–322 SQL mode definitions, 204 ACLs (Access Control Lists) ! (exclamation point), 89 defined, 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
  6. 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 defined, 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 defined, 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 definition 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 defined, 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 file system snapshot, 451–452 defined, 524–525 frequency, 443–444 multi-master, 538–539 LVM, 455–460 atomicity, 321 maatkit parallel dump/restore, 450–451 824
  7. 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 defined, 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 defined, 356–357 BEGIN, 322 InnoDB configuration, 385 BEGIN WORK, 322 InnoDB status, 389 benchmarking InnoDB storage engine options, 367–369 defined, 545–546 MyISAM, 365–366 with mysqlslap, 547–552 profiling, 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 defined, 98 C binary file vs. source code, 10 CA (Certificate 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 defined, 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 configuration, 412 binary packages, 20–24 profiling, 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
  8. C Index Callaghan, Mark, 525 SHOW commands, 136–137 canceling queries, 59–60 table definition 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 defined, 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 definition extensions, 147–148 object. See object catalog table maintenance extensions, 156 catch-all partitions, 499 child tables, 234 Certificate Authority (CA) chilling, 406 command-line tool options, 53 ciphers, 53 encrypted connectivity, 662 circular replication certificate files, 661–662 defined, 537–539 certification, 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 defined, 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 file, 10 changing stored routine, 264 coercibility, 810–811 changing trigger, 249–250 cold backups character string type attributes and, 165–166 defined, 441 defined, 98–105 location, 445 MySQL support, 108 tools, 446 826
  9. 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 definition extensions, 148 MySQL support, 108 commercial backup options, 464–467 SHOW commands, 137 commercial monitoring systems, 644–646 table definition extensions, 148 COMMIT COLUMN_PRIVILEGES, 712–713 defined, 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 defined, 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 defined, 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 configuration storage engine, 421–422 Archive, 417–419 SysBench, 553 Configuration Wizard on Windows, 31–36 table maintenance extensions, 150–156 CSV, 420–421 827
  10. C Index configuration (continued) control flow 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 defined, 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 configuration files. See option files CREATE FUNCTION, 269 Confirmation screen CREATE PROCEDURE, 268 configuration options, 36 CREATE TABLE defined, 24 defined, 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 profiling, 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 definition 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
  11. 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 definitions, 204, 207–209 security, 656–665 datetime types synchronization. See synchronization defined, 183–193 table maintenance extensions, 150–156 functions, 797–806 transfer, 354 DAY_TIME data types, 193–194 DDL (Data Definition Language) statements, 322 Data Definition 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
  12. D Index definers diagnostics management, 110 changing trigger, 250–252 diagrams, Workbench, 82–83 specifying view, 306–307 Dictionary, MySQL Data. See MySQL Data Dictionary defining views directives abstraction and simplification, 307–308 configuration file, 29 changing definitions, 316–317 in server option file, 362 definer specification, 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 definitions, 206 InnoDB tablespace, 390 table definition extensions, 148 partition table, 509 trigger storage, 252–254 query cache, 433–434 dirty reads table maintenance extensions, 154–155 defined, 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 Configuration, 31–32 double quotation mark (”) determinism naming limitations and, 93 caching SELECT statements, 427–428 SQL mode definitions, 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
  13. 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 defined, 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 finding 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 flow 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 definitions, 206 MySQL deviations, 110 storage. See storage engines plans, 595 table definition 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 defined, 517–518 EXTENDED, 612–613 831
  14. 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 fields 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 definitions, 206 extensions triggers aliases, 244 additional, 127–129 file 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 files server maintenance, 129–131 comparing storage engines, 377–378 SET and user-defined variables, 131–135 configuration, 29–31 SHOW, 135–147 copying databases to backup, 467 SHOW GLOBAL STATUS, 566–568 CSV, 420–421 summary, 158 ibdata, 390–392 table definition, 147–150 InnoDB status, 389 table maintenance, 150–156 limit of open, 357–358 transactional statement, 156–158 option. See option files 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 finding events, 295 F triggers, 252 failover fixed-width strings defined, 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
  15. Index G FLOAT FULLTEXT sizes and ranges, 174–176 ALTER TABLE extensions, 116 SQL mode definitions, 210 index types, 229–230, 237–239 floating point, 174 myisam_ftdump and, 382–383 flow, data, 657–658 searching, 601 flow 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 flushing compression and encryption, 788–789 commands, 63 CREATE FUNCTION syntax, defined, 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 defined, 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 flow, 789–792 row, 149 using index by eliminating, 623–626 forums, MySQL, 814–815 XML, 812 fragmentation in file 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 defined, 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 defined, 596 gopher threads, 403 optimization, 616–618 grandfather-father-son backup rotation, 444 833
  16. G Index GRANT help defined, 110 command-line tools, 49 privileges and privilege levels, 651–653 resources, 813–820 SHOW commands, 141 SysBench commands, 553 SQL mode definitions, 206 high availability architectures user management, 481–487 defined, 442 grant tables DRBDs, 738–739 defined, 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 defined, 495–496 MySQL Enterprise Monitoring, 644–645 programmatic, 514 OpenNMS, 640–641 horizontal scaling, 727 GROUP BY, 620–621 host strings grouping defined, 473–474 MySQL support, 108 wildcards in, 475–476, 650 SQL mode definitions, hosts 209–210 command-line tool options, 51 GUI (Graphical User Interface) tools flushing, 63 Administrator, 74–80 server maintenance extensions, 130 phpMyAdmin, 69–71 hot backups Query Browser, 71–74 defined, 441 SQLyog, 66–69 file 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 file system partitions, 353–356 ibbackup, 464–466 hardware, 349–352 ibdata files, 390–392 Harrison, Guy, 282 IDENTIFIED BY, 479 HASH partitioning identifiers defined, 497 dot notation, 95–97 working with, 503–504 naming limitations and quoting, 93–95 hashes IF, 282 defined, 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
  17. Index I implicit COMMIT, 322 Tablespace screen, 34 implicit transactions, 156 understanding, 384–394 inconsistent reads, 331 INOUT arguments, 261–262 incremental backups inputs defined, 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 definition extensions, 148–149 adding to queries, 616–620 updatable views, 313–316 ALTER TABLE extensions, 116–117 updating indexes, 223 binary log files, 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 prefixes, 226 Configuration Wizard on Windows, 31–36 relay log files, 520 initial configuration, 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 configuration on Unix, 36–39 table definition 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 configuration 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 configuration, 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
  18. 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 files, 661–662 invalidation, query cache KEY_COLUMN_USAGE, 682–684 defined, 428 keys, partition minimizing, 433–434 defined, 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 defined, 525 national character string types, 166 InnoDB status, 389 numeric types, 170–171 semisynchronous replication, 527 KILL slave promotion and, 730–731 defined, 63 IRC channels, 6 server maintenance extensions, 130 isolation levels Kneschke, Jan, 739 defined, 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 flow 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
Đồng bộ tài khoản