MySQL
From DocForge
MySQL is a popular relational database server. It's often used for dynamic web sites due to its fast performance for small queries and quick connectivity from languages such as PHP. MySQL is the M in the LAMP software stack. It can use multiple storage engines, each tuned for performance in different situations. For scalability across multiple servers it supports database replication. MySQL supports most of the SQL-92 standard.
Contents |
[edit] System Requirements
MySQL can run on the following operating systems:
MySQL has very low memory requirements. It's been known to run on Linux systems with as little as 64MB of RAM. Depending on MySQL's settings, increasing available memory can improve performance by allowing data to be cached in memory.
[edit] Scalability
See also: MySQL/Performance
[edit] Replication
When one server becomes too busy it's often desirable to add more servers to increase overall application performance. While one large multi-processor server may work well, multiple smaller servers are often more cost effective.
Standard MySQL features master/slave database replication. Master/slave replication is one-way and asynchronous. Both single and multi-master replication are possible, but singe-master replication is generally simpler to maintain and develop with. With single-master replication, one server acts as the master while one or more other servers act as slaves.
In single-master replication, the master server writes updates to binary log files. The logs are tracked with system tables. The binary log files store every update made to the database, both to data and structure. When a slave connects to its master, it informs the master of the position up to which it read the logs at its last successful update. The slave receives any updates that have taken place since that time, and then waits for the master to notify it of new updates.
A chain of servers can be set up by making a slave master to other servers.
When using replication all updates to the database schema that are replicated should be performed on the master server. This avoids conflicts by updating the slave and master separately.
While setting up master/slave replication is relatively easy, applications can be written in various ways to use the multiple servers properly. Special considerations must be made when building an application to scale with database replication. See Programming for database replication.
[edit] General Usage (Linux/UNIX)
The server is started by launching the daemon, usually as root. The MySQL processes can be configured to run under a limited user account, which is typically a good idea for security and maintenance.
$ mysqld_safe &[edit] Client
Command line client for executing SQL:
$ mysql -h host -u user -p databaseTypical command line options:
-h <host> : Host to connect to; defaults to localhost -u <username> : Username to connect as; defaults to your local user name -p : Request password (required if account has a password) -e "<command>" : Execute command (SQL) and exit immediately
[edit] Tools
- myisamchk - Checks and repairs ISAM tables
- mysqladmin - Perform some basic administrative tasks
- mysqldump - Export database table definitions and data in SQL format
[edit] SQL
MySQL supports most of the SQL-92 standard. It also has its own set of SQL features in addition to the standard. Using extensions to standard SQL can make code less portable to other database systems.
[edit] SQL Errors
While MySQL will return an error for all syntax mistakes, it will not return errors or warnings for all SQL execution problems. In general, MySQL has a tendency to fall back to default actions when the stated action fails.
For example, CREATE TABLE statements can define which storage engine to use. The default setting will be used if none is specified. But if one is specified which isn't supported, the table will be created with the default engine and no warning or error is returned. This can happen if the InnoDB storage engine fails to initialize when the database server is launched and the problem goes unnoticed. Subsequent CREATE TABLE ... ENGINE=InnoDB statements will actually cause MyISAM tables to be created.
In another example, ENUM column definitions let a set of allowed values be defined for a column. But an enum will allow empty strings to be set as a value, even if an empty string is not one of the pre-defined enum values. No warning or error is returned.
If strict SQL mode is not enabled, strings will be automatically truncated to fit within a field's length. So, for example, inserting a value of "abcd" into a varchar(2) field will result in "ab" being stored in the database. MySQL will return a warning, not an error. It's therefore important that applications check value length before inserting them into the database if warnings aren't captured.
[edit] SQL Functions
MySQL implements additional and differing scalar functions:
- CONCAT(string1, string2, ...)
- Concatenates all of the parameters to return one string. If any parameter is null the return value is null. This function differs from the standard CONCATENATE function.
- FROM_UNIXTIME(timestamp [, format])
- Converts the timestamp to a string or numeric value (depending on context).
- IFNULL(expression, value)
- If the expression evaluates to null, return value, else return the evaluated expression.
- SUBSTRING(string,position), SUBSTRING(string,position,length)
- Returns part of string, starting from position. MySQL implements these in addition to the standard, which use a FROM syntax.
- UNIX_TIMESTAMP([date])
- Return the current timestamp, or convert the date string or numeric value to a timestamp.
[edit] Differences from the SQL Standard
MySQL's SQL differs from standard ANSI SQL in several ways:
- The REPLACE statement, available in MySQL, is not in ANSI SQL. Similar functionality can be found ANSI SQL's MERGE statement, but the MERGE statement is not supported by MySQL.
- The LIMIT clause, available in MySQL, is not in ANSI SQL. There a few different ways to imitate this functionality via ANSI SQL, for example, a simple "LIMIT n" in MySQL SQL can be replaced by "FETCH FIRST x ONLY" in ANSI SQL. Another way to imitate a LIMIT clause is to use the ANSI SQL feature of window functions. However, these ANSI SQL methods are not available in MySQL.
[edit] Versions
MySQL database server versions 3.23, 4.0, and 4.1 have reached the end of their software lifecycle with MySQL AB. There will no longer be active development and general support by the company. Only what MySQL AB calls "Security Level 1" issues will still be fixed for these versions.
Therefore developers should consider the 5.x and newer versions, especially for new development.
