MySQL Reference Manual

Post-Installation Setup and Testing

Once you’ve installed MySQL (from either a binary or source distribution), you need to initialise the grant tables, start the server, and make sure that the server works okay. You may also wish to arrange for the server to be started and stopped automatically when your system starts up and shuts down.

Normally you install the grant tables and start the server like this for installation from a source distribution:

shell> ./scripts/mysql_install_db
shell> cd mysql_installation_directory
shell> ./bin/safe_mysqld --user=mysql &

For a binary distribution (not RPM or pkg packages), do this:

shell> cd mysql_installation_directory
shell> ./bin/mysql_install_db
shell> ./bin/safe_mysqld --user=mysql &

This creates the mysql database which will hold all database privileges, the test database which you can use to test MySQL, and also privilege entries for the user that run mysql_install_db and a root user (without any passwords). This also starts the mysqld server.

mysql_install_db will not overwrite any old privilege tables, so it should be safe to run in any circumstances. If you don’t want to have the test database you can remove it with mysqladmin -u root drop test.

Testing is most easily done from the top-level directory of the MySQL distribution. For a binary distribution, this is your installation directory (typically something like /usr/local/mysql). For a source distribution, this is the main directory of your MySQL source tree. In the commands shown in this section and in the following subsections, BINDIR is the path to the location in which programs like mysqladmin and safe_mysqld are installed. For a binary distribution, this is the bin directory within the distribution. For a source distribution, BINDIR is probably /usr/local/bin, unless you specified an installation directory other than /usr/local when you ran configure. EXECDIR is the location in which the mysqld server is installed. For a binary distribution, this is the same as BINDIR. For a source distribution, EXECDIR is probably /usr/local/libexec.

Testing is described in detail:

1. If necessary, start the mysqld server and set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. This is normally done with the mysql_install_db script:

   shell> scripts/mysql_install_db

   Typically, mysql_install_db needs to be run only the first time you install MySQL. Therefore, if you are upgrading an existing installation, you can skip this step. (However, mysql_install_db is quite safe to use and will not update any tables that already exist, so if you are unsure of what to do, you can always run mysql_install_db.)

   mysql_install_db creates six tables (user, db, host, tables_priv, columns_priv, and func) in the mysql database. A description of the initial privileges is given in Section 4.3.4. Briefly, these privileges allow the MySQL root user to do anything, and allow anybody to create or use databases with a name of test or starting with test_.

   If you don’t set up the grant tables, the following error will appear in the log file when you start the server:

   mysqld: Can't find file: 'host.frm'

   This may also happen with a binary MySQL distribution if you don’t start MySQL by executing exactly ./bin/safe_mysqld! See Section 4.7.2.

   You might need to run mysql_install_db as root. However, if you prefer, you can run the MySQL server as an unprivileged (non-root) user, provided that the user can read and write files in the database directory. Instructions for running MySQL as an unprivileged user are given in Section A.3.2.

   If you have problems with mysql_install_db, see Section 2.4.1.

   There are some alternatives to running the mysql_install_db script as it is provided in the MySQL distribution:

   • You may want to edit mysql_install_db before running it, to change the initial privileges that are installed into the grant tables. This is useful if you want to install MySQL on a lot of machines with the same privileges. In this case you probably should need only to add a few extra INSERT statements to the mysql.user and mysql.db tables!

   • If you want to change things in the grant tables after installing them, you can run mysql_install_db, then use mysql -u root mysql to connect to the grant tables as the MySQL root user and issue SQL statements to modify the grant tables directly.

   • It is possible to re-create the grant tables completely after they have already been created. You might want to do this if you’ve already installed the tables but then want to re-create them after editing mysql_install_db.

   For more information about these alternatives, see Section 4.3.4.

2. Start the MySQL server like this:

   shell> cd mysql_installation_directory
   shell> bin/safe_mysqld &

   If you have problems starting the server, see Section 2.4.2.

