architectures supported, 480–483
AWS case study, 484, 487–516
best practices, 520–522
characteristics of, 479
defined, 478
deployment models, 480
economical considerations, 483–484
elasticity and, 482
grid computing and, 481
MySQL Enterprise and, 473
open source, 522
potential benefits, 485
replication and, 517–520
service models, 479
software libraries and, 483
transactional computing and, 482
use cases, 484
vendor support, 486
virtualization and, 481
clustered indexes, 353
comments
hash marks in, 90
retrieving, 177
COMMIT statement
logging transactions, 75
query events and, 234
slave promotion, 132
two-phase commit and, 150
commit_and_sync function, 186, 190
compressing tables, 345, 347
concat function, 319

Create Basic Task Wizard, 43
CREATE FUNCTION statement, 69
CREATE INDEX command, 538
CREATE PROCEDURE statement, 67
CREATE ROUTINE privilege, 70
CREATE statement
binlog events and, 48
ENGINE parameter, 334
logging transactions, 76
CREATE TABLE IF NOT EXISTS statement,
46, 135
CREATE TABLE statement
implicit commits and, 135
logging, 51
CREATE TRIGGER statement
DEFINER clause, 65
security considerations, 63
CREATE USER privilege, 16
Create_file_log_event, 58
credentials
getting, 498
instances and, 500
cron facility, 253, 268
crontab files, 42
CSV storage engine, 334
CURDATE function, 53
current database
binary log filters, 59–61, 567
logging queries, 51, 52
query events and, 97

example, 440
importance of, 419
PITR and, 439–445
replication and, 438
terminology, 419
data sharding
common usage, 167
database considerations, 175
dynamic, 170, 178–180
example, 173–184
load balancing and, 168, 171–173, 180–
184
managing, 168
moving between shards, 173
moving to different nodes, 171–173
naming considerations, 169
partition keys/functions, 175–176
partitioning data, 170
process overview, 165–168
reading shards, 177–178
representing shards, 168
slave lag and, 385
static, 170
writing data and, 149
data-mining queries, 37
databases
copying files, 108
measuring performance, 320–331
optimizing, 331–339, 368
performance information, 320

(see also data recovery)
contingency plans, 107
goal of, 414
high availability and, 418, 550–551
information integrity and, 412, 413–419
planning considerations, 415
practicing, 417
slave servers, 222–226
tools and strategies, 417–419
workflow considerations, 415–417
disk usage
AWS considerations, 511–516
Linux/Unix environments, 261
Mac OS X environment, 274
monitoring, 247, 250
optimizing, 344
Index | 579
Disk Usage Analyzer, 264
disk-bound processes, 250
distributed data, redundancy and, 531
Distributed Replicated Block Device (DRBD),
118
distributed transaction processing, 79–81
distribution costs, managing replication, 153
DML (Data Manipulation Language), 50
DRBD (Distributed Replicated Block Device),
118
DROP command, 335
DROP INDEX command, 538
DROP TABLE IF EXISTS statement, 46

Elasticfox (Firefox plug-in), 494
elasticity, cloud computing and, 482
EmptyRowError exception, 25
_enable_binlog helper method, 136
encryption
security considerations, 64
SSL support, 202
Enki Consulting, 486
Enterprise Dashboard
Advisors tab, 469
consolidated server graphs, 466
functionality, 456
heat charts, 464
Query Analyzer and, 459
Replication tab, 468
server details, 467
epochs, defined, 531
Error class, 25
error handling
log messages and, 282
logging statements, 72–75
error logs, 314
Event Scheduler, 42
Event Viewer, 281–283
events, 46
(see also binary log events)
affecting replication, 81
defined, 71
DEFINER clause, 71
executing, 236–237

fetch_global_trans_id function, 140
fetch_master_pos function, 35, 186, 191
fetch_relay_chain function, 191
fetch_remote_binlog function, 40, 138
fetch_slave_pos function, 35
fetch_trans_id function, 190
file IDs, 57
filesystem
coordinating synchronization, 83
disk usage and, 250
logging changes, 51, 76
logical volumes and, 434
memory considerations, 250
object definitions and, 180
reading remote files, 93
snapshot support, 108
filtering
current database, 567
partitioning events to slaves, 164
replication events, 162–164
row-based replication, 240
scaling out and, 149
skipping events, 221–222
SQL threads and, 217
filtering, binary log filters and, 59–61
Final role, 29
find_datetime_position function, 40
FLUSH LOGS command
binlog file support, 47
functionality, 18, 84

