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.
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
/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
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:
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.
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.
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.
shell> BINDIR/mysqladmin -u root shutdown
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.
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
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
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
shell> BINDIR/mysql -vvf test < ./tests/auto_increment.tst
The expected results are shown in the
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:
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 22.214.171.124.
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.
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.
If you don’t have write access to create a socket file at the default place
/tmp) or permission to create temporary files in
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 &
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.
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 126.96.36.199.
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.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
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
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
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 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.
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
under the MySQL installation directory or in the
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
/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
/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 &'
[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:
mysqld and server
mysql.server, mysqld, and server
mysql.server, mysqld, and server
See Section 4.1.2.