2.9.2.3. Starting and Troubleshooting the MySQL Server
This section provides troubleshooting suggestions for problems
starting the server on Unix. If you are using Windows, see
Section 2.3.13, “Troubleshooting a MySQL Installation Under Windows”.
If you have problems starting the server, here are some things
to try:
Check the error log to see why the server does not start.
Specify any special options needed by the storage engines
you are using.
Make sure that the server knows where to find the data
directory.
Make sure that the server can access the data directory.
The ownership and permissions of the data directory and
its contents must be set such that the server can read and
modify them.
Verify that the network interfaces the server wants to use
are available.
Some storage engines have options that control their behavior.
You can create a my.cnf
file and specify
startup options for the engines that you plan to use. If you
are going to use storage engines that support transactional
tables (InnoDB
, BDB
,
NDB
), be sure that you have them configured
the way you want before starting the server:
Storage engines will use default option values if you specify
none, but it is recommended that you review the available
options and specify explicit values for those for which the
defaults are not appropriate for your installation.
When the mysqld server starts, it changes
location to the data directory. This is where it expects to
find databases and where it expects to write log files. The
server also writes the pid (process ID) file in the data
directory.
The data directory location is hardwired in when the server is
compiled. This is where the server looks for the data
directory by default. If the data directory is located
somewhere else on your system, the server will not work
properly. You can determine what the default path settings are
by invoking mysqld with the
--verbose
and --help
options.
If the default locations don't match the MySQL installation
layout on your system, you can override them by specifying
options to mysqld or
mysqld_safe on the command line or in an
option file.
To specify the location of the data directory explicitly, use
the --datadir
option. However, normally you
can tell mysqld the location of the base
directory under which MySQL is installed and it looks for the
data directory there. You can do this with the
--basedir
option.
To check the effect of specifying path options, invoke
mysqld with those options followed by the
--verbose
and --help
options. For example, if you change location into the
directory where mysqld is installed and
then run the following command, it shows the effect of
starting the server with a base directory of
/usr/local
:
shell> ./mysqld --basedir=/usr/local --verbose --help
You can specify other options such as
--datadir
as well, but note that
--verbose
and --help
must be
the last options.
Once you determine the path settings you want, start the
server without --verbose
and
--help
.
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 host_name
variables
host_name
is the name of the MySQL
server host.
If you get Errcode 13
(which means
Permission denied
) when starting
mysqld, this means that the privileges of
the data directory or its contents do not allow the server
access. In this case, you change the permissions for the
involved files and directories so that the server has the
right to use them. You can also start the server as
root
, but this raises security issues and
should be avoided.
On Unix, change location into the data directory and check the
ownership of the data directory and its contents to make sure
the server has access. For example, if the data directory is
/usr/local/mysql/var
, use this command:
shell> ls -la /usr/local/mysql/var
If the data directory or its files or subdirectories are not
owned by the login account that you use for running the
server, change their ownership to that account. If the account
is named mysql
, use these commands:
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql/var
If the server fails to start up correctly, check the error
log. Log files are located in the data directory (typically
C:\Program Files\MySQL\MySQL Server
5.1\data
on Windows,
/usr/local/mysql/data
for a Unix binary
distribution, and /usr/local/var
for a
Unix source distribution). 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 examine the last few lines of these files.
On Unix, you can use tail
to display them:
shell> tail host_name
.err
shell> tail host_name
.log
The error log should contain information that indicates why
the server couldn't start. For example, you might see
something like this in the log:
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 did not start mysqld
with the --bdb-no-recover
option and Berkeley
DB found something wrong with its own log files when it tried
to recover your databases. To be able to continue, you should
move the old Berkeley DB log files from the database directory
to some other place, where you can later examine them. The
BDB
log files are named in sequence
beginning with log.0000000001
, where the
number increases over time.
If you are running mysqld with
BDB
table support and
mysqld dumps core at startup, this could be
due to problems with the BDB
recovery log.
In this case, you can try starting mysqld
with --bdb-no-recover
. If that helps, you
should remove all BDB
log files from the
data directory and try starting mysqld
again without the --bdb-no-recover
option.
If either of the following errors occur, it means that some
other program (perhaps another mysqld
server) is using the TCP/IP port or Unix socket file that
mysqld is trying to use:
Can't start server: Bind on TCP/IP port: Address already in use
Can't start server: Bind on unix socket...
Use ps to determine whether you have
another mysqld server running. If so, shut
down the server before starting mysqld
again. (If another server is running, and you really want to
run multiple servers, you can find information about how to do
so in Section 5.12, “Running Multiple MySQL Servers on the Same Machine”.)
If no other server is running, try to execute the command
telnet your_host_name
tcp_ip_port_number
. (The
default MySQL port number is 3306.) Then press Enter a couple
of times. If you don't get an error message like
telnet: Unable to connect to remote host: Connection
refused
, some other program is using the TCP/IP port
that mysqld is trying to use. You'll need
to track down what program this is and disable it, or else
tell mysqld to listen to a different port
with the --port
option. In this case, you'll
also need to specify the port number for client programs when
connecting to the server via TCP/IP.
Another reason the port might be inaccessible is that you have
a firewall running that blocks connections to it. If so,
modify the firewall settings to allow access to the port.
If the server starts but you can't connect to it, you should
make sure that you have an entry in
/etc/hosts
that looks like this:
127.0.0.1 localhost
This problem occurs only on systems that do not have a working
thread library and for which MySQL must be configured to use
MIT-pthreads.
If you cannot get mysqld to start, you can
try to make a trace file to find the problem by using the
--debug
option. See
Section E.1.2, “Creating Trace Files”.