3. Use mysqladmin to verify that the server is running. The following commands provide a simple test to check that the server is up and responding to connections:

   shell> BINDIR/mysqladmin version
   shell> BINDIR/mysqladmin variables

   The output from mysqladmin version varies slightly depending on your platform and version of MySQL, but should be similar to that shown here:

   shell> BINDIR/mysqladmin version
   mysqladmin  Ver 8.14 Distrib 3.23.32, for linux on i586
   Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
   This software comes with ABSOLUTELY NO WARRANTY. This is free software,
   and you are welcome to modify and redistribute it under the GPL license.
   
   Server version          3.23.32-debug
   Protocol version        10
   Connection              Localhost via Unix socket
   TCP port                3306
   UNIX socket             /tmp/mysql.sock
   Uptime:                 16 sec
   
   Threads: 1  Questions: 9  Slow queries: 0
   Opens: 7  Flush tables: 2  Open tables: 0
   Queries per second avg: 0.000
   Memory in use: 132K  Max memory used: 16773K

   To get a feeling for what else you can do with BINDIR/mysqladmin, invoke it with the --help option.

4. Verify that you can shut down the server:

   shell> BINDIR/mysqladmin -u root shutdown

5. Verify that you can restart the server. Do this using safe_mysqld or by invoking mysqld directly. For example:

   shell> BINDIR/safe_mysqld --log &

   If safe_mysqld fails, try running it from the MySQL installation directory (if you are not already there). If that doesn’t work, see Section 2.4.2.

6. Run some simple tests to verify that the server is working. The output should be similar to what is shown here:

   shell> BINDIR/mysqlshow
   +-----------+
   | Databases |
   +-----------+
   | mysql     |
   +-----------+
   
   shell> BINDIR/mysqlshow mysql
   Database: mysql
   +--------------+
   |    Tables    |
   +--------------+
   | columns_priv |
   | db           |
   | func         |
   | host         |
   | tables_priv  |
   | user         |
   +--------------+
   
   shell> BINDIR/mysql -e "SELECT host,db,user FROM db" mysql
   +------+--------+------+
   | host | db     | user |
   +------+--------+------+
   | %    | test   |      |
   | %    | test_% |      |
   +------+--------+------+

   There is also a benchmark suite in the sql-bench directory (under the MySQL installation directory) that you can use to compare how MySQL performs on different platforms. The sql-bench/Results directory contains the results from many runs against different databases and platforms. To run all tests, execute these commands:

   shell> cd sql-bench
   shell> run-all-tests

   If you don’t have the sql-bench directory, you are probably using an RPM for a binary distribution. (Source distribution RPMs include the benchmark directory.) In this case, you must first install the benchmark suite before you can use it. Beginning with MySQL Version 3.22, there are benchmark RPM files named mysql-bench-VERSION-i386.rpm that contain benchmark code and data.

   If you have a source distribution, you can also run the tests in the tests subdirectory. For example, to run auto_increment.tst, do this:

   shell> BINDIR/mysql -vvf test < ./tests/auto_increment.tst

   The expected results are shown in the ./tests/auto_increment.res file.

Problems Running mysql_install_db

The purpose of the mysql_install_db script is to generate new MySQL privilege tables. It will not affect any other data! It will also not do anything if you already have MySQL privilege tables installed!

If you want to re-create your privilege tables, you should take down the mysqld server, if it’s running, and then do something like:

mv mysql-data-directory/mysql mysql-data-directory/mysql-old
mysql_install_db

This section lists problems you might encounter when you run mysql_install_db:

mysql_install_db doesn’t install the grant tables

You may find that mysql_install_db fails to install the grant tables and terminates after displaying the following messages:

starting mysqld daemon with databases from XXXXXX
mysql daemon ended

In this case, you should examine the log file very carefully! The log should be located in the directory XXXXXX named by the error message, and should indicate why mysqld didn’t start. If you don’t understand what happened, include the log when you post a bug report using mysqlbug! See Section 1.6.2.3.

