Installing a MySQL Binary Distribution
You need the following tools to install a MySQL binary distribution:
GNU gunzip to uncompress the distribution.
A reasonable tar to unpack the distribution. GNU tar is known to work. Sun tar is known to have problems.
An alternative installation method under Linux is to use RPM (RedHat Package Manager) distributions.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to This e-mail address is being protected from spam bots, you need JavaScript enabled to view it Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `bin' directory after you unpack the distribution.

The basic commands you must execute to install and use a MySQL binary distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql
shell> bin/safe_mysqld --user=mysql &


You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.
A more detailed description follows.

Pick the directory under which you want to unpack the distribution, and move into it. In the example below, we unpack the distribution under `/usr/local' and create a directory `/usr/local/mysql' into which MySQL is installed. (The following instructions therefore assume you have permission to create files in `/usr/local'. If that directory is protected, you will need to perform the installation as root.)

How to Get MySQL. MySQL binary distributions are provided as compressed tar archives and have names like `mysql-VERSION-OS.tar.gz', where VERSION is a number (for example, 3.21.15), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-gnu-i586). Add a user and group for mysqld to run as:
shell> groupadd mysql
shell> useradd -g mysql mysql

These commands add the mysql group and the mysql user. The syntax for useradd and groupadd may differ slightly on different Unixes. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql.
Change into the intended installation directory:
shell> cd /usr/local>
Unpack the distribution and create the installation directory:
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql

The first command creates a directory named `mysql-VERSION-OS'. The second command makes a symbolic link to that directory. This lets you refer more easily to the installation directory as `/usr/local/mysql'.
Change into the installation directory:
shell> cd mysql

You will find several files and subdirectories in the mysql directory. The most important for installation purposes are the `bin' and `scripts' subdirectories.
`bin'
This directory contains client programs and the server You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.
`scripts'
This directory contains the mysql_install_db script used to initialize the server access permissions.
If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard place, you must change the location where mysqlaccess expects to find the mysql client. Edit the `bin/mysqlaccess' script at approximately line 18. Search for a line that looks like this:
$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, you will get a Broken pipe error when you run mysqlaccess.
Create the MySQL grant tables (necessary only if you haven't installed MySQL before):
shell> scripts/mysql_install_db

Note that MySQL versions older than Version 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true! Change ownership of the installation directory to the user that you will run mysqld as:
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql

The first command changes the owner attribute of the files to the mysql user, and the second changes the group attribute to the mysql group.
If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself.
After everything has been unpacked and installed, you should initialize and test your distribution.

You can start the MySQL server with the following command:

shell> bin/safe_mysqld --user=mysql &

MySQL - Quick Installation Overview
The basic commands you must execute to install a MySQL source distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If you start from a source RPM, then do the following:

shell> rpm --rebuild MySQL-VERSION.src.rpm

This will make a binary RPM that you can install.

You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.
A more detailed description follows.

Pick the directory under which you want to unpack the distribution, and move into it.
If you are interested in using Berkeley DB tables with MySQL, you will need to obtain a patched version of the Berkeley DB source code. Please read the chapter on Berkeley DB tables before proceeding.
MySQL source distributions are provided as compressed tar archives and have names like `mysql-VERSION.tar.gz', where VERSION is a number like 3.23.33.
Add a user and group for mysqld to run as:
shell> groupadd mysql
shell> useradd -g mysql mysql

These commands add the mysql group, and the mysql user. The syntax for useradd and groupadd may differ slightly on different Unixes. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql.
Unpack the distribution into the current directory:
shell> gunzip < /path/to/mysql-VERSION.tar.gz | tar xvf -

This command creates a directory named `mysql-VERSION'.
Change into the top-level directory of the unpacked distribution:
shell> cd mysql-VERSION

Note that currently you must configure and build MySQL from this top-level directory. You can not build it in a different directory.
Configure the release and compile everything:
shell> ./configure --prefix=/usr/local/mysql
shell> make

When you run configure, you might want to specify some options. Run ./configure --help for a list of options. If configure fails, and you are going to send mail to
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it to ask for assistance, please include any lines from `config.log' that you think can help solve the problem. Also include the last couple of lines of output from configure if configure aborts. Post the bug report using the mysqlbug script.
Install everything:
shell> make install

You might need to run this command as root.
Create the MySQL grant tables (necessary only if you haven't installed MySQL before):
shell> scripts/mysql_install_db

Note that MySQL versions older than Version 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true!
Change ownership of the installation to the user that you will run mysqld as:
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql

The first command changes the owner attribute of the files to the mysql user, and the second changes the group attribute to the mysql group.

If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself.

After everything has been installed, you should initialize and test your distribution:
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If that command fails immediately with mysqld daemon ended then you can find some information in the file `mysql-data-directory/'hostname'.err'. The likely reason is that you already have another mysqld server running.

MySQL - MySQL Extensions to ANSI SQL92
MySQL includes some extensions that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the form /*! ... */. In this case, MySQL will parse and execute the code within the comment as it would any other MySQL statement, but other SQL servers will ignore the extensions. For example:

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

If you add a version number after the '!', the syntax will only be executed if the MySQL version is equal to or newer than the used version number:

CREATE /*!32302 TEMPORARY */ TABLE (a int);

The above means that if you have Version 3.23.02 or newer, then MySQL will use the TEMPORARY keyword.

MySQL extensions are listed below:

The field types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types.
The field attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL.
All string comparisons are case insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
MySQL maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has a few implications:
Database names and table names are case sensitive in MySQL on operating systems that have case-sensitive filenames (like most Unix systems).
Database, table, index, column, or alias names may begin with a digit (but may not consist solely of digits).
You can use standard system commands to backup, rename, move, delete, and copy tables. For example, to rename a table, rename the `.MYD', `.MYI', and `.frm' files to which the table corresponds.
In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL doesn't support tablespaces as in: create table ralph.my_table...IN my_tablespace.
LIKE is allowed on numeric columns.
Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement.
The SQL_SMALL_RESULT option in a SELECT statement.
EXPLAIN SELECT to get a description on how tables are joined.
Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement.
Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
Use of COUNT(DISTINCT list) where 'list' is more than one element.
Use of CHANGE col_name, DROP col_name, or DROP INDEX, IGNORE or RENAME in an ALTER TABLE statement.
Use of RENAME TABLE.
Use of multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement.
Use of DROP TABLE with the keywords IF EXISTS.
You can drop multiple tables with a single DROP TABLE statement.
The LIMIT clause of the DELETE statement.
The DELAYED clause of the INSERT and REPLACE statements.
The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE, and UPDATE statements.
Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's LOAD DATA INFILE.
The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.
The SHOW statement.
Strings may be enclosed by either `"' or `'', not just by `''.
Use of the escape `\' character.
The SET OPTION statement.
You don't need to name all selected columns in the GROUP BY part. This gives better performance for some very specific, but quite normal queries.
One can specify ASC and DESC with GROUP BY.
To make it easier for users who come from other SQL environments, MySQL supports aliases for many functions. For example, all string functions support both ANSI SQL syntax and ODBC syntax.
MySQL understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it's easy to convert use of the || operator to MySQL.
CREATE DATABASE or DROP DATABASE.
The % operator is a synonym for MOD(). That is, N % M is equivalent to MOD(N,M). % is supported for C programmers and for compatibility with PostgreSQL.
The =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;


The LAST_INSERT_ID() function.
The REGEXP and NOT REGEXP extended regular expression operators.
CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL, these functions can take any number of arguments.)
The BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY() functions.
Use of TRIM() to trim substrings. ANSI SQL only supports removal of single characters.
The GROUP BY functions STD(), BIT_OR(), and BIT_AND().
Use of REPLACE instead of DELETE + INSERT.
The FLUSH flush_option statement.
The possiblity to set variables in a statement with :=:
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

MySQL - Running MySQL in ANSI Mode
If you start mysqld with the --ansi option, the following behavior of MySQL changes:

|| is string concatenation instead of OR.
You can have any number of spaces between a function name and the `('. This forces all function names to be treated as reserved words.
`"' will be an identifier quote character (like the MySQL ``' quote character) and not a string quote character. REAL will be a synonym for FLOAT instead of a synonym of DOUBLE.
5.3 MySQL Differences Compared to ANSI SQL92
We try to make MySQL follow the ANSI SQL standard and the ODBC SQL standard, but in some cases MySQL does some things differently:

-- is only a comment if followed by a white space.
For VARCHAR columns, trailing spaces are removed when the value is stored.
In some cases, CHAR columns are silently changed to VARCHAR columns.
Privileges for a table are not automatically revoked when you delete a table. You must explicitly issue a REVOKE to revoke privileges for a table.
NULL AND FALSE will evaluate to NULL and not to FALSE. This is because we don't think it's good to have to evaluate a lot of extra conditions in this case.

MySQL - Functionality Missing from MySQL
The following functionality is missing in the current version of MySQL. For a prioritized list indicating when new extensions may be added to MySQL, you should consult the online MySQL TODO list. That is the latest version of the TODO list in this manual.

MySQL - Sub-selects
The following will not yet work in MySQL:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);


However, in many cases you can rewrite the query without a sub-select:


SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in sub-selects). For this situation there are two options available until subqueries are supported by MySQL.

The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)).

The second option is to use interactive SQL to contruct a set of DELETE statements automatically, using the MySQL extension CONCAT() (in lieu of the standard || operator). For example:

SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;

You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter:

prompt> mysql --skip-column-names mydb > myscript.sql | mysql mydb

MySQL only supports INSERT ... SELECT ... and REPLACE ... SELECT ... Independent sub-selects will probably be available in Version 4.0. You can now use the function IN() in other contexts, however.