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: = - += MySQL does not have such a convenience arithmetic operator. Instead, you must spell out the operation on a field: = + \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 | 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 = + 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 [, ] ... [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 [] ADD COLUMN [FIRST | AFTER ]; 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 MODIFY COLUMN ; 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 CHANGE COLUMN ; Example: ALTER TABLE MyTable CHANGE COLUMN Column3 Column3 INT; Column, change contents UPDATE SET = WHERE ; 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 CHANGE COLUMN ; 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 '' as part of the definition of the column. Column, computationally update See: UPDATE Column, move position ALTER TABLE []Table_Name MODIFY COLUMN FIRST | AFTER ; Note: MySQL may balk at this if the column is an index. Column, populate one UPDATE SET = WHERE ; Example: UPDATE Db1.Table1 SET Address = '132 Main Street' WHERE Person = 'Smith,Joe'; Column, remove Invoked under 'mysql': ALTER TABLE []Table_Name DROP COLUMN Column_Name; Column, rename Invoked under 'mysql': ALTER TABLE []Table_Name CHANGE COLUMN Column_Cur_Name Column_New_Name ; 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 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 '' 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 LIKE ; 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 SELECT * FROM ; 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 SELECT ... FROM [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> ( [NOT NULL | NULL] [DEFAULT ] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE [KEY]] [COMMENT ''] | [CONSTRAINT ] PRIMARY KEY () | [CONSTRAINT ] UNIQUE [INDEX|KEY] [] () | {INDEX|KEY} [] () | FULLTEXT [INDEX|KEY] [] () | [CONSTRAINT ] FOREIGN KEY [INDEX|KEY] [] () [] | [CONSTRAINT ] CHECK () [, ...] ) Where: DEFAULT 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 | mysql & 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 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 ; 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 [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 ;'. 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 [, ]; 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 log­slave­updates 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 or from within the 'mysql' command: SHOW TABLE STATUS in ; 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 CHANGE COLUMN 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/.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 =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 .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" "root" "localhost" "root" "" "127.0.0.1" "" where 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 ...'. (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: [] 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 (...) 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 ADD INDEX (); Involves a write lock on the table. See: ALTER Index, remove ALTER TABLE DROP {INDEX|KEY} ; 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} , ADD {INDEX|KEY} (); 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 [.]; 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 . (...) 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 .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 .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 SET = , = ; Form to insert one row, by positional column values: INSERT INTO VALUES (Col1val,Col2val); Form to insert multiple rows, by positional column values: INSERT INTO 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; relay­log.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 .MYD (See: .MYD) .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 ; 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 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 (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 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. 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. 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 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 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 [,] Kill server threads. old-password Like the password command, but stores the new password using the old (pre-4.1) password-hashing format. password 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 /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/.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 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 To backup one table: mysqldump --opt --result-file=/tmp/TableName.mysqldump 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 ) - 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 ', 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 ;' 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/.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 .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 ; 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 ... 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 -p -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 SET = , = ; 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 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" - (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 [(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 IS NULL" (not "WHERE =NULL"). To find non-null column values, use "WHERE 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 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/.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 ' - '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/.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/ .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 ; 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 ; 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: .MYD .MYI .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 .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 ... Table, update (structure) ALTER TABLE (q.v.) Table engine type, conver to InnoDB ALTER TABLE ENGINE=InnoDB; Table engine type, show SHOW TABLE STATUS FROM ; 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 ; SHOW TABLES; or SHOW TABLES FROM ; 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 (...) 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 = NULL to get the automatic timestamp: doing = 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 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 SET WHERE ; 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 //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. //mysqld_safe[380]: //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 ', 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 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 ; Step 2 - Align the AUTO_INCREMENT counter on table: ALTER TABLE AUTO_INCREMENT = ; 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)