The purpose of the mysql_install_db script is to generate new MySQL privilege tables. It does not overwrite existing MySQL privilege tables, and it does not affect any other data.
If you want to re-create your privilege tables, first stop the
mysqld server if it is running. Then rename
the mysql
directory under the data
directory to save it, and then run
mysql_install_db. Suppose that your current
directory is the MySQL installation directory and that
mysql_install_db is located in the
bin
directory and the data directory is
named data
. To rename the
mysql
database and re-run
mysql_install_db, use these commands.
shell>mv data/mysql data/mysql.old
shell>bin/mysql_install_db --user=mysql
When you run mysql_install_db, you might encounter the following problems:
mysql_install_db fails to 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 mysqld ended
In this case, you should examine the error 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 do not understand what happened, include the
log when you post a bug report. See
Section 1.7, “How to Report Bugs or Problems”.
There is a mysqld process running
This indicates that the server is running, in which case the grant tables have probably been created already. If so, there is no need to run mysql_install_db at all because it needs to be run only once (when you install MySQL the first time).
Installing a second mysqld server does not work when one server is running
This can happen when you have an existing MySQL installation, but want to put a new installation in a different location. For example, you might have a production installation, but you want to create a second installation for testing purposes. Generally the problem that occurs when you try to run a second server is that it tries to use a network interface that is in use by the first server. In this case, you should see one of the following error messages:
Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on unix socket...
For instructions on setting up multiple servers, see Section 5.6, “Running Multiple MySQL Servers on the Same Machine”.
You do not have write access to the
/tmp
directory
If you do not have write access to create temporary files
or a Unix socket file in the default location (the
/tmp
directory), an error occurs when
you run mysql_install_db or the
mysqld server.
You can specify different locations for the temporary
directory and Unix socket file by executing these commands
prior to starting mysql_install_db or
mysqld, where
some_tmp_dir
is the full path
name to some directory for which you have write
permission:
shell>TMPDIR=/
shell>some_tmp_dir
/MYSQL_UNIX_PORT=/
shell>some_tmp_dir
/mysql.sockexport TMPDIR MYSQL_UNIX_PORT
Then you should be able to run mysql_install_db and start the server with these commands:
shell>bin/mysql_install_db --user=mysql
shell>bin/mysqld_safe --user=mysql &
If mysql_install_db is located in the
scripts
directory, modify the first
command to scripts/mysql_install_db
.
See Section B.5.4.5, “How to Protect or Change the MySQL Unix Socket File”, and Section 2.14, “Environment Variables”.
There are some alternatives to running the mysql_install_db script provided in the MySQL distribution:
If you want the initial privileges to be different from
the standard defaults, you can modify
mysql_install_db before you run it.
However, it is preferable to use
GRANT
and
REVOKE
to change the
privileges after the grant tables
have been set up. In other words, you can run
mysql_install_db, and then use
mysql -u root mysql
to connect to the
server as the MySQL root
user so that
you can issue the necessary
GRANT
and
REVOKE
statements.
If you want to install MySQL on several machines with the
same privileges, you can put the
GRANT
and
REVOKE
statements in a file
and execute the file as a script using
mysql
after running
mysql_install_db. For example:
shell>bin/mysql_install_db --user=mysql
shell>bin/mysql -u root < your_script_file
By doing this, you can avoid having to issue the statements manually on each machine.
It is possible to re-create the grant tables completely
after they have previously been created. You might want to
do this if you're just learning how to use
GRANT
and
REVOKE
and have made so
many modifications after running
mysql_install_db that you want to wipe
out the tables and start over.
To re-create the grant tables, remove all the
.frm
, .MYI
, and
.MYD
files in the
mysql
database directory. Then run the
mysql_install_db script again.
You can start mysqld manually using the
--skip-grant-tables
option
and add the privilege information yourself using
mysql:
shell>bin/mysqld_safe --user=mysql --skip-grant-tables &
shell>bin/mysql mysql
From mysql, manually execute the SQL commands contained in mysql_install_db. Make sure that you run mysqladmin flush-privileges or mysqladmin reload afterward to tell the server to reload the grant tables.
Note that by not using mysql_install_db, you not only have to populate the grant tables manually, you also have to create them first.
User Comments
When you move to a new data dir and are executing the mysql_install_db script make sure you've disabled the bin-logging.
Otherwise you will get error messages that the bin-log.index files are not found.
You can re-enable bin-logging after running the script.
If you get a warning about not being able to create test tables, this indicates a permissions problem. First ensure that the user mysql will run as has the ability to write to the data directories. Note also that SELinux and AppArmor can cause permissions problems, even if the file permissions would allow access. This is a particular issue if you are wanting to run a server in a sandbox. Check the documentation for how to configure these security systems to permit mysql to access the directories in question.
Add your own comment.