Go to the first, previous, next, last section, table of contents.

M Pieces of the manual in transit

M.1 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. See section 2.1.1 Installing MySQL on Linux.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. 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. See section 1.2.22.3 How to Report Bugs or Problems.

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 root  /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/data
shell> chgrp -R mysql /usr/local/mysql
shell> chown -R root /usr/local/mysql/bin
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.

To install a binary distribution, follow the steps below, then proceed to section 2.4 Post-installation Setup and Testing, for post-installation setup and testing:

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.)
Obtain a distribution file from one of the sites listed in section 2.2.1 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).
If you see a binary distribution marked with the -max prefix, this means that the binary has support for transaction-safe tables and other features. See section 4.7.5 mysqld-max, An extended mysqld server. Note that all binaries are built from the same MySQL source distribution.
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 versions of Unix. 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. See section H Environment Variables.
`scripts'
This directory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.
If you would like to use mysqlaccess and have the MySQL distribution in some non-standard 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 binaries to root and ownership of the data directory to the user that you will run mysqld as:
```
shell> chown -R root  /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/data
shell> chgrp -R mysql /usr/local/mysql
```
The first command changes the owner attribute of the files to the root user, the second one changes the owner attribute of the data directory to the mysql user, and the third one changes the group attribute to the mysql group.
If you want to install support for the Perl DBI/DBD interface, see section M.2 Perl Installation Comments.
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 and in section 2.4.3 Starting and Stopping MySQL Automatically.

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 &

See section 4.7.2 safe_mysqld, the wrapper around mysqld.

See section 2.4 Post-installation Setup and Testing.

M.2 Perl Installation Comments

M.2.1 Installing Perl on Unix

Perl support for MySQL is provided by means of the DBI/DBD client interface. See section 8.2 MySQL Perl API. The Perl DBD/DBI client code requires Perl Version 5.004 or later. The interface will not work if you have an older version of Perl.

MySQL Perl support also requires that you've installed MySQL client programming support. If you installed MySQL from RPM files, client programs are in the client RPM, but client programming support is in the developer RPM. Make sure you've installed the latter RPM.

As of Version 3.22.8, Perl support is distributed separately from the main MySQL distribution. If you want to install Perl support, the files you will need can be obtained from http://www.mysql.com/Downloads/Contrib/.

The Perl distributions are provided as compressed tar archives and have names like `MODULE-VERSION.tar.gz', where MODULE is the module name and VERSION is the version number. You should get the Data-Dumper, DBI, and Msql-Mysql-modules distributions and install them in that order. The installation procedure is shown below. The example shown is for the Data-Dumper module, but the procedure is the same for all three distributions:

Unpack the distribution into the current directory:
```
shell> gunzip < Data-Dumper-VERSION.tar.gz | tar xvf -
```
This command creates a directory named `Data-Dumper-VERSION'.
Change into the top-level directory of the unpacked distribution:
```
shell> cd Data-Dumper-VERSION
```

Build the distribution and compile everything:

shell> perl Makefile.PL
shell> make
shell> make test
shell> make install

The make test command is important because it verifies that the module is working. Note that when you run that command during the Msql-Mysql-modules installation to exercise the interface code, the MySQL server must be running or the test will fail.

It is a good idea to rebuild and reinstall the Msql-Mysql-modules distribution whenever you install a new release of MySQL, particularly if you notice symptoms such as all your DBI scripts dumping core after you upgrade MySQL.

If you don't have the right to install Perl modules in the system directory or if you to install local Perl modules, the following reference may help you:

http://www.iserver.com/support/contrib/perl5/modules.html

Look under the heading Installing New Modules that Require Locally Installed Modules.

M.2.2 Installing ActiveState Perl on Windows

To install the MySQL DBD module with ActiveState Perl on Windows, you should do the following:

Get ActiveState Perl from http://www.activestate.com/Products/ActivePerl/index.html and install it.
Open a DOS shell.
If required, set the HTTP_proxy variable. For example, you might try:
```
set HTTP_proxy=my.proxy.com:3128
```
Start the PPM program:
```
C:\> c:\perl\bin\ppm.pl
```
If you have not already done so, install DBI:
```
ppm> install DBI
```

If this succeeds, run the following command:

install ftp://ftp.de.uu.net/pub/CPAN/authors/id/JWIED/DBD-mysql-1.2212.x86.ppd

The above should work at least with ActiveState Perl Version 5.6.

If you can't get the above to work, you should instead install the MyODBC driver and connect to MySQL server through ODBC:

use DBI;
$dbh= DBI->connect("DBI:ODBC:$dsn","$user","$password") ||
  die "Got error $DBI::errstr when connecting to $dsn\n";

M.2.3 Installing the MySQL Perl Distribution on Windows

The MySQL Perl distribution contains DBI, DBD:MySQL and DBD:ODBC.

Get the Perl distribution for Windows from http://www.mysql.com/download.html.
Unzip the distribution in C: so that you get a `C:\PERL' directory.
Add the directory `C:\PERL\BIN' to your path.
Add the directory `C:\PERL\BIN\MSWIN32-x86-thread' or `C:\PERL\BIN\MSWIN32-x86' to your path.
Test that perl works by executing perl -v in a DOS shell.

M.2.4 Problems Using the Perl `DBI`/`DBD` Interface

