Index
Symbols
+ (addition) operator, 112
(backslash), 67
^ (carat) character, 118
/ (division) operator, 112
$ (dollar sign), 118
= (equality) operator, 113
> (greater than) operator, 113
< (less than) operator, 113
; (semicolon), 45
- (subtraction) operator, 112
Numerics
1NF (first normal form), 32–34
2NF (second normal form), 34–35
3NF (third normal form), 35–36
A
abs() function, 120
access, securing, 208
accounts. See also privileges
anonymous, deleting, 15, 206–207
creating for basic use, MySQL installation, 15–16
security
anonymous accounts, deleting, 15, 206–207
dangerous privileges, 207
encrypted passwords, 207
root passwords, 206
ACID compliance, transactions, 153–154
activestate.com Web site, 241
adddate() function, 121
addition (+) operator, 112
administrative database activities
caches, clearing, 186–187
database information, retrieving, 183–184
grant and privilege information, viewing, 185
log files, 187
MySQL server, starting up and shutting down, 181–182
mysqladmin option summary, 188
process information, viewing, 185
server status and variables, viewing, 184
table information, viewing, 185–186
threads, terminating, 186
variables, setting, 186
administrator-level privileges, 164
aliases, SELECT statement, 84–85
ALL keyword, 104
ALL privilege, 164
ALTER privilege, 164
ALTER TABLE command, 54–56
ANALYZE TABLE command, 234, 244
AND operator, 114
anomalies
deletion, 31
insertion, 31
update, 32
anonymous accounts, deleting, 15, 206–207
ansi option, MySQL configuration, 176
ANY keyword, 104
applications, error-checking, 208
arithmetic operators, 112
asterisk (*), 118
attributes, database tables, 29
AUTO INCREMENT keyword, 49–50
autocommit mode, transactions, 151
avg() function, 125
AVG ROW LENGTH option, table optimization, 50
B
backslashes (), 67
BACKUP TABLE command, 198
FLUSH TABLES command, 197
from binary log, 198–199
LOCK TABLES command, 197
locking table, 192
manual, 197–198
mysqldump script
—add-drop-table option, 195
—add-locks option, 195
advantages of, 196
—all-databases option, 196
—allow-keywords option, 196
—d option, 196
—databases option, 196
disadvantages of, 196
—extended-insert option, 196
—lock-tables option, 196
—no-data option, 196
—opt option, 192
—quick option, 195
sample output, 192–195
mysqlhotcopy script, 196–197
reasons for, 192
RESTORE TABLE command, 198
tables, checking and repairing
CHECK TABLE command, 200
myisamchk program, 200–201
mysqlcheck program, 201
REPAIR TABLE command, 200
techniques for, 199
techniques, 191–192
testing, 199
BACKUP TABLE command, 198
basedir option, MySQL configuration, 176
BCNF (Boyce-Codd normal form), 36
BDB (Berkeley) tables, 50, 138
benchmark() function, 123, 240
benchmarking, queries, 240
BerkeleyDB (BDB) tables, 50, 138
BIGINT integer column type, 52
bin directory, 18
BINARY keyword, 113
binary logs
database restoration, 198–199
discussed, 187
replication, 214
BLOB column type, 53
Boolean expression subqueries, 103–105
Boolean full-text searching, 136–137
Boyce-Codd normal form (BCNF), 36
b-trees, 138
buffers, 227
built-in functions/operators, 111
built-in query optimization, 243–244
C
caches, clearing, 186–187
candidate keys, 29
carat (^) character, 118
cascading masters, replication with, 219
CASE function, 115
case sensitivity, identifiers, 42
cast functions, 123
ceiling() function, 120
CHAR column type, 53
char command, 46
character lengths, identifiers, 43
character sets, MySQL configuration, internationalization, 178
CHECK TABLE command, 200
checking and repairing tables
CHECK TABLE command, 200
myisamchk program, 200–201
mysqlcheck program, 201
REPAIR TABLE command, 200
techniques for, 199
CHECKSUM option, table optimization, 50
clauses
DEFAULT, INSERT statement, 69
DELAYED, INSERT statement, 68
DISTINCT, SELECT statement, 87–88
FIELDS, LOAD DATA INFILE statement, 74
FROM
derived table subqueries, 102
joins, 96
GROUP BY, SELECT statement, 88–89
HAVING, SELECT statement, 89
IDENTIFIED BY, GRANT statement, 163
IF NOT EXISTS, 49
IGNORE
INSERT statement, 69
LOAD DATA INFILE statement, 74
UPDATE statement, 73
LIKE, 49
LIMIT
DELETE statement, 72
SELECT statement, 90–91
UPDATE statement, 73
LINES, LOAD DATA INFILE statement, 74
LOW PRIORITY
DELETE statement, 72
INSERT statement, 68
LOAD DATA INFILE statement, 74
UPDATE statement, 73
ON, GRANT statement, 162
ON DUPLICATE KEY UPDATE, INSERT statement, 69
ORDER BY
DELETE statement, 72
SELECT statement, 90
table rows, 56
UPDATE statement, 73
QUICK, DELETE statement, 72
RENAME, tables, 56
REQUIRE, GRANT statement, 163
TO, GRANT statement, 162
WHERE
joins, 96–97
SELECT statement, 85–86
UPDATE statement, 73
WITH, GRANT statement, 163
clearing caches, 186–187
column_priv columns
columns_priv tables, 169
tables_priv tables, 168
column privileges, granting, 165
columns
columns_priv table, 169
database tables, 29
date and time types, 54
db table, 167
host table
privilege columns, 168
scope columns, 167
keys, superkeys, 29
numerical types, 51–52
selecting, 83
string and text types
BLOB, 53
CHAR, 53
ENUM, 53
SET, 54
TEXT, 53
VARCHAR, 53
tables_priv table, 168
unique, 50
user table
privilege columns, 166
resource limitations columns, 167
scope columns, 166
secure connection columns, 166
columns_priv tables, privileges, 169
comma-separated values, 74
commands. See also statements
ALTER TABLE, 55–56
char, 46
CHECK TABLE, 200
COMMIT, transactions, 151
DROP INDEX, 55
EXPLAIN, 241–243
FLUSH, caches, clearing, 186–187
RESET, caches, clearing, 186–187
SET
autocommit mode, disabling, 151
log files, turning on, 187
START TRANSACTION, 151
UNLOCK TABLES, 152–153
varchar, 46
COMMENT command
table optimization, 50
transactions, 151
comparison operators, 112–114
compression
concat() function, 116
MyISAM tables, 133–134
configuring MySQL
InnoDB options, 176–177
internationalization configuration, 178
installation, 13–14
multi-install configuration options, 177–178
mysqld, setting options for, 175–176
options file approach, 173–175
connections
MySQL, testing, 14–15
server security, 205
SSL (Secure Sockets Layer), 208–209
consistency, transactions, 153
control flow functions, 115
conv() function, 116
convert() function, 123
count() function, 125
crackers, security, 208
CREATE DATABASE SQL statement, 43
CREATE privilege, 163
CREATE TABLE SQL statement, 43, 48–51
CREATE TEMPORARY TABLES privilege, 163
creating
accounts, basic use, 15–16
databases, CREATE DATABASE SQL statement, 43
HEAP tables, 141
indexes, 54–55
snapshots, database replication, 216–217
tables
CREATE TABLE SQL statement, 43, 48–51
employee database example, 44–48
optimization options, 50–51
cross joins, 100
curdate() function, 122
curtime() function, 122
D
data, uploading, 73–75
Data Definition Language (DDL), 42
data directory, 18
DATA DIRECTORY option, table optimization, 51
Data Manipulation Language (DML), 42, 65
data types, float, 47
databases
administration activities
caches, clearing, 186–187
database information, retrieving, 183–184
grant and privilege information, viewing, 185
log files, 187
MySQL server, starting up and shutting down, 181–182
mysqladmin option summary, 188
process information, viewing, 185
server status and variables, viewing, 184
table information, viewing, 185–186
threads, terminating, 186
variables, setting, 186
analyzation, ANALYZE TABLE command, 234
backup and restoration
BACKUP TABLE command, 198
from binary log, 198–199
locking tables, 192
manual, 197–198
mysqldump script, 192–196
mysqlhotcopy script, 196–197
RESTORE TABLE command, 198
tables, checking and repairing, 199–201
techniques, 191–192
testing, 199
creating, CREATE DATABASE SQL statement, 43
deleting, 55
design principles
anomalies, 31–32
design considerations, 30
normalization, 32–36
NULL values, 32
redundancy versus loss of data, 30–31
entities, defined, 27
identifiers
character lengths, 43
quote characters, 42
reserved words, 43
indexes
creating, 54–55
deleting, 55
optimization
considerations for, 231–232
design choices, 232
indexes, 233–234
OPTIMIZE TABLE command, 234
relational database management system (RDBMS), 28
relationships
defined, 27
many-to-many, 27
one-to-many, 27
one-to-one, 27
replicating
advanced topologies, 219
binary logging, 214
cascading masters with, 219
failover features, 220
fault-tolerance, 213
future of, 220
load balancing, 219
master configuration, checking, 215–216
multimastering, 220
permissions, granting, 215
principles of, 213–214
security implications, 215
slave servers, configuring, 217–218
slave servers, starting, 218
snapshots, creating, 216–217
version specification, 214
schema, defined, 30
tables
ALTER TABLE, 54
attributes, 29
Boolean expression subqueries, 103–105
CREATE TABLE SQL statement, 48–51
deleting, 55
derived table subqueries, 102
functional dependencies, 30
indexes, 233–234
joining, 96–101
keys, 29
privileges, 165–169
records, 29
renaming, 56
rows, 29
rows, deleting, 70–72
rows, duplicates, 87–88
rows, grouped, 88–89
rows, inserting, 65–69
rows, replacing, 70
rows, selecting, 85–86
rows, truncating, 72
rows, updating, 72–73
selecting, 83–84
single-value subqueries, 102–103
structure of, altering, 55–56
transaction safe, 131–132
tuples, 29
uploading data, 73–75
datadir option, MySQL configuration, 176
date and time functions, 121–123
date column types, 54
DATETIME column type, 54
dayname() function, 122
db tables, privileges, 167
DDL (Data Definition Language), 42
Decimal column type, 52
DEFAULT clause, INSERT statement, 69
DEFAULT keyword, 49
defragmenting MyISAM tables, 134
DELAY KEY WRITE option, table optimization, 51
DELAYED clause, INSERT statement, 68
DELETE privilege, 163
DELETE statement, 70–72
deleting
anonymous accounts, 15, 206–207
databases, 55
indexes, 55
rows, 70–72
tables, 55
deletion nomalies, 31
derived table subqueries, 102
describe command, 48
design choices, database optimization, 232
design principles, databases
anomalies
deletion, 31
insertion, 31
NULL values, 32
update, 32
design considerations, 30
normalization
Boyce-Codd normal form (BCNF), 36
first normal form (1NF), 32–34
second normal form (2NF), 34–35
third normal form (3NF), 35–36
redundancy versus loss of data, 30–31
directory structure, MySQL, 17–18
dirty read transaction isolation level, 155
disk space, MyISAM tables, 133
DISTINCT clause, SELECT statement, 87–88
division (/) operator, 112
DML (Data Manipulation Language), 42, 65
docs directory, 18
dollar sign ($), 118
DOUBLE column type, 52
DROP INDEX command, 55
DROP privilege, 164
DROP TABLE command, 55, 195, 198
dump files, 196
duplicated rows, 87–88
durability, transactions, 154
dynamic MyISAM tables, 133–134
E
Emic Application Cluster tool, 220
encrypt() function, 123
encrypted passwords, 207
entities, defined, 27
ENUM column type, 53
equality (=) operator, 113
equijoins, 100
error-checking applications, 208
error logs, 187
executables, MySQL, 18–19
EXECUTE privilege, 163
EXISTS keyword, 104
EXPLAIN keyword, query execution information, 241–243
extra value, EXPLAIN command, 242
extract() function, 122
F
failover features, replication, 220
fault-tolerance, replication, 213
FIELDS clause, LOAD DATA INFILE statement, 74
files
dump, 196
log files, 187
filtered data, security tips, 208
first normal form (1NF), 32–34
FLOAT column type, 52
float data type, 47
floating point numbers, 47
floor() function, 120
flow control functions, 115
FLUSH command, caches, clearing, 186–187
FLUSH PRIVILEGES statement, 206–207
FLUSH TABLES command, 140, 197
FOREIGN KEY keyword, 50
found rows() function, 124
fragmentation, MyISAM tables, 133
FROM clause
joins, 96
subqueries, derived table, 102
full joins, 100
FULLTEXT keyword, 50
full-text searching
Boolean full-text search, 136–137
MyISAM tables, 135–136
functional dependencies, concepts and terminology, 30
functions
abs(), 120
adddate(), 121
avg(), 125
built-in, 111
CASE, 115
cast, 123
ceiling(), 120
concat(), 116
count(), 125
curdate(), 122
curtime(), 122
date and time, 121–123
dayname(), 122
encrypt(), 123
extract, 122
floor(), 120
flow control, 115
found rows(), 124
grouping, 124–125
IF, 115
last insert id(), 124
length(), 116
LIKE, 117
load file, 116
locate(), 116
lower(), 116
MATCH, 117
max(), 125
md5(), 124
min(), 125
mod(), 120
now(), 122
numeric, 120–121
operators
arithmetic, 112
comparison, 112–113
logical, 114
password(), 124
power(), 120
quote(), 116
rand(), 120
replace(), 116
RLIKE, 117–119
round(), 120
soundex(), 116
sqrt(), 120
std(), 125
string
string comparison, 117–119
string processing, 116
subdate(), 121
substring(), 116
sum(), 125
timestamp, 122
trim(), 116
upper(), 116
G-H
global privileges
granting, 164
security, 206
Google Web site, 138
grant columns, tables_priv tables, 168
GRANT statement
IDENTIFIED BY clause, 163
ON clause, 162
REQUIRE clause, 163
TO clause, 162
WITH clause, 163
WITH GRANT OPTION clause, 163
granting permissions, for replication, 215
granting privileges, 162
administrator-level, 164
column, 165
database, 165
global, 164
table, 165
user-level privileges, 163
greater than (>), 113
GROUP BY clause, SELECT statement, 88–89
grouped rows, 88–89
grouping functions, 124–125
HAVING clause, SELECT statement, 89
host tables, privileges
privilege columns, 168
scope columns, 167
Huffman coding, compression, 134
I
id option, EXPLAIN command, 241
IDENTIFIED BY clause, GRANT statement, 163
identifiers
case sensitivity, 42
character lengths, 43
quote characters, 42
reserved words, 43
IF function, 115
IF NOT EXISTS clause, 49
IGNORE clause
INSERT statement, 69
LOAD DATA INFILE statement, 74
UPDATE statement, 73
IN keyword, 104
INDEX DIRECTORY option, table
optimization, 51
INDEX keyword, 50
INDEX privilege, 163
index value, EXPLAIN command, 242
indexes
creating, 54–55
database optimization, 233–234
deleting, 55
leftmost prefix, 233
multicolumn, 233
optimization considerations, 231
query optimization, 244
single-column, 233
InnoDB configuration options, MySQL configuration, 176–177
InnoDB tables, 50
consistent nonlocking, 137
licensing agreement, 138
row-level locking, 137
transactions with, 147–150
ACID compliance, 153–154
autocommit mode, 151–153
transactions isolation, 154–155
Web site for, 138
INSERT METHOD option, table optimization, 51
INSERT privilege, 163
INSERT statement
clauses, 68–69
general form of, 68
listing, 65–66
output example, 67
inserting rows into tables, 65–69
insertion anomalies, 31–32
installation files, securing
access and privileges, 208
application-level error checking, 208
do’s and don’ts, 207
filtered data, 208
physical security, 209
SSL (Secure Sockets Layer), 208–209
installing MySQL, 9
accounts, creating for basic use, 15–16
anonymous accounts, deleting, 15
on Linux, 10
on OS X, 12
on Windows, 10–11
root password, setting, 15
system configuration, 13–14
testing, 14–15
Integer column type, 52
interfaces
MySQL Control Center, 19
MySQL monitor, 19–21
phpMyAdmin, 19
internationalization configuration, MySQL configuration, 178
ISAM tables, 50
limitations, 132–133
overview, 132
isolation levels (transactions), 153
dirty read, 155
phantom reads, 155
read committed, 155
read uncommitted, 155
repeatable read, 154
serializable, 154
J-K
joins
cross joins, 100
equijoin, 100
full joins, 100
LEFT joins, 100–101
multiple tables, joining, 97–98
RIGHT joins, 100–101
self joins, 99
two tables, joining, 96–97
key buffer, defined, 227
key_len value, EXPLAIN command, 242
key value, EXPLAIN command, 242
keys, candidate keys and superkeys, 29
keywords
ALL, 104
ANY, 104
AUTO INCREMENT, 49
BINARY, 113
EXISTS, 104
FOREIGN KEY, 50
FULLTEXT, 50
IN, 104
INDEX, 50
PRIMARY KEY, 49
ROLLBACK, 150
SOME, 104
TEMPORARY, 49
UNIQUE, 50
UNSIGNED, 52
L
last insert id() function, 124
LEFT join, 100–101
length() function, 116
lengths, identifier characters, 43
less than (<) operator, 113
licensing agreements, InnoDB tables, 138
LIKE clause, 49
LIKE function, 117
LIMIT clause
DELETE statement, 72
SELECT statement, 90–91
UPDATE statement, 73
LINES clause, LOAD DATA INFILE
statement, 74
Linux, MySQL installation, 10
listings
backup and restoration, mysqldump script, 192–195
databases, table creation, 44–46
INSERT statement, 65–66
MERGE tables, 139–140
MySQL configuration, options file approach, 174–175
MySQL installation, configuration options, 13–14
load balancing, replication, 219
LOAD DATA INFILE statement, 73–75
load file() function, 116
locate() function, 116
LOCK TABLES command, 152–153, 195, 197
LOCK TABLES privilege, 163
locking tables, 192
logging
error logs, 187
log-bin option, MySQL configuration, 176
log-error option, MySQL configuration, 176
log-slow-queries option, MySQL configuration, 176
logging options, MySQL configuration, 177
query logs, 187
relay logs, 214
rotating, 187
slow query logs, 187
turning on, 187
logical operators, 114
long query time variable (slow query log), 241
loss of data versus redundancy, database design, 30–31
LOW PRIORITY clause
DELETE statement, 72
INSERT statement, 68
LOAD DATA INFILE statement, 74
UPDATE statement, 73
lower() function, 116
M
manual backups and restoration, 197–198
many-to-many relationships, 27–28
master servers, replication, master configuration, 213–216, 219
MATCH function, 117
max() function, 125
MAX ROWS option, table optimization, 51
md5() function, 124
MEDIUM integer column type, 52
min() function, 125
MIN ROWS option, table optimization, 51
mod() function, 120
multicolumn indexes, 233
multimastering, replication, 220
multiplication (*) operator, 112
MyISAM tables, 50
compressed, 133
compressing, 134
dynamic, 133–134
full-text searching, 135–137
improvements to, 132–133
overview, 133
repairing, 134
static, 133–134
myisamchk command-line tool, 134
myisamchk program, 18, 200–201
MySQL
configuring
InnoDB, 176–177
internationalization configuration, 178
multi-install configuration options, 177–178
mysqld, setting options for, 175–176
options file approach, 173–175
directory structure, 17–18
executables, 18–19
installing, 9
accounts, creating for basic use, 15–16
anonymous accounts, deleting, 15
on Linux, 10
on OS X, 12
on Windows, 10–11
root password, setting, 15
system configuration, 13–14
testing, 14–15
server
process information, viewing, 185
starting up and shutting down, 181–182
server tuning, optimization
parameters, 226–227
RAID utility, 228
Solaris utility, 228
speed, compiling and linking for, 225–226
user interfaces
MySQL Control Center, 19
MySQL monitor, 19–21
phpMyAdmin, 19
MySQL Control Center, 19
MySQL monitor, 19–21
mysqlbinlog program, 18
mysqlcheck program, 201
mysqld, setting options for, 175–176
mysqldump script, 18
—add-drop-table option, 195
advantages of, 196
—all-databases option, 196
—allow-keywords option, 196
—databases option, 196
disadvantages of, 196
—extended-insert option, 196
—lock-tables option, 196
—no-data option, 196
—opt option, 192
—quick option, 195
sample output, 192–195
mysqlhotcopy script, 196–197
mysqlshow program, 18
N-O
normalization
Boyce-Codd normal form (BCNF), 36
defined, 32
first normal form (1NF), 32–34
second normal form (2NF), 34–35
third normal form (3NF), 35–36
NOT NULL value, table creation, 49
NOT operator, 114
now() function, 122
NULL value
comparison operators, 112–113
database design, 32
table creation, 49
numeric functions, 120–121
numerical columns, 51–52
ON clause, GRANT statement, 162
ON DUPLICATE KEY UPDATE clause, INSERT statement, 69
one-to-many relationships, 27–28
one-to-one relationships, 27–28
OpenSSL Library Web site, 209
operators
arithmetic, 112
built-in, 111
comparison, 112–114
logical, 114
optimization
databases
considerations for, 231–232
design choices, 232
indexes, 233–234
OPTIMIZE TABLE command, 234
MySQL server tuning
parameters, 226–227
RAID utility, 228
Solaris utility, 228
speed, compiling and linking for, 225–226
queries
ANALYZE TABLE command, 244
benchmarking, 240
built-in query optimization, 243–244
EXPLAIN command, 241–243
indexes, adding, 244
OPTIMIZE TABLE command, 244
slow queries, finding, 239
slow query log, 240–241
table creation, 50–51
OPTIMIZE TABLE command, 134, 234, 244
options files, MySQL configuration, 173–174
InnoDB options, 176–177
internationalization configuration, 178
multi-install configuration options, 177–178
mysqld, setting options for, 175–176
OR operator, 114
ORDER BY clause
DELETE statement, 72
SELECT statement, 90
table rows, 56
UPDATE statement, 73
OS X, MySQL installation, 12
P
PACK KEYS option, table optimization, 51
page-level locking, BerkeleyDB (BDB) tables, 138
parameters, servers, optimization techniques, 226–227
password() function, 124
PASSWORD option, table optimization, 51
passwords
encrypted passwords, 207
permissions, for replications, 215
phantom reads transaction isolation level, 155
phpMyAdmin interface, 19
pid-file options, MySQL configuration, 177
portability, MyISAM tables, 132
possible_keys value, EXPLAIN command, 242
power() function, 120
PRIMARY KEY keyword, 49
privilege columns
db tables, 167
host tables, 168
user tables, 166
FILE, 207
granting, 162
administrator-level, 164
column privileges, 165
database privileges, 165
global privileges, 164
table privileges, 165
user-level privileges, 163
PROCESS, 207
revoking, 165
securing
dangerous privileges, 207
FLUSH PRIVILEGES statement, 206–207
global privileges, 206
server connections, 205
tables, 165
columns_priv tables, 169
db tables, 167
host tables, 167–168
tables_priv tables, 168
user tables, 166–167
viewing information about, 185
WITH GRANT OPTION, 207
process information (server status), 185
Q
queries
joins
cross joins, 100
equijoin, 100
full joins, 100
joining multiple, 97–98
LEFT joins, 100–101
RIGHT joins, 100–101
self joins, 99
log files, 187
optimization
ANALYZE command, 244
benchmarking, 240
built-in query optimization, 243–244
EXPLAIN command, 241–243
indexes, adding, 244
OPTIMIZE TABLE command, 244
slow queries, finding, 239
slow query log, 240–241
SELECT statement, 82
subqueries
Boolean expression, 103–105
derived table, 102
single-value, 102–103
types of, 101
QUICK clause, DELETE statement, 72
quote characters, identifiers, 42
quote() function, 116
R
RAID TYPE option, table optimization, 51
RAID utility, MySQL server, optimization techniques, 228
rand() function, 120
range testing, comparison operators, 114
range value, EXPLAIN command, 242
RDBMS (relational database management system), 29
read buffer, 227
read committed transaction isolation level, 155
read uncommitted transaction isolation level, 155
records, database tables, concepts and terminology, 29
recovery. See backup and restoration
redundancy versus loss of date, database design, 30–31
ref value, EXPLAIN command, 242
REFERENCES privilege, 164
regular expression matching, RLIKE function, 117–119
relational database management system (RBMS), 28
relationships
defined, 27
many-to-many, 27–28
one-to-many, 27–28
one-to-one, 27–28
relay logs, 214
RELOAD privilege, 164
RENAME clause, 56
renaming tables, 56
REPAIR TABLE command, 134, 200
repairing MyISAM tables, 134
repairing and checking tables
CHECK TABLE command, 200
myisamchk program, 200–201
mysqlcheck program, 201
REPAIR TABLE command, 200
techniques for, 199
repeatable read transaction isolation level, 154
replace() function, 116
REPLACE statement, 70
replacing rows, 70
replicating databases
advanced topologies, 219
binary logging, 214
failover features, 220
fault-tolerance, 213
future of, 220
load balancing, 219
master configuration, checking, 215–216
multimastering, 220
permissions, granting, 215
principles of, 213–214
security implications, 215
slave configuration, 217–218
slaves, starting, 218
snapshots, creating, 216–217
version specification, 214
with cascading masters, 219
REPLICATION CLIENT privilege, 164
REPLICATION SLAVE privilege, 164
REQUIRE clause, GRANT statement, 163
reserved words, identifiers as, 43
RESET command, caches, clearing, 186–187
resource limitation columns, user tables, 167
restoration. See backup and restoration
RESTORE TABLE command, 198
REVOKE statement, 165
revoking privileges, 165
RIGHT join, 100–101
RLIKE function, 117–119
ROLLBACK keyword, 150
rotating log files, 187
round() function, 120
ROW FORMAT option, table optimization, 51
row-level locking, InnoDB tables, 137
rows
database tables, concepts and terminol- ogy, 29
deleting, 70–72
duplicates, 87–88
grouped, 88–89
inserting into tables, 65–69
replacing, 70
selecting, 85–86
truncating, 72
updating, 72–73
rows value, EXPLAIN command, 242
RPMs, MySQL installation, 10
S
schema, defined, 30
scope columns
columns_priv tables, 169
db tables, 167
host tables, 167
tables_priv tables, 168
user tables, 166
scripts directory, 18
searching
full-text searching, 136–137
MyISAM tables, 135–136
second normal form (2NF), 34–35
secure connection columns, user tables, 166
Secure Sockets Layer (SSL), 208–209
security
accounts
anonymous accounts, deleting, 206–207
dangerous privileges, 207
encrypted passwords, 207
root passwords, 206
installation files
access and privileges, 208
application-level error checking, 208
do’s and don’ts, 207
filtered data, 208
physical security, 209
SSL (Secure Sockets Layer), 208–209
privilege systems
global privileges, 206
server connections, 205
replication systems, 215
SELECT privilege, 163
SELECT statement
aliases, 84–85
columns, 83
databases, 83–84
DISTINCT clause, 87–88
GROUP BY clause, 88–89
HAVING clause, 89
LIMIT clause, 90–91
ORDER BY clause, 90
overview, 82
simple query example, 82
table creation, 51
tables, 83–84
WHERE clause, 85–86
selecting
databases, use statement, 43
rows, 85–86
select_type option, EXPLAIN command, 241
self joins, 99
semicolon (;), 45
serializable transaction isolation level, 154
servers
master, replication, 213–216, 219
MySQL
optimization techniques, 225–228
process information, viewing, 185
starting up and shutting down, 181–182
status of, viewing, 184
slaves
starting, 218
Services window (Windows Administrative tools), 182
SET column type, 54
SET command
autocommit mode, disabling, 151
log files, turning on, 187
variables, setting, 186
shared-memory-base-name options, MySQL configuration, 177
SHOW DATABASES privilege, 163
show processlist statement, 186
show variables statement, 186
SHUTDOWN privilege, 164
shutting down MySQL server, 181–182
single-column indexes, 233
single-value subqueries, 102–103
Slashdot Web site, 138
slave servers
starting, 218
Sleepycat Web site, 138
slow queries server value, 184
SMALLINT integer column type, 52
snapshots, creating, 216–217
socket options, MySQL configuration, 177
Solaris utility, MySQL server, optimization techniques, 228
SOME keyword, 104
sort buffer, 227
soundex() function, 116
speed, MySQL server, 225–226
sql-bench directory, 18
SQL statements
CREATE DATABASE, 43
DELETE, 70–72
INSERT, 65–69
REPLACE, 70
SELECT, 82–91
TRUNCATE, 72
UPDATE, 72–73
sqrt() function, 120
SSL (Secure Sockets Layer), 208–209
START TRANSACTION command, 151
starting
MySQL server, 181–182
slaves, 218
statements. See also commands
ALTER TABLE, 54
DROP TABLE, 195
FLUSH PRIVILEGES, 206–207
GRANT, 162–163
LOAD DATA INFILE, 73–75
LOCK TABLES, 195
ON, GRANT statement, 162
REVOKE, privileges, 165
SELECT, table creation, 51
SET, variables, 186
show processlist, threads, 186
show variables, 186
SQL
CREATE DATABASE, 43
DELETE, 70–72
INSERT, 65–69
REPLACE, 70
SELECT, 82–91
TRUNCATE, 72
UPDATE, 72–73
UNLOCK, 195
use, database selection, 43
static MyISAM tables, 133–134
std() function, 125
stop words, full-text searching, 136
string and text column types, 53–54
string functions
LIKE function, 117
MATCH function, 117
RLIKE function, 117–119
string processing, 116
subdate() function, 121
subqueries
Boolean expression, 103–105
derived table, 102
single-value, 102–103
types of, 101
substring() function, 116
subtraction (-) operator, 112
sum() function, 125
SUPER privilege, 164
superkeys, 29
T
table cache parameter, 227
table value, EXPLAIN command, 242
tables
ALTER TABLE statement, 54
BACKUP TABLE command, 198
Boolean expression subqueries, 103–105
checking and repairing
CHECK TABLE command, 200
myisamchk program, 200–201
mysqlcheck program, 201
REPAIR TABLE command, 200
techniques for, 199
columns
date and time types, 54
numerical type, 51–52
selecting, 83
string and text types, 53–54
unique, 50
creating
CREATE TABLE SQL statement, 43, 48–51
employee database example, 44–48
optimization options, 50–51
database tables, 28
derived table subqueries, 102
DROP TABLE command, 198
FLUSH TABLES command, 197
indexes
leftmost prefix, 233
multicolumn, 233
optimization, 233–234
single-column, 233
InnoDB, 50
consistent nonlocking, 137
licensing agreement, 138
row-level locking, 137
transactions with, 147–155
Web site for, 138
ISAM, 50
limitations, 132–133
overview, 132
joining
cross joins, 100
equijoin, 100
full joins, 100
LEFT joins, 100–101
multiple tables, 97–98
RIGHT joins, 100–101
self joins, 99
two tables, 96–97
LOCK TABLES command, 197
locking, 192
MERGE, FLUSH TABLES command, 140
MyISAM, 50
compressed, 133–134
dynamic, 133–134
full-text searching, 135–137
improvements to, 132–133
overview, 133
repairing, 134
static, 133–134
privileges, 165
columns_priv tables, 169
db tables, 167
host tables, 167–168
tables_priv tables, 168
user tables, 166–167
reference information, viewing, 185–186
renaming, 56
RESTORE TABLE command, 198
rows
deleting, 70–72
duplicates, 87–88
grouped, 88–89
inserting, 65–69
replacing, 70
selecting, 85–86
truncating, 72
updating, 72–73
selecting, 83–84
single-value subqueries, 102–103
structure of, altering, 55–56
transaction safe, 131–132
uploading data, 73–75
tables_priv tables, privileges, 168
TEMPORARY keyword, 49
testing
backup procedures, 199
MySQL installation, 14–15
text and string column types
BLOB, 53
CHAR, 53
ENUM, 53
SET, 54
TEXT, 53
VARCHAR, 53
TEXT column type, 53
third normal form (3NF), 35–36
threads, terminating, 186
threads connected server value, 184
time and date functions, 121–123
time column types, 54
TIMESTAMP column type, 54
Timestamp columns, columns_priv tables, 169
timestamp() function, 122
TINYINT integer column type, 52
TO clause, GRANT statement, 162
transaction safe tables
BerkeleyDB (BDB), 138
InnoDB
consistent nonlocking, 137
licensing agreement, 138
row-level locking, 137
Web site for, 138
types of, 131–132
transactions
defined, 147–150
START TRANSACTION command, 151
with InnoDB tables, 147–150
ACID compliance, 153–154
autocommit mode, 151–153
transaction isolation, 154–155
trim() function, 116
TRUNCATE statement, 72
truncating rows, 72
tuples, concepts and terminology, 29
type value, EXPLAIN command, 242
U
UNION LENGTH option, table optimization, 51
UNIQUE keyword, 50
UNLOCK TABLES command, 152–153, 195
UNSIGNED keyword, 52
update anomalies, 32
UPDATE privilege, 163
UPDATE statement, 72–73
updating rows, 72–73
uploading data, 73–75
uppercase() function, 116
uptime server value, 184
USAGE privilege, 163
use statement, database selection, 43
user accounts, security
anonymous accounts, deleting, 206–207
dangerous privileges, 207
encrypted passwords, 207
root passwords, 206
user interfaces
MySQL Control Center, 19
MySQL monitor, 19–21
phpMyAdmin, 19
user-level privileges, 163
user option, MySQL configuration, 176
user permissions, for replication, 215
user tables, privileges
privilege columns, 166
resource limitation columns, 167
scope columns, 166
secure connection columns, 166
V-W
VARCHAR column type, 53
varchar command, 46
variables
setting values of, 186
viewing, 184
version specification, database replication, 214
Web sites
activestate.com, 241
Google, 138
InnoDB, 138
OpenSSL, 209
Slashdot, 138
Sleepycat, 138
Yahoo!, 138
WHERE clause
joins, 96–97
SELECT statement, 85–86
UPDATE statement, 73
wildcard matching, LIKE function, 117
Windows, MySQL installation, 10–11
Windows Administrative tools, Services window, 182
WITH clause, GRANT statement, 163
WITH GRANT OPTION clause, GRANT statement, 163
WITH GRANT OPTION privilege, 164, 207
X-Y-Z
XOR operator, 114
Yahoo! Web site, 138
YEAR column type, 54