MySQL QuickFacts
as of 2013/05/29
Note: All executables (e.g., mysqladmin) reside in the install directory "bin"
subdirectory unless noted otherwise.
; (semicolon) In interactive mode, conventionally used
to end your input and request results.
It looks like just a final delimiter,
but in fact is the MySQL "go" command:
you can use "go", "\g", or ";".
# In MySQL options files, a pound-sign
starts a comment, where it may be at the
beginning of a line or in the midst of a
line. See: Comments
% (percent sign) On a GRANT statement, to allow a
username access from any host, etc.
% (percent sign) Arithmetic/modulo operation to return
the remainder resulting from dividing
one number by another, rather than the
quotient. Form: N1 % N2
Example: 5 % 3 returns 2
% (percent sign) In a LIKE, serves as a pattern-matching
operator to match any number of
characters, 0 to infinity.
_ (underscore) In a Select LIKE, serves as a
pattern-matching operator to match any
single character.
Note that if you encounter an SQL
implementation which does not adhere to
the standard of a backslash character
serving as an escape char to turn off
the wildcardness of the underscore, as
in 'Oraclsfs\_data%'
you can usually make use of the ESCAPE
operator to define the backslash to be
the escape char, as in
'Oraclsfs\_data%' ESCAPE '\'
` (grave accent mark) Backtick character, as so named in MySQL
documentation.
-= MySQL does not have such a convenience
arithmetic operator. Instead, you must
spell out the operation on a field:
<FieldName> = <FieldName> - <Number>
+= MySQL does not have such a convenience
arithmetic operator. Instead, you must
spell out the operation on a field:
<FieldName> = <FieldName> + <Number>
\G The MySQL EGo command. In interactive
mode, use that at the end of your input
(or "ego") to conclude your input and
request results, to have them output
"vertically", meaning Label: Value
pairs, which is to say line by line
reporting instead of tabular reporting.
\g The MySQL Go command. In interactive
mode, use that at the end of your input
(or "go" or ";") to conclude your input
and request results.
|| Logical OR operator.
In some SQL implementations, || effects
concatenation, as in
SELECT First_Name || Last_Name AS
"Person's name" ...
but not in MySQL (unless started with
--ansi or --sql-mode PIPES_AS_CONCAT):
use CONCAT() instead to concatenate
strings.
!= SQL: Means "not equal", in a query.
(Equivalent to <> .)
!include An options file specification to define
a supplementary file whose contents
should be inserted at this point.
This is a good way to have a single,
"boiler plate" /etc/my.cnf for multiple
MySQL servers and to have variations, as
for example log file names which vary by
host.
<> SQL: Means "not equal", in a query.
(Equivalent to != .)
For example, in a Select, use the form
WHERE Something <> "Whatever"
to report records where the field does
not equal that value, as opposed to
WHERE Something = "Whatever"
which would report records where the
field does equal that value.
[ ] Construct occupying a line in an options
file (e.g., /etc/my.cnf) to name a group
to which all following option-setting
lines apply, up to the end of the option
file or another group line is given.
Example: [mysqld]
3306 The standard mysqld service port number,
TCP.
Is in /etc/services as "mysql", both TCP
and UDP, though UDP doesn't make much
sense.
May be specified by environment variable
MYSQL_TCP_PORT when mysqld_safe is
invoked, or directly via invocation
parameter --port NNNN.
See also: --port
Access control See: GRANT
Accounts See: MySQL accounts
Add record See: INSERT INTO
AIX version/release compatibility What version and release level of AIX is
a given MySQL version for? You can find
out by examining the machine-specific
subdirectory of the distribution
directory structure. For example, a
given MySQL version which has a top
level directory of "mysql-4.0.18" may
have an immediate subdirectory of
"mysql-standard-4.0.18-ibm-aix4.3.3.0-
powerpc", which obviously comes right
out and says what AIX it is for.
ALTER Statement to modify the structure of a
table, to add, change, or remove columns
from an existing table and to remove
indexes. Operates by copying the table
to effect the change: all table altering
user operations are held in queue.
ALTER TABLE To rename tables, add or drop or rename
columns, change column types, etc.
This operation always proceeds by
copying the subject table to incorporate
the desired change into the copy, where
the copy will replace the original if
all goes well (else the new copy will be
discarded, the original remaining
intact). This process necessitates a
write-lock on the table; but reading can
proceed. The amount of time taken for
the operation is proportional to the
amount of data in the table.
If the database/table is being
replicated, an Alter performed in the
master server will be effected in both
the master and slave.
Arithmetic MySQL performs all arithmetic using
signed BIGINT or DOUBLE values.
It's okay for column names and operators
and operands to be abutted...
Col1=Col1+5
is as good as
Col1 = Col1 + 5
AS SQL clause to define a column alias, as
in:
SELECT USER_FIRST_NAME AS "First Name"
That is, rather than the output column
header having the natural name of the
column, an arbitrary name/string is
assigned for the header instead. More
than that, AS can be used to create
output columns whose contents derive
from other columns, as in:
SELECT (Col1 - Col2) AS "Difference".
And AS can create a column from
arbitrary text which has no relation to
table contents, as in:
SELECT "Blah blah" AS "Gibberish";
MySQL 5.x now adheres to SQL rules which
disallow using column aliases in
qualified searches, such as in an ORDER
BY clause. The actual column name must
be used there. See http://dev.mysql.com
/doc/refman/5.0/en/problems-with-alias.html
ASC Ascending: a sort method modifier of
the Order By clause like:
order by "Balance" ASC
to sort on that column in ascending
order, smallest value first.
See also: DESC
Attributes, column See: Column attributes
Attributes, row These are actually table attributes,
via CREATE TABLE. Such attributes are:
AUTO_INCREMENT, AVG_ROW_LENGTH,
[DEFAULT] CHARACTER SET, CHECKSUM,
COMMENT, DATA DIRECTORY,
DELAY_KEY_WRITE, INDEX DIRECTORY,
INSERT_METHOD, MAX_ROWS, MIN_ROWS,
PACK_KEYS, PASSWORD, RAID_TYPE,
ROW_FORMAT, TYPE, UNION.
Note that there is no retention period
for automatically purging older table
entries.
Authorization for access Is established via the GRANT statement,
as in example:
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE
ON SomeDB.* TO 'SomeUser'@'128.197.%.%'
IDENTIFIED BY 'Password';
AUTO_INCREMENT Attribute modifier for a column data
type, which causes each new instance to
have an unsigned numeric value one
greater than the largest value currently
in the table.
The SQL function LAST_INSERT_ID(), or
mysql_insert_id() C API function,
returns the last auto-incremented value
which was effected by this session
(network address and port).
Rules to be aware of:
- Only the integer or floating-point
types work correctly as AUTO_INCREMENT
columns. The only spec which really
makes sense is INTEGER UNSIGNED.
- Non-positive numbers are not allowed.
- The numbering does not wrap around:
once the highest possible value is
reached, attempts to Insert result in
an error condition. NOTE WELL!!
- The starting number can be defined
when defining the table, or you can
alter it as in:
ALTER TABLE tbl AUTO_INCREMENT = 1000;
where the specified value should be
higher than the highest number in the
table currently.
- An auto_increment field is almost
always a key, and can be used for the
Where clause in an Update operation to
choose the record for updating.
- Performing TRUNCATE TABLE, to clear
the full contents of a table, will
cause the sequence number to revert
to 1 for the next inserted row.
- Adding an AUTO_INCREMENT column to a
table without one will cause all
current entries to be numbered.
- Renumbering after, say, deleting
random rows from within the table, can
be achieved by removing then
redefining that sequence column. This
is obviously no good where the number
is a key to specific data.
- Copying a table does not result in the
re-sequencing of Auto Increment field
numbering: such numbers are copied
as-is.
Drawbacks to AUTO_INCREMENT:
- An Insert does not return the
generated sequence number, which you
may want your program to know for
reporting purposes: you always have to
issue the additional LAST_INSERT_ID().
- Getting the LAST_INSERT_ID() requires
a persisting connection to the MySQL
server, meaning that one could not
have a utility program perform an
Insert and then expect some second
command to obtain the instance number
resulting from the Insert, as those
would necessarily be two, separate
sessions.
Variations:
- If you delete the records having the
highest increment values, the vacated
numbers in the sequence will *not* be
reclaimed in InnoDB. Thus, if your
table had a high increment value of 5
and you inserted 10 records, then
deleted them, the next record inserted
would get an increment value of 16,
not 6.
Modifiers: auto_increment_increment
(MySQL 5.0.2) can specify that the
incrementation value be more than 1.
auto_increment_offset (MySQL 5.0.2) can
specify that the starting value be other
than 1.
See also: INSERT, DELAYED modifier;
LAST_INSERT_ID()
Automatic field updating See: Timestamp
Backup, recovery Data preservation obviously needs to be
considered in a MySQL implementation.
Multiple methods are possible, some
provided by MySQL itself, and some
through operating system facilities.
In Linux, an opportunity is provided
where one can do 'mysqladmin flush-tables'
followed immediately by an LVM snapshot
copy, to then perform a backup from that
copy. BUT: Be aware that a Flush Tables
operation is purely within MySQL, while
the media writer is a storage engine
such as InnoDB, which may still be
caching data, such that your backup may
not be fully consistent, despite your
best efforts.
Another approach, for InnoDB-based
tables is to use 'mysqldump'with
--single-transaction.
See: mysqlhotcopy
BACKUP TABLE Deprecated statement for online backup.
Noted here only for awareness - do not
use.
Related: RESTORE TABLE
Base Directory Where the MySQL software lives.
See: basedir; Data Directory
basedir Server option specifying the path name
to the root directory of the MySQL
installation...where the MySQL bin,
include, lib, and other directories
live. Normally, you do not need to
specify this, as its location is
implicit in invocation of mysqld.
Command line: --basedir=_____
An RPM install on Linux puts the MySQL
executables into /usr/bin/ and modules
into /usr/lib64/mysql/.
See also: datadir
Basedir, determine Command SHOW VARIABLES like 'basedir';
will display it.
BEGIN Statement to begin a transaction, as
present in MySQL 4.
Syntax:
BEGIN [WORK]
In MySQL 5 and beyond, instead use:
START TRANSACTION
See also: COMMIT; ROLLBACK
BIGINT MySQL data type: an integer value.
Length: 8 bytes
Defaults to a signed number, having
possible values: -9223372036854775808
to 9223372036854775807
Can be defined with UNSIGNED, as in
"LargeNumField bigint unsigned" to have
a range of 0 to 18446744073709551615.
See also: INT
bin-log You mean: log-bin
Binary Log The modern method of recording table
updates, for supplementing periodic
database backups in performing MySQL
databases so as to reflect all changes.
SQL statements which do not result in
changes (SELECT, SHOW) are not reflected
in the binary log. Thus, failed
transactions are not recorded in the
log.
Config file spec for [mysqld] section:
log-bin = ________
where the filename is relative to
datadir (though you can specify a full
path to have the binary log elsewhere).
As mysqld command line parameter:
--log-bin _________
log-bin is chiefly used to cause the
binary logs to be written to a disk
separate from the database, as a
recoverability measure (though that
provides no protection if the disk
containing the logs dies).
Binary logs are created with a numeric
filename extension, making each unique.
For example, if log-bin = binary_log
then each binary log will be named like
binary_log.000145
As a Flush Logs or server restart is
performed, the server transitions from
the generation-numbered log that it had
been using to a new generation-numbered
log. This transition is automatic when
the log's size reaches the
max_binlog_size. When invoking
mysqldump to create a database backup,
use the --flush-logs option to cause a
binary log transition.
Management: A ____.index file is
created when binary logging is in
effect, to track all the individual
binary log files. (The server option
log-bin-index specifies the index file
name.)
Pruning: Excess, old generations can be
cleaned out via PURGE BINARY LOGS, which
updates the index file. You would do
this only if expire_logs_days was not in
effect, or that today's logs are rapidly
piling up. Missing from MySQL is a way
to prune binary logs by number of
generations. DO NOT physically delete
binary log files.
Inspection: The 'mysqlbinlog' command
can be used to see what is in a given
binary log file.
Recovery: The ___.sql file output from
mysqldump includes lines which identify
the binary log file that was started in
the transition per presence of the
--flush-logs option on the command line,
allowing you to know what binary log
files need to be applied following a
restoral from the backup file, to being
the database up to currency. Employing
a date-time (YYYYYMMDD+hhmmss) in the
backup file name greatly facilitates log
identification, using log timestamps.
Applying binary log files is
accomplished via
'mysqlbinlog <Filenames> | mysql'.
It is wise to do 'mysqladmin flush-logs'
on a scheduled basis followed by a
backup of the logs, to safeguard them.
Performance impact: Minimal. Doc says
a 1% slowdown.
The binary log is required for MySQL
Replication, as the cumulation point for
statements to be sent to slave servers.
See also: expire_log_days; log-bin;
max_binlog_size; mysqlbinlog; Update Log
Binary Log, inspect 'mysqlbinlog' command (q.v.)
Binary Log files, prune See: Binary Log; expire_logs_days;
PURGE BINARY LOGS
Binary Log files, report SHOW BINARY LOGS
Binary Log files, restore? Where replication has gone awry and the
Slave Status Master_Log_File shows a
master server binary log file which
expired from the master some time ago,
is it feasible to restore the old binary
logs to the master server directory and
bring the slave forward?
That's problematic... Sure, old binary
logs can be restored to the server
directory, but: the master server is
working from a list of binary log files
as showd in SHOW BINARY LOGS output and
in the binary logs index file. You
cannot simply update the index file.
The only way is to bring down the
master, update its index file, change
its expire_logs_days to 0 to prevent
obliteration of the restored binary log
files, and then start the server and do
START SLAVE on the slave server. This
is very disruptive. It may make more
sense to take a database dump from the
master and restore databases to the
slave server - which is not functioning
anyway.
Binary Log Index file Contains a list of all the binary log
files, like:
./binary_log.004215
./binary_log.004216
The list is as reported by command
SHOW BINARY LOGS;.
Replication inherently references this
list of log files.
This file is updated according to the
expire_logs_days value when the server
is restarted, or a FLUSH LOGS is done,
or max_binlog_size is reached.
The server keeps this file open, so you
should not meddle with it.
See also: expire_logs_days; FLUSH LOGS;
log-bin-index; RESET MASTER
bind-address Server configuration option (my.cfg) to
bind mysqld's TCP port to a specific
network interface.
For some purposes, you may want to
specify it as 127.0.0.1 .
See also: skip-networking
bind_columns In the Perl API (DBI), this is a
"method', to associate Perl variables
with the output columns of a SELECT
statement.
binlog_format Server configuration option (my.cfg) to
spefify the binary logging format.
Possible values:
STATEMENT Statment-based.
ROW Row-based.
MIXED (new as of 5.1.8)
Mixed logging format: the server
switches between statement- and
row-based logging automatically.
Default, as of 5.1.12: MIXED
BITINT (UNSIGNED) MySQL data type: the largest integer
type.
Storage size: 8 bytes
BLOB MySQL data type: Binary form of TEXT.
Is implicitly variable length.
Note that if you have BLOB columns in a
table, you cannot also have CHAR
columns: MySQL silently convers them to
VARCHAR. (This behavior is not exhibited
by other SQL databases.)
Boolean data type? There is no such thing. What you can
instead do is utilize an Enum variable
and give it True, False values.
Booleans SQL has three-valued logic: TRUE, FALSE
and UNKNOWN (or NULL).
C API MySQL provides an API for C programmers.
SQL may be executed via the functions:
mysql_query()
mysql_real_query()
mysql_send_query()
(Though the function names include the
word "query", the functions may be used
for general SQL operations - data
retrieval, updating, etc.) Query
functions can acquire data, into a
MYSQL_RES result area, which is
subsequently retrieved for processing by
the C program via one of:
mysql_store_result()
mysql_use_result()
When done with the MYSQL_RES area,
release its memory with:
mysql_free_result()
Programming steps:
1. Initialize environment.
2. Connect to server - start session.
3. Issue SQL command.
4. Accept results back to client.
5. Process the results.
6. Close the connection to the server -
end session.
7. Free the client results area.
Note: All data is retrieved as character
strings, no matter the data format as
defined in MySQL. As such, it is valid
to process most data as conventional
strings; but beware database binary
data, which can contain binary zeroes
which look like string-end nulls.
C API and SQL programming concepts The C programmer is accustomed to
calling functions to perform operations,
and naturally looks for MySQL C API
functions to perform database table
operations such as column manipulation.
There are no such functions: the
orientation is different... The gist of
SQL work with C is that you simply use C
to pass SQL operations and data to and
from the MySQL server. For example, to
increment a numerical column value, you
do not have to do a Select, convert
returned character numerical to binary,
increment the binary, convert back to
character, then do a Replace: instead,
you simply perform an SQL operation via
mysql_query() to Update the column,
using the form
<Column_Name> = <Column_Name> + 1 .
Cascade Term specifying that a delete or update
to a parent table should also occur to a
child table, per such specification in
its Foreign Key (q.v.).
CAST() CAST(Expression AS Type)
To force solumns to have a particular
type within a table.
CHANGE MASTER TO MySQL replication command to be invoked
on the slave server to change parameters
use by the slave in accessing the master
server. Like:
CHANGE MASTER TO
MASTER_HOST = 'master_host',
MASTER_PORT = 3306,
MASTER_USER = 'slave_user',
MASTER_PASSWORD = 'slave_password';
(Don't overlook the need for a comma
after each parameter.)
Note: MASTER_PORT default is 3306.
Additional parameters are:
MASTER_LOG_FILE = ________
MASTER_LOG_POS = ________
which are not needed if replication is
being initially set up: they are needed
only if replication is being adjusted
after having been in effect for some
time.
Can also change the name of the slave's
relay log file
RELAY_LOG_FILE = "..."
To fully remove replication, do:
STOP SLAVE;
CHANGE MASTER TO Master_Host='';
If problems:
RESET SLAVE;
CHAR(length) MySQL data type: a fixed length
character field. The length limits the
number of characters which can be
stored. Contents are right-padded with
blanks to satisfy the fixed length
during storage: such padding is removed
in retrieval.
CHAR(0) may be used as a placeholder for
defining a column for future use. This
definition requires only one bit in the
table.
This is the most common column type.
However: CHAR and VARCHAR cannot both be
used in the same table.
See also: TEXT; VARCHAR
CHARACTER Synonym for CHAR.
character_set_server System variable: The server's default
character set.
Note that MySQL generally operates with
a utf8 character set, an
ASCII-preserving encoding method for
Unicode (ISO 10646).
Example: character_set_server = utf8
CHARACTER VARYING Synonym for VARCHAR.
CHECK TABLE Statement for interfacing to the
server's table checking capabilities.
Works for MyISAM and InnoDB tables.
Syntax:
CHECK TABLE <TableName> [, <TableName>]
... [Options] ...
where Options are:
FOR UPGRADE Checks whether the named
tables are compatible with the current
version of MySQL.
QUICK Do not scan the rows to
check for incorrect links.
FAST Check only tables that
have not been closed properly.
MEDIUM Scan rows to verify that
deleted links are valid. This also
calculates a key checksum for the rows
and verifies this with a calculated
checksum for the keys.
EXTENDED Do a full key lookup for
all keys for each row. This ensures that
the table is 100% consistent, but takes
a long time.
CHANGED Check only tables that
have been changed since the last check
or that have not been closed properly.
In general, this function will only
check tables; however, it may make some
changes, if the table is marked as
"corrupted" or "not closed properly" but
CHECK TABLE does not find any problems
in the table. In this case, CHECK TABLE
marks the table as okay.
Sample usage: See if the table was
closed properly:
CHECK TABLE TableName FAST QUICK;
Child Table Terminology to identify a table
containing a FOREIGN KEY definition,
referencing a key field in another table
(called the Parent Table).
Client-server connection methods The following transmission methods can
be used for client-server communication:
- TCP sockets.
- Unix domain socket files.
Column, add ALTER TABLE [<DatabaseName.>]<TableName>
ADD COLUMN <ColumName_and_Declaration>
[FIRST | AFTER <ColumName>];
Column, address by name We would all like to be able to address
a field within a result set by name -
but there is no such capability.
Instead, we have to inspect each field
via mysql_fetch_field() or
via mysql_fetch_fields() or
via mysql_fetch_field_direct().
Column, change attributes Invoked under mysql:
ALTER TABLE <TableName> MODIFY COLUMN
<ColumName> <Column_Declaration>;
Example:
ALTER TABLE MyTable MODIFY COLUMN
Column3 INT;
This can also be done with slightly more
work with the Change operator, but you
have to specify the column name twice:
ALTER TABLE <TableName> CHANGE COLUMN
<ColumName> <ColumName_and_Declaration>;
Example:
ALTER TABLE MyTable CHANGE COLUMN
Column3 Column3 INT;
Column, change contents UPDATE <TableName> SET
<ColumnName> = <Value>
WHERE <row selector...>;
Example:
UPDATE Db1.Table1 SET
Address = '132 Main Street'
WHERE Person = 'Smith,Joe';
Note that MySQL will not update a field
where the replacement value is the same
as the current value: it results in
Rows matched: 1 Changed: 0
Column, change varchar len Invoked under mysql:
ALTER TABLE <TableName> CHANGE COLUMN
<ColumName> <ColumName_and_Declaration>;
Example:
ALTER TABLE Db1.Table1 CHANGE COLUMN
Address Address VARCHAR(32);
Note that you need to specify the column
name twice, identically, as the second
occurrence is the "new name", and you
don't want to rename it, just change its
attributes.
Column, descriptive comment COMMENT '<string...>' as part of the
definition of the column.
Column, computationally update See: UPDATE
Column, move position ALTER TABLE [<DatabaseName.>]Table_Name
MODIFY COLUMN
<ColumName> <Column_Declaration>
FIRST | AFTER <ColumName>;
Note: MySQL may balk at this if the
column is an index.
Column, populate one UPDATE <TableName> SET
<ColumnName> = <Value>
WHERE <row selector...>;
Example:
UPDATE Db1.Table1 SET
Address = '132 Main Street'
WHERE Person = 'Smith,Joe';
Column, remove Invoked under 'mysql':
ALTER TABLE [<DatabaseName.>]Table_Name
DROP COLUMN Column_Name;
Column, rename Invoked under 'mysql':
ALTER TABLE [<DatabaseName.>]Table_Name
CHANGE COLUMN
Column_Cur_Name Column_New_Name
<Declaration>;
Example:
ALTER TABLE Db1.Table1 CHANGE COLUMN
Addr Address VARCHAR(255);
Column alias See: AS
Column attributes Those are "data types" with all their
qualifiers, which you can change via
ALTER TABLE <TableName> MODIFY COLUMN.
Attributes are specific to data types
and are discussed with them.
Column index Simply an integer for selecting a column
(field) within a returned row, where the
number can be 0..mysql_num_fields() - 1.
Used in mysql_fetch_field_direct() to go
directly to that relative field, rather
than stepping to it with
mysql_fetch_field() or
mysql_fetch_fields().
Column name Up to 64 chars long.
Any alphanumeric chars, plus underscore
(_) and dollar-sign ($); but name should
not be all numeric, to keep from being
taken as a number rather than a name.
DO NOT attempt to use trivial column
names which conflict with MySQL
keywords, as for example trying to
define a column name of When.
Columns, list Issue a query to do SHOW COLUMNS.
Columns, number of mysql_field_count(mysql_struct_ptr);
MYSQL_RES *result_ptr;
MYSQL *mysql_struct_ptr;
columns_priv MySQL table containing the column-level
privs for specific columns in MySQL's
databases.
See also: tables_priv
Combined indexing See: Multiple-column indexes
Commands mysql, mysqladmin
See also: SQL commands
COMMENT Descriptive comment for a column, as:
COMMENT '<string...>' as part of the
definition of the column, in CREATE
TABLE.
Maximum text length: 60 chars.
Comments Can be included in batch files fed into
the 'mysql' command, where the best
example is in 'mysqldump' output.
Recognized types:
# On a line, everything from the pound
sign to the end of the line is
ignored.
/* ... */
The familiar C style comment format
can be used to encode a comment
anywhere on a line or across multiple
lines.
-- A line beginning with "-- " (dash
dash space) is ignored.
COMMIT Statement to commit a transaction.
Syntax:
COMMIT [WORK] [AND [NO] CHAIN]
[[NO] RELEASE]
See also: BEGIN; ROLLBACK;
SET autocommit; START TRANSACTION
Composite Indexes Are single indexes that are based on
multiple columns. MySQL will use only
one index per table when processing a
query. Where you frequently employ
multiple columns in Where clauses, a
composite index can boost performance.
A composite index is defined by listing
multiple columns as one, such as:
INDEX City_State (City, State);
Note that because of the way that MySQL
builds composite indexes, it will use
them only if the query is used in a
left-to-right manner. Thus, in our
example, it will honor City, or City and
State, but not just State, as that does
not include what's to the left of it.
Composite Indexes are also known as
Compound Indexes.
Compression MySQL can employ data compression in the
transmission of data between the client
and the server, as of MySQL 3.22.3.
Compression is chosen on commands via
the --compress or -C option.
Compression is chosen in the C API via
the MYSQL_OPT_COMPRESS option value in
mysql_options() or CLIENT_COMPRESS in
mysql_real_connect().
Compression is a good, mild security
measure to obscure TCP/IP packet
contents - certainly preferable to
sending userids and passwords in
readable ASCII.
See also: Encryption
connect() DBI function to connect to a database.
Inverse of disconnect().
Connection still alive? mysql_ping()
Continue command across lines Simple: just press return for the next
physical line constituting the long
logical line. MySQL waits for the
terminating semicolon to know that the
logical line is finally complete.
Copy a table To make an empty copy of a table, use
CREATE TABLE <NewName> LIKE
<SourceTbl>;
This operation makes a full-fidelity
copy of the table structure, including
all column attributes (key,
AUTO_INCREMENT, Timestamp field's
CURRENT_TIMESTAMP default). You can
then populate, perhaps like
INSERT INTO <NewName> SELECT * FROM
<SourceTbl>;
This is the preferred method, due to
factors summarized below.
----
To create and populate a copy of a table
in one step, use:
CREATE TABLE <NewName> SELECT ... FROM
<SourceTbl> [WHERE ...]
But: Note that, by default, this does
*not* copy column attributes, such as a
field being a key, or AUTO_INCREMENT, or
a Timestamp field's CURRENT_TIMESTAMP
default.
--------------- Notes -----------------
- Copying a table does not result in the
re-sequencing of Auto Increment field
numbering: such numbers are copied
as-is.
COUNT SQL keyword used in a Select to return a
count of the number of records matching
a search criterion, rather than return
information from within records.
Example:
Select COUNT(*) from Table1 where
Type='whatever';
Contrast with: SUM()
CREATE DATABASE Statement to create a new database.
Basic: CREATE DATABASE _dbname_
Advan: CREATE DATABASE IF NOT EXISTS
_dbname_
An error occurs if the database exists
and you did not specify IF NOT EXISTS.
CREATE TABLE Statement to define a table within a
database.
CREATE TABLE [IF NOT EXISTS]
<[DatabaseName.]TableName>
( <ColumnName> <ColumnType>
[NOT NULL | NULL]
[DEFAULT <DefaultValue>]
[AUTO_INCREMENT] [PRIMARY KEY]
[UNIQUE [KEY]]
[COMMENT '<String>']
| [CONSTRAINT <Symbol>] PRIMARY KEY
(<IndexColumns>)
| [CONSTRAINT <Symbol>] UNIQUE
[INDEX|KEY] [<IndexName>]
(<IndexColumns>)
| {INDEX|KEY} [<IndexName>]
(<IndexColumns>)
| FULLTEXT [INDEX|KEY] [<IndexName>]
(<IndexColumns>)
| [CONSTRAINT <Symbol>] FOREIGN KEY
[INDEX|KEY] [<IndexName>]
(<IndexColumns>)
[<ReferenceDefinition>]
| [CONSTRAINT <Symbol>] CHECK
(<Expression>)
[, ...]
)
Where:
DEFAULT <DefaultValue>
Assigns a default value to a field, for
initializing the field if a row is
inserted into a table without a value
for the field. Without a default
defined, MySQL will insert a null
value, unless the field is defined as
NOT NULL, in which case a value is
chosen based upon field type.
NOT NULL
Specifies that a field must not be
null, meaning that attempting to insert
a null into the field results in an
error. It does *not* mean that a value
must be supplied for the field when
creating a table record.
New in 4.1 is the ability to copy the
structure of an existing table to create
a new one with the same structure:
CREATE TABLE tbl_name2 LIKE tbl_name1
CREATE USER MySQL 5.0.2+ command to creates a new
account and optionally assigns a password.
CREATE USER Account
[IDENTIFIED BY 'password'];
where Account is of the form
Username@Hostname
where Username is that which a
connecting client will specify, and
Hostname is the system from which such
sessions will originate, or can be '%'
as a wildcard for "any host".
Examples: 'Fred'@'localhost'
'Fred'@'%' 'Fred'@'111.222.333.444'
'Fred'@'111.222.333.%'
In earlier MySQL versions, the user was
defined as part of GRANT.
In a replication scenario, the username
is entered into the CHANGE MASTER
command MASTER_USER spec and password
goes into the MASTER_PASSWORD spec.
Note that CREATE USER grants no
privileges; that is done with GRANT.
See also: DROP USER; GRANT
Currency See: Dollars, cents
CURDATE() Return the current date, like: 2010-06-28
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE().
Data Directory Where the databases live.
Note that an RPM install of MySQL on
Linux results in the mysql_install_db
command invoked by the install creating
the data directory in the conventional
/var/lib/mysql/ directory.
See: datadir
Data types (for columns) CHAR, ENUM, TEXT, VARCHAR, et al.
MySQL will attempt to avidly save space
in a table and, like an optimizing
compiler, will change the semantics of
your table definition to do so,
converting fixed length fields to
variable if there are other variable
length columns in the table.
Database In SQL, a database is a set of tables
containing related information.
Each MySQL database resolves to a
directory name within its "data"
directory.
See also: Table
Database, create Via: CREATE DATABASE
Like: CREATE DATABASE parts_db;
Resolves to creating a directory of the
given name within the "data" directory.
Database, destroy The following will delete a database and
all of its tables:
DROP DATABASE [IF EXISTS] db_name;
Its tables do not have to be destroyed
first.
Database, load from mysqldump file See: mysqldump, guidance for backup
Database, remove See: Database, destroy
Database, rename MySQL does not provide a command for
renaming a database, ostensibly because
of the complications involving the
directory and file namings in the
various table engines.
Database, restore See: Database recovery
Database, where Gets its own db-named subdirectory,
wherein table files will be created.
Database directory, file permissions See: umask
Database in use, query In a series of database operations, you
may want to issue a query to see what
database you are currently positioned in
(via USE). That can be achived via:
SELECT DATABASE();
which returns simply the database name,
in one row and one column.
Database name Is case-sensitve.
Unquoted, may contain ASCII chars:
0-9 a-z A-Z $ _
(basic Latin letters, digits 0-9,
dollar, underscore).
Note that punctuation such as commas is
non-standard, but might be used in
quoted names - which is why listing
databases, comma-separated, is avoided
in various areas of MySQL.
Database qualifier When you address a database table in
dotted form: Dbname.Tablename
as you may do if you started a session
with mysql_real_connect() and a null
database name parameter, and did not
establish a prevailing database via the
SQL 'USE db_name' statement.
Database recovery Use mysqldump or mysqlhotcopy to create
a backup of the database, in logical or
physical form, respectively.
Logical recovery ------------------
With a mysqldump backup, recovery
proceeds with the MySQL server up,
feeding the mysqldump output file into
the 'mysql' command, for it to execute
all the commands in it.
Physical recovery ------------------
With a mysqlhotcopy backup, recovery
proceeds with the MySQL server taken
down, to replace the flat files which
constitute the database; then the MySQL
server is restarted.
In both cases, any applicable Binary Log
files need to replayed, to bring the
database up to currency.
See: mysqldump
Database recovery, master to slave Here we have the scenario where, in a
replication pair, the master is fine,
but the slave's replication is
hopelessly kaput, as perhaps in having
failed at some time in the past, where
the binary logs involved have passed
away. To get the slave viable again,
its copy of the database(s) need to be
refreshed/reloaded from the
primary/master server copy. That can be
accomplished, during a quiet time.
Allow some hours for it. The procedure
is:
- First, assess disk space available:
you want to have a goodly number of
gigabytes free, for binary and general
logs growth during the operation.
- On both servers, STOP SLAVE
- Be on the slave server, as root.
- Invoke command:
mysqldump -h acsgm__
--single-transaction --flush-logs
--master-data <DBname>
| mysql <DBname> &
where you employ the client-server
functionality of the MySQL commands
to suck the database contents across
from the master server. The option
--master-data causes the name and
position in the master server's binary
log to be carried in the database dump
stream, as CHANGE MASTER TO info.
It's best to background the process so
that it can continue if your terminal
or connection fails.
- During the reload, watch disk space.
The general log will grow huge as this
continues, where you will likely have
to cut off, gzip and move the log file
off via the procedure:
Unix: bkurfile <GeneralLog>
MySQL: FLUSH LOGS
- When the reload finishes, you need to
tweak the replication position in the
master (before restarting
replication). Why? The reload causes
the slave's binary log to be populated
with DROP TABLE and INSERT INTO
sequences, reflecting what is in the
mysqldump. You don't want that to be
replicated back into the master: that
would be a mess. Thus, you need to
set the master's positioning to be
that of post-load on the slave. You
do that by first performing the
following on the slave:
SHOW MASTER STATUS\G
to get its position info. Use the
values to reset those in the master
server:
CHANGE MASTER TO
MASTER_LOG_FILE='binary_log.______',
MASTER_LOG_POS=______;
- On both servers, START SLAVE
(Note: no need for the ssh command.)
Database to use in next operations USE <Dbname>;
Databases, administer Use 'mysqladmin'.
Databases, list Do: SHOW DATABASES;
See also: Tables in database, show
datadir Server option for the [mysqld] section
of the configuration file to specify
MySQL's default home directory, where
logs and databases are created by
default.
Example:
[mysqld]
datadir=/var/lib/mysql
The mysqld process cd's to this
directory when it starts.
Verify: Command 'mysqladmin variables'
reports it under "datadir".
Command line: --datadir=_____
Permissions: drwxr-xr-x
See also: basedir; Binary Log;
mysql directory
Datadir, determine Command SHOW VARIABLES like 'datadir';
will display it.
DATE MySQL data type: a standard date value.
Format: YYYY-MM-DD
Storage size: 3 bytes
See also: DATETIME; TIME
DATE() Returns the date part of the expression
inside the parentheses.
Example:
DATE('2008-03-12 16:15:00')
yields: '2008-03-12'
Obviously works equivalently on a
datestamp field, as in:
DATE(revised_when)
Date See also: Today's records; Year 0;
Yesterday's date
Date, subtract time value from DATE_SUB(Date, INTERVAL expr Unit)
Date, time format Via system variables date_format and
datetime_format.
Date comparison Perform the select with like:
... where DateTime>'2010-08-20'
Note that this will include everything
on that specified day, as the comparison
value is effectively
2010-08-20 00:00:00
DATE_ADD() SQL function to add a time value
(interval) to a date.
Syntax: DATE_ADD(Date,INTERVAL expr Unit)
Date specifies the starting date or
datetime value. expr is an expression
specifying the interval value to be
added or subtracted from the starting
date. expr is a string; it may start
with a "-" for negative intervals.
Unit is a keyword indicating the units
in which the expression should be
interpreted.
Example construction:
DATE_ADD('1970-1-1', INTERVAL 10 YEAR);
See also: DATE_SUB()
DATE_FORMAT() SQL function to nicely format a date
string (including TIMESTAMPs).
Example of getting YYYY/MM/DD hh:mm:ss
output:
DATE_FORMAT(Timestamp,
'%%Y/%%m/%%d %%T') AS \"When\", ...
DATE_SUB() SQL function to subtract a time value
(interval) from a date.
Syntax: DATE_SUB(Date,INTERVAL expr Unit)
Date specifies the starting date or
datetime value. expr is an expression
specifying the interval value to be
added or subtracted from the starting
date. expr is a string; it may start
with a "-" for negative intervals.
Unit is a keyword indicating the units
in which the expression should be
interpreted.
Example, to delete records older than 2
days:
DELETE FROM Table WHERE Date_Field <
DATE_SUB(NOW(), 2 DAY);
See also: DATE_ADD()
DATEDIFF() Returns the difference in number of days
between the two expressions, which
should be date or date and time values.
Syntax: DATEDIFF(expr1,expr2)
The result is positive if the first
argument is later than the second.Any
time part in the values is ignored.
Example:
DATEDIFF('1987-01-01','1987-01-08')
returns 7.
Datestamp You mean: Timestamp
DATETIME MySQL data type: the date and time.
Format: YYYY-MM-DD hh:mm:ss
Storage size: 8 bytes
See also: DATETIME; TIME; TIMESTAMP
DAYNAME(date) Returns a string containing the weekday
name for the date value date, or NULL
if the name cannot be determined.
Returned weekday name values are:
'Sunday' 'Monday' 'Tuesday' 'Wednesday'
'Thursday' 'Friday' 'Saturday'
SELECT DAYNAME(NOW()); returns today's
dayname.
DAYOFWEEK(Datestamp) Returns the numeric value of the weekday
for the date value. Weekday values are
in the range from 1 for Sunday to 7 for
Saturday, per the ODBC standard.
db MySQL table housing privileges relevant
to individual databases.
DBI A database-independent interface for the
Perl programming language. DBD::mysql is
the driver for connecting to MySQL
database servers with DBI.
DBI is the basic abstraction layer for
working with databases in Perl.
DBD::mysql is the driver for using MySQL
with DBI.
Replaces older interface 'mysqlperl'.
Book: O'Reilly "Programming the Perl DBI".
DEC Synonym for DECIMAL.
DECIMAL [(precision, [scale])] MySQL data type: a floating point number
where precision is essential, as with
money. The precision is the number of
significant digits in the value. The
scale is the number of those digits
which appear after the decimal point.
When the scale is 0, the value contains
no fractional part.
Storage size: precision + 2 bytes
MySQL (<5.0.3) actually stores DECIMAL
values as strings, not as computer
floating point numbers: one char per
digit, one char for the decimal point
when the scale is greater than 0, and
one char for the sign of negative
numbers. As of 5.0.3, DECIMAL numbers
are stored as binary, packing nine
digits into four bytes.
NUMERIC is implemented the same as
DECIMAL.
See also: DOUBLE; FLOAT; NUMERIC
DEFAULT value for a column See: CREATE TABLE
default-storage-engine MySQL server option to define the
storage engine that will be used if a
table is defined without an explicit
ENGINE = engine_name option.
Default:
Was MyISAM until 5.5; now is InnoDB.
The engine name is not case-sensitive.
For serious work, a specification of
default-storage-engine = InnoDB
would be best.
Verify: Command 'mysqladmin variables'
reports it under
"storage_engine".
Define table Via: CREATE TABLE
DELETE Statement to delete rows from a table.
Form:
DELETE [LOW_PRIORITY] [QUICK]
FROM <TableName>
[WHERE expression...]
[ORDER BY ...] [LIMIT n]
Modifiers:
LIMIT Specifies the maximum number of
rows that will be deleted in the
invocation of the DELETE. This is of
particular value with the MyISAM
engine, to avoid having the table
locked for a considerable period.
LOW_PRIORITY Says to wait until no
clients are reading from the table
before performing the deletion.
QUICK Causes the table handler to
suspend the merging of indexes during
the deletion, to speed the deletion.
Omit the WHERE clause at your own peril:
it will delete all records in the
table!!!
Returns: Normally returns the number of
records/rows affected/deleted. But note
that by the nature of Delete, no row
contents are returned, so don't go
trying to report row contents.
Unlike the INSERT errno returned by
mysql_query() when the user is already
in the table, DELETE give no errno if
the user is not in the table: call
mysql_affected_rows() after the Delete
to get the count of entries deleted to
assure that it occurred. The difference
is due to INSERT inherently operating on
a single record, whereas DELETE may
operate on any number.
Note that you can delete records older
than N days via a construct like:
DELETE FROM Table WHERE Date_Field <
DATE_SUB(NOW(), 2 DAY);
--delete-master-logs mysqldump invocation option for
replication: Deletes the binary log
files on the server and begins a new one
by issuing a FLUSH MASTER statement (old
name for current RESET MASTER) after
generating the dump output. Don't use
this option unless you're sure you want
the existing binary logs to be wiped
out.
This option also enables --master-data,
causing the operation to issue a
SHOW MASTER STATUS before the dump in
order to write a
CHANGE MASTER TO
MASTER_LOG_FILE='binary_log.000002',
MASTER_LOG_POS=4;
statement into the dump file. But: my
experience is that it writes it as a
comment (two hyphens at the front), such
that no CHANGE MASTER command will be
invoked when the data is later used for
a load. You can address this by adding
--master-data to the command line, which
results in a non-commented CHANGE MASTER
command in the dump.
Further, in that MASTER_HOST and
MASTER_USER are not included with that,
the usefulness of that inclusion is
limited. You might as well do that
stuff yourself, and be certain of what
you are getting.
DESC Descending: a sort method modifier of
the Order By clause like:
order by "Balance" DESC
to sort on that column in descending
order, largest value first.
See also: ASC
DESCRIBE To obtain a description of a table.
Syntax:
DESCRIBE tbl_name [col_name | wild]
Reports: Field, Type, Null, Key,
Default, Extra.
Key values:
(empty) The column either is not
indexed or is indexed only as a
secondary column in a multiple-column,
nonunique index.
PRI The column is a PRIMARY KEY or is
one of the columns in a multiple-column
PRIMARY KEY.
UNI The column is the first column of a
unique-valued index that cannot contain
NULL values.
MUL Multiple occurrences of a given
value are permitted within the
column. The column is the first column
of a nonunique index or a unique-valued
index that can contain NULL values.
Seen where the column is an index other
than the primary.
Does not report descriptive data added
to the column definition via COMMENT:
to see that, instead use
'SHOW FULL COLUMNS FROM
<DatabaseName.><TableName>;'.
Example: DESCRIBE Db1.Table1;
DISCARD TABLESPACE An ALTER TABLE spec for InnoDB tables
that use individual tablespaces
(innodb_file_per_table). For such a
table, it removes the tbl_name.ibd file
that stores the table contents. This
action cannot be used in conjunction
with other actions.
See also: IMPORT TABLESPACE
disconnect() DBI function to disconnect from the
database when done.
Inverse of connect().
Display mode Use the --table or -t option with the
mysql command to force the output to
DISTINCT SQL keyword to cause only unique
instances of field content to be reported.
Sample syntax:
Select DISTINCT JobTitle from
EmployeeDataTable;
Performing Distinct over two fields is
more complex, but perhaps achievable via
like:
Select DISTINCT(CONCAT(Field1,Field2)),
Field1, Field2 where...
To find unique dates among table recs:
Select DISTINCT(DATE(revised_when))
FROM employee_records;
do() DBI function to perform a query that
returns a row count.
See also: execute()
Dollars, cents Some info on techniques for working with
dollars and cents values...
It is common and traditional to store
money values as integer cents, and then
convert that to dollars and cents for
display and report purposes. For
example, a table entry for currency
values might be defined as:
Price_in_cents INT NOT NULL;
A raw value in that field of value 5125
would be 5,125 cents, meaning $51.25 .
To convert that in a report:
select format((Price_in_cents / 100), 2)
from Database.Table
would yield 51.25 . You could add a
dollar sign as follows:
select CONCAT('$',
format((Price_in_cents / 100), 2))
from Database.Table
It is possible to use Decimal for such
monetary values, as in DECIMAL(8,2),
but Decimal is stored as ASCII numerals
in MySQL before 5.0.3, and packed
decimal thereafter, making math less
inefficient than with Integer.
Dot files See: .my.cnf
DOUBLE [(display_Size, digits)] MySQL data type: a double-precision
floating point number.
Storage size: 8 bytes
FLOAT and DOUBLE are subject to rounding
error and may not be suitable except for
records in which you need only
approximate accuracy.
Use DECIMAL instead if precision is
vital, as with money.
See also: DECIMAL
DOUBLE PRECISION Synonym for DOUBLE.
DROP DATABASE Command to dispose of a database and all
of its tables. Syntax:
DROP DATABASE [IF EXISTS] db_name
With InnoDB tables, the whole directory
of that name, which contains all of the
tables files, will go away. An
implication of this is that a subsequent
'mysql DBname' to reload the database
from a mysqldump will fail because the
database name no longer exists: you
would need to perform a CREATE DATABASE
to reinstate it, first.
DROP TABLE SQL statement to dispose of a database
table - obviously, a drastic operation.
Syntax:
DROP TABLE Table_Name;
DROP TABLE Table_Name1, Table_Name2, ...
Use form:
DROP TABLE IF EXISTS Table_Name;
to avoid an error condition where the
table does not exist.
Given the severe nature of the
operation, it is best to code the
database name in the statement, rather
than have it in effect via USE; so do
like:
DROP TABLE Database_Name.Table_Name;
A Drop is usually done just ahead of a
CREATE TABLE operation, as in recreating
a database from a mysqldump backup after
its failure (where mysqldump generates
such SQL statements into its output).
If the database/table is being
replicated, a Drop performed in the
master server will be effected in both
the master and slave.
DROP USER Statement to remove one or more MySQL
accounts. Syntax:
DROP USER <User>[, <User>];
like: DROP USER 'alma'@'localhost';
Dual-master replication scenario This is an extension of master-slave
replication where each master is also a
slave of the other master, allowing
updates to get to both. It is best that
client updates go only to one of the two
servers, to help avoid greater
complexity in countering side effects,
as in auto_increment fields, in that
there is no coordination across multiple
MySQL servers, so one has to watch out
for duplicate key ("Duplicate entry")
situations.
This arrangement is sometimes called
multi-master.
Details: Option logslaveupdates is not
needed where there are just the two
servers, as nothing is to be relayed
beyond those two.
Duplicate records, find Use the following form of a Select to
report duplicate values in a field:
select name, count(name) as cnt from
TableName group by name having cnt > 1
order by cnt;
Encryption MySQL can employ SSL to secure the
client-server traffic. Use of SSL
requires installing the openSSL library,
providing a certificate, and generating
and using a public/private key set.
MySQL doesn't use encrypted connections
by default, because doing so would make
the client/server protocol much slower.
(And SSL has to be compiled into MySQL
for it to be selectable: the binary
distributions lack it.)
See also: Compression
Engine See: Storage engine
Engine type of a database, report Can be done from the OS command line:
mysqlshow --status <Dbname>
or from within the 'mysql' command:
SHOW TABLE STATUS in <Dbname>;
ENUM MySQL data type: enumerated value, being
a complex string column type.
The instance shall contain only one of
the values defined at column definition
time. The values must be strings, which
are abstracted to single-byte numbers
(an index, whose values are the offset
of each value in the collection) rather
than being stored as strings.
Examples: Sex ENUM('M', 'F').
Fruit ENUM('Apple', 'Orange",
'Pear');
Example with a default:
AccountFlag ENUM('Unflagged',
'Overdue') NOT NULL DEFAULT 'Unflagged';
Example of adding an ENUM column:
ALTER TABLE MyDb.MyTable
ADD COLUMN Fruit ENUM('Apple',
'Banana', 'Orange", 'Pear')
AFTER ColumnX;
Here's how you can report ENUM field
names and their numeric values:
SELECT Fruit, Fruit+0 FROM MyTable;
As a string type, ENUM supports the use
of LIKE and wildcards.
Contrast with: SET
ENUM, change It is almost inevitable that an ENUM's
possible values will have to evolve over
time. It is *not* possible to perform a
change which will simply add an enum
value to an existing enum list: you have
to fully redefine the enum's contents.
The operation to do it is:
ALTER TABLE <TableName>
CHANGE COLUMN <ColName> <ColName>
ENUM(... ;
And in redefining the enum list you must
be *very* careful that your new list is
accurately based upon the present list.
Note well: To add a value to an enum,
add it to the end of the enum values
list, *not* in the midst (which would
incite a table rebuild). If you want
to eliminate a value, before doing so,
update all tables records whose columns
contain that value, then adjust the
enum list.
Note that CHANGE COLUMN has the ability
to rename a column, so in just updating
the contents of an existing column, the
column name needs to appear twice, so
that its new name equals its old name.
Environment variables MySQL honors a variety of Unix
environment variables. (The honoring is
done by its binary programs, not its
shell scripts.)
Some envvars are used by mysqld - but
getting them to be in effect is very
difficult, as the MySQL developers
failed to accommodate standard Linux
conventions for utilization. The major
problem is the 'service' command - which
is used for daemon start/stop - invokes
the named service with a fresh set of
environment variables, which thus
excludes any that were in place in the
environment wherein the 'service'
command was executed. Services
generally accommodate this by their init
scripts being written to adopt values
defined in the /etc/sysconfig/ file
corresponding to the service. MySQL's
init script has no such accommodation.
Thus, the only way to have environment
variables in effect for mysqld is either
to invoke mysqld_safe directly, or
modify the MySQL init script to set the
environment variable(s) just before
launching mysqld.
See: umask
--err-log See: Error log
Errno, MySql Ref: mysqld_errno.h
(Its error numbers are 1000 and upward,
to not conflict with Unix errno values.)
Error log It is the log where errors are recorded.
The /etc/my.cnf config file identifies
it on the log-error=_____ line. (The
config file spec used to be err-log, but
that is now deprecated.
The error log defaults to data/<Hostname>.err
(The log conventionally resides in the
"data" directory of the given MySQL
instance.) Content tends to be minimal:
server start and stop records.
Internal timestamps are of the form
031028 11:09:18 where the first token
is of form YYMMDD.
It is perhaps better to assign it a
name, via the --err-log=____ operand to
mysqld-safe. Be sure to specify the full
path name, as this option oddly differs
from other log spec options, in being
relative to the directory in which
mysqld-safe lives.
See also: Server Log
ESCAPE See: _ (underscore)
/etc/my.cnf For Linux RPMs, the standard MySQL
configuration file, containing global
options. These are default options,
which may be overridden by command line
specification of such options.
You make changes to this file manually:
there is no provided command for doing
so. Changes made do not take effect
until mysqld restarts.
The various MySQL commands capture their
defaults via the my_print_defaults
command, which looks in my.cnf for what
is called a "group name", being a name
in brackets at the head of a line, like:
[mysqld_safe]
which is followed by lines containing
<OptionName>=Value specifications
appropriate to that command.
Note that the name "my.cnf" is rather
vague in /etc/. To address that: as of
MySQL 5.1.15 the config file location is
/etc/mysql/my.cnf.
Global options, in a single file like
this, may be unsuitable for multiple
applications in the system which have
differing needs; but, if nothing else,
the file *is* useful for setting tuning
options.
Assure that the file owner assignment
and/or permissions allow mysqld to read
the file.
MySQL reads and then closes this file
when it starts - mysqld does not keep
this file open as it runs.
You can reportedly cause mysqld to
reread this file by sending the daemon a
HUP signal. (But: You should never make
substantial configuration changes with
the daemon up - take it down, make the
changes, then restart.)
See also: my_print_defaults;
Options files
/etc/mysql/my.cnf For Linux RPMs, the standard MySQL
configuration file, containing global
options, as of MySQL 5.1.15.
See /etc/my.cnf for more info.
execute() DBI function to perform a query that
returns a result set.
See also: do()
expire_logs_days MySQL daemon option specifying the
number of days of age allowed for a
binary log file at which time the MySQL
server will expire it (and update the
binary log index file).
When this takes effect: When server is
restarted, or a FLUSH LOGS is done, or
max_binlog_size is reached.
Default: 0
A value of 0 (which is the default)
nullifies this option.
Note that this option is undesirable for
test servers, which may be shut down for
weeks and reactivated only when needed:
their binary logs could then disappear,
which causes replication to immediately
go out of sync. In such systems, the
max_binlog_size option would be more
appropriate (which transition to the
next generation log file).
Command PURGE BINARY LOGS (q.v.) may be
used to immediately get rid of old logs.
If log-bin is removed from the start-up
parameters, this option must also be
removed, else mysqld may not start.
To check: SHOW VARIABLES like 'expire%';
See also: max_binlog_size
EXPLAIN SQL statement can be used to determine
how the optimizer resolves a query,
rather than performing the query.
Use by putting EXPLAIN at the head of
your query statement. Example:
EXPLAIN SELECT firstname FROM employee
WHERE overtime_rate/2<20;
Returns a row of information for each
table used in the SELECT statement. The
tables are listed in the output in the
order that MySQL would read them while
processing the query. In the returned
info are the following columns:
id The row number.
select_type The type of SELECT...
SIMPLE: Simple SELECT
(not using UNION or
subqueries).
PRIMARY: Outermost
SELECT.
UNION: Second or later
SELECT statement in a
UNION.
DEPENDENT UNION: Second
or later SELECT
statement in a UNION,
dependent on outer
subquery.
SUBQUERY: First SELECT
in subquery.
DEPENDENT SUBQUERY:
First SELECT in
subquery, dependent on
outer subquery.
DERIVED: Derived table
SELECT (subquery in FROM
clause).
table The table involved.
type The type of Join being
performed. From best to
worst:
System: The table has
only one row (= system
table). This is a
special case of the
const join type.
Const: The table has at
most one matching row,
which will be read at
the start of the query.
Because there is only
one row, values from the
column in this row can
be regarded as constants
by the rest of the
optimizer. Very fast.
Eq_ref: One row will be
read from this table for
each combination of rows
from the previous tables.
Other than the const
types, this is the best
possible join type. It
is used when all parts
of an index are used by
the join and the index
is a PRIMARY KEY or
UNIQUE index.
Ref: All rows with
matching index values
will be read from this
table for each
combination of rows from
the previous tables.
ref_or_null: This join
type is like ref, but
with the addition that
MySQL will do an extra
search for rows that
contain NULL values.
Index_merge: The Index
Merge optimization is
used.
unique_subquery:
Replaces ref for some IN
subqueries of the
following form: value IN
(SELECT primary_key FROM
single_table WHERE
some_expr). Is just an
index lookup function
that replaces the
subquery completely for
better efficiency.
Index_subquery: Similar
to unique_subquery. It
replaces IN subqueries,
but it works for
non-unique indexes in
subqueries.
Range: Only rows that
are in a given range
will be retrieved, using
an index to select the
rows.
Index: This join type
is the same as ALL,
except that only the
index tree is scanned.
ALL: A full table scan
will be done for each
combination of rows from
the previous tables.
possible_keys Which indexes apply to
this table.
key Which index is actually
used; NULL if none.
key_len Length of the key used.
Shorter is better.
ref Which column, or a
constant, is used.
rows The number of rows which
MySQL believes it must
examine to get the data.
Extra Further info.
Distinct: Searching
stops after the first
matching row.
Not exists: Performed
LEFT JOIN optimization,
to find match that way.
Range checked for each
record: No good index
found to use, despite
repeated trying.
Using filesort: An extra
pass is done to find out
how to retrieve the rows
in sorted order. This is
obviously expensive.
Using index: Retrieval
is via index tree; no
additional seek to read
the actual row.
Using temporary: A temp
table has to be created
to hold the result, as
when the query contains
GROUP BY and ORDER BY
clauses listing columns
in different order. This
is expensive.
Using where: A Where
clause will be used to
restrict which rows to
match against the next
table or send to the
client.
The administrator should also use
ANALYZE TABLE to evaluate performance.
External locking Is the use of file system locking to
manage contention for MyISAM database
tables by multiple processes. This is
unusual, used in situations where a
single Unix process such as the MySQL
server cannot be assumed to be the only
process that requires access to tables.
Server option: skip_external_locking
fetch() Same as fetchrow_arrayref().
fetchrow_array() DBI function to return an array of row
values.
fetchrow_arrayref() DBI function to return a reference to
array of row values.
fetchall_arrayref() DBI function to return a reference to
array of row values - all the rows.
fetchrow_hashref() DBI function to return a reference to a
hash of row values - keyed by column
name. Traverse the hash like:
while ( %hashed =
%{$stmt_handle->fetchrow_hashref()} )
{ foreach $key (keys(%hashed))
{ printf(" Key: %-32s Value: '%s'\n",
"'".$key."'", $hashed{$key}); } }
Field Conventional database term referring to
a unit subdivision of a database record.
In SQL terms, a database record is a
Row, and a field is a Column within a
row. It is perhaps better to use the
term Field to describe a single row
element, as Column tends to refer to
that position in all rows of the table.
In MySQL, you are well to think of Field
as being just the structural container
of a row,column's data, in that the C
API mysql_fetch_field*() functions
return information about field
structure, *not* the data itself: use
mysql_fetch_row() to retrieve the row's
data as an array of character strings.
Field, change See: Column, change
finish() DBI function to complete a partial
query.
Flags type field The best way to implement a flags field
in a table record is via the SET data
type. See: SET
FLOAT [(display_Size, digits)] MySQL data type: a single-precision
floating point number.
Storage size: 4 bytes
FLOAT and DOUBLE are subject to rounding
error and may not be suitable except for
records in which you need only
approximate accuracy.
Use DECIMAL instead if precision is
vital, as with money.
See also: DECIMAL; DOUBLE
FLUSH HOSTS MySQL command to clear the host cache.
You should flush the host cache if some
of your hosts change IP address or if
the error message Host 'host_name' is
blocked occurs. When more than
max_connect_errors errors occur
successively for a given host while
connecting to the MySQL server, MySQL
assumes that something is wrong and
blocks the host from further connection
requests. Flushing the host cache
enables further connection attempts from
the host. The default value of
max_connect_errors is 10. To avoid this
error message, start the server with
max_connect_errors set to a large value.
Alternately, you can invoke the
'mysqladmin flush-hosts' command.
See also: max_connect_errors
FLUSH LOGS MySQL command to cause the server to
close and reopen most log files.
(General Log, Update Log, Binary Update
Log and index file, and Slow-query Log).
This causes binary log pruning according
to the expire_logs_days value.
Can also be accomplished via SIGHUP.
Note that if your intention is to cut
off the general log, what you need to do
is first rename it (as to having a
.YYYYMMDD suffix) and then do the flush,
which then causes the server to open the
original name afresh (new instance).
See also: Signals
--flush-logs mysqldump option to flush the server log
files before dumping tables.
FLUSH TABLE | FLUSH TABLES Before a backup, to ensure that the all
active index pages is written to disk -
or at least the backup engine. The
database table is closed, and the server
knows that it will have to perform a
fresh open when it next needs to access
the table.
There is no Unix signal which can be
sent to the mysqld process to effect the
flush.
Prior to a backup, the equivalent
'mysqladmin flush-tables' command would
be the most convenient to perform.
There is no need to perform this flush
in Replication.
Note that a table flush causes mysqld to
flush cached data to the underlying
storage engine - but there is no
assurance that the engine itself has
flushed the data to disk: the only way
to assure that is to shut down mysqld.
FLUSH TABLES WITH READ LOCK Special form of FLUSH TABLES to render
them static and immutable for some
external purpose.
Flushes all tables in all databases (to
their engines, and thus to disk) and
then places a global read lock on them,
which is held until you issue an UNLOCK
TABLES statement (or you log out of the
client session which issued the FLUSH
command). This action allows clients to
read tables, but prohibits any changes
from being made. This is desirable when:
- Using a backup utility to make an
image of the disk files involved in
the database tables.
- Using an OS environment utility (e.g.,
scp) to copy the database table files
to a slave MySQL server in setting up
replication.
After the operation, do UNLOCK TABLES to
remove the read lock.
Foreign Key A database concept for relating tables.
In a multiple table arrangement, it can
be the case that one table is dependent
upon the content of another. An example
is a literature database, where there is
a table listing authors and another
listing the books written by all
authors. If an author is not present in
the authors table, it is inappropriate
for that author's works to be tracked in
the books table, as that would represent
an informational inconsistency.
The dependent table is often referred to
as the child of the table it depends
upon.
The InnoDB engine enforces foreign key
rules, and so insures integrity.
Attempting to insert a row into a child
table where the foreign key does not
match a key value in the parent table
results in "ERROR 1452 (23000): Cannot
add or update a child row: a foreign key
constraint fails ...".
At a minimum, the concept allows you to
determine if a given table is dependent
upon another table.
Foreign keys help you keep tables
consistent: you can define that a record
may not be added to a child table
without having first been added to a
parent table.
Cascading delete: You can define that
when a record is removed from the parent
table that all records with its key be
removed from the child table. The spec
is ON DELETE CASCADE.
Cascading update: You can define that
when a keyed column's value is changed
in the parent table that it will also be
updated in the child table. The spec
is ON UPDATE CASCADE.
FORMAT() To format a number, as for example
converting a string to a number.
Thousands will be separated by commas, as
is conventional in U.S. number
representation.
FORMAT(n, d)
where n is the number, and d is the
number of decimal positions. d can be
0, to omit the decimal point and decimal
portion. So...
FORMAT(123.45, 2) -> 123.45
FORMAT(1234.56789, 3) -> 1,234.568
FORMAT(999999.99, 2) -> 999,999.99
FORMAT(999999.99, 0) -> 1,000,000
Notes: Use of this function is
particularly needed in MySQL 5.x, where
it was not needed in MySQL 4.x, in that
"2000 / 100" would return 20.00 in MySQL
4.x but would return 20.0000 in 5.x.
Applying this function to a number
effectively turns the result into a
string, which will cause the value to be
left-justified in report column display.
This could be avoided if FORMAT()
allowed specifying a length for the
integer portion of the number, as the C
programming %fI.D formatting
specification does. Doing a CAST() on
the FORMAT() result does not work
because the result is not all numeric.
See also: Dollars, cents; ROUND()
.frm Filename suffix of a format definition
file for a database table, as found in
the server data directory. In short:
the database table definition. There is
one for each table, regardless of the
storage engine type.
See also: .ibd; .MYD; .MYI; Table
ft_min_word_len System variable: The maximum length of
words that can be included in FULLTEXT
indexes. Longer words are ignored. If
you change the value of this variable,
you should rebuild the FULLTEXT indexes
for any tables that have them.
Default: 84
ft_stopword_file System variable: The stopword file for
FULLTEXT indexes. The default is to use
the built-in list of stopwords. To
disable stopwords, set the value to the
empty string. If you change the value
of this variable or the contents of the
stopword list, you should rebuild the
FULLTEXT indexes for any tables that
have them.
Full Table Scan Where a general query is invoked, to
seek certain values in a table, without
testing Indexed columns, then the whole
table is scanned for all occurrences of
the values, rather than stopping at the
first occurrence. Setting up a table to
have indexed (keyed) values results in
the creation of an adjunct, hashed db
where lookups can be rapid. (This is
kind of a "cheat" on relational database
structures, effectively saying that they
aren't that good at fast lookups such
that a "real" database needs to be set
up to do the job.)
See also: Index
Full-text search Has been a standard feature of the
MyISAM database table engine.
As of MySQL 5.6.4, full-text search is
available in the InnoDB engine.
General Log See: Server Log
general-log = ____ Server option, new in 5.1.12, to specify
whether general loggin should be active.
Is inactive by default.
Specify 1 or ON to activate; 0 or OFF to
deactivate.
general-log-file = ____ Server option, new in 5.1.12, to specify
the name/path of the general log, which
logs SQL queries.
If no such specification, the default is
log name <Hostname>.log in the datadir.
Note that this specification alone is
insufficient to have logging occur, you
also have to specify general-log (q.v.).
GRANT To implicitly create usernames and
access privileges.
GRANT Privileges (columns) ON what
TO account [IDENTIFIED BY 'password']
[REQUIRE encryption requirements]
[WITH grant or resource management options];
Privileges are predefined keywords such
as "CREATE USER" or "ALTER".
When specifying the accessing hostname,
it is good practice to double define it,
with and without domain name (e.g.,
myhost and myhost.company.com), as DNS
lookups may cause one or the other to
be in effect; or you could specify the
host by IP address.
Privileges: ALL PRIVILEGES/ALL; ALTER;
CREATE; DELETE; DROP; FILE; INDEX;
INSERT; PROCESS; REFERENCES; RELOAD;
SELECT; SHUTDOWN; UPDATE; USAGE.
There is no Cary Grant.
Opposite: REVOKE
Example of granting administrative
access from a certain system:
GRANT ALL PRIVILEGES ON *.* TO
'root'@'acsgm13.bu.edu'
IDENTIFIED BY 'SomePassword'
WITH GRANT OPTION;
See also: CREATE USER;
Password, change; SHOW GRANTS
Grant tables In database "mysql", the tables:
user Users who can connect to
the server and their
global privs.
db Database privs.
tables_priv Table privs.
columns_priv Column privs.
host
The grant tables define the initial
MySQL user accounts and their access
privileges.
On Unix, the grant tables are populated
by the mysql_install_db program, which
will establish the following entries:
Host User
"localhost" "root"
<Hostname> "root"
"localhost" "root"
<Hostname> ""
"127.0.0.1" ""
where <Hostname> is a string, like
"system3", and a null User is for
anonymous access. Initially, anyone can
create or use databases having the name
"test" or names starting with "test_".
Note that no entry is created for
hostname + domain name (e.g.,
"system3.bu.edu"), which can result in
access denial when performing actions
like 'mysqladmin -h <Hostname> ...'.
(Note that "root" is MySQL's private
root user, having nothing to do with the
Unix root user.)
You can update the Grant Tables via the
GRANT statement (GRANT ALL PRIVILEGES ON
*.* TO 'Username'@'Host' IDENTIFIED BY
SomePassword WITH GRANT OPTION;) or by
the more physical INSERT INTO
mysql.user VALUES ('Host','Username','',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','','','','',0,0,
0,0); *and* do FLUSH PRIVILEGES after
the INSERT to tell MySQL to re-read the
Grant Tables.
See also: root user; SHOW GRANTS
Group In an options file (e.g., /etc/my.cnf),
a group is a subsection (stanza) led by
the name of the group, in brackets, of
form:
[<GroupName>]
where GroupName is any textual name you
may choose.
Standard MySQL commands look for options
therein, under their own name, via the
my_print_defaults command to load those
global variables as defaults,
overrideable by command line options.
GROUP BY An SQL clause, usually used in a Select,
to cause an operation (e.g., SUM) to
return results by each unique value in a
specified column, rather than producing
a result by operating on all columns
regardless of content.
For example: Given a table of people
living in various cities, you want to
report population by city. COUNT would
be used to achieve this, but by itself
it would produce a count of all people,
and report that aggregate for every city
name. To produce a cound by city name,
you need to use GROUP BY on the city
name column.
Halt See: Server, shut down
HAVING Analogous to WHERE in specifying
conditions which must be satisfied in
the query, but unlike WHERE, HAVING can
refer to the results of summary function
such as COUNT().
Example in reporting states from which
to or more presidents came from:
Select state, COUNT(*) as count from
Presidents GROUP BY state HAVING
count > 1 ORDER BY count DESC;
Heap table A variation of a Temporary Table (q.v.)
which resides in memory rather than on
disk. Is created via:
CREATE TEMPORARY TABLE <Name> (...)
TYPE=HEAP
See also: Temporary Table
History file .mysql_history (q.v.)
host MySQL table for managing provileges
based upon location.
--host=____ Standard MySQL commands option
specifying the network identity of the
host to connect to when conducting a
client-server session.
Hostname, how to look up There are times when you will want to
query a MySQL server to determine what
host it is running on, particularly with
virtual IP access methods.
As of MySQL 5 there is now a system
variable called 'hostname', which you
can look up via the query
mysql -e "show variables like 'hostname'"
which will return like:
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| hostname | sys123 |
+---------------+--------+
In MySQL 4 there is no convenient way to
determine the hostname. Depending upon
how databases were defined on that
system, you may be able to do:
mysql -e 'select Host from mysql.db'
and hopefully find the local hostname
defined in there as a network name.
I/O thread In replication, the software by which
the slave server interacts with the
master server to pull the newest entries
from the master's binary log.
See also: SQL theread; START SLAVE
ib_logfile0, ib_logfile1, ibdata1 InnoDB engine (table processor)
binary tables/log files, specific to the
engine, which track the state of the
InnoDB databases on that server. (These
are different and separate from the
MySQL binary log.)
In the absence of server option
innodb_file_per_table, all tables are
intermingled in these files. This is
termed the "shared tablespace".
There is no utility provided to show
their contents, as the contents are for
engine use only.
If you do not specify any InnoDB log
parameters, the default is to create two
5 MB files names ib_logfile0 and
ib_logfile1 in the MySQL data
directory.
At MySQL restart, if it does not find
these files it will think that InnoDB
engine is not initialized and just
create them afresh.
Ref: http://dev.mysql.com/doc/mysql/en/
innodb-start.html
See: InnoDB; innodb_file_per_table
ib_logfile0, ib_logfile1 InnoDB engine (table processor)
binary log files.
All data changes are written into the
logfiles sequentially, which allows
write-ahead logging (crucial for
transactions).
.ibd Filename suffix for the database files
constituting a table based upon the
InnoDB storage engine, when the
innodb_file_per_table server option is
in effect.
Contains the data and index.
There is no other accompanying file
which participates in InnoDB procesing;
but there is an accompanying .from file,
which is a MySQL file containing format
definition info.
The .ibd files cannot be freely moved
around (as MyISAM files can be), because
the table definition is stored in the
InnoDB shared tablespace (ibdata1 file),
and also because InnoDB must preserve the
consistency of transaction IDs and log
sequence numbers. In particular, each
InnoDB table has an ID number,
reflecting its order of creation, where
that ID number is stored in both the
.ibd file and the ibdata1 shared
tablespace file.
The InnoDB developers have stated that
.ibd portability is a design goal.
See also: DISCARD TABLESPACE; .frm;
innodb_file_per_table; .MYD; .MYI
ibdata1 InnoDB engine (table processor) file:
the global tablespace file.
This is where system data (data
dictionary) and UNDO data are stored.
InnoDB stores information in this
This file is in the MySQL datadir, not
in a database directory thereunder.
If you do delete ibdata1, a new one will
be automatically generated - but without
information about the current tables,
meaning that you will lose access to all
your InnoDB tables (MySQL won't
find/list them if you try to reference
them in a query, insert, update, delete,
etc.).
See also: InnoDB component files;
innodb_file_per_table
IDENTIFIED BY Clause as appears with GRANT, to
establish a user password.
See: User, create
IMPORT TABLESPACE An ALTER TABLE spec for InnoDB tables
that use individual tablespaces
(innodb_file_per_table). For such a
table, it associates the tbl_name.ibd
file in the table's database directory
with the table. (Presumably, the table¢s
former .ibd file previously had been
removed with DISCARD TABLESPACE.) This
action cannot be used in conjunction
with other actions.
This would be used where there was a
table problem such that the table .ibd
file had to be restored from a backup
tape.
Example:
ALTER TABLE Employees.Timekeeping
IMPORT TABLESPACE;
Include files for options See: !include
Index A pointer construct, outside a table, to
allow essentially random access to the
elements of the table, greatly improving
database performance. Indexing is of
value where you will have lots of table
operations involving Where clauses. It
makes the most sense to limiting
indexing to fields which will contain
unique values.
An index is a separate set of rows
ordered according to the chosen index,
containing pointers to the target
table's rows, as implemented in a mini
hash database (which, in its way, says
that Relational databases aren't that
impressive, such that a "real" database
needs to come to its rescue). The
trade-off with indexes is that table
updates take longer as both the target
table and its index have to be updated.
See also: Full Table Scan; Performance
Index, add ALTER TABLE <TableName> ADD INDEX
<IndexName> (<Indexed_Columns...>);
Involves a write lock on the table.
See: ALTER
Index, remove ALTER TABLE <TableName> DROP {INDEX|KEY}
<IndexName>;
Index, rename There is no MySQL command to rename an
index: what you have to do is drop and
then add it again, causing the index to
be removed and rebuilding on the new
one. To do so, employ form:
ALTER TABLE ... DROP {INDEX|KEY}
<IndexName>, ADD {INDEX|KEY}
<NewIndexName> (<IndexedColumnName>);
Index types, by definition keyword UNIQUE An index in which all keys
must be unique.
Can contain null values,
which themselves constitute
"multiple" values of the
same thing.
PRIMARY KEY Like UNIQUE, but cannot
contain null values: all
column entries must be
populated, to facilitate
data addressing.
Reported by DESCRIBE as a
Key type of PRI.
INDEX A mundane index, allowing
null values and duplicates.
Reported by DESCRIBE as a
Key type of MUL.
FULLTEXT For MyISAM tables, to
perform full text searches,
with pattern matching, on
TEXT columns and non-BINARY
CHAR and VARCHAR columns.
KEY Is just a synonym for INDEX.
Indexes They speed access to rows by creating an
index to key values; but they can slow
table updating by requiring that an
index be updated as well as the table
itself. At least conceptually, indexes
are sorted, whereas tables are in
whatever order they were written.
A given table may have multiple indexes.
Performance: Indexes degrade over time.
Use the myisamchk tool to reoptimize
indexes.
MySQL will *not* use an existing index
if it decides that it would be faster to
simply scan the entire table. Generally,
if an index tells MySQL to access
roughly 30% of the rows in a table, it
ignores the index and performs a full
table scan.
If multiple indexes may be used to
satisfy a query, MySQL will use the most
restrictive one...the one which results
in the fewest rows being fetched.
If the columnns being selected are all
part of an index, MySQL may read all the
needed data directly from the index and
not use or lock the table itself.
When joining several tables, MySQL will
read data from the table which is likely
to return the fewest rows first: it may
not use the tables in the order
specified. Whereas this affects the
order in which the rows are returned in
a query, use ORDER BY in the query to
get the rows in a particular order.
See: Indexing; FULLTEXT, INDEX, KEY,
PRIMARY KEY, UNIQUE;
Multiple-column indexes; Performance
Indexes, report The DESCRIBE/SHOW COLUMNS command twins
identify index columns, but don't report
details. To see all details on them,
use the command
SHOW INDEX FROM [<DBname>.]<TableName>;
Indexing Means to employ an index to rapidly get
to any entry in a table. Indexing is
the single largest performance measure
that can be utilized in SQL.
Indexing makes the most sense where it
is necessary to quickly get to a very
small portion of a table. (In contrast,
an index is of little value where a
report program is going after most or
all of the data in a table.)
Indexing is most efficient where the
table has a stable population of
fixed-length data - meaning that changes
to the table don't necessitate changes
to the index as well, which would add
overhead.
INET_NTOA(expr) Given a numeric network address in
network byte order (4 or 8 byte),
returns the dotted-quad representation
of the address as a string.
INNER JOIN To perform a Select across two tables as
though they were one, to return rows when
there is at least one match in both
tables. Another way of saying this is
that the query is to return data which
is determined to be in both tables.
This is the most frequently used join.
Example: SELECT * FROM employees
INNER JOIN department
ON employee.DepartmentID =
department.DepartmentID;
The syntax of this statement is somewhat
bewildering as to what it does: it can
be much more simply written as:
SELECT * FROM employee, department
WHERE employee.DepartmentID =
department.DepartmentID;
q in which the left-right order of the
tables is implicit and obvious by naming
one to the left of the other.
InnoDB InnoDB provides MySQL with a
transaction-safe (ACID compliant)
storage engine with commit, rollback,
and crash recovery capabilities. InnoDB
supports FOREIGN KEY constraints. In SQL
queries you can freely mix InnoDB type
tables with other table types of MySQL,
even within the same query. When InnoDB
starts, it automatically checks for a
variety of table problems, such as
completing committed transactoins
reflected in its redo log and rolling
back uncommitted transactions via the
undo log.
Where defined: During a CREATE TABLE
operation, the table type is specified
at the bottom of the definition, as
ENGINE=INNODB, like:
CREATE TABLE <DBname>.<TableName>
(...) ENGINE=INNODB;
Files: If you specify no InnoDB
configuration options, MySQL creates an
auto-extending 10 MB data file named
ibdata1 and two 5 MB log files named
ib_logfile0 and ib_logfile1 in the MySQL
data directory.
InnoDB has been designed for maximum
performance when processing large data
volumes. Its CPU efficiency is probably
not matched by any other disk-based
relational database engine.
Fully integrated with MySQL Server, the
InnoDB storage engine maintains its own
buffer pool for caching data and indexes
in main memory. InnoDB stores its tables
and indexes in a tablespace, which may
consist of several files (or raw disk
partitions). This is different from, for
example, MyISAM tables where each table
is stored using separate files. InnoDB
tables can be of any size even on
operating systems where file size is
limited to 2GB.
InnoDB is included in binary
distributions by default as of MySQL
4.0.
From MySQL 4.0 on, the InnoDB storage
engine is enabled by default. If you
don't want to use InnoDB tables, you can
add the skip-innodb option to your MySQL
option file.
Problems: By default, all InnoDB
databases and tables pile into a single,
huge ibdata1 file. That file lives in
the MySQL datadir proper. The vague
name gives no clue as to the contents or
purpose of the file. And if you delete
a table, the ibdata1 file does not
shrink in size.
See: DISCARD TABLESPACE;
IMPORT TABLESPACE; innodb_file_per_table;
MyISAM; SHOW INNODB STATUS
InnoDB component files ibdata1 ib_logfile0 ib_logfile1
located in the MySQL datadir
.ibd, .frm
located in the database directory.
Though option innodb_file_per_table may
be in effect, the InnoDB engine will
still store some (not much) table
related data in the global tablespace
file (ibdata1).
When option innodb_file_per_table is in
effect, each table is a <TableName>.ibd
file, in a directory whose name is the
database. Of further importance with
this choice is that each .ibd file
contains a table ID number - which is
also tracked in the ibdata1 tablespaces
file. The need for all these files to
operate as a whole precludes the
possiblity of moving .ibd and .frm files
across MySQL servers.
See also: .frm; ib_logfile0; ibdata1;
.ibd
InnoDB locking InnoDB does locking on the row level
(rather than the table level, as with
MyISAM) and also provides an Oracle-style
consistent non-locking read in SELECT
statements. These features increase
multi-user concurrency and performance.
There is no need for lock escalation in
InnoDB because row-level locks in InnoDB
fit in very little space.
Deadlocks are possible for InnoDB
because it automatically acquires locks
during the processing of SQL statements,
not at the start of the transaction.
InnoDB statistics SHOW ENGINE INNODB STATUS;
innodb_data_file_path Server option for the [mysqld] section
of the configuration file to specify the
filename for the InnoDB data file, plus
attributes. Despite the name of this
option it is for specifying a file name,
not a full path: the full path to each
data file is formed by prepending
innodb_data_home_dir to
innodb_data_file_path, adding an
intervening slash if necessary.
The default name for the InnoDB data
file is ibdata1.
innodb_data_home_dir Server option for the [mysqld] section
of the configuration file to cause
InnoDB table files to be created in the
specified directory, rather than
defaulting to the MySQL datadir.
The InnoDB storage engine does not
create directories, so you need to
create the directory before starting the
server.
innodb_file_per_table Server option to cause InnoDB tables and
indexes to each have their own
<TableName>.ibd file, within a directory
having the name of the database, within
the datadir area. This is in contrast
with the default behavior of having all
InnoDB type databases and tables piling
into a huge ibdata1 file. This modularity
helps the administrator better visualize
what's going on in that directory. It
can also help isolate any damage. And
file system space is reclaimed with a
table is deleted. Separating tables
into individual files also make it
possible to physically copy those files
to a replication server, with both
servers shut down, in order to initially
set up the replication slave server.
BUT: Note that while tables are put into
database-named directories, there still
needs to be a shared tablespace in the
MySQL datadir, which is where it puts
its internal data dictionary and undo
logs. In more emphatic terms: The .ibd
files are not sufficient for InnoDB to
operate. This greatly influences how
you need to approach data assurance
backup and restoral.
If this server option is later removed,
any existing .ibd files will continue to
be used as is, and any new tables will
be in the shared tablespace.
Equivalent: innodb_file_per_table=1
Innotop A public domain 'top' like command for
MySQL, particularly for InnoDB engine
database tables.
INSERT INTO SQL command to add an entry (a row) to a
table. It has multiple forms..
Form to insert by column names:
INSERT INTO <TableName>
SET <ColumnName> = <Value>,
<ColumnName> = <Value>;
Form to insert one row, by positional
column values:
INSERT INTO <TableName>
VALUES (Col1val,Col2val);
Form to insert multiple rows, by
positional column values:
INSERT INTO <TableName> VALUES
(Col1val,Col2val),(Col1val,Col2val);
Example:
INSERT INTO Db1.Table1
SET LastName = 'Smith',
FirstName = 'Larraby';
Being a "write-only" operation, INSERT
returns no results, so don't attempt
mysql_store_result(), which would report
an error if attempted.
ER_DUP_ENTRY is the most common errno
returned from mysql_query(), meaning
that there is already such an entry in
the table.
Modifiers:
DELAYED As in INSERT DELAYED INTO
causes control to return immeidately,
with the intended inserts queued but
not executed - which can be dastardly
for LAST_INSERT_ID() purposes.
See also: LOAD DATA; REPLACE
INSERT_ID This variable is used to set the value
that is used by the subsequent INSERT or
ALTER TABLE statement when inserting an
AUTO_INCREMENT values.
Syntax: INSERT_ID = value
Inserting records: technique Adding a record to a table is performed
via the INSERT statement. If the record
already exists and you are actually
changing it, the UPDATE statement serves
that purpose. But before knowingly
performing UPDATE you would need to
first test for the existence of the
record via a query - which adds time and
overhead to your processing. Instead,
you can simply perform a REPLACE, which
amounts to an UPDATE if the record
already exists, or INSERT if it doesn't.
INT MySQL data type: an integer value.
Length: 4 bytes
Defaults to a signed number, having
possible values: -2147483648 to 2147483647
Can be defined with UNSIGNED, as in
"NumField int unsigned" to have a range
of 0 to 4294967295.
See also: BIGINT
INT UNSIGNED MySQL data type: an unsigned integer
value.
INTEGER Synonym for INT.
IP restricting See: Authorization for access
Join A database lookup which retrieves data
from multiple tables. It depends upon
there being common (redundant) column
data in the multiple tables in order to
match across the tables. The tables are
independent, but they all contain one
column whose content has the same
meaning and values across them all. An
example would be a company having an
employees database with separate tables
for employment history records and
another for personnel data (birth date,
home address, etc.), where both tables
would contain an employee number
column. The tables can be separate, but
are extensions of each other, and can be
queried as a composite.
Joins may be done on any column of
tables; but performance is optimized by
having the target columns indexed.
See also: INNER JOIN
Join types These are operations on two tables,
thought of as one on the left, and one
on the right...
JOIN: Return rows when there is at least
one match in both tables. This is the
most frequently used join.
INNER JOIN: Another name for JOIN.
LEFT JOIN: Return all rows from the left
table, even if there are no matches in
the right table
LEFT OUTER JOIN: Another name for LEFT
JOIN.
RIGHT JOIN: Return all rows from the
right table, even if there are no
matches in the left table
RIGHT OUTER JOIN: Another name for RIGHT
JOIN.
FULL JOIN: Return rows when there is a
match in one of the tables
FULL OUTER JOIN: Another name for FULL
JOIN.
This "inner", "outer" seems to derive
from that familiar (Venn, Euler) diagram
in logic study showing two circles
partially overlapping, where "left"
refers to the left circle portion
wherein there is no commonality with the
right circle; "right" likewise refers to
the right circle wherein there is no
commonality with the left circle;
"outer" refers to both those left and
right sections having no commonality;
"inner" refers to the overlapping
portion, where there is commonality.
Justify column output SQL wants to left-justify all column
output - something that is undesirable
for arabic numerals, which should be
right-justified.
See: Left justify...; Right justify...
Key See: PRIMARY KEY
KEY Specification in CREATE TABLE, as a
synonym for INDEX.
key_buffer_size Server variable for the size of the
buffer used for caching index blocks for
MyISAM tables. This buffer is shared
among connection-handler threads.
KILL MySQL statement to terminate a server
thread, by its number, or a query being
processed by the thread. Syntax:
KILL [CONNECTION | QUERY] Thread_ID
Privilege level required: SUPER, unless
it is one of your own.
The 'mysqladmin kill' command performs
the same operation, but allows multiple
thread ID values to be specified on the
command line.
The CONNECTION option has the same
effect as no option: The thread with the
given ID is terminated.
QUERY terminates any statement that the
thread is executing, but not the thread
itself.
See also: SHOW PROCESSLIST
LAST_INSERT_ID() SQL function whose original design
purpose is to return the last
AUTO_INCREMENT value which was put into
effect by this client-server session.
That is, though multiple clients may be
updating a certain table, each client
performing LAST_INSERT_ID() will see
only the last auto increment value which
it caused to be in the table: it is not
the case that LAST_INSERT_ID() will
return the most recent ID in the table.
This function was subsequently enhanced
to accept an argument to set a value,
which will be returned by the next
LAST_INSERT_ID() having a null
argument. The intent in this is to allow
you to effect auto incrementation
yourself.
Typical usage: SELECT LAST_INSERT_ID();
which returns a column name of
last_insert_id() and a character
numerals value.
Watch out for use of the DELAYED
modifier with INSERT, as the table will
not have been updated yet, and
LAST_INSERT_ID() will be unable to
return a useful value.
See: AUTO_INCREMENT; INSERT_ID
Latest record date/time in table Select MAX(Time_Field) from TableName;
libmysqlclient.a Typical contents:
array.o
bchange.o
bcmp.o
bmove.o
bmove_upp.o
charset-def.o
charset.o
client.o
ctype-big5.o
ctype-bin.o
ctype-cp932.o
ctype-czech.o
ctype-euc_kr.o
ctype-extra.o
ctype-gb2312.o
ctype-gbk.o
ctype-latin1.o
ctype-mb.o
ctype-simple.o
ctype-sjis.o
ctype-tis620.o
ctype-uca.o
ctype-ucs2.o
ctype-ujis.o
ctype-utf8.o
ctype-win1250ch.o
ctype.o
dbug.o
default.o
errmsg.o
errors.o
get_password.o
hash.o
int2str.o
is_prefix.o
libmysql.o
list.o
llstr.o
longlong2str.o
manager.o
md5.o
mf_cache.o
mf_dirname.o
mf_fn_ext.o
mf_format.o
mf_iocache.o
mf_iocache2.o
mf_loadpath.o
mf_pack.o
mf_path.o
mf_tempfile.o
mf_unixpath.o
mf_wcomp.o
mulalloc.o
my_alloc.o
my_compress.o
my_create.o
my_delete.o
my_div.o
my_error.o
my_file.o
my_fopen.o
my_fstream.o
my_gethostbyname.o
my_getopt.o
my_getwd.o
my_init.o
my_lib.o
my_malloc.o
my_messnc.o
my_net.o
my_once.o
my_open.o
my_port.o
my_pread.o
my_pthread.o
my_read.o
my_realloc.o
my_seek.o
my_sleep.o
my_static.o
my_strtoll10.o
my_symlink.o
my_thr_init.o
my_time.o
my_vsnprintf.o
my_write.o
net.o
pack.o
password.o
safemalloc.o
sha1.o
str2int.o
strcend.o
strcont.o
strend.o
strfill.o
string.o
strinstr.o
strmake.o
strmov.o
strnlen.o
strnmov.o
strtod.o
strtoll.o
strtoull.o
strxmov.o
strxnmov.o
thr_mutex.o
typelib.o
vio.o
viosocket.o
viossl.o
viosslfactories.o
xml.o
libmystrings.a Typical contents:
bchange.o
bcmp.o
bfill.o
bmove.o
bmove512.o
bmove_upp.o
ctype-big5.o
ctype-bin.o
ctype-cp932.o
ctype-czech.o
ctype-euc_kr.o
ctype-extra.o
ctype-gb2312.o
ctype-gbk.o
ctype-latin1.o
ctype-mb.o
ctype-simple.o
ctype-sjis.o
ctype-tis620.o
ctype-uca.o
ctype-ucs2.o
ctype-ujis.o
ctype-utf8.o
ctype-win1250ch.o
ctype.o
int2str.o
is_prefix.o
llstr.o
longlong2str.o
my_strtoll10.o
my_vsnprintf.o
r_strinstr.o
str2int.o
strappend.o
strcend.o
strcont.o
strend.o
strfill.o
strinstr.o
strmake.o
strmov.o
strnlen.o
strnmov.o
strstr.o
strtod.o
strtol.o
strtoll.o
strtoul.o
strtoull.o
strxmov.o
strxnmov.o
xml.o
Libs to be used for client compiles 'mysql_config --libs'
will report libs and options required to
link with the MySQL client library.
Output is like:
-Wl,-brtl
-L/Our/Path/To/mysql/lib
-lmysqlclient -lz -lnsl_r -lm
Note that you can use this command
within a compile command, like
`mysql_config --libs`, to generate all
needed options and not have to manually
enter them yourself.
LIKE, NOT LIKE SQL string pattern matching operator,
for basic tests. Used with wildcards:
% Matches any zero or more chars.
_ Matches any single character.
Examples:
Select * from birds where birdname
like 'Sparro%'
Select * from birds where birdname
not like 'Sparro%'
(To literally have % or _ in a pattern,
escape them: \% \_ .)
Caution: Watch out for null column
values throwing off comparisons. Null
values are not zero or null strings -
they are undefined, unknown data, and as
such cannot participate in the test. A
comparison involving a null column
evaluates to an unknown condition, which
is treated as "false".
See also: REGEXP
LIMIT SQL clause to limit the number of
records returned, to avoid a Full Table
Scan. Often used with OFFSET.
Put LIMIT at the end of a Select.
LOAD DATA A batch means of populating a table.
This is faster than doing INSERTs, and
conveniently allows specification of a
data source file.
LOAD DATA LOCAL A batch means of populating a table,
locally from the mysql client, as like:
LOAD DATA LOCAL INFILE 'parts-file'
INTO TABLE parts;
The capability inherent with this means
that you should have good security on
the server, to prevent some random
person from adding things to the
database system.
localhost Client access within a server system
causes the session to come from
"localhost". Such a session is conducted
via a Unix Domain Socket, meaning a
Socket special file within /tmp, like:
srwxrwxrwx 1 nobody nobody 0
Aug 29 11:21 mysql.sock
This shows up in a 'mysql' "status"
subcommand report like:
UNIX socket: /tmp/mysql.sock
If your system uses a socket file other
than the conventional /tmp/mysql.sock
name, your /etc/my.cnf file will likely
define the path in the [client] section
of that file, as "socket = ________".
LOCK Is in effect only during the session
which performed the LOCK. If you invoke
the 'mysql' command, issue LOCK, and
then exit the 'mysql' command, the lock
is automatically released because of the
exiting.
LOCK TABLES SQL statement to lock tables against
read or write processing.
Flavors:
LOCK TABLES Tablename READ:
- The session that holds the lock can
read the table (but not write it).
- Multiple sessions can acquire a READ
lock for the table at the same time.
- Other sessions can read the table
without explicitly acquiring a READ
lock.
LOCK TABLES Tablename WRITE:
- The session that holds the lock can
read and write the table.
- Only the session that holds the lock
can access the table. No other session
can access it until the lock is
released.
- Lock requests for the table by other
sessions block while the WRITE lock is
held.
A list of tables can be provided,
separated by commas.
Undo with UNLOCK TABLES;.
Note that table locks do not inhibit
table operations such as DROP TABLE.
Locking in general in MySQL MySQL grants table write locks as
follows:
- If there are no locks on the table,
put a write lock on it.
- Otherwise, put the lock request in the
write lock queue.
MySQL grants table read locks as follows:
- If there are no write locks on the
table, put a read lock on it.
- Otherwise, put the lock request in the
read lock queue.
Pending locks are subject to a timeout,
in:
- innodb_lock_wait_timeout
- GET_LOCK(Str,Timeout).
Locking methods MySQL uses table-level locking for
MyISAM, MEMORY and MERGE tables,
page-level locking for BDB tables, and
row-level locking for InnoDB tables.
Table locking in MySQL is deadlock-free
for storage engines that use table-level
locking. Deadlock avoidance is managed
by always requesting all needed locks at
once at the beginning of a query and
always locking the tables in the same
order.
Deadlocks are possible for InnoDB and
BDB because they automatically acquire
locks during the processing of SQL
statements, not at the start of the
transaction.
Locks, show Incredibly, there is no MySQL command to
show locks (locked tables) that are
extant.
Doing 'mysqladmin debug' is said to be
the only current way to reveal them -
which inconveniently writes its output
to the error log.
There is also SHOW OPEN TABLES, where
the In_use column lists the number of
table locks or lock requests there are
for the table.
log Deprecated server option for the general
log: use general-log these days.
Log cut-off See: Server Log cut-off
log-bin The mysqld option to specify the base
name of the binary log.
As a simple name, it is taken to be
relative to the datadir (but you can
specify a full path to put the binary
log elsewhere).
Example: log-bin=binary_log
Example: log-bin=/OURmysql/binary_log
Verify: There is no current way to
verify that the desired binary log name
is being used, via system variables;
the only apparent corresponding system
variable is log_bin, and that reports
only ON or OFF. Research shows that
this lack of reporting is a known
deficiency, Bug #19614.
Your only alternative is to use
SHOW BINARY LOGS, which shows the base
name plus attached generation number.
log-bin-index The mysqld option to specify the name of
the binary log index.
If not specified, the default name is
the same as the basename of the binary
log files, with an .index extension.
If specified, as a relative path, it is
interpreted starting at the datadir.
Can be specified as a full path, to have
the index in a separate area.
Example: log-bin=binary_log.index
Does not show up in system variables.
log_bin System variable which *should* report
the log-bin setting, but instead reports
whether binary logging is ON or OFF.
This inconsistency is a known issue with
the MySQL developers (Bug #19614).
Your only alternative is to use
SHOW BINARY LOGS, which shows the base
name plus attached generation number.
log-error mysqld_safe option specifying the path
name for the file to use for the error
log.
In [mysqld], the error log name is
relative to the datadir.
In [mysqld_safe], prior to MySQL 5.1.11
the log is taken to be in the directory
from which mysqld_safe was invoked. As
of 5.1.11 the log location is relative
to the datadir. You thus may want to
code a full path.
(err-log is the deprecated predecessor
name)
Does not show up in system variables.
log-slave-updates Server option for a replication slave
server such that the slave server will
record updates that it receives from the
master server to its own binary log.
It is not common for this option to be
in effect: it is used for replication
through a chain of servers.
With a simple slave server, this
option is *not* in effect, where the
slave server is only acting on entries
in its Relay Log which it copied from
the master's binary log. In this case,
the slave server's binary log will have
nothing written to it.
Where the slave server performs as a
master to a downline slave server, this
slave server obviously has to write to
its binary log for the second slave
server to have something to copy from
such that log-slave-updates needs to be
in effect on this first slave.
Logging See: Error log; Server Log
Logging command/function There is no MySQL command or function to
cause an entry to be added to the
General/Server Log - because there is no
real need for one, in that you can
readily accomplish that by issuing a
SELECT followed by an arbitrary
character string, like:
SELECT "Now we proceed to revise the
customer's address...";
Logs See: Error log; Server Log
LONGBLOB Binary form of LONGTEXT.
LONGTEXT MySQL data type: Storage for prodigious
text, up to 4,294,967,295 chars.
Storage size: Length of value + 4 bytes
You obviously should not expect 4 GBs in
a single column value: be realistic.
LPAD(String, Length, PadString) Function to return a string consisting
of String, left-padded with the
specified PadString, to the specified
Length.
LTRIM(String) Removes leading blanks (spaces) from the
returned string.
See also: RTRIM()
MASTER-CONNECT-RETRY Replication: Parameter to the
CHANGE MASTER command to specify the
number of seconds to wait between
attempts to connect to the master.
Default: 60 (seconds)
Report value via SHOW SLAVE STATUS\G
where it will appear under the less
specific identifier of
Connect_Retry: 60
master-host Former server option, to specify the
host name or IP address of the master
replication server.
Deprecated in 5.1.17; removed in 5.5.
master.info Replication: ASCII file in the slave
server MySQL directory containing
tracking info about the slave's
acquisition of entries from the master
server's binary log.
Specifically, the master.info file
contains:
- Read coordinates: The master log name
and master log position.
- Connection info: hostname of the
master plus the username and password
employed for access, plus port number
and connection retry interval.
- SSL keys and certificates.
The info herein is the partial source of
what is reported by command
SHOW SLAVE STATUS\G
In particilar, the Yes/No status values
reported by the command are not
reflected in the file.
Note that the password which Grants the
slave access to the master is in plain
text in this file, so protect.
If the command CHANGE MASTER is invoked,
its parameters will be stored in this
file.
The presence of this file on the slave
apparently causes it to initiate
replication when the slave is started.
Note that this is the descriptive and
default name of the file: its name is
assigned via the slave server option
master-info-file = ________
This file is cleared by the RESET SLAVE
command.
See also: relay-log; relaylog.info;
RESET SLAVE
Math See: Arithmetic
max_allowed_packet System variable: The maximum size of the
buffer used for communication between
the server and the client. The buffer
is initially allocated to be
net_buffer_length bytes long but may
grow up to max_allowed_packet bytes as
necessary. The value also constrains
the maximum size of strings handled
within the server. The default and
maximum values for max_allowed_packet
are 1MB and 1GB, respectively.
max_binlog_size MySQL daemon option specifying the size
limit for any binary log, whereupon the
MySQL server should transition to a
fresh binary log file.
Default: 1GB
In practice, the maximum size tends not
to be reached, as server restarts and
log flushing causes transition to a new
binary log file.
See also: expire_logs_days;
PURGE BINARY LOGS
max_connect_errors System variable: Defines how many
successive interrupted connection
requests are permitted from a single
host.
Default value: 10
Apparently, the following conditions
contribute to the number:
- Client program did not call
mysql_close() before exiting.
- Client had been sleeping more than
wait_timeout or interactive_timeout
without issuing any requests to the
server.
- Client program ended abruptly in the
middle of a data transfer.
- Client doesn't have privileges to
connect to a database.
- Client uses incorrect password.
- It takes more than connect_timeout
seconds to get a connect packet.
Lockout can occur where someone on a
host pursues the concept of using telnet
to test MySQL server viability: each
such connection is bogus in not being
the right protocol.
There is no automatic reset for this
condition within MySQL: the MySQL
administrator has to invoke
'mysqladmin flush-hosts', or do
'FLUSH HOSTS' within MySQL.
There is no known means for querying
MySQL for a list of blocked hosts.
The variable can be changed by one of:
- Updated the max_connect_errors value
in /etc/my.cnf and then send a HUP
signal to the mysqld to cause it to
reread the file.
- Issue the MySQL query
SET GLOBAL max_connect_errors=10000;
However, consider that this limit exists
as a security measure to keep villains
from performing guessing expeditions to
try to finally determine an access
password, where you don't want to give
them a large number of tries.
max_connections System variable: The maximum number of
simultaneous client connections allowed.
The default is 151 as of MySQL 5.1.15
and 100 for older versions.
max_user_connections System variable: The maximum number of
simultaneous client connections allowed
to any single account. The default value
is zero, which means "no limit". The
number of per-account connections is
bound in any case by the value of
max_connections.
MEDIUMBLOB Binary form of MEDIUMTEXT.
MEDIUMTEXT MySQL data type: Medium-length text
values, up to 16,777,215 chars.
Storage size: Length of value + 3 bytes
MEDIUMINT [(display_size)] [UNSIGNED] MySQL data type: a basic, signed whole
number with a range of -8,388,608 to
8,388,607 if signed, or 0 to 16,777,215
unsigned.
Storage size: 3 bytes
Modify table See: ALTER TABLE
Money values data type Use DECIMAL.
MONTHNAME(timestamp) Returns the month name for the date/time
value, such as 'January'.
To see this month's name:
select MONTHNAME(now());
Multiple-column indexes MySQL can create composite indexes (that
is, indexes on multiple columns). An
index may consist of up to 15
columns. For certain data types, you
can index a prefix of the column.
A multiple-column index can be
considered a sorted array containing
values that are created by concatenating
the values of the indexed columns.
The multi index is defined with an
artificial name which is not that of any
column in the table, but instead is an
arbitrary name whose purpose is to
constitute the index for some actual
columns in the table. In constructing
queries, the multi index name is not
used: the actual column names are, and
the multi index is then used by MySQL
*if* the query includes one or more
columns in the same order as in the
multi definition, and if more than one
that the query performs an AND operation
on them. If a multiple column query
requests column beyond the first one, or
doesn't AND the tests, then the multi
index is not used.
See also: Index
my_global.h C header file, to be included before any
other MySQL header files.
my_print_defaults MySQL command to display options from
option files. Displays the options that
are present in option groups of option
files. The output indicates what options
will be used by programs that read the
specified option groups. For example,
the mysqlcheck program reads the
[mysqlcheck] and [client] option
groups. To see what options are present
in those groups in the standard option
files, invoke my_print_defaults like
this:
my_print_defaults mysqld_safe
which reports:
--err-log=/var/log/mysqld.log
--pid-file=/var/run/mysqld/mysqld.pid
from /etc/my.cnf, which has:
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Likewise, invoke as
my_print_defaults mysqld
to see the other my.cnf parameters it
picks up.
See also: /etc/my.cnf
my_ulonglong A typedef used principally in C function
results. Its name certainly indicates
that it is an unsigned long long - but
the MySQL doc universally treats it as
signed. And printf() may not handle
unsigned long longs correctly, which
further confuses things. It is advisable
to do a conversion to simple int after
the call (myull = Func(); n = myull;) or
cast (unsigned long) in a printf().
.my.cnf My personal MySQL configuration file.
Location: $HOME/.my.cnf
The file permissions should exclude
access by anyone else.
Contains groups of statements headed by
an identifier in brackets ([...]:
[client] is a generic group name
delimiting options which apply to all
client accesses, by any program, where
there is not a specific group for that
client program.
Groups listed later take precedence, and
the last occurrence of a group
definition takes precedence.
my.cnf The MySQL server configuration file.
For at least Linux, its conventional
location is /etc/my.cnf (q.v.).
.MYD Filename suffix for MySQL MyISAM Data
files, such as data/mysql/user.MYD
as found in the server data directory.
See also: .frm; .MYI; Table
.MYI Filename suffix for MySQL MyISAM Index
files, such as data/mysql/user.MYI
as found in the server data directory.
See also: .frm; .MYD; Table
MyISAM The default FORM (engine) of MySQL
databases, as of version 3.23 .
File naming: Each table will consiste
of two files, named
<TableName>.MYD (See: .MYD)
<TableName>.MYI (See: .MYI)
Not suitable for MySQL transactions.
The engine is engineered to support
updating (Inserts) and reading (Selects)
at the same time.
Free space usage: There may be "holes"
(free blocks) in the midst of the MyISAM
data file, being the result of
deletions. If so, those are preferred
writing places for new data, so that it
is more centrally located. If no such
free blocks, new records are added at
the end of the MyISAM file.
Blocking: If an Insert or Replace is
running, an incoming Select is blocked
until that updating completes. Further,
MySQL gives updates higher priority than
retrievals, so if there are multiple
updates queued, they will be dispatched,
one by one, before the retrieval can run.
MyISAM concurrency As noted in the MyISAM description, the
MyISAM engine supports concurrency under
some conditions. The engine prefers to
fill "holes" during Inserts, and in that
circumstance no concurrency is allowed.
If there are no "holes" such that new
data is to be appended to the end of the
MyISAM file, concurrency is allowed,
because it's an orderly process of
appending records. If there are holes,
concurrent inserts are disabled but are
enabled again automatically when all
holes have been filled with new data.
(Note that the concurrency is internal
to the engine and is a performance
element: the concurrency is not known to
MySQL itself, and so does not figure
into locking regimens - which for MyISAM
is table locking, anyway.) The
concurrent_insert system variable can be
use to alter the behavior.
I've demonstrated this behavior as
follows... A newly defined database and
table are loaded with data (no holes).
A long-running Select is then performed,
and during it an Insert is attempted.
That Insert completes while the Select
is running. Now Delete that record.
That effectively creates a "hole".
Start the long-running Select again and
again attempt the Insert while the
Select is running: now the Insert waits
until the Select has finished.
MyISAM locking Is by table (not by row).
While this is very coarse, the saving
grace is that it is very simple and
therefore quite fast (as contrasted with
more complex row locking in InnoDB).
MyISAM performance MyISAM has in-row fragmentation which
means single row may be stored in many
pieces.
Indexes may be cached in key_buffer
while data is cached in OS cache.
Option delay_key_writes may improve
performance dramatically in certain
cases by avoiding flushing dirty index
blocks from key_buffer to disk, but it
also comes at great danger if the MySQL
server process crashes or the computer
system fails. Some advocate using this
option on replication slave servers
without worry; but your slave server
usually exists to stand in for a failed
primary, so still a bad risk.
Table lock blockages may be reduced by
spreading data fields over more tables.
MyISAM recovery planning Make sure you have decent
myisam_sort_buffer_size and large
myisam_max_sort_file_size otherwise
recovery may be done by key_cache rather
than sort, which can take even longer.
myisam_sort_buffer_size System variable: The size of the buffer
that is allocated to sort an index for
MyISAM tables during operations such as
ALTER TABLE, CREATE INDEX, and REPAIR
TABLE.
myisamchk An integrity checker for MyISAM-based
tables. Is an independent standalone
program that operates directly on the
files which constitute tables: it is not
a MySQL client.
MYSQL C: A structure type. Contains
information about a connection
(session) - a connection handler.
As created with mysql_init().
When done with it, do mysql_close().
Sample definition:
MYSQL mysql_struct;
MYSQL *mysql_struct_ptr;
MySQL Derived from the mSQL database system.
MySQL is named after co-founder Monty
Widenius's daughter, My.
MySQL 5.0 Adds stored procedures, views, security
upgrades, and triggers.
MySQL accounts MySQL has its own users namespace: you
define each MySQL and give it a
password. That data is stored in the
database named "mysql", in the table
named "user".
Accounts are removed via
DROP USER <Username>;
MySQL commands Must have ';' at the end, like:
show databases;
mysql command OS command to execute SQL queries within
MySQL, which is to say that the command
is a general client to any mysqld
server. (By default, connects to the
local server, but can reach any server.)
Can be used to execute SQL commands
stored in a file. Runs in interactive
mode, responding to movement and editing
operations akin to tcsh: Up Arrow key to
retrieve last command, Ctrl-D to delete
char in line, Ctrl-E to go to end of
line, etc.
The mysql command seems to default to
MySQL username "root" if -u _____ is not
entered on the command line, and assumes
that host is "localhost". The command
can, however, readily connect to another
host via --host, --port.
This command looks in /etc/mysql/my.cnf
for a [client] section, where it
observes any definitions there.
This command communicates with the
mysqld through a Unix socket file, whose
default path is /tmp/mysql.sock. The
socket file path may be defined
otherwise in the [mysqld] section of the
/etc/mysql/my.cnf file: if so, there
needs to be a [client] section in that
file, with the same definition, else the
'mysql' command will be unable to
connect to the server.
Within 'mysql', command need to be
terminated with a semicolon (;) - which
allows you to feed in complex,
structured info such as table
definitions.
Options:
-e 'Query', -e-execute='Query'
To perform just the one query, and
exit. A single query can be entered
without a trailing semicolon; multiple
queries should have intervening
semicolons.
-h <Host> The command can connect to a
remote host, providing a powerful means
for doing things like 'mysqldump' on
the local host to copy a database or
table to another MySQL server.
-N (--skip-column-names) No column
names in results.
-p<Password> (Note that this option
does not allow a space after the option
letter, before the password.)
-r (--raw) Raw output: no escape
prefixing of chars.
-u <Username>
Note: You may feed pre-written
definition data (such as CREATE TABLE)
to the command, but use spaces within
the data, not Tab chars.
You can invoke this command without the
nuisance of having to supply a password
if you set up a ~/.my.cnf file.
See also: mysqladmin; mysqldump
mysql command, across systems The 'mysql' command is a client command
to interact with mysqld on any system.
To use it across systems, do like:
mysql --host=host27 --password='Abcd'
-u root -e 'SHOW MASTER STATUS;'
mysql command, feed in file Beyond the -e command line option to
perform a single action, you can feed in
a file via Stdin, or even a command via
pipe. Examples:
Show databases:
echo 'show databases' | mysql
Perform all statements in a file, via
redirection:
mysql < statements_file
(Each line of the file should have a ';'
at the end.)
mysql database Basic product database.
See its tables by doing
SHOW TABLES FROM mysql;
which reveals like:
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Tables and purposes:
db Defines which users can
access which databases
from which hosts.
No passwords in here.
func Info about user-defined
functions.
help_xxx Used for server-side
help.
proc Contains info about
stored procedures and
functions.
time_zone_xxx Contain time zone info.
user Defines Host, User,
Password, and an array
of privs. Is used by
the mysqld to govern
access to the server.
The privs in the 'user' table are such
that if a Y is set for a priv, that priv
is definitely granted, to operate on all
databases, and no further validation is
required. If the priv is N, it does
*not* mean that the priv is denied, but
rather than the 'db' table must further
be queried to see if specific database
privs are granted there. This is in
concert with the good practice of
limiting global permissions to only
those for whom they are appropriate, and
defining fine-grained permissions
according to database. (Note that the
'user' table contains no database
column, so db-specific privs cannot be
itemized there.) It is sometimes seen
that userids defined for housekeeping
(database backup, restore) will be
wholly defined in the 'user' table, with
Y privs, and not in the 'db' table.
A further case is that the 'db table can
define the Db and User, but leave the
Host undefined. This triggers reference
to the 'host' table, which defines Host,
Db, and privs. And even further are the
'tables_priv' and 'columns_priv' tables.
Try to avoid over-using definitions
further down in the hierarchy, because
the serial lookups can impact
performance in repeated client sessions.
mysql directory Lives in the datadir, and contains the
MySQL control database.
Like: /var/lib/mysql/mysql
Permissions: drwx--x--x
mysql username in /etc/passwd Typically ends up in /etc/passwd looking
like the following, with a UID of 101:
mysql:x:101:157:MySQL server:/mysql/data:/bin/bash
The username should match the 'user'
spec in /etc/my.cnf.
157 is the group, mysql.
Note that the "home directory" is the
MySQL data directory. This Unix
username should not be used for direct
'su' or like usage, as it could operate
in the data dir, where it could
interfere with or even clobber database
elements.
MySQL server, start See: Server, start
MySQL version See: Version of MySQL
MySQL Workbench Provides DBAs and developers an
integrated tools environment for:
- Database Design & Modeling
- SQL Development (replacing MySQL Query
Browser)
- Database Administration (replacing
MySQL Administrator)
Available for platforms:
Windows Linux Macintosh
mysql.sock The MySQL Unix domain socket special
file, created by the MySQL server,
through which local client invocation
access the server. The file is created
when the server starts. The file is
removed when mysqld is shut down.
Is specified in /etc/my.cnf
section [mysqld]
as: socket=__path__
The my.cnf file should also contain a
[client] section specifying the same
thing.
It is best that this is not in the
datadir directory, so that non-server
accesses can be kept out of that
directory.
mysql-bench Name of RPM package containing MySQL
benchmark scripts and data.
(Not to be confused with MySQL
Workbench.)
mysql_affected_rows() Returns the number of rows changed by
the last UPDATE, deleted by the last
DELETE or inserted by the last INSERT
statement. May be called immediately
after mysql_query() for UPDATE, DELETE,
or INSERT statements.
Note: The function returns an int, not a
my_ulonglong as some doc says: using a
my_ulonglong yields false results.
mysql_query() by itself returns a
success 0 indication, but otherwise
nothing to indicate whether or not the
operation did anything, as in
accomplishing a deletion.
For SELECT statements,
mysql_affected_rows() works like
mysql_num_rows().
my_ulonglong myull;
MYSQL mysql, *mysql_ptr;
myull = mysql_affected_rows(mysql_ptr);
mysql_character_set_name() C function to get the name of the
default character set being used by the
MySQL server, probably ISO-8859-1.
Invoke after the connection is achieved,
as via mysql_real_connect().
MYSQL mysql;
char *char_ptr;
char_ptr =
mysql_character_set_name(&mysql);
Returns: string
Example of returned string: "latin1"
mysql_close() C function to terminate a connection to
the database server, using the MYSQL
struct pointer which had been returned
by mysql_init() or
mysql_real_connect().
This will deallocate the connection
handler struct space.
MYSQL mysql, *mysql_ptr;
mysql_close(&mysql);
or:
mysql_close(mysql_ptr);
Returns: No return value - is a Void
function.
mysql_config Command to show useful info for
compiling MySQL client programs and
connecting them to MySQL.
Options:
--cflags
Compiler flags to find include files
and critical compiler flags and defines
used when compiling the libmysqlclient
library. The options returned are tied
to the specific compiler that was used
when the library was created and might
clash with the settings for your own
compiler. Use --include for more
portable options that contain only
include paths.
--include
Compiler options to find MySQL include
files.
--libmysqld-libs, --embedded
Libraries and options required to link
with the MySQL embedded server.
--libs
Libraries and options required to link
with the MySQL client library.
--libs_r
Libraries and options required to link
with the thread-safe MySQL client
library.
--port
The default TCP/IP port number, defined
when configuring MySQL.
--socket
The default Unix socket file, defined
when configuring MySQL.
--version
Version number for the MySQL
distribution.
mysql_data_seek() C function to seek to a specific row of
the result set returned by
mysql_store_result().
MYSQL_RES *result_ptr;
my_ulonglong row_num;
mysql_data_seek(result_ptr, row_numl);
Returns: Void function - nothing
returned.
mysql_errno() Returns an unsigned int which, if zero,
indicates that no error occurred in the
most recently invoked client library
routine which returns a status; or
non-zero if an error occurred, where you
may invoke mysql_error() to get that
error's indicative string.
MYSQL mysql, mysql_ptr;
unsigned int errnum;
errnum = mysql_errno(&mysql);
Ref: mysqld_errno.h
Notes: This is useful after performing
a mysql_store_result() a null result,
so you would want to determine if there
were actually no error (MySQL errno = 0)
as when the operation had been an INSERT
or DELETE, which returns no data.
mysql_error() Returns a null-terminated string
containing an error message for the most
recently invoked client library routine
which returns a status.
Typically invoked after mysql_errno()
was invoked to return an error code
which indicated that the latest
operation failed.
MYSQL mysql, mysql_ptr;
char *errstring;
errstring = mysql_error(&mysql);
Returns: Empty string if no error.
Note that MySQL provides no function by
which you can provide a MySQL errno and
get back the corresponding error
string.
mysql_fetch_field() C function to get info (metadata) about
successive fields in a table... that is,
structural information.
MYSQL_RES *result_p;
MYSQL_FIELD *field_ptr;
field_ptr =
mysql_fetch_field(result_p);
You can then examine the field via
field_ptr.<ElementName> and thereby
catalog the names of the fields
returned.
Again, note that this function is to
obtain structural info. To access the
data contained in the field (column),
use mysql_fetch_row().
Returns: pointer to a field structure.
mysql_fetch_field_direct() C function to get info (metadata) about
a given field in a table, via direct
addressing (as opposed to examining
successive fields via
mysql_fetch_field()).
MYSQL_RES *result_p;
MYSQL_FIELD *field_ptr;
field_ptr =
mysql_fetch_field(result_p);
You can then examine the field via
field_ptr.<ElementName> and thereby
catalog the names of the fields
returned.
Again, note that this function is to
obtain structural info. To access the
data contained in the field (column),
use mysql_fetch_row().
Returns: pointer to a field structure.
mysql_fetch_fields() C function to get info (metadata) about
all fields in a table.
MYSQL_RES *result_p;
MYSQL_FIELD *field_ptr;
field_ptr =
mysql_fetch_fields(result_p);
You can then examine each field via
field_ptr[index] up to the
mysql_num_fields() value and thereby
catalog the names of the fields
returned.
Returns: pointer to an array of field
structures.
You might search through and catalog the
names of the returned fields - but that
should only be necessary if you
performed a Select with *: but if you
performed the Select with column names,
then you have defined the order in which
the columns will be returned and can
directly access them as needed.
Again, note that this function is to
obtain structural info. To access the
data contained in the field (column),
use mysql_fetch_row().
mysql_fetch_lengths() C function to get the lengths of the
fields (columns) in the returned row.
MYSQL_RES *result_p;
unsigned long *ul_p;
ul_p = mysql_fetch_lengths(result_p);
You must call this function each time
you call mysql_fetch_row(), or suffer
lengths being out of sync with data.
This function provides the only means of
determining the lengths of variable
length fields, such as BLOB and VARCHAR
in processing the field data, and is
safer than always expecting the data to
be character, with a string end null:
data might be binary, where embedded
binary zeroes throw off processing.
Returns: array of unsigned longs if
successful, else 0.
mysql_fetch_row() C function to retrieve the next row of
data from the result set. Thereafter you
may index to the field of interest
within the row, with the index starting
at 0 and going up to value
mysql_num_fields() - 1.
MYSQL_ROW row;
char *first_field;
row = mysql_fetch_row(result_p);
first_field = row[0];
Returns: Non-NULL pointer when a row has
been returned, else NULL when there are
no more rows.
Field order is, naturally, determined by
the order specified in your Select.
MYSQL_FIELD A structure containing structural
information (metadata) about a specific
field in a table. Struct fields:
char *name; /* Column name. */
char *table; /* Table of column if
column was a field */
char *def; /* Default value (set by
mysql_list_fields) */
enum enum_field_types type; /* Type of
field, per mysql_com.h */
unsigned int length; /* Col. width */
unsigned int max_length; /* Max width
of col data in all rows */
unsigned int flags; /* Div flags */
unsigned int decimals; /* Number of
decimals in field */
It is important to realize that this is
information about the "container", not
what is contained in the field... The
structure does not reveal the data in
the field, which you need to instead
obtain via mysql_fetch_row().
See also: MYSQL_ROW
mysql_field_count() C function to find the number of columns
contained in a result set.
MYSQL *mysql_ptr;
unsigned int Fields;
Fields = mysql_field_count(mysql_ptr);
Returns: unsigned int number of fields
(columns).
Note that MySQL engenders some confusion
in getting row vs. columns counts, as
the field count function uses the
connection struct pointer while the row
count function uses the results struct
pointer.
See also: mysql_num_rows()
mysql_get_client_info() C function to get the version,release
numbers of the MySQL library software
being used by the client.
Invoke any time - does not involve any
prior mysql_init() or connection.
char *char_ptr;
char_ptr = mysql_get_client_info();
Returns: string
Example of returned string:
"3.23.55"
mysql_get_host_info() C function to get the hostname of the
MySQL db server system, and the type of
connection used.
Invoke after the connection is achieved,
as via mysql_real_connect().
MYSQL mysql;
char *char_ptr;
char_ptr = mysql_get_host_info(&mysql);
Returns: string
Example of returned string:
"Localhost via UNIX socket"
mysql_get_proto_info() C function to get the MySQL protocol
version used in the current connection.
connection used.
Invoke after the connection is achieved,
as via mysql_real_connect().
MYSQL mysql;
unsigned int n;
n = mysql_get_proto_info(&mysql);
Returns: unsigned integer
Example of returned number: 10
mysql_get_server_info() C function to get the version,release
numbers of the MySQL software being used
by the server.
Invoke after the connection is achieved,
as via mysql_real_connect().
MYSQL mysql;
char *char_ptr;
char_ptr =
mysql_get_server_info(&mysql);
Returns: string
Example of returned string:
"3.23.55"
.mysql_history Home directory commands history.
mysql_init() C function to optionally obtain, and
initialize a connection handler
structure, as will be needed for a
subsequent session connection, as via
mysql_real_connect().
You can achieve this in one of two ways:
1. Call with the address of the MYSQL
structure in your own storage area:
MYSQL mysql;
mysql_init(&mysql);
This will cause that structure to be
initialized and its address
returned.
2. Call with 0, to have the function
create a MYSQL structure, initialize
it, and return its address to the
caller:
MYSQL *mysql_ptr;
mysql_ptr = mysql_init(NULL);
This method is deemed preferable in
that there is no preconception about
the structure.
Returns: non-zero MYSQL struct address
if successful, zero if failure.
Do mysql_close() later, to close the
connection.
mysql_install_db Command to initialize the MySQL data
directory and creates the system tables
that it contains, if they do not exist.
Specifically, it:
- Creates the data directory
(default: /var/lib/mysql/, owner and
group = "mysql")
- Creates subdirectory "mysql" and the
grant tables, with initial accounts
for the root and anonymous users.
- Creates empty subdirectory "test"
for a test database.
- Creates and populates subdirectory
"performance_schema".
Default dirs: /var/lib/mysql/
/var/lib/mysql/mysql/
/var/lib/mysql/test/
Most installation methods run this
command for you, so that you don't have
to; but this can be undesirable, as you
may want the database directory to not
be in /var.
mysql_install_db by default reads
/etc/my.cnf for its key values. You can
invoke it with -defaults-file=_____ to
point it to some other configuration file.
Whereas the RPM install does not create
a /etc/my.cnf file, a technique to get
the data directory where you want it
would be for there to be a pre-install
step which plants a temporary
/etc/my.cnf file with a datadir spec
saying what you really want.
Options:
--basedir=path
The path to the MySQL installation
directory.
--datadir=path, --ldata=path
The path to the MySQL data directory.
--user=____
To create the directories with this
username and groupname. Conventionally,
this username is "mysql".
mysql_list_dbs() C function to obtain a list of the names
of databases on the MySQL server.
This is a deprecated function! Instead,
issue a query to do SHOW DATABASES.
Returns: A non-null MYSQL_RES pointer if
successful, else null.
When done with the results, call
mysql_free_result() to free the
MYSQL_RES structure storage space.
mysql_list_fields() C function to obtain a list of the names
of columns in the specified table within
the current database.
This is a deprecated function! Instead,
issue a query to do SHOW COLUMNS.
Returns: A non-null MYSQL_RES pointer if
successful, else null.
When done with the results, call
mysql_free_result() to free the
MYSQL_RES structure storage space.
mysql_list_processes() C function to obtain a list of the
processes currently running on the MySQL
server.
This is a deprecated function! Instead,
issue a query to do SHOW PROCESSLIST.
Returns: A non-null MYSQL_RES pointer if
successful, else null.
When done with the results, call
mysql_free_result() to free the
MYSQL_RES structure storage space.
mysql_list_tables() C function to obtain a list of the names
of tables in the current database.
This is a deprecated function! Instead,
issue a query to do SHOW TABLES.
Returns: A non-null MYSQL_RES pointer if
successful, else null.
When done with the results, call
mysql_free_result() to free the
MYSQL_RES structure storage space.
mysql_num_fields() C function to get the number of columns
(database record fields) in the result
set. May be invoked after
mysql_store_result(), but best invoked
after mysql_fetch_row().
unsigned int n;
MYSQL_RES result;
n = mysql_num_fields(&result);
printf("Columnss: %u\n", n);
This function is deprecated: you should
use mysql_field_count() instead.
Caution: Using this function as an
argument to printf() may yield erroneous
results!
mysql_num_rows() C function to get the number of rows of
data in the result set, accurate only if
the result set was retrieved with
mysql_store_result().
my_ulonglong myull;
MYSQL_RES result;
myull = mysql_num_rows(&result);
printf("Rows: %lu\n", myull);
Returns: number of rows, as an unsigned
long long value.
Caution: Using this function as an
argument to printf() may yield erroneous
results!
Note that MySQL engenders some confusion
in getting row vs. columns counts, as
the field count function uses the
connection struct pointer while the row
count function uses the results struct
pointer.
See also: mysql_field_count()
mysql_ping() C function to reestablish a session
which timed out due to inactivity. The
session must, naturally, have been
previously established with all settings
via mysql_real_connect().
int mysql_ping(MYSQL *conn);
Returns: 0 if all is well (the
connection was up, or was successfully
re-established); else non-zero, where
you might examine the error condition
and reattempt several times.
mysql_query() C function to execute a given SQL query,
when the query consists of only
displayable chars. This would be done
after establishing a session with the
MySQL server, as via
mysql_real_connect(). Syntax:
MYSQL *mysql;
const char Query;
mysql_query(mysql, Query);
Returns: int, 0 if successful, non-zero
if non-successful. Note that the return
code indicates only the success of the
mysql_query() function in interoperating
with the server: it does not reflect the
successfulness of the query that was
performed - that can be determined by
the number of rows returned, where 0
means that the query failed.
See also: mysql_real_query()
mysql_real_connect() C function to connect to the MySQL
server and establish a session,
optionally with a specific database.
MYSQL *Mysql_ptr;
const char *Hostname;
const char *Username;
const char *Password;
const char *Dbname;
uint Port;
const char *Unix_Socket;
uint Client_Flag;
/* */
mysql_init(&mysql);
mysql_real_connect(&Mysql_ptr,
Hostname,
Username,
Password,
Dbname,
Port,
Unix_Socket,
Client_Flag);
Operands:
Mysql_ptr is the address of the MYSQL
struct that was initialized by a
preceding mysql_init() call. Note that
mysql_real_connect() success returns a
pointer to this same thing - trivial,
but convenient...and potentially
confusing.
Password is your MySQL password.
See Password topic for more info.
Dbname The name of the database to
use, for upcoming table references
without employing a database
qualifier - effectively doin an SQL
'USE db_name' for you.
A null value will cause no specific
database selection, such that you must
subsequently either do an SQL USE
statement or employ a database
qualifier (Dbname.Tablename).
Port specifies the client port number,
if you want to specify one, to use in
connecting to the server (where the
server is using its own, standard
port, which is independent of this
specification). Specify 0 to let the
MySQL client choose a local port
number.
Unix_Socket may be used if the client
is running on the same host as the
server, to specify a socket file to
use. Specify an empty string to let
the MySQL client choose a default
socket filename (in /tmp).
Returns: Success: a pointer to the same
MYSQL struct that was passed as the
first operand - a trivial re-reference,
not a "new value". The pointer is a
connection handle, for use in
subsequent operations such as
mysql_query(). Failure: 0
Errors:
2003 (Can't connect to MySQL server on
'_Hostname_' (79))
79 is the Unix errno, which in this
case is ECONNREFUSED.
Note that long intervals between
interactions with the MySQL server can
cause the session to time out. Use the
mysql_ping() function to reestablish the
connection.
MYSQL_RES MySQL Results set structure (a typedef)
of largely null-terminated strings for
containing the rows of results from
SELECT or SHOW operations, as returned
by mysql_store_result() and freed by
mysql_free_result(). Use mysql_fetch()
to retrieve contents.
Looks like:
typedef struct st_mysql_res {
my_ulonglong row_count;
unsigned int field_count,
current_field;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
MEM_ROOT field_alloc;
MYSQL_ROW row;
MYSQL_ROW current_row;
unsigned long *lengths;
MYSQL *handle;
my_bool eof;
} MYSQL_RES;
MYSQL_ROW For C programming, a "char **" typedef
pointing to an array of strings,
addressing the data returned from
mysql_fetch_row(). That is, the array
addresses strings which constitute the
data in the columns of that row.
mysql_store_result() C function to read the entire result of
a query and store it into a MYSQL_RES
structure.
When done with the results, call
mysql_free_result() to free the
MYSQL_RES structure storage space which
was dynamically allocated by the
mysql_store_result() operation.
MYSQL *Mysql_ptr;
MYSQL_RES *result_ptr;
Mysql_ptr = mysql_init(NULL);
result_ptr =
mysql_store_result(Mysql_ptr);
Note that this function should be called
only after a query which returns data:
it is not valid to call this function
after a DELETE or INSERT operation - you
should call mysql_affected_rows()
instead. But, of course, a generalized
processing routine would not necessarily
know if a DELETE or INSERT was at play,
so it should act per the following.
Returns: MYSQL_RES value if successful
and data was returned; else null if the
query returned no data or an error
occurred. If null (as could be expected
from an INSERT), then you may call
mysql_errno(), where a zero would
indicate no error and thus that the
mysql_store_result() was in fact
successful.
See also: mysql_free_result();
mysql_use_result()
mysqlaccess OS command to manage users.
It is a perl script which contains a
$MYSQL variable which specifies the path
to mysql executable (default:
'/usr/local/mysql/bin/mysql') which will
probably have to be updated for your
site.
to mysql executable
mysqladmin OS command whose base purpose is for
operating the MySQL server. Can be used
to create and delete databases, but the
mysql command is the intended means for
doing such things.
Note that the command is NOT
interactive, as the mysql command is.
You can perform Grants and like
operations using 'mysql' rather than
'mysqladmin'.
Syntax:
mysqladmin [Options] Command...
where the list of Commands is:
create <DBname>
Create a new database. But: better to
do this work via the mysql command.
debug
To have debugging info written to the
error log.
drop <DBname>
Deletes the named database and all its
tables. But: better to do this work
via the mysql command.
extended-status
Display the server status variables
and their values.
flush-hosts
Flush all information in the host cache.
flush-logs
Flush all logs. (Sending the server a
SIGHUP does the same.)
flush-privileges
Reload the grant tables. (Same as
reload command operand.)
flush-status
Clear status variables.
flush-tables
Flush all tables.
See: FLUSH TABLES
flush-threads
Flush the thread cache.
kill <ThreadID>[,<Threadid>]
Kill server threads.
old-password <NewPassword>
Like the password command, but stores
the new password using the old
(pre-4.1) password-hashing format.
password <NewPassword>
Sets new password.
ping
Test for server responsiveness. If so,
returns line "mysqld is alive".
processlist
Show a list of active server threads,
like the output of the SHOW
PROCESSLIST statement.
reload
Reload the grant tables.
refresh
Flush all tables and close and open
log files.
shutdown
Stop the server.
status
Yields a one-line summary of the
server status, including its uptime
(in seconds), number of threads,
questions, slow queries, opens,
flush-tables, open-tables, and queries
per second.
stop-slave
Stop replication on a slave server.
variables
Displays the (long) list of server
system variables and their values.
version
Options include:
--host=host_name, -h host_name
Connect to the MySQL server on the
given host.
--password[=password], -p[password]
The password to use when connecting to
the server.
--port=port_num, -P port_num
The TCP/IP port number to use for the
connection.
--user=user_name, -u user_name
The MySQL username to use when
connecting to the server.
Example:
mysqladmin status
You can invoke this command without the
nuisance of having to supply a password
if you set up a ~/.my.cnf file.
See also: mysql
mysqlbinlog Utility to work with the binary log
files involved in transaction recording
and replication.
Syntax:
mysqlbinlog [options] log_file ...
Options:
--database=db_name
--host=host_name
--port=port_num
--result-file=name
--start-datetime=datetime
--start-position=#
--stop-datetime=datetime
--stop-position=#
By default, this command will generate
readable ASCII of the file content, in
much the same way that mysqldump
generates ASCII form statements of
database table content. Such output can
be fed to the 'mysql' command in a
database recovery operation to reapply
the changes which occurred after the
point in time represented by a database
restoral.
Use --stop-datetime="YYYY-MM-DD hh:mm:ss"
to include updates which occured up to
and including the time specified. This
would allow you to eliminate a bad
action that had occured, such as an
ill-considered DROP TABLE.
Use --start-datetime="YYYY-MM-DD hh:mm:ss"
to cause updated to be replaced from
that time forward, as probably used with
--stop-datetime, to omit a bad action.
But, rather than using such datetimes as
an attempt to exclude bad stuff, it may
well be better to invoke mysqlbinlog to
generate a file of all the updates and
edit that to exclude bad stuff, to
thereafter feed the revised file to
command 'mysql' to restore goodness.
You can also use the --stop-position and
--start-position options to specify
statement numbers to exclude bad
actions.
See also: Binary Log
mysqlcheck Utility to assist in table analysis and
optimization, as well as recovering from
damaged tables. Operates as a MySQL
client of a running server, to operate
upon MyISAM and InnoDB type tables.
May be invoked via cron to look for
problems periodically.
BUT: A table being checked cannot be
updated, which can be very disruptive,
so schedule for quiet times.
mysqlclient The C client module provided in the
MySQL package, linked in like:
-L <Path>/lib -l mysqlclient
Note that in AIX, at least, an
additional requirement is -lm, to as to
pick up the "floor" module.
mysqld OS command which the MySQL daemon runs.
/usr/sbin/mysqld
See also: Shut down
mysqld PID In file: data/<Hostname>.pid
See also: PID fil
mysqld_multi Program for starting and stopping
multiple servers on a single host.
mysqld_safe Program for a host where there is a
single MySQL server, to start the mysqld
process, monitor it, and restart it.
The mysqld_safe command should be
invoked with superuser privileges: after
some set-up, it invokes mysqld as a
child of the mysqld_safe process, where
mysqld is run as the 'user' specified in
the /etc/my.cnf file (usually, 'mysql').
mysqld_safe invokes programs
my_print_defaults mysqld
my_print_defaults mysqld_safe
to harvest specifications, where it sets
variables, such as $open_files to the
value gotten from the open_files_limit
spec in my.cnf, whereafter, if it got a
number and ran as root, does: 'ulimit -n
$open_files'; otherwise it tries to run
with the default open files value, which
is likely 1024.
If the mysqld server fails, mysqld_safe
restarts it. (If there is a segfault,
this will result in mysqld_safe looping
in starting mysqld again again,
futilely.)
Options: Has a few of its own options,
but is expected to pass on all others to
mysqld. Its own options:
--err-log=____ The name of the error
log, either as relative to the
directory from which mysqld_safe was
invoked, or full absolute.
--ledir=____ Look for the server in
this directory (the libexec dir).
--mysqld=____ Path to the mysqld pgm.
--timezone=____ The local time zone,
if necessary.
Internally, the command issues
my_print_defaults to get any system
options coded for it in the
[mysqld_safe] section of the /etc/my.cnf
file.
See also: mysqld_multi;
my_print_defaults; Server Log
mysqldump OS command to dump the definition and
contents of a MySQL database or table to
a file, to perform a backup of a
database or table, later usable in a
mysqlrestore command scenario. Operates
as a MySQL client to step through data.
The output is textual (ASCII). Such
output has multiple purposes:
- Database backup/restore image.
- A portable image of the database,
usable for migration to another
computer or platform, or even DBMS.
- Showing database structure and content.
The command sends its output to Stdout,
by default. Use --result-file=______ to
designate an output file.
For usage, do: mysqldump --help
Command syntax:
mysqldump <Options> DBname
[Tablename ...]
where Options include:
--add-drop-table Add statement
DROP TABLE IF EXISTS before each
CREATE TABLE statement.
--add-locks Add LOCK TABLE and UNLOCK
TABLE statements around the set of
INSERT statements that load the data
for each table.
--all Add info to the CREATE TABLE
statements, such as table type, the
beginning AUTO_INCREMENT value, etc.
--all-databases To have all databases
dumped. Caution: The dump will
include the grant table values that
are in the mysql database, which are
usually inappropriate for restoring
into a different server.
--allow-keywords Allow for the
creation of column names that are
keywords.
--complete-insert Use INSERT statments
that name each column to be inserted.
--databases Interpret all args as
database names and dump all tables in
each database.
--delayed-insert Write INSERT DELAYED
statements.
--delete-master-logs
See separate description.
--disable-keys Add ALTER TABLE
... DISABLE KEYS and ALTER TABLE ...
ENABLE KEYS statements to the output
so as to disable key updating while
INSERT statements are being processed,
to speed up index creation for MyISAM
tables.
--extended-insert Write multi-row
INSERT statements, which can be loaded
more efficiently than single-row
statements.
--first-slave Lock all tables across
all databases using FLUSH TABLES WITH
READ LOCK.
--flush-logs Flush the server log
files before starting the dump.
(See FLUSH LOGS)
--force Continue execution even if no
errors occur.
--lock-all-tables Lock all tables
across all databases. This is achieved
by acquiring a global read lock for
the duration of the whole dump.
This option invokes
FLUSH TABLES WITH READ LOCK
during its operation, then performs
UNLOCK TABLES.
Is mutually exlusive with option
--single-transaction, which alone is
sufficient for InnoDB engine tables.
--lock-tables Obtain locks for all
tables being dumped before dumping
them.
--master-data For replication, adds
coordinates data to the front of the
dump file, like:
CHANGE MASTER TO
MASTER_LOG_FILE='binary_log.000007',
MASTER_LOG_POS=98;
such that in reload on the slave
server, that info is automatically
instated. This option automatically
turns off --lock-tables. It also
turns on --lock-all-tables, unless
--single-transaction also is
specified, in which case, a global
read lock is acquired only for a short
time at the beginning of the dump (see
--single-transaction).
--no-autocommit Write the INSERT
statements for each table within a
transaction, so that the dump can be
loaded more efficiently than executing
each statement in auto-commit mode.
--no-create-db Do not write CREATE
DATABASE statements.
--no-create-info Do not write CREATE
TABLE statements.
--no-data Omit data: just give me the
table structure stuff.
--opt To optimize the dump file to be
smaller so that it can be processed
more quickly when reloaded. In short,
compaction.
--quick Read and process a row at a
time, rather than the usual of taking
table into memory before starting the
dump of it.
--quote-names Quote table and column
names by enclosing them within accent
(`) chars (aka backtick).
--result-file=______ Write output to
the named file, rather than Stdout.
Using this option may be more
desirable than using redirection.
--single-transaction For consistent
dumps of InnoDB tables, so that all
tables are dumped in a single
transaction. It provides consistency
but does not perform any locking -
which is unnecessary because this
option performs a START TRANSACTION
which assures consistency during the
dump operation.
See also the specification
FLUSH TABLES WITH READ LOCK;
for use with non-InnoDB.
Is mutually exclusive with option
--lock-all-tables.
--tab=_____ Write two files per table,
using the specified, existing
directory as the destination
location.
--tables Override --databases.
--where='_______' Only dump records
selected by the specified WHERE
clause.
--xml Generate XML output.
Status codes:
2 Typically means no access, where the
error message will be via Stdout
(which, redirected, will be in your
output file), which would be like:
Got Error: 1045: Access denied
To do a full backup, of all databases:
mysqldump --tab=/path/to/some/dir --opt
--all
To backup one database:
mysqldump --opt <DBname>
To backup one table:
mysqldump --opt
--result-file=/tmp/TableName.mysqldump
<DBname> <TableName>
The --opt option serves to fully
optimize the operation.
This kind of backup operates within the
MySQL server, rather than directly
operating on the physical database table
files.
mysqldump, guidance for backup It is best to be formal and specific
when proceeding to make a database
backup which can be confidently used in
a later recovery/rebuild. Thus, it is
best to clearly define the output file
on the command line (rather than using
redirection) and to specify both the
database and table name. Example:
mysqldump --result-file=Db1Table1.dump
Db1 Table1
In particular, note that this form of
mysqldump records the name of the table
in the dump file, but NOT the database
name, where attempting to feed that to a
simple invocation of the 'mysql' command
would yield: ERROR 1046 (3D000) at line
22: No database selected.
mysqldump, how to restore database Before doing so:
- Assure that the database is defined.
(CREATE DATABASE <DBname>)
- There should be no current tables
under the database to be restored: if
any, do Drop Table.
To use the contents of a mysqldump file
backup of a database to restore the full
database, you employ the 'mysql'
command, specifying the database name,
like:
cat Db1Table1.dump | mysql --database=Db1
or more simply as:
cat Db1Table1.dump | mysql Db1
or
mysql Db1 < Db1Table1.dump
(Expect no Stdout or Stderr output from
the mysql command if the loading
worked okay.)
mysqldump, how to restore one table How about restoring just a single table
from the mysqldump file of the full
database? MySQL does not provide an
easy way to do that. Why not? Because
tables are typically inter-related, and
restoring one will usually result in it
being out of step with its peers.
If a MySQL implementation's backup is a
mysqldump of the full database - all its
tables - that implicitly means that they
need to be considered a cohesive whole,
where the tables should not be restored
independently. A mysqldump can specify
individual table names, readily allowing
individual table restorals.
mysqldump, how to retrieve records I haven't seen any provided means for
extracting records directly from the
mysqldump file. There may be a utility
out there somewhere for that.
What some people probably do is pare
down the file to sections they need then
feed an altered form of it to the mysql
command to generate a temporary table,
where they can then go in to perform the
query they need to extract some info,
and then delete the table.
mysqldump across servers You may need to refresh a database on a
MySQL replication slave or the like.
Traditionally, the administrator
laboriously goes to the good server,
does a mysqldump, creates a huge file,
and somehow gets that over to the
problem server, to load it there. It is
often overlooked that the MySQL commands
in general are client-server. This is to
say that the commands are not restricted
to operating on what's local.
Exploiting this functionality allows you
to *drastically* improve database
content transfer. Here's the prototype
command line for that:
mysqldump -h $master_host
--single-transaction --flush-logs
--master-data $dbname | mysql $dbname";
Thus, on the receiving system, you
invoke mysqldump to operate on a remote
system, where the client software pulls
that data from the remote server to feed
it to local command 'mysql', to restore
the database to the local store.
See also: mysqldump
mysqldump file notes Sadly, the mysqldump output file does
not contain helpful contextual
information you would expect and want,
such as when it was performed or on what
host.
mysqlhotcopy OS command to perform a hot backup of a
MySQL database, which is a physical copy
of the database, made outside the
server, but in communication with it.
It does the following:
LOCK TABLES
FLUSH TABLES
Copies the table files to the desired
location with cp or scp.
UNLOCK TABLES
Invoked like:
mysqlhotcopy database /path/to/some/dir
This approach needs to be considered in
an evaluation of backup-recovery methods
for one's databases. This method is
convenient, in leaving a MySQL server
running, but may be infeasible with
large databases, where the lock may be
in place for a considerable amount of
time.
mysqlimport OS command to import data in different
file formats into a MySQL table.
mysqlrestore No, there is no such command, for use in
restoring databases and tables.
Instead, the output of 'mysqldump' is
fed to command 'mysql' to regenerate the
database/table.
mysqlshow OS command to show info about the MySQL
server and any objects (such as
databases and tables) in that server.
Invoked without args, it displays the
list of databases.
Invoked as 'mysqlshow <DBname>', it
reports the tables in the database.
Name lengths See: Column name
NATIONAL CHAR Synonym for CHAR.
NATIONAL CHARACTER Synonym for CHAR.
NATIONAL VARCHAR Synonym for VARCHAR.
NCHAR Synonym for CHAR.
net_read_timeout Value specifying the number of seconds
to wait for data from a client TCP/IP
connection before timing out.
(Probably is the socket SO_RCVTIMEO
timeout value on a read.)
The conventional value is 30 seconds.
(This value is probably such to match
the conventional timeout for a general
UDP connection, which retries up to 5
times, every 5 seconds, thus 30 seconds
overall.)
In replication, this is how often the
slave server will re-contact the master
server, as reflected in the master
server's general log.
no-auto-rehash Deprecated in favor of skip-auto-rehash
(q.v.).
Not equal See: <> or !=
NOT NULL Column attribute, saying that the column
may not be empty.
Contrast with NULL.
NULL Column attribute, saying that the column
has not been populated. Null means no
content, and is not the same as Zero,
which would be a numeric population of
the field with that value.
In field tests via Where, code a null
test as "where Fieldx is NULL" as
opposed to "where Fieldx=NULL".
Contrast with NOT NULL.
NUMERIC Synonym for DECIMAL.
OFFSET SQL clause to skip a certain number of
records, as for example to skip the
first 100 records to return the 50 which
follow those (per LIMIT).
--old-passwords Server option to force the server to
generate short (pre-4.1) password hashes
for new passwords. This is useful for
compatibility when the server *must*
support older client programs; but avoid
if no pre-4.1 clients, as you want
better security.
open_files_limit System variable: The number of file
descriptors the server will attempt to
reserve. MySQL is run by root invoking
mysqld_safe, which does some set-up and
then runs mysqld as the username
specified via 'user' in /etc/my.cnf.
The MySQL daemon should be started by
root, which has the power to unlimit
its Unix resources and so the number of
open files, independent of any limits
specified in /etc/security/limits.conf;
so, you don't have to tinker with the
limits.conf file to have a high
open_files_limit value in effect for
mysqld running.
mysqld_safe invokes programs
my_print_defaults mysqld
my_print_defaults mysqld_safe
to harvest specifications, where it sets
variable $open_files to the value gotten
from the open_files_limit spec,
whereafter, if it got a number and ran
as root, does: 'ulimit -n $open_files';
otherwise it tries to run with the
default open files value, which is
likely 1024.
Note that you can do
'cat /proc/NNNN/limits'
on the mysqld PID to see what Unix
limits values it is running with.
Option, show 'SHOW VARIABLES like <OptionName>;'
Options, show 'SHOW VARIABLES;'
Options files As of the 4.1 level, MySQL supports
options files, for MySQL commands and
utilities to find default invocation
options appropriate to your environment.
Under Unix, the following options files
order is observed:
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options (as of
MySQL 5.1.15)
SYSCONFDIR/my.cnf Global options
$MYSQL_HOST/my.cnf Server-specific options
~/.my.cnf User-specific options
ORDER BY SQL clause to cause sorting of output.
The default order in which Select output
appears is indeterminate. You can
request sorted output (orderly/ordered
output) via the ORDER BY clause. Used in
a Select in the form:
SELECT ____ FROM ____ ORDER BY ____;
To order by multiple, string out the
fields, separated by commas, like:
ORDER BY person, birthdate
Parent Table See: Child Table
Password Most typically, the password for a MySQL
userid which has been GRANTed access to
certain database tables, such that the
userid is effectively associated with
that database and tables, with the
password affording use. Example of such
access granting:
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE
ON DepartmentDB.*
TO 'Admuser'@'128.197.%.%'
IDENTIFIED BY 'ThePassword';
The password is used in
mysql_real_connect() to establish a
session from client to server.
Network tracing shows that the password
is not sent from client to server in
ASCII.
Password, change Do like:
USE mysql;
UPDATE user SET Password=PASSWORD('newpw')
WHERE User='Whom' AND Host='Hostname';
FLUSH PRIVILEGES;
Password encryption As of MySQL 4.1.7, uses a new password
encryption system that is incompatible
with prior methods.
Password lost/unknown If the root password to the MySQL server
is no longer known, and you have
physical access to the server
directories, you can restore access by
copying the mysql/user.MYD and user.MYI
files from a server with known passwords
into that same directory on the problem
MySQL server system, with that server
down. Upon restart, it will make use of
the replacement files and you should
then be able to log in.
--password=____ Standard MySQL commands option
specifying the password to use when
connecting from client to server.
See also: --user
Password, user, establish See: IDENTIFIED BY
Password security When connecting to the MySQL server from
the client, a password is used: that
password is secured by good encryption,
not transmitted in clear text.
Prior to level 4.1, the password hash
was a mere 16 bytes in length. As of
4.1 it is 41 bytes - much harder to
crack.
See also: old-passwords
Pattern matching See: REGEXP
Performance measures General:
- Do not start the server with option
--flush, if at all possible.
Database, tables:
- Use indexing (indexed columns).
- Use Stored Procedures (MySQL 5.x).
- Use numeric keys whenever possible.
Numeric keys are unique and avoid the
spelling and married-name-change
issues which plague textual keys.
Also, numeric joins are more efficient
then character-based joins.
- Use Case statements.
Realize that some of the decisions made
by MySQL are best guesses, based upon
its current programming. To reveal how
MySQL is proceeding, emply the EXPLAIN
command, by adding that to the front of
your query, where MySQL will report the
list of indexes it would use, rather
than actually running the query.
See also: Indexes
PID file The MySQL server PID number is stored in
the file specified by the server option
pid-file, else in data/<Hostname>.pid .
The file consists of a single line,
containing the PID, with no newline at
the end.
PID file, determine Command SHOW VARIABLES like 'pid_file';
will display it.
pid-file MySQL server option to define the name
of the PID (process ID) file.
This option should be specified in the
[mysqld] section of the config file, not
the [mysqld_safe] section.
As a simple name, it is relative to the
datadir; but you can specify a full path
to have the file elsewhere.
If not specified, the PID file will be
in the datadir, as <Hostname>.pid.
If you specify an alternate location and
mysqld finds that it cannot write there
(as when you forget to make that
directory writable by user 'mysql'), the
PID file will be in the datadir.
Note: If you update the pid-file spec
before doing 'service mysql stop', the
command will look in that new location
and not see the PID file.
Command line: --pid_file=_____
--port Invocation option for either starting
the mysqld server for it to listen on a
specific TCP port, or for the mysql
client commands to connect to that port
number to reach the server.
See also: 3306
Port numbers Standard port numbers in MySQL:
3306 For mysqld. (Override with
'port=' spec in my.cnf sections
[mysqld] and [client].
prepare() DBI function to set up a query.
PRIMARY KEY Table column attribute for a Unique
Identifier. Can occur alongside a
column definition, like
TxID INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
or later in the table declaration
TxID INT UNSIGNED NOT NULL
AUTO_INCREMENT,
...
PRIMARY KEY (TxID),
Such a column will always be populated
(never null) with content that is unique
withing that column of the table. As
such, a search on it will always yield
one record; and the column is ideally
suited as the subject of an index.
A table may contain just one Primary Key
in that there may only be one index with
the name PRIMARY.
"Privileges" I ran into an ERROR 1064 when trying to
define a table column having the name
"Privileges" in MySQL 4: slightly
mutating the name allowed the column to
be defined - which indicates that the
command interpreter believes the word to
be a special keyword rather than a
column name. This, despite being unable
to find any doc on such a restriction.
Privileges, show See: SHOW GRANTS
Processes, list SHOW [FULL] PROCESSLIST.
PURGE BINARY LOGS Command for manually pruning binary
logs. (Normally, expire_logs_days is
sufficient to take care of this,
automatically; but sometimes binary logs
pile up during the day and need prompt
attention.) To prune:
Syntax:
PURGE BINARY LOGS
{ TO 'log_name' | BEFORE datetime_expr }
TO purges up to but not including the
named log.
Returns: Query OK, 0 rows affected
(It's an administrative function, so no
database rows are affected.)
Examples:
PURGE BINARY LOGS TO 'binary_log.000050';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
(Remember that the TO/BEFORE args are
text, and must be in single quotes.
See also: expire_logs_days
query_cache_size System variable: The amount of memory to
use for query result caching. Setting
this variable to zero disables the query
cache, even if query_cache_type is not
OFF. Conversely, setting this variable
to a non-zero value causes that much
memory to be allocated, even if
query_cache_type is OFF. The value
should be a multiple of 1024.
quick System variable for [mysqldump] section
of the my.cnf file: For dumping large
tables, it forces mysqldump to retrieve
rows for a table from the server a row
at a time rather than retrieving the
entire row set and buffering it in
memory before writing it out.
Quoting Database, table, column, and routine
names, if quoted, must be quoted using
identifier quoting characters. Usernames
and hostnames can be quoted using
identifier or string quoting
characters. For example:GRANT INSERT
(`mycol`) ON `test`.`t` TO
'myuser'@'localhost';
Note that you can use double quotes
explicitly, as in:
$date = "2008-09-16";
or by using qq, as in:
$date = qq{2008-09-16};
where qq is handy to allow you to code
quotes inside the braces without having
to think about escape coding. (In the
qq, the delimiter chars can be anything
that is not in the string.)
RDBMS Relational Database Management System
See also: Relational
read_buffer_size System variable: The size of the buffer
used by threads that perform sequential
table scans. A buffer is allocated as
necessary per client.
read_rnd_buffer_size System variable: The size of the buffer
used for reading rows in order after a
sort. A buffer is allocated as
necessary per client.
REAL Synonym for DOUBLE.
Record Generic database term for the unit of
storage consisting of a set of related
fields including a keyed field.
SQL equivalent: Row.
Record, create See: INSERT INTO
Records count in database tables SHOW TABLE STATUS FROM <DBname>;
REGEXP In MySQL you can employ (extended)
regular expressions in pattern matching:
. Matches any single character.
[] Enclose a set of characters to match
any character in the set.
[a-z] matches any lower case letter
[0-9] matches any digit.
* Matches zero or more instances of
the thing before it.
^ Located at the head of the
expression causes the comparison to
be anchored to the front of the
string being tested.
$ Located at the end of the
expression causes the comparison to
be anchored to the tail of the
string being tested.
Example:
Select ... <Some expression>
REGEXP '.*( pie| cake).*'
matches on either of those two dessert
favorites.
Use NOT REGEXP for negation in pattern
matching.
RLIKE is a synonym for REGEXP.
See also: LIKE; STRCMP
Relational Means that groups of information are
stored in different tables which can
then be "joined" together (related)
based on the data they have in common,
such as key fields.
Relay Log In Replication, the slave server has to
communicate with the master server to
get a bunch of entries from the master's
binary log, representing work items that
the slave server needs to do on its copy
of the database. Those copied entries
need to reside somewhere on the slave
server, and that place is the Relay Log.
Relay Log corruption, handling Should the replication slave server's
Relay Log somehow be corrupted, its
processing will stop. The Relay Log can
be inspected for the issue via the
'mysqlbinlog' command.
The problem can be fixed by writing down
the position in the master server's
binary log corresponding to the problem,
then shut down the slave server, delete
the Relay Log(s), start the slave
server, and tell it to begin replication
at the position you recorded.
relay�log Replication: Slave server option
defining the name of the Relay Log file.
Where: In the data directory, by default.
Specify as
relay-log
or
relay-log = _______________
If you give the option without a
filename, the server generates relay log
files in numbered sequence, using
HOSTNAME-relay-bin as the basename:
HOSTNAME-relay-bin.000001,
HOSTNAME-relay-bin.000002, and so
forth.
Verify: Does not show up in system
variables. SHOW SLAVE STATUS may reveal
it, if START SLAVE has been done.
relay�log-index Replication: Slave server option
defining the name of the Relay Log index
file.
Where: In the data directory, by default.
Example: relay-log-index = relay-log.index
Typically, this is not specified, such
that the name defaults to the basename
of the relay log files, with an
extension of ".index". But, for
explicitness, you may want to specify
this option.
Does not show up in system variables.
relay�log.index Replication: Name of the ASCII file in
the slave server MySQL directory
containing the name of the current Relay
Log.
Sample contents:
./relay-log.000061
(has a newline at the end).
relay�log-info-file Replication: Slave server option
defining the name of the Relay Log info
file (see relay-log.info).
Where: In the data directory, by default.
Example: relay-log-info-file = relay-log.info
Does not show up in system variables.
relay�log.info Replication: ASCII file in the slave
server MySQL directory containing:
- Group master coordinates: Master log
name and master log position.
(This info is intentionally redundant
to the read coordinates in the
master.info file, for corroboration.)
- Group relay log coordinates: Relay log
name and relay log position.
Sample contents:
./relay-log.000061
145532
binary_log.000135
145180
4
5
Note that this is the descriptive and
default name of the file: its name is
assigned via he slave server option
relay-log-info-file = ________
See also: master.info; relay-log
RELOAD privileges Mainly used when a new user is created
and you want to apply the changes
without re starting MySQL.
Syntax to use:
mysql -u <Username> -p<Password>
-e "flush privileges";
RENAME DATABASE This was briefly available in MySQL 5.1
but was quickly removed as being too
dangerous.
REPLACE INTO Same form as INSERT INTO, but will update
an existing record, else will add a
record with the given values if there is
no record existing with the specified
key. This is to say that a Replace
should always include a key column.
Form:
REPLACE INTO <TableName>
SET <ColumnName> = <Value>,
<ColumnName> = <Value>;
Example:
REPLACE INTO Db1.Table1
SET LastName = 'Smith',
FirstName = 'Larraby';
Note that the affected rows count will
be 2 rather than 1 where the action was
actually replacing a pre-existing entry.
Replicate_Do_DB Element of 'show slave status\G'.
See: Replication: which database, table?
replicate-do-db Configuration spec to name a database to
participate in replication.
Example: replicate-do-db=DB1
To specify multiple databases, code this
specification twice, like:
replicate-do-db=DB1
replicate-do-db=DB2
DO NOT STRING OUT MULTIPLE DB NAMES ON
THE SAME LINE!!! THIS IS WRONG:
replicate-do-db=DB1,DB2
because everything after the equal sign
is being taken as the singular database
name and "DB1,DB2" is not a database
name. (This stuff is because database
names may contain commas!)
If you do not specify replicate-do-db to
limit replication, all databases are
replicated.
BUT: This spec leads you to believe
that will straightforwardly cause
database replication to occur - but
that's not fully the case: if you simply
use this spec, you likely will silently
lose a lot of data in replication. The
replicate-do-db spec causes full
replication *only* if the SQL programmer
does a USE <DBname> ahead of a table
update. If, instead of a USE, the
programmer performs updating via form
DB.Table, replication will not happen
because the update operation constitutes
a Cross-database Update. So, the safe
thing to do is use the spec form:
replicate-wild-do-table=DBname.%
See: replicate-wild-do-table;
Replication: which database, table?
replicate-do-table Configuration spec to name a table to
participate in replication.
replicate-wild-do-table Configuration spec to specify the names
of tables, by wildcard pattern, which
are to participate in replication. For
example, to have all tables in database
Parts participate in replication,
code: replicate-wild-do-table Parts.%
See notes in: replicate-do-db
Replication MySQL provides the facility called
Replication, which allows database
changes on a "master server" to be
replicated to a logically mirrored copy
of the database on a "slave server".
The slave server is told the network
address of the master server and the
database(s) to be replicated, whereupon
the slave server contacts the master
server to obtain any new database change
entries present in the master server's
Binary Log, whereupon the slave server
copies those to its Relay Log, to
serially apply them to the database.
Note that there is no need to perform
FLUSH TABLES in replication.
Replication can be by database
(replicate-do-db spec) or by table
(replicate-do-table,
replicate-wild-do-table).
See also: net_read_timeout
Replication: fix If the replication slave gets hoplessly
out of sync with the master, you will
need to refresh the database on the
slave. To efficiently do that, see:
mysqldump across servers.
Thereafter, you can get replication
going again via
SHOW SLAVE STATUS
START SLAVE
See also: 1236 error
Replication: fix duplicates problem See: sql_slave_skip_counter
Replication: which database, table? Instructions for setting up replication
are often incomplete, and leave you
wondering what controls the databases
and tables which are to participate in
replication. The control is via the
following mysqld parameters in
/etc/mysql/my.cnf:
------- On the replication master -------
To include only a specific database:
binlog-do-db=db_name
which will show up in SHOW MASTER STATUS
as Binlog_Do_DB.
To have all but one database participate:
binlog-ignore-db=db_name
which will show up in SHOW SLAVE STATUS
as Replicate_Ignore_DB
But: Be very aware if you intend to
employ the above options, as they result
in binary logging for *only* the named
databases or tables. While this nicely
limits the amount of data in the binary
log so as to facilitate replication, it
is undesirable where you want binary
logging to occur for all databases on
the server. It is probably better to
control replication via the following
slave options.
------- On the replication slave -------
To include only a specific database:
replicate-do-db=db_name
which will show up in SHOW SLAVE STATUS
as Replicate_Do_DB.
IMPORTANT!! If you omit USE as a
preliminary to database updating, or
employ a USE specifying a database
different than in the DBname.Tablename
spec in your table update action,
replication will *not* occur!! This is
because replication is based upon the
Default Table (via USE). The absence of
a USE or different-db spec in a USE
coupled with a DBname.Tablename spec in
your update operation constitutes a
Cross-database Update. The best way to
set up replication is to omit this spec
and use replicate-wild-do-table, like:
replicate-wild-do-table=Parts.%
to have all tables in the Parts database
participate in replication, whether or
not a USE is in play.
To include only a specific table:
replicate-do-table=db_name.tbl_name
To have all but one database participate:
replicate-ignore-db=db_name
To have all but one table participate:
replicate-ignore-table=db_name.tbl_name
The slave checks for and evaluates table
options only if no matching database
options were found.
Settings that are in effect can be
reported via SHOW SLAVE STATUS\G on the
slave server.
Replication, start Via command: START SLAVE;
Replication, suspend Via command: STOP SLAVE;
This will keep the slave server from
contacting the master server for any
further available updates.
Later resume with: STARTSLAVE;
REPLICATION CLIENT privilege Allows replication processes in a peer
system to inquire about master and slave
positions. When a replication slave is
active, you will see such activity in
the General Log.
See also: REPLICATION SLAVE
Replication considerations The nature of the processing in the
replication master vs. slave is very
different. The master will be handling
multiple SQL operations concurrently.
The slave's processing, in contrast, is
strictly driven by the order of things
in the binary log, which it operates
upon serially. The difference can cause
the slave to fall behind the master.
Further note that transactions occur in
the binary log in the order that they
commit, not when they begin. Thus, work
done on the slave is not necessarily in
the same order that it was on the
master.
Replication health Is basically checked by doing
SHOW SLAVE STATUS\G
where Slave_IO_Running and
Slave_SQL_Running should both be Yes, as
a fundamental measure of health.
The Seconds_Behind_Master value is a
reasonable but somewhat vague measure of
replication health.
More thoroughly, the Read_Master_Log_Pos
should be equal to or close to what SHOW
MASTER STATUS reports on the peer system.
Replication interval How frequently does the slave seek
updates from the master? That info is
not published, and SHOW SLAVE STATUS
does not report it.
In an unused master-slave set, I see the
slave looking every 30 seconds.
Replication management Commands you can use:
On the slave server:
SHOW SLAVE STATUS
will report many details about the
slave's status.
On the master server:
SHOW MASTER STATUS
reports the few elements about the
master's role in replication (which is
minimal, as the slave server does the
substantive work).
REPLICATION SLAVE privilege Allows a replication slave to perform
replication to the peer master: the
slave can connect to the master server
and request slave server updates.
See also: REPLICATION CLIENT
Report formatting MySQL is designed primarily as a server
facility, not a reporting tool, and
provides only rudimentary report
formatting. There is no inherent way to
cause columns to wrap, as when you are
employing a "SELECT ... AS" construct to
assign a multi-word column name alias
and want the multiple words to stack
vertically instead of splay out
horizontally.
RESET MASTER Replication: Deletes all binary log
files listed in the binary log index
file, resets the binary log index file
to be empty, and creates a new binary
log file.
Replication slaves should not be running
when this command is executed.
In detail:
- Removes all binary log files that are
listed in the index file, leaving only
a single, empty binary log file with a
numeric suffix of .000001 .
(This operation differs from the
PURGE BINARY LOGS command, which
removed the logs but does not reset
numbering.)
This statement is intended to be used
only when the master is started for the
first time, or in a situation where
replication has failed and you have to
physically transfer the whole database
to the slave again, in a downtime
procedure.
See also: RESET SLAVE
RESET SLAVE Replication: Command to be run on the
slave server to reset (clear) the info
in the master.info file.
Makes the slave forget its replication
position in the master's binary log.
Effects:
- Deletes master.info file
- Deletes relay-log.info file
- Deletes all Relay Log files
- Starts a new Relay Log file
Before doing this, the slave server's
replication threads need to be
terminated, which can be done with
command STOP SLAVE.
THIS IS DRASTIC, and is meant to be used
only for a clean start. This command
can obliterates your definitions for
these resources, so you had better have
a copy of your original definitions,
ready for a CHANGE MASTER reinvocation.
Indeed, a test showed the MASTER_USER
changed from its original value to
"test", with the slave then unable to
communicate with the master. MySQL 5.4
corrects this bad behavior, such that
original definitions are retained.
See also: CHANGE MASTER TO; RESET MASTER
RESTORE TABLE Companion to the deprecated BACKUP TABLE
statement. Should not be used.
REVOKE Opposite: GRANT
Right justify column output There is no fundamental way to do
this... SQL has no RJUST() function.
This can be done with a function which
pads on the left with blanks:
LPAD(String, Len, ' ')
Note, though, that LPAD returns a string
value, which can thwart Order By column
sorting which needs to be numeric,
particularly in the display of negative
numbers, as the LPAD changes the
inherent numeric type to string, causing
short strings to appear first in
ascending column display, rather than
negative values first.
RLIKE Synonym for REGEXP (q.v.).
ROLLBACK Statement to undo a transaction.
Syntax:
ROLLBACK [WORK] [AND [NO] CHAIN]
[[NO] RELEASE]
See also: BEGIN; COMMIT;
START TRANSACTION
root user MySQL's root user is, like in Unix, the
principal admin, who can create other
MySQL users and grant them privs.
(MySQL has its own usernames name space,
where 'root' is its own root,
independent of the Unix root user.)
Root user access capabilty is
established as part of the install
process, to populate the "mysql"
database grant tables (mysql.user), in a
step which the install doc calls
"Securing the Initial MySQL Accounts".
The grant tables define the initial
MySQL user accounts and their access
privileges.
On Unix, the grant tables are populated
by the mysql_install_db program, which
will establish the root user under three
Host identities:
- "localhost"
- "127.0.0.1"
- <Hostname> (e.g. "system3")
IMPORTANT: The root user is established
in the "user" table with a null
password. This is relatively safe, in
that MySQL's initial definitions for
root limit access to root on the local
system, which by practice should be
inherely secure; but it is best to
establish a password for root.
root user, establish password 'mysqladmin -u root
password '______' '
root user, log on 'mysql -u root -p'
You will be prompted for the password.
Consider establishing a .my.cnf file in
order to avoid having to remember and
enter the password.
ROUND() ROUND(Number[,Decimal])
Round a number to the given number of
decimals. If Decimal is not supplied,
Number is rounded to an integer.
See also: FORMAT()
Row Essentially, a database record.
In a relational database, rows are
typically stored in the order in which
they are introduced to the database.
Retrieving some random value from a
database record requires a sequential
scan of the database: use indexing to
quickly get at frequently sought data.
See also: Index
Row, add one See: INSERT INTO
Row, define That is actually table definition.
See: Table, define
Row, delete See: DELETE
Row, populate one See: INSERT INTO
Row, update one See: UPDATE
Rows, number in table See: SHOW TABLE STATUS
Rows, number of (API pgming) To determine the number of rows in the
result set, issue the following call:
mysql_num_rows(result_ptr);
where result_ptr is from
mysql_store_result(mysql_struct_ptr)
MYSQL_RES *result_ptr;
MYSQL *mysql_struct_ptr;
Rows, populate multiple INSERT INTO <TableName> [(ColumnList)]
VALUES (...,...);
rpm query for package info Like: rpm -qi mysql-4.1.22-2.el4
RTRIM() To remove trailing spaces from a string.
RTRIM(String)
The results will probably not be what
you expect: I have found that rather
than trimming trailing blanks from each
individual line, this function trims all
lines to match the longest text of any
line in the table, thus leaving a lot of
blanks at the end of most lines.
See also: LTRIM; TRIM
Scalar subquery Is a subquery that returns a single
value of a basic data type - meaning
that the result of the subquery contains
no rows or columns. This can also be
thought of as one column from one
row. Scalar subqueries may be used in
the field list of a SELECT statement,
for comparison in a WHERE statement, or
in the VALUES list of an INSERT
statement. Scalar subqueries are the
kind that are most easily replaced by a
join, whereas some of the other subquery
types cannot be replicated with joins.
Available in MySQL 4.1.
Scan table for problems See: CHECK TABLE
Schema The name for the structure of the
database.
Schema, change To change elements of the schema, us the
ALTER TABLE command (not UPDATE TABLE,
which is for operating on data stored in
the table).
Security MySQL 5.0 enhanced security. In previous
releases, a hacker or rogue system admin
could gain control of your MySQL
database by using a text editor to view
the file that stored the user accounts
and manually change information; he
could also access the table itself to
alter data or create and delete tables.
But now, log-in accounts are stored in
the database account.
See also: Compression; Encryption
SELECT SQL command to retrieve rows from one or
more tables.
Locking: A Select implicialy results in
a Read Lock. This blocks any new,
incoming updating operations, but not
other, consecutive Select operations.
(But: Because updating operations are
given higher priority than retrieval
operations, a Select arriving after an
update which is blocked by a Select,
will be blocked from running
conncurrently with the current Select
because the update operation is in line
before it.)
Actually, the command is much more
generalized: you can invoke it simply
with a character string, which will
cause that string to be returned as a
single row and column. Such capability
seems pointless, except that it's a
convenient way to have arbitrary info
appear in the Server Log (q.v.).
To find null column values, use
"WHERE <ColumnName> IS NULL"
(not "WHERE <ColumnName>=NULL").
To find non-null column values, use
"WHERE <ColumnName> IS NOT NULL".
See also: <>; STRAIGHT_JOIN
SELECT MAX() A construct you will find used where the
objective is to return the most recent
record, as would be indexed by a
transaction record identifier, for
example. Thus:
SELECT MAX(TxID)
would return the highest transaction
number identifier, which could be used
in an enveloping Select to go after just
that record.
Server, shut down 'mysqladmin -u root -p shutdown'
Requests the MySQL root user password.
Note that mysqld (the server daemon) in
Unix responds to a SIGTERM by cleanly
terminating.
Consider adding a MySQL shutdown to the
Unix /etc/rc.shutdown file.
Server, start In Linux, the standard method is to be
root and then do:
service mysqld start
or, in later MySQLs:
service mysql start
This will invoke mysqld_safe, which will
delve into /etc/my.cnf for key values,
and start the mysqld process as the user
defined on "user=___" in /etc/my.cnf's
[mysqld_safe] or [mysqld] section.
This method precludes starting the
daemon with any server environment
variables, because the 'service' command
does 'env -i ...' to exclude any
prevailing environment variables as it
establishes its own, clean set.
Like: su nobody "-c mysqld_safe
--user=mysql &"
See: mysqld
Server daemon Is 'mysqld'.
See: mysqld
Server Log The general, non-error log for the MySQL
server, aka General Log. Logs all
database accesses.
Specified in the server options file as
general-log-file = __________
(as of 5.1.12); and you need to turn it
on as well, via:
general-log = ON
Internal timestamps are of the form
031028 11:09:18
where the first token is of form YYMMDD.
Each aspect of a session is logged on
its own line, identified by one of the
following:
Connect MySQL_username@OriginatingHost
on <DB_name>
Query (The issued query, exactly as
gotten from the client, with
upper/lower casing preserved.)
Quit (End of session.)
To cut off the log: The log grows
endlessly unless you cut it off
periodically. To do so: Rename the log
file (as to having a .YYYYMMDD suffix)
and then do FLUSH LOGS, which causes the
server to open the original name afresh
(new instance). You can then gzip and
archive the renamed log.
Tips: In many applications, the MySQL
username is a generalized access name,
and certainly does not identify the
individual who invoked the application;
but the app knows who it was, and can
easily have that appear in the log after
the Connect by doing a Select with a
simple character string, like:
SELECT "Invoked by user: Fred";
Given the nature of what's logged, the
log can grow rapidly.
Is not really created by the server, but
by the mysqld_safe script which is
conventionally used to start the mysqld
server daemon. mysqld_safe redirects the
server's Stdout and Stderr to the
designated file.
Default log name: data/<Hostname>.err
(The log conventionally resides in the
"data" directory of the given MySQL
instance.)
Most log entries will start with a
"YYMMDD hh:mm:ss" timestamp, like:
"030826 15:58:12".
See also: Error log; FLUSH LOGS
Server Log cut-off An ideal time to cut off the Server Log
is just before the server is started.
If it has to be cut off during
operation, be aware that the server
keeps the log open continually, so to
cut it off you should do:
- 'mv <LogName> <LogNewname>'
- 'mysqladmin flush-logs'
which will cause the server to close
the file and open a new one (with the
name it was told to use)
Server options, show 'SHOW VARIABLES;'
Server PID In file: data/<Hostname>.pid
See also: PID file
Server process A 'ps -efl | grep mysqld' will show the
server process to look like:
/var/db/MySQL/mysql/bin/mysqld
--basedir=/var/db/MySQL/mysql
--datadir=/var/db/MySQL/mysql/data
--pid-file=/var/db/MySQL/mysql/data/
<Hostname>.pid
server-id The mysqld server identity number, as
used in replication, to uniquely
identify the server within a collection
of MySQL servers doing replication.
(This does not make the MySQL server
part of a replication set, but is an
ingredient for that.)
Code as a number between 1 and 2^32 - 1.
(Leading zeroes are fine.)
Default is 0 if omitted, where 0 means
not participating in replication: a
master refuses connections from all
slaves, and a slave refuses to connect
to a master.
Verify: Command 'mysqladmin variables'
reports it under "server_id".
or: SHOW VARIABLES LIKE 'server_id';
server-id, query You can inspect the /etc/my.cnf file for
the definition to be adopted by mysqld
when it starts.
To see what it's currently using:
SHOW VARIABLES LIKE 'SERVER_ID'
--service-startup-timeout=____ Arg to /etc/init.d/mysql
to specify value, in seconds, after
which the script should timeout waiting
for server start.
The value here is overriden by value in
my.cnf.
0 means don't wait at all;
Negative numbers mean to wait
indefinitely.
Default: 900 seconds
Sessions, prevent? MySQL provides no command for preventing
sessions from occurring. Some systems
such as TSM provide a 'disable sessions'
command to do that, but not MySQL.
The only means for throttling sessions:
- Lock all tables.
- Restart mysqld with --skip-networking
or my.cnf having "skip_networking ON".
SET MySQL data type: a complex string column
type which may hold any combination of a
set of possible values. Each defined
string resolves to a unique,
non-conflicting binary position (0x001,
0x010, 0x100, etc.) The limit is 64
possible values. SET is much like an
ENUM, but instead of being stored as
small numbers, the set is stored as a
complex bitmap, which is to say that the
first value is stored as binary 0001,
the second as 0010, the third as 0100,
fourth as 1000, etc. SET allows the
column to contain any number of values,
rather than being limited to one with
ENUM.
SET is a good data type for a column
which is to hold "flags".
Example:
INSERT INTO
Automobile_ordering_options
SET('Sun roof', 'Trip computer',
'Heated seats');
where the first item, 'Sun roof',
resolves to 0x001, 'Trip computer' to
0x010, 'Heated seats' to 0x100.
Contrast with: ENUM
See: INSERT INTO
SET autocommit Statement for transactions, to disable
or enable the default autocommit mode
for the current session.
See also: BEGIN; COMMIT; ROLLBACK;
START TRANSACTION
SHOW ... SQL statement to provide info about
various parts of the SQL environment.
SHOW BINARY LOGS SQL statement to report the binary log
files which are in use. Example:
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| binary_log.000004 | 117 |
| binary_log.000005 | 117 |
| binary_log.000006 | 98 |
+-------------------+-----------+
SHOW COLUMNS SQL statement to list the columns for a
given table. Alias: SHOW FIELDS.
Usage: SHOW COLUMNS FROM <TableName>;
Example: SHOW COLUMNS FROM Db1.Tbl1;
See also: SHOW FULL COLUMNS
SHOW CREATE DATABASE SQL statement to display the CREATE
DATABASE statement corresponding to the
structure of the named database, which
is a way of generating the statement to
recreate the database.
SHOW CREATE TABLE SQL statement to display the CREATE
TABLE statement corresponding to the
structure of the named table, which
is a way of generating the statement to
recreate the table.
SHOW DATABASES SQL statement to list the databases
available on the server host.
The results you get back depend upon
what granted privileges you have. If
you are issuing the Show from a username
associated with a database grant, the
command will reveal only that database.
If you issue the command from the MySQL
root user, you will see all databases.
SHOW ENGINE INNODB STATUS Command form for displaying info about
the internal operation of the InnoDB
storage engine.
The output is voluminous.
Replaces SHOW INNODB STATUS, which is
deprecated.
Use STATUS instead.
SHOW ENGINES See: Storage engine
SHOW FULL COLUMNS Variant of SHOW COLUMNS to additionally
show the COMMENT value (if at MySQL 4.1
or later). Syntax:
SHOW FULL COLUMNS
FROM <DatabaseName.><TableName>;
See also: DESCRIBE
SHOW GRANTS Displays the privileges held by existing
accounts.
To see your own privs:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
To see someone else's:
SHOW GRANTS FOR 'someuser'@'localhost';
Example:
show grants for root@localhost;
However: experience is that this command
does *not* show all grants. Perform
select * from mysql.user;
to fully inspect users and privs.
To show all users for a specific
capability, do like:
select Host, User, Repl_client_priv
from mysql.user;
You can generate SHOW GRANTS commands
for all users via:
SELECT CONCAT('SHOW GRANTS FOR \'',
user,'\'@\'', host, '\';') AS mygrants
FROM mysql.user ORDER BY mygrants;
See also: GRANT
SHOW INNODB STATUS Old command form for displaying info
about the internal operation of the
InnoDB storage engine.
Use SHOW ENGINE INNODB STATUS instead.
SHOW MASTER LOGS MySQL statement to report the binary
logs files on the slave server which are
being used for replication.
Same as SHOW BINARY LOGS.
SHOW MASTER STATUS Replication command to be invoked on the
master server, to show its replication
details of the moment.
What it reports is information about the
binary log position in the local MySQL
server.
There isn't much to report:
File Binary log file name
on the local system.
Position The current position
in that file, being
the entry number, as
in 206 for the 206th
entry in the binary
log file.
Binlog_Do_DB Comma-separated list
of databases that are
explicitly replicated.
Binlog_Ignore_DB Comma-separated list
of databases that are
explicitly excluded.
Note that the Position value should
equal the MASTER_LOG_POS value in the
slave server.
See description of invocation options
--binlog-do-db and --binlog-ignore-db.
See also: SHOW SLAVE STATUS
SHOW PROCESSLIST Displays information about the currently
executing server activity. If you have
the PROCESS privilege, the statement
displays all information. Otherwise, it
displays information only about your own
activity. Syntax:
SHOW [FULL] PROCESSLIST
Replication: This command is very
helpful to reveal what the master server
or slave server are actually doing.
See also: KILL
SHOW SLAVE STATUS Replication command to be invoked on the
slave server, to show its replication
"coordinates". The command has no
further elements.
Privileges required:
SUPER or REPLICATION CLIENT
Is best entered as "SHOW SLAVE STATUS\G"
to yield a readable keyword:value report
rather than way-too-wide table.
The report contains some two dozen
elements describing the slave's state
relative to the master.
The last row tells you how many seconds
it is behind the master.
You need to have performed CHANGE MASTER
when MySQL was configured on this
system, to define that there is a master
host, and that replication is to be
done, for this command to produce
results, and not "Empty set".
Regarding some report elements:
Master_Log_File:
Should be the same binary log file
name as reported in the peer server's
SHOW MASTER STATUS output "File".
Read_Master_Log_Pos:
Should be the same binary log file
position as reported in the peer
server's SHOW MASTER STATUS output
"Position".
What to particularly look for:
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Seconds_Behind_Master: 0
See also: CHANGE MASTER TO;
SHOW MASTER STATUS
SHOW STATUS Command to report server status info,
which mostly shows various counts.
Syntax:
SHOW [GLOBAL | SESSION] STATUS
[LIKE 'pattern' | WHERE expr]
SHOW TABLE STATUS Command to report statistics on the
tables comprising a database.
Syntax:
SHOW TABLE STATUS [FROM db_name]
[LIKE 'pattern']
BUT!!! Experience shows that the record
count results are unreliable for engine
type InnoDB: doing the same command
repeatedly on static tables will yield
differing results. Better to do
'select count(*) from DBname.TBLname'
to see a true count with InnoDB.
The column elements in the resulting
report:
Name [of table]
Engine E.g., "InnoDB"
Version E.g., 10
Row_format E.g., Compact
Rows Number of rows
Avg_row_length
Data_length
Max_data_length
Index_length
Data_free
Auto_increment
Create_time E.g., 2009-11-23 07:09:36
Update_time
Check_time
Collation E.g., latin1_swedish_ci
Checksum
Create_options
Comment
SHOW TABLES Command to list the tables that are in a
given database. Syntax:
SHOW TABLES [FROM db_name] [LIKE 'pattern']
Shut down See: Server, shut down
Signals, to mysqld SIGHUP (1): Causes the server to close
and re-open its logs (= FLUSH LOGS),
and reloads the privileges from the
grant tables in the mysql database.
Cmd: 'mysqladmin flush-logs'
Note that SIGHUP is not intended to be
used to cause mysqld to reread its
configuration file, and will not do so:
server config file changes often
require that the server operate in a
different structural way, and so making
config file changes on the fly is a Bad
Idea.
SIGKILL (9): Cause the server to
restart, oddly enough. Gets a new
process ID (PID). A restart retains the
original invocation options.
SIGTERM (15): Terminat the server -
cause it to shut down (shutdown).
(This is the default signal from the
'kill' command.)
--single-transaction A mysqldump invocation option for data
consistency during a data backup
operation on databases implemented with
InnoDB type tables. Guarantees that
data seen by mysqldump does not
change during the mysqldump duration.
(Changes made by other clients to InnoDB
tables are not seen by the mysqldump
process.)
skip-auto-rehash System variable for the [mysql] section
of my.cnf to specify that name hashing
not be provided as a convenience
service, to eliminate that overhead in
'mysql' launching.
skip_external_locking System variable to specify that external
locking (filesystem locking) is not to
be.
See: External locking
--skip-grant-tables Server start-up invocation option to
cause it to ignore the grant tables for
verifying connections. To be done only
in special circumstances, as in
resetting root's password. Should be
accompanied by --skip-networking to
disallow other access.
Note that when in this mode you cannot
perform Grant operations, as you might
want to fix root's privileges.
Note: Does not work to put
skip-grant-tables into /etc/my.cnf .
skip_name_resolve System variable: Do not perform DNS
lookups on host names when checking
client connections: use only IP numbers;
and all Host column values in the grant
tables must be IP addresses or
localhost.
skip-networking mysqld option for the server to not
operate on the network: no TCP port
listening. This is applicable where
server and client operations are
confined to the single host, as in a Web
server application using MySQL as its
database. Client-server interaction
occurs through a Unix socket file.
See also: bind-address; socket
Slave, start See: START SLAVE
slave-skip-errors, --slave-skip-errors Replication will stop when an error
occurs on the slave. To cause the
mysqld to disregard certain errors, this
option can be used in the /etc/my.cnf
file or on the start-up command line to
continue replication when a statement
returns any of the errors listed in the
option value.
Example:
--slave-skip-errors=1062
to skip "Duplicate entry" errors.
You can also add this to your
/etc/my.cnf file as in:
slave-skip-errors=1062
Dynamically, can use command:
SET GLOBAL sql_slave_skip_counter = N
Obviously, it is best to ascertain why
errors are occurring rather than simply
trying to obviate them.
See also: sql_slave_skip_counter
skip_slave_start Replication: Slave server option to
specify that the replication threads not
be started automatically.
You will, at the right time, be
performing START SLAVE.
This option is mostly used when
replication is being re-established on
the slave server.
Command line form: --skip-slave-start
slave_net_timeout Replication: Server option defining the
number of seconds to wait for data from
a master server before timing out.
Pertains to TCP/IP connections.
Default: 3600 (seconds)
SMALLINT [(display_size)] [UNSIGNED] MySQL data type: a basic whole number
with a range of -32,768 to 32,767 if
signed, 0 to 65,535 unsigned.
Storage size: 2 bytes
See also: INT; TINYINT
socket MySQL daemon specification for the
location of the Unix domain socket file
to be used by local host sessions for
clients ('mysql' command, C API) to
connect to the mysqld.
The default file is /tmp/mysql.sock
but, obviously, /tmp/ is a volatile
place for it, so best to specify a solid
location, such as the datadir - but
assure that directory permissions allow
access to that location by those who
need to.
Verify: Command 'mysqladmin variables'
reports it under "socket".
Socket communication The C client attempts to connect to the
server via socket file /tmp/mysql.sock
(default file location).
See also: localhost
Socket file The Unix special file through which the
'mysql' command and similar client
facilities communicate with the MySQL
server daemon.
The file's attributes are:
srwxrwxrwx 1 nobody nobody
The default path of the socket file is
/tmp/mysql.sock, but may be defined
otherwise via "socket = _____" in
/etc/my.conf (which should be in both
the [mysqld] and [client] sections.)
Socket file, determine Command SHOW VARIABLES like 'socket'
will display it.
sort_buffer_size System variable: The size of the buffer
used by threads for performing sort
operations (GROUP BY or ORDER BY). This
buffer is allocated as necessary per
client.
Sorted output See: ORDER BY
SQL commands These are verbs like Select, Update,
Insert, etc.
MySQL requires that each command be
terminated by a semicolon (;). This
allows multiple commands to appear on
one like, as in:
Select ...; Select ...;
SQL thread In replication, the software by which
the slave server writes table changes
which it pulled from the master server
via the I/O thread.
See also: I/O thread; START SLAVE
sql_slave_skip_counter Global variable for replication.
The skip counter value causes the slave
to skip that many entries in order to
skip invalid queries causing replication
to stop, via procedure:
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS\G
where the value 1 is conservative.
The value shows up in Skip_Counter
from 'SHOW SLAVE STATUS\G', which slave
replication decrements as it skips
records, until the Skip_Counter value is
0 (once again).
This can be used to to get by the
relatively innocuous problem of
duplicate records.
There is also: --slave-skip-errors
SSL in use? This shows up in a 'mysql' "status"
subcommand report like:
SSL: Not in use
START SLAVE Command to be invoked on a replication
slave server to start/resume replication
- to tell the slave server to start its
ongoing process to periodically ask the
master server for any new entries in the
master's binary log, for the slave to
put into its relay log for processing
into the replication database.
Results in a "Connect Out" entry in the
MySQL general log.
The side which communicates with the
master server is called the I/O thread,
and the side which then processes the
contents of the relay log is called the
SQL thread. These may be controlled
individually, in:
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;
Note that these are manual control
commands, to be invoked if needed. The
START SLAVE command is not normally
needed to initiate replication on a
slave server which has all needed
definitions for replication: that
happens automatically. The exeception
is if you choose to start the slave
server with --skip-slave-start, when you
want to do some work before later
invoking START SLAVE to get things
moving.
This command has no provisions for
identifying or limiting the databases to
participate in replication: use the
replicate-do-db slave server option for
that.
See also: STOP SLAVE; SHOW MASTER
STATUS; SHOW SLAVE STATUS
START TRANSACTION Statement to begin a transaction, as of
MySQL 5.
Syntax:
START TRANSACTION
[WITH CONSISTENT SNAPSHOT]
In MySQL 4, there had been:
BEGIN [WORK]
See also: COMMIT; ROLLBACK
STOP SLAVE Command to be invoked on a replication
slave server to stop (suspend) replication.
The side which communicates with the
master server is called the I/O thread,
and the side which then processes the
contents of the relay log is called the
SQL thread. These may be controlled
individually, in:
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
See also: START SLAVE; SHOW MASTER
STATUS; SHOW SLAVE STATUS
Storage engine Terminology found in some documentation,
referring to the physical files and
software which MySQL makes use of in
implementing tables. When you define a
table, you can define a TYPE parameter
which specifies your choice of sortware
technology for implementing the table.
The "storage engines" are effectively
plug-ins which MySQL uses as needed.
The storage engine type is defined in
the CREATE TABLE statement via ENGINE=.
To see what storage engines your MySQL
supports, do 'SHOW ENGINES;'.
See also: default-storage-engine
storage_engine System variable, as set via
configuration file specification like:
default-storage-engine = InnoDB
MySQL leaves the value as "MyISAM"
unless set otherwise.
Stored procedures Is SQL code that is compiled and stored
in the server database, allowing later
execution by the server without having
to be sent from the client and parsed
again, thus being a performace booster.
Supported in MySQL 5.0 - but in a
non-standard way: not pre-compiled; they
are just stored in the database, making
them closer to named queries in Excel
than traditional stored procedures.
Because of this, performance benefits
are limited to reduced network traffic.
STRAIGHT_JOIN Used in a Select to force the optimizer
to use a join order corresponding to the
order in which the tables are named in
the SELECT statement. So, begin the
statement with SELECT STRAIGHT_JOIN ...
STRCMP() String comparison function:
STRCMP(expr1, expr2);
Returns 0 if the strings are the same,
-1 if the first is smaller than the
second, and 1 otherwise.
Subselect The name for the technique of using one
Select under another, in order to obtain
a subset of values for the outer Select
to operate with.
Here is an example from TSM:
Select Count(VOLUME_NAME) from
VOLHISTORY where TYPE='BACKUPSET' and
VOLUME_NAME not in (Select VOLUME_NAME
from LIBVOLUMES)
SUM() SQL function to add the contents of a
certain field in all selected records so
as to yield a sum.
Example:
Select Name, SUM(QuantityOnHand) from
Inventory where ...
Contrast with: COUNT()
Sync I think you mean FLUSH (q.v.).
System Variable, show SHOW VARIABLES LIKE '__Var_name__';
System Variables Command line invocation options for the
mysqld command and /etc/my.cnf
configuration file options resolve to
server variables having underscores
linking their word elements.
To see all: Under the 'mysql' command do
'SHOW VARIABLES;'
or do 'mysqladmin variables' on the Unix
command line - a method for obtaining
this list which allows grepping.
You can display one variable's value
like the following:
SHOW VARIABLES LIKE 'version';
or with a Select, like:
SELECT 'Default storage engine:',
@@storage_engine;
Note that not all system variables can
be displayed. For example, the
relay-log setting doesn't show up in any
output.
Table In SQL, a set of rows and columns which
house data.
Each table resolves to a triplet of
files in the database directory:
<TableName>.MYD
<TableName>.MYI
<TableName>.frm
See also: Database
Table, copy See: Copy a table
Table, create CREATE TABLE (q.v.)
Table, define CREATE TABLE (q.v.)
Table, delete 'DROP TABLE Table_Name'
'DROP TABLE <DBname>.Table_Name'
Table, number of rows in See: SHOW TABLE STATUS
Table, rename RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
Table, revise ALTER TABLE (q.v.)
Table, temporary See: Temporary table
Table, update column UPDATE <TableName> ...
Table, update (structure) ALTER TABLE (q.v.)
Table engine type, conver to InnoDB ALTER TABLE <TableName> ENGINE=InnoDB;
Table engine type, show SHOW TABLE STATUS FROM <DBname>;
Table locking MySQL uses table-level locking for
MyISAM, MEMORY and MERGE tables. (It
uses page-level locking for BDB tables,
and row-level locking for InnoDB
tables.)
Table locking in MySQL is deadlock-free
for storage engines that use table-level
locking. Deadlock avoidance is managed
by always requesting all needed locks at
once at the beginning of a query and
always locking the tables in the same
order.
See also: Lock*
Table locking contention Can be checked by inspectinng the
Table_locks_immediate and
Table_locks_waited status variables,
which indicate the number of times that
requests for table locks could be
granted immediately and the number that
had to wait, respectively, via:
SHOW STATUS LIKE 'Table%';
table_open_cache Server variable: The number of open
tables for all threads. Increasing this
value increases the number of file
descriptors that mysqld requires. You
can check whether you need to increase
the table cache by inspecting the
Opened_tables status variable.
Table structure, show 'DESCRIBE DatabaseName.TableName;'
See also: SHOW COLUMNS
Table types Static An on-disk table type, being
the fastest of the on-disk types. But
they cannot contain variable-length
columns.
Dynamic Much slower than Static
tables, but handle variable-length
columns.
Heap In-memory database table,
so very fast. Should be used only for
modest-sized tables.
Compressed A read-only table, very
fast.
Temporary To create a subset of a
permanent table, for special processing.
Tables in database, show Do one of:
USE <Dbname>; SHOW TABLES;
or
SHOW TABLES FROM <Dbname>;
Those show a list of the tables
contained in the database.
To see statistics on the tables, use
SHOW TABLE STATUS [FROM db_name]
[LIKE 'pattern']
Table name Is case-sensitve.
tables_priv MySQL table containing the table-level
privs for the tables in MySQL's
databases.
See also: columns_priv
Telnet test You can use the 'telnet' command to test
if the MySQL daemon is running, which
looks like the following if the daemon
is running:
telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
8
4.1.22-log��Fr/1HWZ@,q8`\-gCWl<#:
Connection closed by foreign host.
Where the MySQL 'version' variable value
(in this case, 4.1.22-log) is revealed
in the response.
The session automatically closes after 5
seconds, where the MySQL daemon drops
the session for lack of protocol
interaction.
If the daemon is not running, the
attempt will look like:
telnet localhost mysql
Trying...
telnet: connect: A remote host refused
an attempted connect operation.
BUT: See max_connect_errors for an
important advisory on this approach.
Temporary Table A temporary table is something you would
use to contain an extract of a much
larger table where it makes sense to
create such a subset for special
processing, as where you need to
repeatedly operate on the same records.
The table exists in memory, in its own
namespace, and as such can have the same
name as some other, existing table. If
the temp table is given a name which is
equal to an existing (permanent) table,
the temp table will effectively eclipse
the real table for the duration of your
processing.
A temporary table is created via
CREATE TEMPORARY TABLE <Name> (...)
and will exist for the duration of your
MySQL session: it goes away when that
session ends - no DROP TABLE needed (its
convenience factor). You can dispose of
the table before that via DROP TABLE.
See also: Heap Table
test directory Lives in the datadir, created by the
MySQL install to potentially house a
simple test database. Few customers use
this, and the directory is empty.
Like: /var/lib/mysql/test
Permissions: drwxr-xr-x
TEXT String data type, 0 - 65,535 chars.
Expansive variable length storage area,
having a 2-byte length value plus up to
64K chars of text.
Storage size: Length of value + 2 bytes
Note that if you have TEXT columns in a
table, you cannot also have CHAR
columns: MySQL silently convers them to
VARCHAR. (This behavior is not exhibited
by other SQL databases.)
See also: VARCHAR
thread_cache_size System variable: The maximum number of
threads to maintain in the thread cache.
Threads from clients that disconnect are
put in the cache if it's not already
full. This enables new connections to
be serviced by reusing cached threads
rather than creating new threads, as
long as threads remain in the cache.
The thread cache is used when the server
uses one thread per currently connected
client.
TIME MySQL data type: a standard time value.
Format: hh:mm:ss
Storage size: 3 bytes
TIME values may range from -838:59:59 to
838:59:59.
See also: DATE; DATETIME
Time value, subtract from a date DATE_SUB(Date, INTERVAL expr Unit)
TIMEDIFF() Form: TIMEDIFF(Time1, Time2);
Returns: Time value difference.
The two values may be simple time, or
date-and-time, but must both be the same
type.
TIMESTAMP MySQL data type: the date and time, down
to the second. The developers
substantially changed their minds over
time as to the nature and behavior of
the Timestamp, as described in the
versioned explanations below...
TIMESTAMP, as of MySQL 4.1 In this modern version, the format is:
'YYYY-MM-DD HH:MM:SS'.
Display width: 19 chars (not modifiable)
Storage size: 4 binary bytes
Range: '1970-01-01 00:00:01'
to '2038-01-19 03:14:07'
The overhaul of Timestamp reflects
widespread dissatisfaction with the
unilateral manner in which it worked
before: now you can control its
behavior, per the following specs:
The Timestamp field may be assigned
defaults, which.
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE
CURRENT_TIMESTAMP
Neither:
ts TIMESTAMP DEFAULT 0
BUT: In defining and modifying column
definitions, adding
"ON UPDATE CURRENT_TIMESTAMP"
to the default is accepted; but it not
reflected in a table Describe, nor does
it result in the timestamp being updated
during an Update operation. Maybe a
defect.
TIMESTAMP, prior to MySQL 4.1 In the older version, the format was:
CCYYMMDDhhmmss (YYYYMMDDhhmmss)
Display width: 14 chars, modifiable to
yield subsets, as in TIMESTAMP(6) for
format YYMMDD.
Storage size: 4 binary bytes
Range: 19700101000000 to 2037....
(which corresponds to the standard Unix
time_t timestamp).
Sample: 20030919090756
Prior to MySQL 4.1.2, the behavior was:
- The first TIMESTAMP column in table
row automatically is set to the current
timestamp when the record is created if
the column is set to NULL or is not
specified at all.
- The first TIMESTAMP column in table
row automatically is updated to the
current timestamp when the value of any
other column in the row is changed,
unless the TIMESTAMP column explicitly
is assigned a value other than NULL.
Inserting a NULL (not to be confused
with a zero) into a TIMESTAMP causes the
current date-time to be inserted - which
means that you cannot insert a NULL into
such a column; but at record creation
time, the field may be null.
Specifically, you must specify
<ColName> = NULL to get the automatic
timestamp: doing <ColName> = 0 is not
the same, and will result in a timestamp
of 00000000000000 instead.
Table design: The first Timestamp column
in a row will automatically be updated
when any other column in the row is
modified; so place such a Timestamp
column accordingly. If you don't want
this behavior, consider instead defining
the column as DATETIME; or you can
define the Timestamp column with a
Default value (0, or other).
Preserving timestamp in an Update: An
update operation on a record containing
an automatic timestamp field will, by
default, cause the timestamp to be
updated - which may well be undesirable.
To preserve the value, you could Select
on it to get the original value and then
write that back in the Update; but the
far simpler method is to employ the
handy MySQL construct "FieldX=FieldX" to
have the field remain unchanged across
the update. Note that a Replace Into
operation is *not* equivalent to an
Update operation: in a Replace Into, a
"FieldX=FieldX" will result in the
timestamp being reset to 0.
See also: DATE; DATETIME
Timestamp, format Use DATE_FORMAT(), like:
DATE_FORMAT(Timestamp,
'%%Y/%%m/%%d %%T') AS \"When\", ...
Timestamp, in Unix form Normally, the timestamp is returned in
form YYYY-MM-DD hh:mm:ss
which is cumbersome for processing.
To get it in Unix "seconds since 1970"
form, use the UNIX_TIMESTAMP() function
on the timestamp content.
Timestamps difference, in hours Select HOUR(TIMEDIFF(TS1, TS2));
Note that the maximum difference value
that will be returned by MySQL is
838:59:59 (see TIME).
TINYBLOB Binary form of TINYTEXT.
TINYINT [(display_size)] [UNSIGNED] MySQL data type: a basic, whole number
with a range of -128 to 127 if signed, 0
to 255 signed.
Storage size: 1 byte
See also: SMALLINT
TINYTEXT MySQL data type: Shortest text storage
field, 0 to 255 chars.
Storage size: Length of value + 1 byte
/tmp dependency When mysqld is running, it needs to use
a Unix domain socked file named
mysql.sock, where the directory
permissions must allow its creation and
file permissions must allow writing.
tmp_table_size System variable: The maximum number of
bytes allowed for internal temporary
tables (tables that the server creates
automatically while processing
statements.) If a temporary table
exceeds the smaller of
max_heap_table_size and tmp_table_size,
the server converts it from an internal
in-memory table to a MyISAM table on
disk. If you have memory to spare,
higher values of this variable allow the
server to maintain larger temporary
tables in memory without converting them
to on-disk format.
tmpdir Server option defining the directory
that mysqld is to use for its temporary
files. Like: tmpdir=/tmp
If this is not defined, the server may
fail to start.
TO_DAYS() Function to convert a date to days,
being the number of days since year 0.
Alternately, TIMESTAMPDIFF() could be
used.
Today's records Do like:
Select * from Table where -
DATE(TimeField) = CURDATE();
See also: Yesterday's records
Trailing blanks, remove from Select Use RTRIM(String).
Transaction A set of operations bracketed by
BEGIN;
...
COMMIT;
such that they are treated in an atomic
way, locking out any other sessions
competing for the same data, so that
they have a coherent view of that data -
so as to not see data that is in the
midst of being updated.
Should a problem be detected in the
midst of the transaction a ROLLBACK can
be performed to quit out of the whole
transaction such that all data is as it
was before the transaction.
Whereas BEGIN is a rather vague
pronouncement, START TRANSACTION is
available for clarity.
A second transaction can be started via
another BEGIN, in place of the COMMIT,
though that it is a bit libertarious.
The use of MySQL transaction processing
requires certain table types: BDB and
InnoDB are allowed; ISAM, MyISAM, and
HEAP are *not* allowed.
Triggers New in MySQL 5.0. Triggers are
event-driven stored procedures that are
attached to a specific table; the
trigger code will fire for any write,
even one that occurs on that table.
Missing is support for "instead of"
triggers, as found in major databases.
TRIM() To remove leading and/or trailing
characters from a string.
TRIM([[LEADING|TRAILING|BOTH]
[Trim_string] FROM] String)
If Trim_string is omitted, spaces are
implied.
If [LEADING|TRAILING|BOTH] is omitted,
BOTH is implied.
See also: RTRIM()
TRUNCATE TABLE SQL operation to clear out a table.
Syntax: TRUNCATE TABLE <TableName>
Any AUTO_INCREMENT counter to is reset
to zero.
umask By default, MySQL creates database
directories and RAID directories with an
access permission value of 0700
(rwx------).
(There is line "umask 007" in the
mysqld_safe script, which just
eliminates any other-user privs such
that created *files* would have
permissions rw-rw----.)
You can modify this behavior by setting
the UMASK_DIR environment variable: new
directories are created with the
combined UMASK and UMASK_DIR values.
And just to confuse things: despite
their names, the UMASK and UMASK_DIR
environment variables are used as
*modes*, not masks.
Values which start with 0 (zero) are
assumed to be octal.
There is no server option for the umask,
as it is a Unix environment thing rather
than a database environment thing.
UNION A statement for obtaining results from
multiple tables, collected together,
without value overlaps, by taking the
first occurrence of a value from the
table earliest in the Select - which is
to say that it eliminates duplicate rows
from the results set. (In this way, it
is akin to the Unix 'uniq' command.)
Unique field content reporting See: DISTINCT
Unique Identifier Database terminology for an element of a
records which:
- Is unique across all record
instances;
- Has a non-null value;
- Is unchanging over the lifetime of
the record.
An employee ID number is a good example
of a Unique Identifier candidate,
whereas a person name or birth date
would not be.
UNIX_TIMESTAMP() Returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' UTC) as an
unsigned integer.
UNIX_TIMESTAMP(SomeDate) Returns the value of the argument as
seconds since '1970-01-01 00:00:00'
UTC. The arg may be a DATE string, a
DATETIME string, a TIMESTAMP, or a
number in the format YYMMDD or YYYYMMDD.
The server interprets date as a value in
the current time zone and converts it to
an internal value in UTC.
UNLOCK TABLES Releases any table locks held by the
current session.
Syntax: UNLOCK TABLES;
(There is no granularity.)
Undoes LOCK TABLES.
UPDATE (update table) mysql command to update existing records
(rows) in a table. Can be used to rather
blindly replace column contents with new
data (typical in the case of string
data), or computationally update the
values in numerical columns.
General form:
UPDATE <Table_Name>
SET <the column key = value>
WHERE <row selection specification>;
Textual example:
UPDATE Employee
SET Last_Name='Smythe'
WHERE Last_Name='Smithe';
Computational example:
UPDATE Employee
SET ytd_salary = ytd_salary + salary
WHERE empid = 123456789;
Update Log The outdated method of tracking table
updates, for later recovery. This was an
ASCII log, recording all update
operations which actually resulted in
changes to tables. This log is now
deprecated, as the newer Binary Log does
the job much more efficiently.
See also: Binary Log
UPPER() SQL function to force a character string
to be upper case.
Example:
select NODE_NAME, PLATFORM_NAME from
NODES where upper(PLATFORM_NAME)
like 'LINUX%'
Uptime of server 'mysqladmin status' reports like:
Uptime: 8989
Or, use the STATUS command under the
'mysql' command, which reports the
uptime in hours, minute, seconds.
Or, use the SHOW STATUS
command under the 'mysql' command.
which is the number of seconds.
USE SQL statement to implicitly use a given
database in table operations to come.
Syntax:
USE Databasename;
You would do this before going at a
table, without having to name the
database, as in just:
DESCRIBE Table1;
as opposed to coding the database name
as well, like:
DESCRIBE Database1.Table1;
Opposite: DROP
user MySQL server option specifying the Unix
username to be used for running mysqld.
The mysqld daemon should be started as
root, whereafter it changes to this
username.
Command line alternatives:
--user=_____ -u ______
This is the username which should be in
/etc/passwd for the MySQL user account
under which mysqld runs.
This account name should *not* be
directly used, as via 'su': it should be
only for running mysqld.
user MySQL table containing user IDs, their
location, and global privs, plus all
metadata rights.
--user=____ A pooly named option in MySQL...
In server-run scripts (mysqld_safe), it
is the *Unix* username under which the
daemon is to run. Defaults to user
"mysql"... But: the MySQL daemon is
often run under Unix username "nobody"
rather than "mysql", via 'su nobody
...', and in that case, there is no
capability for the script to alter the
environment as root. Specify the proper
username on this invocation option, to
at least avoid a confusing name showing
up in 'ps' command reports.
In non-daemon MySQL commands, this
option specifies the *MySQL* username to
use when connecting from client to
server. (Isn't THAT confusing?!)
See also: --password
User, add See: User, create
User, create In modern MySQL, is create by
CREATE USER
Early MySQLs created users incidentally,
via GRANT, where IDENTIFIED BY would
specify the user password.
Considerations: Modern doc still talks
of using GRANT to create accounts, as
preferable. Further, a repeat of a
CREATE USER will fail if the user
already exists, but a second GRANT works
with no error.
User, remove DROP USER (q.v.)
Users MySQL has its own list of users and
their passwords, independent of whatever
may be in the OS list.
/var/lib/mysql/ For Linux RPMs, the standard directory
where /etc/my.cnf specifies that data
(database file), pidfile, and mysql.sock
will exist.
/var/lock/subsys/ The lock directory found in Red Hat
Linux and SuSE.
/var/lock/subsys/mysql The mysql server lock file in RHEL.
Is an empty file, owned by root.
Is present when mysqld is running;
absent when mysqld is shut down.
May be present erroneously if mysqld had
a start-up problem, in which case it
should be removed.
/var/lock/subsys/mysqld The mysqld lock file. An empty file
created when mysqld is started, and
should be gone after shut down.
Mysqld does not keep this file open as
it runs.
/var/run/mysqld/mysqld.pid The standard location for the MySQL
daemon PID file. The file is an
ordinary ASCII file containing like:
12345\n
File owner and group are 'mysql'.
File permissions are rw-rw----
Ordinary users cannot read this file,
and because of this, 'service mysql
status' will be unable to give a proper
report.
The file is removed when 'service mysql
stop' is performed to stop the server.
Note that the file may disappear and yet
the server processes continue running
where the stop was performed by a non
superuser, who is in group 'mysql': such
an account has enough privilege power to
get the PID file removed, but not stop
the server processes, so the server is
in a "partially down" state
VARCHAR(length) MySQL data type: a variable length
character field, 0 - 255 chars.
Compact variable length storage area,
having a 1-byte length value plus up to
255 chars of text.
Trailing blanks are stripped during
storing - in deviation from the ANSI SQL
standard.
Note that if you have VARCHAR columns in
a table, you cannot also have CHAR
columns: MySQL silently convers them to
VARCHAR. (This behavior is not exhibited
by other SQL databases.)
See also: CHAR; TEXT
Variable-length columns Considerations: When varlen columns are
updated with shorter data, dead space is
left in the MySQL data files. There is
no programming solution to this.
Use the myisamchk tool to free dead
space.
Variables See: System Variables
Version of MySQL (server) Via OS command:
mysql_config --version.
Via RPM query, on Linux:
rpm -qa | grep -i mysql
Via SQL query:
SHOW VARIABLES LIKE 'version';
The "version" variable will have a
value like "4.0.14-standard-log".
Another query:
SHOW VARIABLES LIKE 'version_comment';
is more descriptive.
Via physical inspection: Ideally, you
would have planted the MySQL software
under a directory whose name included
the version+release level. If need be,
you can inspect the mysql binary
module: look in it for string
"Copyright (C) 2002 MySQL AB", which
will be preceded by the level number.
Version of MySQL (client) Via OS command: mysql -V
(Does not report if Community Edition or
Enterprise Edition.)
Views A logical entity which acts like a table
but is not one. I provides a way to look
at columns from different tables as
though they're all part of the same
table. Views are sometimes called
Virtual Tables.
A View can also be a stored query that
doesn't accept parameters, the benefit
being in obscuring query logic from
users.
Supported in MySQL 5.0, in a standard
way.
Virtual Tables See: Views
wait_timeout System variable: The number of seconds a
non-interactive client connection can
remain idle before the server considers
itself free to close it. For
interactive clients, the value of the
interactive_timeout variable is used
instead.This applies only to TCP/IP and
Unix socket file connections.
Who started mysqld Do 'cat /proc/NNNN/loginuid'
on the mysqld PID to get the UID number
of the person, then grep for that in
/etc/passwd.
Wildcards in queries See: LIKE
YEAR MySQL data type: Stores the year number
(Gregorian calendar).
Format: YYYY
Storage size: 1 byte
See also: DATE; DATETIME
Year 0 Various SQL date/time functions are
based upon some starting year. SQL
packages may be programmed with
differing concepts of the zeroth year.
In MySQL, the zeroth year is literally
year 0. Other SQL packages may be based
upon the year 1582, the start of the
Gregorian calendar, which normalized the
calendar. To verify, you can do:
SELECT YEAR(0), MONTH(0), DAY(0);
Yesterday's date Do like:
Select
DATE(FROM_DAYS((TO_DAYS(CURRENT_DATE)
- 1)));
Yesterday's records Do like:
Select * from Table where -
(TO_DAYS(current_date) -
TO_DAYS(TimeField)) = 1;
See also: Today's records
C PROGRAMMING TECHNIQUES AND TIPS:
To make field direct addressing easier, employ an enum whose varnames
correspond in name and order to the column name order in the SELECT. This
inherently creates field offset values that you can use on the result set
via mysql_fetch_field_direct().
For example:
enum { Username, College, Major, Phone_Number } fns;
sprintf(query, "SELECT Username, College, Major, Phone_Number FROM ..."
ERROR MESSAGES, OTHER
/<SomePath>/mysqld_safe[114]: : Cannot access parent directories.
Seen when trying to start the MySQL daemon. This comes from the
mysqld_safe sh script doing MY_PWD=`pwd` where sh is finding that it
cannot accomplish the task of stepping up the directory hierarchy as the
--user=_____ username and saying "Cannot access parent directories.".
What the start-up needs is to first 'cd' to the MySQL installation
directory (the one containing the bin directory) and *then* invoke
mysqld_safe.
/<SomePath>/mysqld_safe[380]: /<SomePath>/rbsd/Databases-and-logs/acsrs6.err:
0403-005 Cannot create the specified file.
Seen when trying to start the MySQL daemon. Usually due to the absense
of an intermediate directory.
ERROR 1005 (HY000): Can't create table '...' (error: 150)
Encountered when trying to define a child table with Foreign Key -
because the child table is InnoDB type (necessarily), but the partent
table is not.
ERROR 1045 (28000): Access denied for user 'SomeUser'@'localhost'
(using password: YES)
Experienced in trying to access MySQL from an sudo (root) session, with
command 'mysql -p'. This may be due to a need for a FLUSH PRIVILEGES.
Try gaining access with 'mysql -p -h <Hostname>', where Hostname is the
local hostname, like acsgm99: that will likely give you access,
whereupon you can perform the FLUSH PRIVILEGES. A further issue can be
that the session was under sudo, rather than a true root login or su,
where it's picking up your personal identity: in this case, establish a
.my.cnf in your home directory, containing "user = root" and
"password = _______"; then try simply 'mysql'.
ERROR 1201 (HY000): Could not initialize master info structure; more error
messages can be found in the MySQL error log
Seen when doing CHANGE MASTER. May need to do RESET SLAVE;.
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
You did like 'GRANT REPLICATION CLIENT ON DBname.* ...' to limit access
to one database name; but it's an administrative privilege, so the spec
has to be "ON *.*".
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)
Seen when invoking command 'mysql' without --socket and the socket file
is defined in /etc/mysql/my.cnf as being other than that named in the
error message. This, despite /etc/mysql/my.cnf being present and fully
readable, and even a symlink from /etc/my.cnf to that file.
The formally defined cause of the error is that there is no MySQL server
running on the system or that you are using an incorrect Unix socket
file name or TCP/IP port number when trying to connect to the server.
However, invoking 'mysql' with --socket=_____ specifying the file named
in /etc/mysql/my.cnf allows the command to work.
Solution: If the socket file is to be other than the /tmp/mysql.sock
name, then /etc/mysql/my.cnf (or your private .my.cnf) needs a
"[client]" section with a 'socket' definition specifying the path of the
socket file.
May be seen with Perl's DBI because it defaults to an assumption as to
where the socket file is. Compensate in your DBI Connect, like:
my $dbh = DBI->connect('dbi:mysql:database=nemo;
mysql_socket=/our/special/place/mysql.sock', 'nemo','password')
(Alternately, you could make a symlink out of /tmp, but given the
volatility of /tmp contents, not the best approach.)
ERROR 2003 (HY000): Can't connect to MySQL server on '________' (79)
Performed using the 'mysql' command on AIX.
79 is the AIX errno, which in this case is ECONNREFUSED, indicating
that the MySQL service on the remote system does not allow connections
from your originating host.
ERROR 2003 (HY000): Can't connect to MySQL server on '________' (146)
Performed using the 'mysql' command on Solaris.
146 is the Solaris errno, which in this case is ECONNREFUSED, indicating
that the MySQL service on the remote system does not allow connections
from your originating host.
[ERROR] /usr/sbin/mysqld: Can't open file: './database/table.frm' (errno: 24)
24 is the OS errno EMFILE: Too many open files (for this process).
Normally, the /etc/my.cnf open_files_limit=____ value is like 8192,
large enough to handle the number. You can use 'lsof' to ascertain how
many files mysqld actually has open. If necessary, you can increase the
open_files_limit value in my.cnf and restart mysqld. Also assure that
the OS allows a large number, as in /etc/security/limits. If mysqld
tries to exceed that limit, the following message will be in its error log:
[Warning] Could not increase number of max_open_files to more than 1024
See the mysqld_safe entry for more info.
Failed to open the relay log './ourhostname-relay-bin.000001' (relay_log_pos 4)
It's trying to use an oddly named & located replication relay log. This
can result from trying to use /etc/my.cnf changes without having
restarted the mysqld for it to incorporate such configuration values
into its running instance.
Host '________' is blocked because of many connection errors; unblock with
'mysqladmin flush-hosts'
The host made too many successive invalid attempts to connect to the
MySQL server, who has now blocked the host from further attempts.
The value of the max_connect_errors system variable determines how many
successive interrupted connection requests are permitted. The customary
value is 10.
Once a host is blocked, flushing the host cache is the only way to
unblock it: there is no automatic unblock after a period of time.
Because of these circumstances, using 'telnet' to test for a MySQL
server being active can be a bad idea, particularly if it is done from a
host which also needs to engage in legitimate sessions, as the legit
usage can be blocked.
mysqld dead but subsys locked
Seen when doing 'service mysqld status'. The immediate reason is that
there is a /var/lock/subsys/mysqld file in place, as may be left behind
by a bad mysqld shutdown. The file looks like:
-rw-r--r-- 1 root root 0 Jun 7 10:12 /var/lock/subsys/mysqld
Certainly, if there are no MySQL processes around, the lock file is
residual and can be removed.
Reconnecting after a failed master event read
The thread is trying to reconnect to the master. When connection is
established again, the state becomes" Waiting for master to send event.
The cause may be a network issue. Less likely: master and slave using
the same Server ID.
Slave_IO_State: Connecting to master
Report item from SHOW SLAVE STATUS\G, as well as Slave_IO_Running: No.
This can be due to firewalling, where port 3306 cannot be reached on the
master. On Linux, do 'service iptables status' to check rules.
Slave_IO_State: Waiting for master to send event
In a replication environment, seen in SHOW SLAVE STATUS\G output.
This is not an error: ere are no issues reflected in the general log.
SHOW PROCESSLIST is more illuminating. On the master its state will be:
Has sent all binlog to slave; waiting for binlog to be updated
On the slave server it will show two processes, one for the I/O thread
and the second for the SQL thread:
Waiting for master to send event
Has read all relay log; waiting for the slave I/O thread to update it
This all simply reflects that once the slave server has connected to the
master server, the slave server sits waiting for data to arrive.
ERROR MESSAGES, AS SEEN IN SERVER GENERAL LOG:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
Seen when using a MySQL 4.1 server and a 4.0 or earlier client. The
best solution is to upgrade the client. Other approaches are summarized
in http://dev.mysql.com/doc/mysql/en/old-client.html .
File '/some/path/binary_log.index' not found (Errcode: 13)
Aborting
Commonly caused by the containing directory not being owner and group
"mysql".
ERROR MESSAGES INVOLVING THE C CLIENT:
ld: 0711-317 ERROR: Undefined symbol: .compress
ld: 0711-317 ERROR: Undefined symbol: .uncompress
Seen in MySQL 4.1 as it requires the zlib compression library, for
compress(),uncompress(); so add "-lz" to the compilation.
ld: 0711-317 ERROR: Undefined symbol: .mysql_close
ld: 0711-317 ERROR: Undefined symbol: .mysql_init
ld: 0711-317 ERROR: Undefined symbol: .mysql_query
ld: 0711-317 ERROR: Undefined symbol: .mysql_error
May unfathomably occur though you have -L <LibDir> and -l mysqlclient
compile comand line options specifying the client lib. I believe that
this is because the client lib is 32-bit whereas the compile is 64-bit.
ERROR NUMBERS, COMMON:
50:
The MySQL server does not allow access, as may be limited to what has
been Granted. Do 'mysql -e "select * from mysql.user"' to best see what
is allowed from where. In using the C API, you may need to specify the
target host by its host name, rather than "localhost".
1062: "Duplicate entry '6295000377' for key 1".
In ordinary processing:
Encountered when performing an Insert Into, and there is already a table
entry there with that key. This may be a situation where you would want
to perform an Update, to have new data in the record.
In replication:
Is reportedly seen where the slave server crashed (or abruptly shut
down) or the Relay Log was not flushed to disk such that updates are
being replayed when the slave server is restarted. A Web search finds
this to be a common problem in replication. The developers have
supposedly been working to make MySQL replication more robust, in that
it has been fairly "loose" since the begining. See
"sql_slave_skip_counter" and "slave-skip-errors" for remediation.
Where the key field is auto_increment type, a way out of this is to
boost the counter...
Step 1 - Get Max id from table:
select max(id) from <TableName>;
Step 2 - Align the AUTO_INCREMENT counter on table:
ALTER TABLE <TableName> AUTO_INCREMENT = <value from step 1 + 100>;
Step 3 - Retry the insert
1236: "Got fatal error 1236: 'Client requested master to start replication
from impossible position' from master when reading data from binary
log"
Replication broke. This may be seen where one of the servers crashed
and it was restarted, the other (slave) server would not connect: SHOW
SLAVE STATUS showed "Slave_IO_Running: No" and "Seconds_Behind_Master:
null". Looking in the MySQL ASCII log files, you find this 1236 error.
Just before this log entry in the slave log you will likely find an
entry indicating the master binary log file and position that it is
trying to read. Go to the master server and into its bin logs
directory. Use the mysqlbinlog utility to inspect the log in question,
by doing like: mysqlbinlog --offset=128694 mysql-bin.000013
This may reveal like:
"Warning: this binlog was not closed properly. Most probably mysqld
crashed writing it."
Aha: When the server crashed the bin log was not closed properly.
To deal with this, return to the slave server, to there stop the slave,
reset the bin log position and started the server, like:
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS = 0;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000014';
START SLAVE;
The slave will likely be happy again, per SHOW SLAVE STATUS\G.
SITUATION HANDLING:
Root can no longer do stuff:
MySQL's root is supposed to be its superuser, wholly unrestricted. If
someone issues a Grant from root to alter root, it can end up with
reduced privileges - including the inability to Grant itself back to
usefulness. In other words, root is partially locked out. The easiest
way to rectify, where you still have basic root access is:
Do like:
update mysql.user set Reload_priv='y' where user='root';
on each of the privs shown from
select * from mysql.user where user='root';
The only other alternative is to create a good mysql.user table on a
test system, shut down mysqld on the problem system, temporarily replace
its mysql.user table files, restart with --skip-networking. make needed
adjustments, shut down, and restart.
REFERENCES AND OTHER RESOURCES:
MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/
Books:
"MySQL", Paul DuBois Developer's Library
An excellent, definitive reference and tutorial
The 2nd, 2003 edition covers MySQL v4.
The 3rd, 2005 edition covers MySQL v4.0, with info on v5.0
The 4th, 2008 edition is "The Definitive Guide to Using, Programming, and
Administering MySQL 5.0 and 5.1"
"Managing & Using MySQL", George Reese O'Reilly & Associates, Inc.
Pretty good book, half the size and thoroughness of the DuBois book.
"MySQL Essentials" - an online book at
http://www.techotopia.com/index.php/MySQL_Essentials
InnoDB: http://www.think-and-link.org/mysql/manual_InnoDB.html
InnoDB: Taking advantage of InnoDB's New Table Formats& The InnoDB Roadmap
(2004 presentation)
http://www.innodb.com/innodbtalkCC2004b.pdf
MySQL toolkit: www.sourceforge.net/projects/maatkit
www.maatkit.org
MySQL web site: http://www.mysql.com/ (http://web.mysql.com/)
http://www.databasejournal.com/features/mysql/
Executing SQL statements in MySQL databases using C:
http://www-106.ibm.com/developerworks/linux/library/l-sql.html
Linux reference card: http://www.digilife.be/quickreferences/quickrefs.htm
Features Available in MySQL 4.1:
http://dev.mysql.com/doc/mysql/en/nutshell-4-1-features.html
Changes in release 4.1.x:
http://dev.mysql.com/doc/mysql/en/news-4-1-x.html
(This is file /usr/csg/QuickFacts/MySQL.QuickFacts)