There is already a mysqld daemon running

In this case, you probably don’t have to run mysql_install_db at all. You have to run mysql_install_db only once, when you install MySQL the first time.

Installing a second mysqld daemon doesn’t work when one daemon is running

This can happen when you already have an existing MySQL installation, but want to put a new installation in a different place (for example, for testing, or perhaps you simply want to run two installations at the same time). Generally the problem that occurs when you try to run the second server is that it tries to use the same socket and port as the old one. In this case you will get the error message: Can't start server: Bind on TCP/IP port: Address already in use or Can't start server: Bind on unix socket.... See Section 4.1.3.

You don’t have write access to /tmp

If you don’t have write access to create a socket file at the default place (in /tmp) or permission to create temporary files in /tmp, you will get an error when running mysql_install_db or when starting or using mysqld.

You can specify a different socket and temporary directory as follows:

shell> TMPDIR=/some_tmp_dir/
shell> MYSQL_UNIX_PORT=/some_tmp_dir/mysqld.sock
shell> export TMPDIR MYSQL_UNIX_PORT

See Section A.4.5.

some_tmp_dir should be the path to some directory for which you have write permission. See Appendix E.

After this you should be able to run mysql_install_db and start the server with these commands:

shell> scripts/mysql_install_db
shell> BINDIR/safe_mysqld &

mysqld crashes immediately

If you are running RedHat Version 5.0 with a version of glibc older than 2.0.7-5, you should make sure you have installed all glibc patches! There is a lot of information about this in the MySQL mail archives. Links to the mail archives are available online at http://lists.mysql.com/. Also, see Section 2.6.1.

You can also start mysqld manually using the --skip-grant-tables option and add the privilege information yourself using mysql:

shell> BINDIR/safe_mysqld --skip-grant-tables &
shell> BINDIR/mysql -u root mysql

From mysql, manually execute the SQL commands in mysql_install_db. Make sure you run mysqladmin flush-privileges or mysqladmin reload afterward to tell the server to reload the grant tables.

Problems Starting the MySQL server

If you are going to use tables that support transactions (InnoDB, BDB), you should first create a my.cnf file and set startup options for the table types you plan to use. See Chapter 7.

Generally, you start the mysqld server in one of these ways:

• By invoking mysql.server. This script is used primarily at system startup and shutdown, and is described more fully in Section 2.4.3.

• By invoking safe_mysqld, which tries to determine the proper options for mysqld and then runs it with those options. See Section 4.7.2.

• For Windows NT/2000/XP, please see Section 2.6.2.2.

• By invoking mysqld directly.

When the mysqld daemon starts up, it changes the directory to the data directory. This is where it expects to write log files and the pid (process ID) file, and where it expects to find databases.

The data directory location is hardwired in when the distribution is compiled. However, if mysqld expects to find the data directory somewhere other than where it really is on your system, it will not work properly. If you have problems with incorrect paths, you can find out what options mysqld allows and what the default path settings are by invoking mysqld with the --help option. You can override the defaults by specifying the correct pathnames as command-line arguments to mysqld. (These options can be used with safe_mysqld as well.)

Normally you should need to tell mysqld only the base directory under which MySQL is installed. You can do this with the --basedir option. You can also use --help to check the effect of changing path options (note that --help must be the final option of the mysqld command). For example:

shell> EXECDIR/mysqld --basedir=/usr/local --help

Once you determine the path settings you want, start the server without the --help option.

Whichever method you use to start the server, if it fails to start up correctly, check the log file to see if you can find out why. Log files are located in the data directory (typically /usr/local/mysql/data for a binary distribution, /usr/local/var for a source distribution, and \mysql\data\mysql.err on Windows). Look in the data directory for files with names of the form host_name.err and host_name.log where host_name is the name of your server host. Then check the last few lines of these files:

shell> tail host_name.err
shell> tail host_name.log

Look for something like the following in the log file:

