2.4 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/mysqld_safe --user=mysql &

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

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

The mysql_install_db script 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 runs mysql_install_db and a root user. The entries are created without passwords. The mysqld_safe script starts the mysqld server. (If your version of MySQL is older than 4.0, use safe_mysqld rather than mysqld_safe.)

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 after starting the server.

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 mysqld_safe 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 Setting Up the Initial MySQL Privileges. 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/mysqld_safe. See section 4.7.2 mysqld_safe, The Wrapper Around 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 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 How to Run MySQL As a Normal User. If you have problems with mysql_install_db, see section 2.4.1 Problems Running mysql_install_db. 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 Setting Up the Initial MySQL Privileges.
2. Start the MySQL server like this:

   shell> cd mysql_installation_directory
   shell> bin/mysqld_safe &
   

   If you have problems starting the server, see section 2.4.2 Problems Starting the MySQL Server.
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 mysqld_safe or by invoking mysqld directly. For example:

   shell> BINDIR/mysqld_safe --log &
   

   If mysqld_safe 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 Problems Starting the MySQL Server.
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 benchmark suite is written in Perl, using the Perl DBI module to provide a database-independent interface to the various databases. The following additional Perl modules are required to run the benchmark suite:

   DBI
   DBD-mysql
   Data-Dumper
   Data-ShowTable
   

   These modules can be obtained from CPAN http://www.cpan.org/. See section 2.7.1 Installing Perl on Unix. 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.

Subsections

• 2.4.1 Problems Running mysql_install_db
• 2.4.2 Problems Starting the MySQL Server
• 2.4.3 Starting and Stopping MySQL Automatically

User Comments

When I execute run-all-tests, I get a prompt,
alter-table:

I have *no* idea what to type there, and the
README in that directory doesn't say either.

It's not clear from this page, but presumably the
RPM ran mysql_install_db when doing rpm -i MySQLxxxx

Installed MySQL in RedHat 8 through the packages included.

1.
All the BINDIR and EXECDIR commands are located: /usr/bin

2.
I noticed that after I ran the 'mysql_install_db', that the script suggested adding a root password. I went ahead and did this using the mysqladmin command (mysqladmin -u root password 'new password').

I then spent the next 10 minutes trying to figure out how to invoke all the other commands, because I had to figure out the syntax of using a command with a password. So here it is, just in case you find yourself in the same predicament:

(The shutdown command is used here as an example)
>mysqladmin -p -u root shutdown

The system will prompt you to enter the password.

I installed on Windows 2K and the databases and permissions were already installed. I had to run the script to install MYSQL as an NT service, but not the scripts to initialize a database and permissions.

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

I struggled with this for a quite a while, trying everything I could think of. The file did indeed exist, but MySQL seemed not to see it. safe_mysqld would start and then end. The mysqld.log file said it could not find host.frm.

It was a permissions problem I was having. All of the other database directories were group=mysql, whereas the mysql database directory was group=root. As soon as I changed this to group=mysql, everything was fine!

Password added after ran 'mysql_install_db'.
To remove password simply type:
mysqladmin -p password ''

In response to message posted by Paul Harvey on Tuesday March 4 2003, @3:46am:

to avoid a permissions' problem in the future, run mysql_install_db with this option: --user=mysql
additionally, to specify an alternate location of the data directory, use the option --ldata=[insert path here]

hope this helps

Re the alter-table 'prompt' when invoking run-all-tests:

It looks like a prompt. Keep waiting.

On my Solaris 2.8 system running MySQL 3.23.46, that first test suite took 18 minutes to complete (on a Sun Blade 100 with 500MHz, 640 MB, files NFS mounted).

The unix 'truss' command showed that the test script was active. If you log into the MySQL server, you will see that there is activity (use test; show tables; describe bench;).

To remove the password you actually have to type the following and provide the old password at the prompt (from very recent experience ;) ).
mysqladmin -u root -p password ''

WRT the host.frm problem Paul Harvey (!!) posted above ... not only do you have to change the group ownership of the 'mysql' dir (/var/lib/mysql/mysql) to user 'mysql', you have to give it read / write / execute perms as well. OK, it may only need g+rx, not g+rwx, but either way ... you gotta let 'mysql' see in that dir as well as have group ownership. :)

If you are running RedHat Linux (not sure which versions) you may run into a problem with the mysql-test/mysql-test-run script unless you have your locale configured to POSIX. The other locales seem to sort "repair_part2" before "repair" and that causes repair_part2 to fail. Be sure to set your LANG environment variable to C or POSIX before running the tests.

Add your own comment.