If Perl reports that it can't find the `../mysql/mysql.so' module, then the problem is probably that Perl can't locate the shared library `libmysqlclient.so'.

You can fix this by any of the following methods:

Compile the Msql-Mysql-modules distribution with perl Makefile.PL -static -config rather than perl Makefile.PL.
Copy libmysqlclient.so to the directory where your other shared libraries are located (probably `/usr/lib' or `/lib').
On Linux you can add the pathname of the directory where `libmysqlclient.so' is located to the `/etc/ld.so.conf' file.
Add the pathname of the directory where `libmysqlclient.so' is located to the LD_RUN_PATH environment variable.

If you get the following errors from DBD-mysql, you are probably using gcc (or using an old binary compiled with gcc):

/usr/bin/perl: can't resolve symbol '__moddi3'
/usr/bin/perl: can't resolve symbol '__divdi3'

Add -L/usr/lib/gcc-lib/... -lgcc to the link command when the `mysql.so' library gets built (check the output from make for `mysql.so' when you compile the Perl client). The -L option should specify the pathname of the directory where `libgcc.a' is located on your system.

Another cause of this problem may be that Perl and MySQL aren't both compiled with gcc. In this case, you can solve the mismatch by compiling both with gcc.

If you get the following error from Msql-Mysql-modules when you run the tests:

t/00base............install_driver(mysql) failed: Can't load '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ../blib/arch/auto/DBD/mysql/mysql.so: undefined symbol: uncompress at /usr/lib/perl5/5.00503/i586-linux/DynaLoader.pm line 169.

it means that you need to include the compression library, -lz, to the link line. This can be doing the following change in the file `lib/DBD/mysql/Install.pm':

$sysliblist .= " -lm";

to

$sysliblist .= " -lm -lz";

After this, you MUST run 'make realclean' and then proceed with the installation from the beginning.

If you want to use the Perl module on a system that doesn't support dynamic linking (like SCO) you can generate a static version of Perl that includes DBI and DBD-mysql. The way this works is that you generate a version of Perl with the DBI code linked in and install it on top of your current Perl. Then you use that to build a version of Perl that additionally has the DBD code linked in, and install that.

On SCO, you must have the following environment variables set:

shell> LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:/usr/progressive/lib
or
shell> LD_LIBRARY_PATH=/usr/lib:/lib:/usr/local/lib:/usr/ccs/lib:/usr/progressive/lib:/usr/skunk/lib
shell> LIBPATH=/usr/lib:/lib:/usr/local/lib:/usr/ccs/lib:/usr/progressive/lib:/usr/skunk/lib
shell> MANPATH=scohelp:/usr/man:/usr/local1/man:/usr/local/man:/usr/skunk/man:

First, create a Perl that includes a statically linked DBI by running these commands in the directory where your DBI distribution is located:

shell> perl Makefile.PL -static -config
shell> make
shell> make install
shell> make perl

Then you must install the new Perl. The output of make perl will indicate the exact make command you will need to execute to perform the installation. On SCO, this is make -f Makefile.aperl inst_perl MAP_TARGET=perl.

Next, use the just-created Perl to create another Perl that also includes a statically-linked DBD::mysql by running these commands in the directory where your Msql-Mysql-modules distribution is located:

shell> perl Makefile.PL -static -config
shell> make
shell> make install
shell> make perl

Finally, you should install this new Perl. Again, the output of make perl indicates the command to use.

M.3 Functions for Use with `GROUP BY` Clauses

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

COUNT(expr)

Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement:

mysql> select student.student_name,COUNT(*)
           from student,course
           where student.student_id=course.student_id
           GROUP BY student_name;

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> select COUNT(*) from student;

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values:

mysql> select COUNT(DISTINCT results) from student;

In MySQL you can get the number of distinct expression combinations that don't contain NULL by giving a list of expressions. In ANSI SQL you would have to do a concatenation of all expressions inside CODE(DISTINCT ..).

AVG(expr)

Returns the average value of expr:

mysql> select student_name, AVG(test_score)
           from student
           GROUP BY student_name;

MIN(expr)

MAX(expr)

Returns the minimum or maximum value of expr. MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value. See section 5.4.3 How MySQL Uses Indexes.

mysql> select student_name, MIN(test_score), MAX(test_score)
           from student
           GROUP BY student_name;

SUM(expr)

Returns the sum of expr. Note that if the return set has no rows, it returns NULL!

STD(expr)

STDDEV(expr)

Returns the standard deviation of expr. This is an extension to ANSI SQL. The STDDEV() form of this function is provided for Oracle compatibility.

BIT_OR(expr)

Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision.

BIT_AND(expr)

Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision.

MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items. For example, you don't need to group on customer.name in the following query:

mysql> select order.custid,customer.name,max(payments)
       from order,customer
       where order.custid = customer.custid
       GROUP BY order.custid;

In ANSI SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you don't run in ANSI mode.

Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.

In some cases, you can use MIN() and MAX() to obtain a specific column value even if it isn't unique. The following gives the value of column from the row containing the smallest value in the sort column:

substr(MIN(concat(rpad(sort,6,' '),column)),7)

See section 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field.

Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to follow ANSI SQL, you can't use expressions in GROUP BY or ORDER BY clauses. You can work around this limitation by using an alias for the expression:

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
           GROUP BY id,val ORDER BY val;

In MySQL Version 3.23 you can do:

mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();

Go to the first, previous, next, last section, table of contents.