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 &
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 below 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 below:
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 [Default privileges], page 198. Brie y, 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’
The above may also happen with a binary MySQL distribution if you don’t start MySQL by executing exactly ./bin/safe_mysqld!
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 user can read and write files in the database directory.
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.
2. Start the MySQL server like this:shell> cd mysql_installation_directory
shell> bin/safe_mysqld &
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 below:
shell> BINDIR/mysqladmin version mysqladmin
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 &
6. Run some simple tests to verify that the server is working. The output should besimilar to what is shown below:
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 in stallation directory) that you can use to compare how MySQL performs on di erent platforms. The ‘sql-bench/Results’ directory contains the results from many runs against di erent 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’shell> run-all-tests
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 a ect 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! 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 di erent 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 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 di erent 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
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
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.
Generally, you start the mysqld server in one of these ways:
• By invoking mysql.server.
• By invoking safe_mysqld, which tries to determine the proper options for mysqld and
then runs it with those options. .
• For Windows NT/2000
• By invoking mysqld directly.
When the mysqld daemon starts up, it changes 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 e ect 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, ‘\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
If you find 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 these. 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.
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
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 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, 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 pidfile.
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
If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog:
0 commenti:
Post a Comment