MySQL High Availability- P14

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

0
45
lượt xem
3
download

MySQL High Availability- P14

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

MySQL High Availability- P14: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions.

Chủ đề:
Lưu

Nội dung Text: MySQL High Availability- P14

  1. architectures supported, 480–483 constraints, best practices, 337 AWS case study, 484, 487–516 context events best practices, 520–522 logging queries, 51–57 characteristics of, 479 SQL threads and, 217–220 defined, 478 contingency plans (high availability) deployment models, 480 defined, 104 economical considerations, 483–484 disaster recovery, 107, 417 elasticity and, 482 master failures, 106 grid computing and, 481 planning considerations, 106 MySQL Enterprise and, 473 relay failures, 107 open source, 522 slave failures, 106 potential benefits, 485 CPU-bound processes, 249 replication and, 517–520 Create Basic Task Wizard, 43 service models, 479 CREATE FUNCTION statement, 69 software libraries and, 483 CREATE INDEX command, 538 transactional computing and, 482 CREATE PROCEDURE statement, 67 use cases, 484 CREATE ROUTINE privilege, 70 vendor support, 486 CREATE statement virtualization and, 481 binlog events and, 48 clustered indexes, 353 ENGINE parameter, 334 comments logging transactions, 76 hash marks in, 90 CREATE TABLE IF NOT EXISTS statement, retrieving, 177 46, 135 COMMIT statement CREATE TABLE statement logging transactions, 75 implicit commits and, 135 query events and, 234 logging, 51 slave promotion, 132 CREATE TRIGGER statement two-phase commit and, 150 DEFINER clause, 65 commit_and_sync function, 186, 190 security considerations, 63 compressing tables, 345, 347 CREATE USER privilege, 16 concat function, 319 Create_file_log_event, 58 configuration file credentials best practices, 404 getting, 498 binary log and, 47, 59–61, 165 instances and, 500 configuring masters, 13–14 cron facility, 253, 268 configuring slaves, 15 crontab files, 42 managing replication, 24 CSV storage engine, 334 options supported, 58 CURDATE function, 53 Server class and, 26 current database SSL considerations, 204 binary log filters, 59–61, 567 troubleshooting replication, 400 logging queries, 51, 52 CONNECTION_ID function query events and, 97 row-based replication and, 229 USE statement, 60 session-specific, 216 current time, logging queries, 52, 53 thread ID and, 56, 57 CURRENT_ID function, 52 connect_to helper function, 157 CURRENT_USER function, 229, 231 consistency (see data consistency) CURTIME function, 53 Console application, 271 cycle function, 228 --console option, 315 Cygwin environment, 429 578 | Index
  2. D measuring performance, 320–331 optimizing, 331–339, 368 dashboards, 453 performance information, 320 (see also Enterprise Dashboard) replication after crashes, 222–225 defined, 453 sharding, 175 MEM support, 456 --datadir startup option, 344, 435 Data Collector Sets, 287 Date, C. J., 320 data consistency dd command, 521 asynchronous replication and, 150, 151 DDL (Data Definition Language) statements in hierarchal deployment, 187–193 backups and, 430 managing, 184–187 logging statements, 51 MyISAM considerations, 82 logging transactions, 76 in nonhierarchal deployment, 185–187 defragmenting tables, 348 Data Definition Language (DDL) statements delayed slaves, 5 backups and, 430 DELETE statement logging statements, 51 LIMIT clause, 229, 240 logging transactions, 76 logging, 50 data managers, 481 nontransactional changes and, 225 Data Manipulation Language (DML), 50 stored procedures and, 66 data protection, 410 WHERE clause, 46 data recovery, 439 Delete_rows events, 232, 237 (see also disaster recovery) deployment backup and restore, 420–424 defined, 24 example, 440 hierarchal, 187–193 importance of, 419 nonhierarchal, 185–187 PITR and, 439–445 DESCRIBE command, 320 replication and, 438 df command, 264 terminology, 419 diamond configuration, 226 data sharding differential backups, 422 common usage, 167 disaster avoidance database considerations, 175 hot standby, 11, 111–114, 148 dynamic, 170, 178–180 remote replication and, 148 example, 173–184 disaster recovery, 439 load balancing and, 168, 171–173, 180– (see also data recovery) 184 contingency plans, 107 managing, 168 goal of, 414 moving between shards, 173 high availability and, 418, 550–551 moving to different nodes, 171–173 information integrity and, 412, 413–419 naming considerations, 169 planning considerations, 415 partition keys/functions, 175–176 practicing, 417 partitioning data, 170 slave servers, 222–226 process overview, 165–168 tools and strategies, 417–419 reading shards, 177–178 workflow considerations, 415–417 representing shards, 168 disk usage slave lag and, 385 AWS considerations, 511–516 static, 170 Linux/Unix environments, 261 writing data and, 149 Mac OS X environment, 274 data-mining queries, 37 monitoring, 247, 250 databases optimizing, 344 copying files, 108 Index | 579
  3. Disk Usage Analyzer, 264 security considerations, 64 disk-bound processes, 250 SSL support, 202 distributed data, redundancy and, 531 Enki Consulting, 486 Distributed Replicated Block Device (DRBD), Enterprise Dashboard 118 Advisors tab, 469 distributed transaction processing, 79–81 consolidated server graphs, 466 distribution costs, managing replication, 153 functionality, 456 DML (Data Manipulation Language), 50 heat charts, 464 DRBD (Distributed Replicated Block Device), Query Analyzer and, 459 118 Replication tab, 468 DROP command, 335 server details, 467 DROP INDEX command, 538 epochs, defined, 531 DROP TABLE IF EXISTS statement, 46 Error class, 25 DROP TABLE statement, 22, 135 error handling dual-master setup log messages and, 282 active-active setup, 115, 116, 120–124 logging statements, 72–75 active-passive setup, 115, 116, 119 error logs, 314 bidirectional replication, 120–124, 166 Event Scheduler, 42 defined, 6 Event Viewer, 281–283 depicted, 152 events, 46 high availability and, 115–124 (see also binary log events) managing replication, 23 affecting replication, 81 replicated disks and, 118 defined, 71 shared disks and, 117 DEFINER clause, 71 dynamic sharding, 170, 178–180 executing, 236–237 filtering, 162–164, 221–222 handling row-based replication, 232–236 E incidents and, 85 ec2-add-key-pair command, 495 interpreting, 94–98 ec2-attach-volume command, 515 logging statements, 61–66, 71 ec2-authorize command, 510 object definitions, 180 ec2-create-snapshot command, 516 partitioning to slaves, 164 ec2-create-volume command, 515 password considerations, 64 ec2-delete-snapshot command, 516 skipping, 217, 221–222 ec2-delete-volume command, 516 slave processing, 215–222 ec2-describe-images command, 496, 509 SQL thread processing, 217 ec2-describe-snapshots command, 516 triggers and, 238–239 ec2-describe-volumes command, 515 excessive lag, 396 ec2-detach-volume command, 515 Execute_load_query event, 57 ec2-run-instances command, 496, 509 Execute_log_event, 58 ec2-start-instances command, 496 expire-logs-days option, 86, 98 ec2-stop-instances command, 496 EXPLAIN command ec2-terminate-instances command, 496, 510 best practices, 339 elastic load balancing, 489 executing queries, 458 Elastic MapReduce, 488 functionality, 320–327 Elasticfox (Firefox plug-in), 494 indexes and, 331 elasticity, cloud computing and, 482 usage example, 313 EmptyRowError exception, 25 EXTENDED keyword, 323 _enable_binlog helper method, 136 extended-status command, 301 encryption 580 | Index
  4. external replication, 552 releasing locks, 27 foreign keys, 338 format description events F binlog event structure, 47, 48–50 Faroult, Stephane, 320 functionality, 19, 20 fdisk command, 264 header restrictions, 97 Federated storage engine, 335 I/O threads and, 216 fetch_global_trans_id function, 140 in binary log, 20, 84 fetch_master_pos function, 35, 186, 191 interpreting, 97 fetch_relay_chain function, 191 printing, 89 fetch_remote_binlog function, 40, 138 XA and, 81 fetch_slave_pos function, 35 fragments, defined, 552 fetch_trans_id function, 190 free command, 253, 259 file IDs, 57 fsync call, 223, 224 filesystem coordinating synchronization, 83 disk usage and, 250 G logging changes, 51, 76 general query logs, 314 logical volumes and, 434 getArticlesForUser function, 177, 182 memory considerations, 250 getCommentsForArticle function, 177 object definitions and, 180 getServerConnection function (PHP), 157 reading remote files, 93 global redundancy, 530, 557 snapshot support, 108 global transaction IDs filtering circular replication, 144, 145 current database, 567 data consistency example, 187, 190 partitioning events to slaves, 164 defined, 130 replication events, 162–164 slave promotion and, 130–135, 137, 139 row-based replication, 240 GNOME desktop project, 264 scaling out and, 149 Gnome System Monitor, 252 skipping events, 221–222 Governor, James, 478 SQL threads and, 217 GRANT OPTION privilege, 16 filtering, binary log filters and, 59–61 graphical user interfaces (GUIs), 268, 302 Final role, 29 grep command, 258, 509 find_datetime_position function, 40 grid computing, 481 FLUSH LOGS command groups, defined, 48 binlog file support, 47 group_by_event function, 138 functionality, 18, 84 group_by_trans function, 138 monitoring master servers, 374 GUIs (graphical user interfaces), 268, 302 privilege considerations, 16 troubleshooting binary log, 392 FLUSH QUERY CACHE command, 300 H FLUSH STATUS command, 307 hardware FLUSH TABLES command, 429 data loss and, 421 FLUSH TABLES WITH READ LOCK node recovery and, 551 command hash mark (#), 90 cloning the master, 31 HA_ERR_KEY_NOT_FOUND error, 399 EBS snapshots and, 514 Health Insurance Portability and InnoDB cautions, 34 Accountability Act (HIPAA), 412 LVM support, 436 heartbeats, 383, 551, 571 pausing replication, 406 heat charts, 464 Index | 581
  5. Heisenberg uncertainty, 306 Incident event type, 86 Hibernate Shards (Google), 168 incident events, defined, 85 hierarchal deployment, 187–193 incremental backups, 422 hierarchal replication index file (see binlog index file) defined, 159 indexes setting up relay servers, 160 best practices, 331, 338 high availability clustered, 353 achieving, 547–550 fine-tuning, 320 backups and, 6 preloading, 349 contingency plans, 104, 106–107 queries and, 294, 299 disaster recovery and, 418 information assurance (see IA) information integrity and, 412 information integrity MyISAM storage engine and, 352 backup and restore, 420–424 node recovery and, 551 data recovery and, 419 procedures and, 104, 107–146 defined, 410, 411–412 redundancy and, 103, 104 disaster recovery and, 412, 413–419 replication and, 6, 552–556 high availability and, 412 system recovery and, 550–551 overview, 411 HIPAA (Health Insurance Portability and information significance, 410 Accountability Act), 412 INFORMATION_SCHEMA database, 363– HiveDB sharding implementation, 168 364 horizontal partitioning (see data sharding) Infrastructure as a Service (see IaaS) host-bin option, 99 --initial option, 543 hostname-bin option, 14 --initial-start option, 543 hot standby init_file option, 389 defined, 11 innobackup script disaster avoidance through, 11, 148 functionality, 427 high availability and, 111–114 options supported, 427 hybrid topology, 403 performing backups, 428 restoring data, 428 InnoDB Hot Backup I application, 425–428, 437 IA (information assurance) cloning the master, 31 additional information, 410 InnoDB storage engine defined, 410 architectural features, 353 importance of, 411 data sharding and, 174 related practices, 410 dual-master setup and, 118 IaaS (Infrastructure as a Service) functionality, 334 defined, 479 handling row locks, 182 virtualization and, 481 Hot Backup, 33, 108, 425–428 ibbackup utility improving performance, 352 --apply-log option, 426 INFORMATION_SCHEMA database, core operations, 425 363–364 performing backups, 425 InnoTop system activity report, 317 restoring data, 427 monitor mechanism, 357–359 --uncompress option, 426 monitoring buffer pools, 360–363 IBM Blue Cloud, 486 monitoring logfiles, 359 ifconfig command, 265 monitoring tablespaces, 363 IGNORE LEAVES clause, 350 OPTIMIZE TABLE command, 330 images, defined, 481 582 | Index
  6. parameters supported, 365 recovery considerations, 119 J semisynchronous replication, 124 Joyent (vendor), 486 SHOW ENGINE INNODB STATUS command, 354–356 K slave promotion and, 131 KDE System Guard, 252 snapshot support, 108 kernel, memory and, 250 XA support, 83 key caches innodb_fast_shutdown option, 365 creating, 350 innodb_thread_consistency option, 365 monitoring, 348 InnoTop system activity report, 317 multiple, 350 INSERT INTO statement, 135 preloading, 349 INSERT statement KILL command, 295, 301 invoking triggers, 65 LIMIT clause, 229, 240 logging, 50 L nontransactional changes and, 73, 225 LAST_INSERT_ID function stored functions and, 69 global transaction ID, 132 stored procedures and, 66 logging queries and, 52, 53, 54 usage examples, 90, 219 LAST_INSERT_ID session variable, 218 INSERT_ID session variable, 218 Layered Technologies (vendor), 487 instances legal requirements, data preservation, 7 defined, 481 L’Hermite, Pascal, 320 launching, 508–511 LIKE clause, 295 running, 501–508 LIMIT clause, 229, 240 storage considerations, 511 Linux class, 26 instances, credentials and, 500 Linux environment integer data, interpreting, 94 automated monitoring, 268 internal replication, 552 disk usage, 261–264 Internet, running replication over, 202–206 general system statistics, 266 Intvar event InnoDB Hot Backup application, 425 functionality, 54, 218 LVM support, 31, 34, 108 mysqlbinlog support, 90, 91 managing replication, 24 stored procedures and, 68 memory usage, 259–261 inventory assessment, 416 monitoring, 246, 253–268 I/O threads network activity, 265 functionality, 200 process activity, 253–258 handling broken connections, 215 Linux High Availability project, 118 housekeeping, 216 load balancing replication and, 371 application-level, 155–158 starting and stopping, 201 data sharding and, 168, 171–173, 180–184 state considerations, 210–212 elastic, 489 synchronizing, 224 managing replication, 152 I/O transfer rates, 262 for reads, 148 I/O-bound processes, 251 slave lag and, 385 I/O-starved processes, 251 for writes, 148 ionice command, 255 LOAD DATA INFILE statement iostat command, 253, 255, 261 data sharding example, 184 itertools module, 228 handling current database, 52 LOAD_FILE function and, 72 Index | 583
  7. logging statements, 57–58 DML statements, 50 logging transactions, 76 error handling, 72–75 LOAD INDEX command, 349 events, 61–66, 71 LOAD_FILE function, 72, 231 LOAD DATA INFILE statement, 57–58 Load_log_event, 58 LOCK_log mutex, 50 LOCAL keyword, 329, 330 nontransactional changes, 72–75 local query handler, 537 query events, 51–57 local redundancy, 530, 557 special constructions, 71 LOCK TABLES command, 172 stored functions, 61, 66, 69–70 locks stored procedures, 61, 66–68 reducing contention, 385 stored programs, 61–66 releasing, 27 stored routines, 61–66 row, 182 triggers, 61–66 LOCK_log mutex, 50 logging transactions --log startup option, 314 implicit commits and, 76 log-bin option starting transactions, 75 controlling binlog files, 47 transaction cache, 76–79 functionality, 13, 99, 315 XA support, 79–81 Server class and, 26 logical backups, 422 slave promotion and, 129 Logical Volume Manager (see LVM) log-bin-index option logical volumes, 434 controlling binlog files, 47 Loukides, Mike, 262 functionality, 13, 99, 315 ls command, 264 Server class and, 26 Lua programming language, 154 log-bin-trust-function-creators option, 71, 99 lvcreate command, 434 --log-error startup option, 315 LVM (Logical Volume Manager) --log-output startup option, 314 backup comparisons, 437 log-slave-updates option cloning slaves, 34 bidirectional replication, 121 cloning the master, 31 binary logging and, 393 functionality, 432 hierarchal replication, 160 snapshot support, 108 hot standby and, 112 usage considerations, 433–436 slave promotion and, 129, 130, 136 lvremove command, 435 --log-slow-queries startup option, 314 lvscan command, 435 --log-slow-slave-statements option, 314 logfiles applying to backups, 426 M best practices, 404 Mac OS X environment Console application and, 271 Activity Monitor, 273–276 Event Viewer support, 281 Console application, 271 InnoDB storage engine and, 353 monitoring, 246, 268–276 monitoring, 359 System Profiler, 268–271 RESET_SLAVE command and, 199 Machine class, 26 server logs, 313 management buy-in, 416 troubleshooting replication, 400 managing binary log, 81–87 troubleshooting tables, 391 Martelli, Alex, 140 logging statements master dump thread, 200 binary log filters, 59–61 master filters DDL statements, 51 creating, 163 defined, 162 584 | Index
  8. master heartbeats, 383 monitoring, 247, 249, 259–261 master log information file node recovery and, 551 flushing, 224 troubleshooting, 389, 395 functionality, 198 Memory storage engine, 335 manipulating slave threads, 201 memory-bound processes, 249 replication status information, 212 Merge storage engine, 335 Master role MERGE view, 122 defined, 29 Microsoft Azure, 483 replicate_from function and, 35 Microsoft Management Console snap-ins, 42 master servers mission statements, 416 checking status, 403 mixed-mode replication, 231 circular replication, 142–146 Mollinaro, Anthony, 320 cloning, 30, 31 monitoring, 292 configuring, 13 (see also performance considerations) connecting to slaves, 14, 15 automated, 268 creating, 7 benefits of, 247 creating replication users, 14 buffer pools, 360–363 delayed slaves, 5 categories of, 246 dual-master setup, 6, 23, 115–124 defined, 246 handling failures, 106, 109 disk usage, 247, 250, 261–264, 274 hierarchal replication, 159 general system statistics, 266 monitoring, 372–376 InnoDB storage engine, 352–365 monitoring thread status, 371 key cache, 348 multimaster issues, 399 Linux environment, 246, 253–268 replication overview, 5 logfiles, 359 scripting the clone operation, 35 Mac OS X environment, 246, 268–276 server roles, 28–30 master servers, 372–376 status variables and, 376 memory, 247, 249, 259–261, 274 switching, 109, 112–114 MyISAM storage engine, 344–352 tips and tricks, 568–570 MySQL Administrator and, 381 troubleshooting, 388–393 MySQL Enterprise, 463–470 two-phase commit and, 150 MySQL servers, 292–319 upgrading, 110 network activity, 248, 251, 265, 275 master-connect-retry option, 215 as preventive maintenance, 288 master-retry-count option, 215 process activity, 253–258 MASTER_POS_WAIT function processor, 247, 248 data consistency example, 185, 186, 187 replication, 367–386 functionality, 40, 172 semisynchronous replication, 127 relay log processing and, 213 slave lag, 383 max-allowed-packet option, 58, 397 slave servers, 376 max-binlog-cache-size option, 100 tablespaces, 363 max-binlog-size option, 100 tools for, 252 Maxia, Giuseppe, 154 Unix environment, 246, 253–268 MD5 function, 220 usage examples, 7 MEM (see MySQL Enterprise Monitor) Windows environment, 246, 276–288 memcached technique, 156, 171 monitoring agents, 457, 462 memory MONyog tool, 317 cautions when tweaking, 250 mount command, 435 Mac OS X environment, 274 mpstat command, 253, 255, 257 Index | 585
  9. multichannel replication, 554, 567 MySQL Cluster multimaster topology, 399, 403 architecture basics, 532–538, 554 multisource replication, 226–228, 566 commit support, 151 Musumeci, Gian-Paolo D., 262 data nodes, 543 mutex, 356 data storage, 533–536 myisam ftdump utility, 345 example configuration, 539–547 MyISAM storage engine features, 528–529 compressing tables, 347 functionality, 526 consistency considerations, 82 getting started, 539–541 defragmenting tables, 348 high availability and, 547–556 dual-master setup and, 118 high performance and, 557–560 functionality, 334 log handling, 531 handling row locks, 182 management node, 541 high availability and, 352 NDB management console, 542 improving performance, 344 online operations, 537 monitoring key cache, 348 partitioning and, 536 nontransactional changes and, 73, 75, 225 redundancy and, 530, 531, 557 OPTIMIZE TABLE command, 330 reload event, 86 optimizing disk storage, 344 replication, 566 parameters supported, 351 replication and, 553 preloading key cache, 349 shutting down clusters, 546 query cache and, 298, 307 SQL nodes, 544 recovery considerations, 119 starting, 541–546 slave promotion and, 131 terminology and components, 526 tables in index order, 347 testing clusters, 546 troubleshooting tables, 397 transaction management, 537 tuning tables, 345–346 typical configuration, 527 myisam-recover option, 392 mysql database myisamchk utility logging transactions, 76 defragmenting tables, 348 object definitions and, 180 functionality, 345–346 MySQL Enterprise tables in index order, 347 alert details, 464 myisamlog utility, 345 background information, 452 myisampack utility, 345, 347 clouding computing and, 473 MySAR system activity report, 316 components, 456–460 MySQL fixing monitoring agents, 462 additional information, 8 installing, 454–455, 460–462 version considerations, 24 monitoring, 463–470 MySQL Administrator production support, 459 Connection Health tab, 303 Query Analyzer, 470–472 functionality, 302 subscription levels, 453 Key Efficiency graph, 307 usage considerations, 460 Memory Health tab, 306 MySQL Enterprise Backup, 425 page tool, 311 MySQL Enterprise Monitor Query Cache Hitrate graph, 307 additional information, 252 replication monitoring, 381 advisors, 457 Server Variables tab, 309 background information, 453 Status Variables tab, 310 Enterprise Dashboard, 456 Traffic graph, 304 functionality, 452, 456 586 | Index
  10. installing, 455 interpreting comments, 90 monitoring agents, 457, 463 interpreting events, 94–98 Query Analyzer, 458 PITR and, 439 MySQL Enterprise Server, 456 pseudo_thread_id variable, 57 MySQL Forge, 155 --read-from-remote-server option, 93 MySQL Migration Toolkit, 302 reading remote files, 93 MySQL monitor, 294 --short-form option, 89, 90 MySQL Monitor and Advisor (MONyog) tool, --start-datetime option, 38, 92 317 --start-position option, 92 MySQL Proxy --stop-datetime option, 38, 93 data sharding and, 168 --stop-position option, 92 load balancing and, 154 troubleshooting replication, 400 multimaster replication, 565 usage example, 39 reporting statistics, 470 viewing error codes, 75 MySQL Python wildcard support, 92 adding relay servers, 161 mysqldump utility additional information, 8 backup comparisons, 437 common replication tasks, 36–43 cloning slaves, 34 handing reporting, 40 cloning the master, 31 handling switchovers, 114 functionality, 430–432 managing replication, 23–25 options supported, 431 PITR and, 443–445 snapshots and, 108 slave promotion, 135–141 mytop utility, 316 MySQL Query Browser, 312–313 MySQL servers benchmark suite, 318–319 N communicating performance, 293 Nagios tool, 252, 288 GUI tools, 302 NAME_CONST function, 68 MySQL Administrator, 302–312 National Institute of Standards and Technology MySQL Query Browser, 312–313 (NIST), 479 mysqladmin utility, 300–302 NDB (network database), 526 performance monitoring, 293 NDB management console, 537, 542, 547 server logs, 313 --NDB-connectstring option, 542, 544 SQL commands, 294–300 --NDB-nodeid option, 543, 544 third-party tools, 316–318 NDBcluster option, 544 MySQL System Tray Monitor, 313 NDB_binlog_index table, 554 mysql utility, 32 NDB_restore utility, 538 mysql.com outage, 110 netstat command, 253, 265 mysqladmin utility network activity commands supported, 300 Linux/Unix environments, 265 --relative option, 301 Mac OS X environment, 275 --sleep option, 301 monitoring, 248, 251 mysqlbinlog utility network database (NDB), 526 --base64-output=never option, 89 network-bound processes, 251 basic usage, 88–93 nice command, 255 --force option, 38 NIST (National Institute of Standards and --force-if-open option, 89 Technology), 479 functionality, 87, 297 node recovery, 551 --hexdump option, 94, 95 nonhierarchal deployment, 185–187 nontransactional changes Index | 587
  11. avoiding problems with, 79 events to slaves, 164 error handling and, 72–75 MySQL Cluster and, 536 implicit commits and, 76 passwords logging, 77–79 AWS requirements, 498 protecting, 225 master log information file, 199 row-based replication and, 229 security considerations, 63, 64 troubleshooting, 392, 397 Patriot Act, 412 NoOptionError exception, 25 pausing replication, 406 normalization, 331, 338 PBXT transactional engine, 119 NOT NULL constraint, 338 peak loads, handling, 153 NotMasterError exception, 25 per-process transfer rate, 250, 251 NotSlaveError exception, 25 Percona open source provider, 432 NOW function, 52, 53 performance considerations, 248 NO_WRITE_TO_BINLOG keyword, 329, (see also monitoring) 330 best practices, 339–341, 558–560 data mining, 37 database, 319 O database object manipulation, 51 on_gid function, 139 defining, 292 open recovery image, 441 high performance, 557 open source cloud computing, 522 InnoDB storage engine, 352 operating systems, 252 MyISAM storage engine, 344 (see also specific systems) MySQL Cluster and, 557–560 class methods, 26 MySQL servers, 292–319 managing replication, 24 optimizing views and, 122 monitoring solutions, 252 replication and, 341, 367 node recovery and, 551 report generation, 12, 148 OPTIMIZE TABLE command synchronous replication, 151 best practices, 339 tuning tables, 345–346 defragmenting tables, 348 Performance Monitor, 285–288 functionality, 330, 345 Perl language, 318, 427 oracle algorithm, 574 PHP programming language, 155 ORDER BY clause, 241, 347 physical backups, 422 overall transfer rate, 250 physical file copy, 428–430, 437 physical volumes, 433 P pid-file option functionality, 13, 99 PaaS (Platform as a Service), 480 Server class and, 26 page cache, 83 PITR (point-in-time recovery) paging technique, 249 backup in replication and, 439 partition functions backup procedure, 442 commonly used schemes, 170 binary log and, 17, 51, 165, 315 sharding databases, 175–176 defined, 4 partition keys filtering considerations, 163 creating, 170 FLUSH LOGS command and, 84 sharding databases, 175–176 InnoDB Hot Backup and, 428 partitioning, 167 Python and, 443–445 (see also data sharding) recovery example, 440 data sharding and, 170, 175–176 recovery images, 441 defined, 433 588 | Index
  12. replication and, 369 solutions to overloading, 248 restoring after replicated error, 439 processlist command, 301 Platform as a Service (PaaS), 480 processor, monitoring, 247, 248 pmap command, 253, 259 processor-bound processes, 249 point-in-time recovery (see PITR) Promotable class, 136 polling, 189 promote_slave function, 140 pool_add function, 158 proxy pool_del function, 158 defined, 153 pool_set function, 158 distributing queries, 154 Position class, 25 ps command, 253, 257 post headers, 48–50, 98 pseudothread ID, 221 primary keys, 338 pseudo_thread_id server variable, 57 primary servers, 116, 117 public certificates, 203 private keys, 203 PURGE BINARY LOGS command, 47, 86, privileges 400 configuring replication, 14, 16 purge index file, 85 reading remote files, 93 pvcreate command, 434 security and binary log, 64 pvscan command, 434 setting thread IDs, 57 Python (see MySQL Python) stored functions and, 70 proactive monitoring, 247 procedures (high availability) Q best practices, 406 queries circular replication, 142–146 analyzing, 153 considerations for, 108–110 best practices, 559 defined, 104 data sharding and, 170 dual-master setup, 6, 23, 115–124 data-mining, 37 hot standby, 11, 111–114 distributing, 153, 154 semisynchronous replication, 116, 124– EXPLAIN command and, 458 127 improving performance, 340 slave promotion and, 109, 127–141 manually executing, 405 process IDs slave lag and, 385 identifying, 258 troubleshooting, 391, 392, 394 temporary tables and, 56 Query Analyzer TLS support, 220 functionality, 458, 470–472 processes troubleshooting, 463 assigning priorities, 249, 255 query cache CPU-bound, 249 best practices, 336, 560 defined, 248 functionality, 298 disk-bound, 250 MySQL Administrator and, 306 I/O-bound, 251 server variables, 299 I/O-starved, 251 query events killing runaway, 248 binlog event structure, 48 memory-bound, 249 context events and, 217 monitoring activity, 253–258 current database and, 97 network-bound, 251 execution contexts, 51, 53–54 processor-bound, 249 functionality, 19 removing unnecessary, 248 interpreting, 94–97 rescheduling, 249 logging, 51–57 mysqlbinlog example, 91 Index | 589
  13. reading remote files, 93 relay-log-index option, 15 row-based replication and, 234 Reliability Monitor, 283 thread IDs and, 56, 221 RELOAD privilege, 16 renice command, 255 REORGANIZE PARTITION command, 538 R REPAIR TABLE command, 345 Rackspace (vendor), 487 Replicant library RAID (redundant array of inexpensive disks), handling failover, 118 412 load balancing functions, 158 Rand event, 54, 218 multisource replication and, 228 RAND function rebalancing shards, 181 context events, 53 replicas, defined, 531 functionality, 52 replicate-do-db option Rand event and, 54, 218 data sharding and, 169, 171 reactive monitoring, 247 replication monitoring, 369 read-only option, 100 slave filters and, 164 reading data thread processing and, 217 avoiding stale data, 189 replicate-do-table option data sharding and, 177–178 replication monitoring, 370 load balancing and, 148 slave filters and, 164 on remote files, 93 thread processing and, 217 scaling out and, 149 replicate-ignore-db option thread-local objects, 220 replication monitoring, 369 recovery images, 441, 444 slave filters and, 164 recovery point objective (RPO), 419, 423 thread processing and, 217 recovery time objective (RTO), 419, 424 replicate-ignore-table option redundancy (high availability) replication monitoring, 370 defined, 103 slave filters and, 164 MySQL Cluster and, 530, 531, 557 thread processing and, 217 principle overview, 104 replicate-rewrite-db option, 370 redundant array of inexpensive disks (RAID), replicate-same-server-id option, 121, 370 412 replicate-wild-do-table option Reese, George, 478 replication monitoring, 370 relay log thread processing and, 217 configuring slaves, 15 replicate-wild-ignore-table option event execution, 236 replication monitoring, 370 maintaining replication positions, 212–214 slave filters and, 164 structure of, 196–200 thread processing and, 217 troubleshooting, 397 replicate_from function, 35 relay log information file replication, 103 functionality, 198, 199 (see also high availability; row-based manipulating slave threads, 201 replication; scaling out; statement-based replication status information, 213 replication) thread synching and, 224 architecture basics, 196–202 relay servers asynchronous, 6, 150–152 adding in Python, 161 backup and recovery, 438 handling failures, 107 basic steps, 12–16 hierarchal replication, 159 bidirectional, 120–124, 166 setting up, 160 binary log example, 18–20 synchronizing with, 187 590 | Index
  14. business functionality, 5 performance considerations, 12, 148 circular, 142–146, 152, 398, 403, 572 process overview, 37–43 common uses, 148 replication bugs, 407 configuration privileges, 14, 16 scaling out and, 148 defined, 5 report-host option, 207, 210, 381 EC2 and, 517–520 report-password option, 207 Enterprise Dashboard and, 468 report-port option, 207 filtering events, 162–164 report-user option, 207 handling broken connections, 214 REQUIRE SSL option, 395 hierarchal, 159–161 RESET MASTER command high availability and, 6, 552–556 binlog file support, 47 improving performance, 341, 367 functionality, 22 inclusive and exclusive, 368–370 slave promotion, 129 managing topologies, 152–158 usage example, 22 managing with Python, 23–25 RESET SLAVE command mixed-mode, 231 balancing shards, 172 monitoring master servers, 372–376 master log information file, 199 monitoring slave servers, 376 STOP SLAVE command and, 22 multichannel, 554, 567 usage example, 22 multisource, 226–228, 566 resource managers, 79 MySQL Administrator and, 381 response time, monitoring, 247 MySQL Cluster and, 553 restarts, best practices, 405 MySQL Proxy and, 565 restore process pausing, 406 after error replication, 439 performing common tasks, 36–43 expectations for, 422 PITR and, 439–445 forming archival plans, 423 process overview, 17 ibbackup utility, 427 repopulating tables, 564 innobackup script and, 428 reporting bugs, 407 LVM support, 436 running over Internet, 202–206 return values, stored routines and, 69 scriptable, 573 ring topology, 403 segmenting, 570 risk assessment, 416 semisynchronous, 116, 124–127 Role class server setup and, 368 create_repl_user method, 29 slave safety and recovery, 222–226 disable_binlog method, 29 slaves processing events, 215–222 enable_binlog method, 29 status information, 206–214 functionality, 28 synchronous, 150, 151 imbue method, 29 time-delayed, 572 set_server_id method, 29 tips and tricks, 563–574 unimbue method, 29 troubleshooting, 393, 398, 399, 406 ROLLBACK statement, 75 REPLICATION CLIENT privilege, 16 Romanenko, Igor, 430 REPLICATION SLAVE privilege rotate events reading remote files, 93 in binary log, 20 usage recommendations, 14, 16, 64 binlog event structure, 47, 49–50 replication threads, 200, 371–372 binlog-in-use flag and, 84 replication topology (see topologies) functionality, 19, 21 repopulating tables, 564 header restrictions, 97 report generation I/O threads and, 216 Index | 591
  15. round-robin DNS, 156 on Unix, 42 round-robin multisource replication, 226 on Windows Vista, 42 Row class, 27 Schlossnagle, Theo, 8 row events, 235, 236 Schwartz, Baron, 8, 148, 558 row-based replication scripting configuration options, 230 clone operation, 35–36 defined, 17 replication, 573 event execution, 236–237 SCSI, 118 events and triggers, 238–239 searches events handling, 232–236 Console application and, 271 filtering, 240 row-based, 237 functionality, 229–230 secondary servers, 116 logging statements, 50 Secure Sockets Layer (see SSL) mixed-mode replication, 231 security nontransactional changes and, 72 AWS support, 501 statement-based replication and, 229 binary log and, 64 tips and tricks, 565 IA and, 410 rpl-semi-sync-master-enabled option, 126 logfile messages, 281 rpl-semi-sync-master-timeout option, 126 monitoring considerations, 246 rpl-semi-sync-master-wait-no-slave option, password considerations, 63, 64 126 replication threads and, 70 rpl-semi-sync-slave-enabled option, 126 SELECT MASTER_POS_WAIT function, 406 rpl_semi_sync_master_clients option, 127 SELECT statement rpl_semi_sync_master_status option, 127 data consistency example, 190 rpl_semi_sync_slave_status option, 127 EXPLAIN command and, 320 RPO (recovery point objective), 419, 423 LIKE clause, 295 RTO (recovery time objective), 419, 424 LIMIT modifier, 157 load balancing example, 157 logging considerations, 46 S nontransactional changes and, 74 SaaS (Software as a Service), 480 ORDER BY RAND() modifier, 157 Salesforce.com, 487 semisynchronous replication, 127 SAN (storage area network), 117, 491 stored functions and, 69 sar command, 253, 255, 262–263 troubleshooting memory tables, 395 Sarbanes-Oxley Act (SOX), 412 troubleshooting queries, 394 savepoints, 334 WHERE clause, 336 scaling out semisynchronous replication asynchronous replication, 150–152 configuring, 125–127 common uses, 148 functionality, 116, 124 data consistency and, 184–193 monitoring, 127 data sharding and, 165–184 serializable transaction execution, 46 defined, 6, 147 Server class hierarchal replication, 159–161 connect method, 27 managing replication topology, 152–158 disconnect method, 27 reading data and, 149 fetch_config method, 28 specialized slaves, 162–165 important parameters, 26–28 writing data and, 149 replace_config method, 28 scaling up, defined, 147 scripting the clone operation, 35 scan_logfile function, 139 sql method, 27 scheduling tasks 592 | Index
  16. ssh method, 27 SHOW ENGINE LOGS command, 296 start method, 28 SHOW ENGINE STATUS command, 296 stop method, 28 SHOW ENGINES command, 296, 332 server IDs SHOW FULL PROCESSLIST command, 316 circular replication, 144 SHOW GRANTS FOR command, 400 configuring masters, 13 SHOW INDEX command, 328 dual-master setup and, 118, 121 SHOW INDEX FROM command, 294 Role class and, 29 SHOW MASTER LOGS command Server class and, 27 cloning masters, 31 slave promotion and, 131 global transaction IDs and, 134 server roles replication status information, 208 creating, 161 SHOW MASTER STATUS command and, functionality, 28–30 133 server versions, 84 SHOW MASTER STATUS command server-id option backup procedure, 442 connection timeouts and, 394 best practices, 403 functionality, 14 cloning the master, 31 SET GLOBAL command, 314 data consistency example, 185, 186 SET statement functionality, 297 creating key caches, 350 master status variables and, 376 usage example, 64 privilege considerations, 16 shard IDs, 168 replication status information, 208 sharding technique (see data sharding) reporting bugs, 407 shardNumber function, 176 SHOW MASTER LOGS command and, shell commands 133 managing replication, 24 troubleshooting replication, 399, 406 Server class and, 27 usage example, 22, 114, 373 SHOW BINARY LOGS command SHOW PLUGINS command, 294 functionality, 38, 297 SHOW PROCESSLIST command monitoring masters, 373 functionality, 214, 294 monitoring slaves, 379 monitoring slave lag, 384 SHOW BINLOG EVENTS command monitoring threads, 371, 372 context events and, 54 mytop utility, 316 error codes and, 75 troubleshooting replication, 400 functionality, 297 SHOW RELAYLOG EVENTS command, 297, hierarchal replication, 159 380 monitoring master servers, 374–376 SHOW SLAVE HOSTS command monitoring slave servers, 379 functionality, 297 troubleshooting replication, 400 slave status variables and, 381 usage examples, 18, 21 status information, 206 SHOW COLUMNS FROM command, 320 troubleshooting replication, 400 SHOW ENGINE INNODB MUTEX SHOW SLAVE STATUS command command, 356 best practices, 403 SHOW ENGINE INNODB STATUS circular replication example, 145 command cloning slaves, 34 functionality, 354–356 cloud computing and, 518 InnoDB monitors and, 357 data consistency example, 191 monitoring buffer pools, 360 functionality, 297 monitoring tablespaces, 363 monitoring lag, 384 Index | 593
  17. monitoring slaves, 377–379 monitoring thread status, 372 privilege considerations, 16 partitioning events, 164 replication status information, 209, 212 processing events, 215–222 reporting bugs, 407 replication overview, 5 slave status variables and, 381 safety and recovery, 222–226 troubleshooting replication, 399, 406 scaling out and, 162–165 troubleshooting slaves, 393, 396 scripting the clone operation, 35 SHOW STATUS command server roles, 28–30 controlling key cache, 348 status variables and, 380 functionality, 295 synchronizing, 128, 150, 222–225 limiting output, 295 tips and tricks, 568–570 MySAR system activity report, 316 transactions and, 222–225 MySQL Administrator and, 311 troubleshooting, 393–398, 563 mytop utility, 316 two-phase commit and, 150 reading variables, 127 upgrading, 109 SHOW TABLE STATUS command, 295 slave threads, 200, 201 SHOW VARIABLES command (see also I/O threads; SQL threads) controlling key cache, 348 slave-net-timeout option, 215 functionality, 295, 314 SlaveNotRunningError exception, 25 limiting output, 295 slow query logs, 314 MySAR system activity report, 316 snapshots SHOW WARNINGS command, 323 defined, 433 show-slave-auth-info option, 207 EBS support, 514 SHUTDOWN command, 547 logical volumes and, 434 shutdowns, best practices, 404 methods for taking, 108 slave filters SOAP protocols, 498 defined, 162 Software as a Service (SaaS), 480 filtering rules, 164 software libraries, 483 slave promotion Solaris class, 26 considerations, 109 Solaris ZFS, 108, 437 high availability and, 127–141 SOX (Sarbanes-Oxley Act), 412 in Python, 135–141 splintering (see data sharding) revised method, 129–135 split-brain syndrome traditional method, 128 defined, 117 slave servers DRBD and, 120 checking status, 403 MySQL Cluster and, 531 cloning, 33–34 shared disk solution, 118 configuring, 15, 32 SQL threads connecting to masters, 14, 15 checking status, 214 creating, 7, 30, 108 context events, 217–220 curing lag, 384 filtering and skipping events, 221–222 database crashes, 222–225 functionality, 200 delayed slaves, 5 processing overview, 215, 217–222 events and, 71 replication and, 371 filtering replication events, 162–164 starting and stopping, 201 handling failures, 106, 109 state considerations, 210–212 hierarchal replication, 159 synchronizing, 224 managing lag, 383 thread-specific events, 220 monitoring, 376 594 | Index
  18. SQL_SLAVE_SKIP_COUNTER variable, 221, Stop event, 85, 216 391, 392 STOP SLAVE command SSH key pair, 500, 505 manipulating slave threads, 202 ssh tunnel mode, 203 RESET SLAVE command and, 22 SSL (Secure Sockets Layer) slave promotion, 129 master log information file, 199 slave status variables and, 381 monitoring replication, 383 troubleshooting replication, 400 MySQL support, 64 usage example, 22, 38 replication over Internet, 202 STOP SLAVE IO_THREAD command, 202, replication support, 204 227 troubleshooting slaves, 395 STOP SLAVE SQL_THREAD command, 202, ssl-capath option, 204, 395 227 ssl-cert option, 204, 395 STOP SLAVE UNTIL command, 40 ssl-key option, 204, 395 storage area network (SAN), 117, 491 star topology, 402 storage engines, 343 START SLAVE command (see also specific storage engines) connecting master and slave, 15 default, 566 manipulating slave threads, 202 monitoring, 343 promoting slaves, 134 overview, 332–336 relay log information file, 200 stored functions slave status variables and, 381 defined, 61, 66, 69 troubleshooting replication, 400 DEFINER clause, 69 START SLAVE IO_THREAD command, 202 INSERT statement and, 69 START SLAVE SQL_THREAD command, logging statements, 69–70 202 privileges and, 70 START SLAVE UNTIL command, 40, 113, SELECT statement and, 69 172 specifying characteristics, 69 START TRANSACTION command, 75 SQL SECURITY DEFINER characteristic, start_trans function, 186, 190 71 statement-based replication SQL SECURITY INVOKER characteristic, defined, 17 70 filtering and, 240 stored procedures logging statements, 50 committing transactions, 141 logging transactions, 77 defined, 61, 66 partial execution of statements, 240 DEFINER clause, 67, 97 row-based replication and, 229 logging statements, 66–68 special constructions, 71 stored programs tips and tricks, 564 defined, 61 statements (see logging statements) handling events, 71 static sharding, 170 logging statements, 61–66 status command, 301 stored routines status variables defined, 61, 66 examples, 97 DEFINER clause, 66 Heisenberg uncertainty and, 306 logging statements, 61–66 monitoring buffer pools, 361 object definitions, 180 monitoring logfiles, 359 return values and, 69 monitoring master servers, 376 string data, interpreting, 94 monitoring slave servers, 380 stunnel command semisynchronous replication, 127 functionality, 203 Index | 595
  19. replication support, 204–206 process IDs and, 56 Sun Management Center, 252 pseudothread IDs and, 221 Sun Microsystems, 437 thread IDs and, 56 SUPER privilege troubleshooting, 396 configuring replication, 16 TEMPTABLE view, 122 disabling, 99 Terremark (vendor), 487 logging statements and, 65 thrashing, 249 setting thread IDs, 57 thread IDs stored functions and, 70 functionality, 56 swapping technique, 249 logging queries, 52 sync-binlog option, 83, 100, 390 TLS support, 220 synchronizing thread-local store (TLS), 220 I/O threads, 224 threads relay servers, 187 replication, 200, 371–372 slave servers, 128, 150, 222–225 security considerations, 70 SQL threads, 224 semisynchronous replication, 116 troubleshooting, 396 slave, 201, 210–212 synchronous replication transaction caches and, 77 asynchronous replication and, 150 3Tera (vendor), 486 performance considerations, 151 timestamps sync_with_master function, 186, 191 logging statements, 52, 53, 61 SYSDATE function, 53 mysqlbinlog support, 90, 93 System Health Report, 277, 278–280, 285 TLS (thread-local store), 220 System Profiler, 268–271 top command, 253, 254–255 topologies best practices, 401–403 T checking server status, 403 table IDs, 236 circular replication, 142–146, 152 tables, 56 defined, 23 (see also temporary tables) dual-master setup, 6, 23, 115–124, 152 AUTO_INCREMENT columns, 52, 53, hot standby, 11, 111–114 123 managing, 152–158 compressing, 345, 347 removing slaves from, 109 data sharding and, 169, 176 tree, 23, 152 defragmenting, 348 tps (transactions per second), 262 nontransactional changes and, 72–75, 78 transaction cache, 76–79 repopulating, 564 transaction coordinator, 537 security considerations, 64 transaction managers, 79 storing in index order, 347 transactional computing, 482 troubleshooting, 391, 395, 397 transactions, 225 tuning for performance, 345–346 (see also nontransactional changes) tablespaces asynchronous replication and, 150 defined, 353 implicit commits, 135 monitoring, 363 logging, 75–81 Table_map events, 232, 234, 236 MySQL Cluster and, 537 tar utility, 428 semisynchronous replication, 124 Task Manager, 285 serializable execution, 46 Task Scheduler, 42 slave servers and, 222–225 temporary tables stored procedures and, 141 nontransactional changes, 79 596 | Index
  20. troubleshooting, 397 scheduling tasks, 42 two-phase commit and, 150 UNIX_TIMESTAMP function, 52, 53 transactions per second (tps), 262 UNLOCK TABLES command, 436, 514 tree topology UPDATE statement depicted, 152 LIMIT clause, 229, 240 managing replication, 23 logging, 50 triggers nontransactional changes and, 226 creating, 63 stored procedures and, 66 DEFINER clause, 97 troubleshooting memory tables, 395 events and, 238–239 usage example, 134 invoking, 65 WHERE clause, 46, 50 logging statements, 61–66 Update_rows events, 232, 237 troubleshooting uptime command, 252, 253, 266 best practices, 401–407 USE statement binary log, 392 current database, 60 binary log events, 389–391 usage example, 90 data loss, 396 User Account Control (UAC), 42, 277 master servers, 388–393 User class, 25 memory, 395 USER function, 229, 231 nontransactional changes, 397 user-defined functions (UDFs), 218, 229 queries, 391, 392, 394 User_var event Query Analyzer, 463 functionality, 54, 218 relay log, 397 mysqlbinlog support, 90, 91 replication, 393, 398, 399, 406 UUID function, 231 slave servers, 393–398, 563 synchronization, 396 tables, 391, 395, 397 V Vagabond role, 29 temporary tables, 396 variables, 306 transactions, 397 (see also specific types of variables) Tuckfield, Paul, 574 binary log, 98–100 two-phase commit, 150 configuring servers, 293 nontransactional changes and, 79 U password considerations, 63, 64 UAC (User Account Control), 42, 277 query events and, 51, 218 UDFs (user-defined functions), 218, 229 thread-specific results, 220 UML (Unified Modeling Language), 173 variables command, 301 umount command, 435 --verbose option, 564 underscore (_), 164 verification procedures, 417 Unified Modeling Language (UML), 173 vgcreate command, 434 Unix environment vgscan command, 434 automated monitoring, 268 views disk usage, 261–264 best practices, 336 general system statistics, 266 optimizing, 122 InnoDB Hot Backup application, 425 virtualization, cloud computing and, 481 managing replication, 24 vmstat command, 253, 264, 267 memory usage, 259–261 volume groups, 434 monitoring, 246, 253–268 Volume Shadow Copy, 432 network activity, 265 process activity, 253–258 Index | 597
Đồng bộ tài khoản