getCommentsForArticle function, 177
getServerConnection function (PHP), 157
global redundancy, 530, 557
global transaction IDs
circular replication, 144, 145
data consistency example, 187, 190
defined, 130
slave promotion and, 130–135, 137, 139
GNOME desktop project, 264
Gnome System Monitor, 252
Governor, James, 478
GRANT OPTION privilege, 16
graphical user interfaces (GUIs), 268, 302
grep command, 258, 509
grid computing, 481
groups, defined, 48
group_by_event function, 138
group_by_trans function, 138
GUIs (graphical user interfaces), 268, 302
H
hardware
data loss and, 421
node recovery and, 551
hash mark (#), 90
HA_ERR_KEY_NOT_FOUND error, 399
Health Insurance Portability and
Accountability Act (HIPAA), 412
heartbeats, 383, 551, 571
heat charts, 464
Index | 581

IA (information assurance)
additional information, 410
defined, 410
importance of, 411
related practices, 410
IaaS (Infrastructure as a Service)
defined, 479
virtualization and, 481
ibbackup utility
--apply-log option, 426
core operations, 425
performing backups, 425
restoring data, 427
--uncompress option, 426
IBM Blue Cloud, 486
ifconfig command, 265
IGNORE LEAVES clause, 350
images, defined, 481
Incident event type, 86
incident events, defined, 85
incremental backups, 422
index file (see binlog index file)
indexes
best practices, 331, 338
clustered, 353
fine-tuning, 320
preloading, 349
queries and, 294, 299
information assurance (see IA)
information integrity

363–364
InnoTop system activity report, 317
monitor mechanism, 357–359
monitoring buffer pools, 360–363
monitoring logfiles, 359
monitoring tablespaces, 363
OPTIMIZE TABLE command, 330
582 | Index
parameters supported, 365
recovery considerations, 119
semisynchronous replication, 124
SHOW ENGINE INNODB STATUS
command, 354–356
slave promotion and, 131
snapshot support, 108
XA support, 83
innodb_fast_shutdown option, 365
innodb_thread_consistency option, 365
InnoTop system activity report, 317
INSERT INTO statement, 135
INSERT statement
invoking triggers, 65
LIMIT clause, 229, 240
logging, 50
nontransactional changes and, 73, 225
stored functions and, 69
stored procedures and, 66
usage examples, 90, 219
INSERT_ID session variable, 218
instances

KDE System Guard, 252
kernel, memory and, 250
key caches
creating, 350
monitoring, 348
multiple, 350
preloading, 349
KILL command, 295, 301
L
LAST_INSERT_ID function
global transaction ID, 132
logging queries and, 52, 53, 54
LAST_INSERT_ID session variable, 218
Layered Technologies (vendor), 487
legal requirements, data preservation, 7
L’Hermite, Pascal, 320
LIKE clause, 295
LIMIT clause, 229, 240
Linux class, 26
Linux environment
automated monitoring, 268
disk usage, 261–264
general system statistics, 266
InnoDB Hot Backup application, 425
LVM support, 31, 34, 108
managing replication, 24
memory usage, 259–261
monitoring, 246, 253–268
network activity, 265
process activity, 253–258

controlling binlog files, 47
functionality, 13, 99, 315
Server class and, 26
slave promotion and, 129
log-bin-index option
controlling binlog files, 47
functionality, 13, 99, 315
Server class and, 26
log-bin-trust-function-creators option, 71, 99
--log-error startup option, 315
--log-output startup option, 314
log-slave-updates option
bidirectional replication, 121
binary logging and, 393
hierarchal replication, 160
hot standby and, 112
slave promotion and, 129, 130, 136
--log-slow-queries startup option, 314
--log-slow-slave-statements option, 314
logfiles
applying to backups, 426
best practices, 404
Console application and, 271
Event Viewer support, 281
InnoDB storage engine and, 353
monitoring, 359
RESET_SLAVE command and, 199
server logs, 313
troubleshooting replication, 400
troubleshooting tables, 391

cloning slaves, 34
cloning the master, 31
functionality, 432
snapshot support, 108
usage considerations, 433–436
lvremove command, 435
lvscan command, 435
M
Mac OS X environment
Activity Monitor, 273–276
Console application, 271
monitoring, 246, 268–276
System Profiler, 268–271
Machine class, 26
management buy-in, 416
managing binary log, 81–87
Martelli, Alex, 140
master dump thread, 200
master filters
creating, 163
defined, 162
584 | Index
master heartbeats, 383
master log information file
flushing, 224
functionality, 198
manipulating slave threads, 201
replication status information, 212
Master role
defined, 29

relay log processing and, 213
max-allowed-packet option, 58, 397
max-binlog-cache-size option, 100
max-binlog-size option, 100
Maxia, Giuseppe, 154
MD5 function, 220
MEM (see MySQL Enterprise Monitor)
memcached technique, 156, 171
memory
cautions when tweaking, 250
Mac OS X environment, 274
monitoring, 247, 249, 259–261
node recovery and, 551
troubleshooting, 389, 395
Memory storage engine, 335
memory-bound processes, 249
Merge storage engine, 335
MERGE view, 122
Microsoft Azure, 483
Microsoft Management Console snap-ins, 42
mission statements, 416
mixed-mode replication, 231
Mollinaro, Anthony, 320
monitoring, 292
(see also performance considerations)
automated, 268
benefits of, 247
buffer pools, 360–363
categories of, 246
defined, 246

Index | 585
multichannel replication, 554, 567
multimaster topology, 399, 403
multisource replication, 226–228, 566
Musumeci, Gian-Paolo D., 262
mutex, 356
myisam ftdump utility, 345
MyISAM storage engine
compressing tables, 347
consistency considerations, 82
defragmenting tables, 348
dual-master setup and, 118
functionality, 334
handling row locks, 182
high availability and, 352
improving performance, 344
monitoring key cache, 348
nontransactional changes and, 73, 75, 225
OPTIMIZE TABLE command, 330
optimizing disk storage, 344
parameters supported, 351
preloading key cache, 349
query cache and, 298, 307
recovery considerations, 119
slave promotion and, 131
tables in index order, 347
troubleshooting tables, 397
tuning tables, 345–346
myisam-recover option, 392
myisamchk utility

high performance and, 557–560
log handling, 531
management node, 541
NDB management console, 542
online operations, 537
partitioning and, 536
redundancy and, 530, 531, 557
reload event, 86
replication, 566
replication and, 553
shutting down clusters, 546
SQL nodes, 544
starting, 541–546
terminology and components, 526
testing clusters, 546
transaction management, 537
typical configuration, 527
mysql database
logging transactions, 76
object definitions and, 180
MySQL Enterprise
alert details, 464
background information, 452
clouding computing and, 473
components, 456–460
fixing monitoring agents, 462
installing, 454–455, 460–462
monitoring, 463–470
production support, 459
Query Analyzer, 470–472