000729 14:50:10  bdb:  Recovery function for LSN 1 27595 failed
000729 14:50:10  bdb:  warning: ./test/t1.db: No such file or directory
000729 14:50:10  Can't init databases

This means that you didn’t start mysqld with --bdb-no-recover and Berkeley DB found something wrong with its log files when it tried to recover your databases. To be able to continue, you should move away the old Berkeley DB log file from the database directory to some other place, where you can later examine it. The log files are named log.0000000001, where the number will increase over time.

If you are running mysqld with BDB table support and mysqld core dumps at start this could be because of some problems with the BDB recover log. In this case you can try starting mysqld with --bdb-no-recover. If this helps, then you should remove all log.* files from the data directory and try starting mysqld again.

If you get the following error, it means that some other program (or another mysqld server) is already using the TCP/IP port or socket mysqld is trying to use:

Can't start server: Bind on TCP/IP port: Address already in use
  or
Can't start server : Bind on unix socket...

Use ps to make sure that you don’t have another mysqld server running. If you can’t find another server running, you can try to execute the command telnet your-host-name tcp-ip-port-number and press Enter a couple of times. If you don’t get an error message like telnet: Unable to connect to remote host: Connection refused, something is using the TCP/IP port mysqld is trying to use. See Section 2.4.1 and Section 4.1.4.

If mysqld is currently running, you can find out what path settings it is using by executing this command:

shell> mysqladmin variables

or

shell> mysqladmin -h 'your-host-name' variables

If you get Errcode 13, which means Permission denied, when starting mysqld this means that you didn’t have the right to read/create files in the MySQL database or log directory. In this case you should either start mysqld as the root user or change the permissions for the involved files and directories so that you have the right to use them.

If safe_mysqld starts the server but you can’t connect to it, you should make sure you have an entry in /etc/hosts that looks like this:

127.0.0.1       localhost

This problem occurs only on systems that don’t have a working thread library and for which MySQL must be configured to use MIT-pthreads.

If you can’t get mysqld to start you can try to make a trace file to find the problem. See Section D.1.2.

If you are using InnoDB tables, refer to the InnoDB-specific startup options. See Section 7.5.2.

If you are using BDB (Berkeley DB) tables, you should familiarise yourself with the different BDB specific startup options. See Section 7.6.3.

Starting and Stopping MySQL Automatically

The mysql.server and safe_mysqld scripts can be used to start the server automatically at system startup time. mysql.server can also be used to stop the server.

The mysql.server script can be used to start or stop the server by invoking it with start or stop arguments:

shell> mysql.server start
shell> mysql.server stop

mysql.server can be found in the share/mysql directory under the MySQL installation directory or in the support-files directory of the MySQL source tree.

Before mysql.server starts the server, it changes the directory to the MySQL installation directory, then invokes safe_mysqld. You might need to edit mysql.server if you have a binary distribution that you’ve installed in a non-standard location. Modify it to cd into the proper directory before it runs safe_mysqld. If you want the server to run as some specific user, add an appropriate user line to the /etc/my.cnf file, as shown later in this section.

mysql.server stop brings down the server by sending a signal to it. You can take down the server manually by executing mysqladmin shutdown.

You might want to add these start and stop commands to the appropriate places in your /etc/rc* files when you start using MySQL for production applications. Note that if you modify mysql.server, and then upgrade MySQL sometime, your modified version will be overwritten, so you should make a copy of your edited version that you can reinstall.

If your system uses /etc/rc.local to start external scripts, you should append the following to it:

/bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld --user=mysql &'

You can also add options for mysql.server in a global /etc/my.cnf file. A typical /etc/my.cnf file might look like this:

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql

[mysql.server]
basedir=/usr/local/mysql

The mysql.server script understands the following options: datadir, basedir, and pid-file.

The following table shows which option groups each of the startup scripts read from option files:

Script	Option groups
mysqld	mysqld and server
mysql.server	mysql.server, mysqld, and server
safe_mysqld	mysql.server, mysqld, and server

See Section 4.1.2.