User Comments

If you get the following error (either to your console or to an error log):

Can't open privilege tables, table 'mysql.host' doesn't exist

please read on.
On Window's systems, if you decide to save your data to a different directory than \mysql\data be sure to save the contents of the \your-mysql-base-dir\data to the directory you specified in your my.ini or my.cnf file. For example, I installed MySQL in C:\Program Files\MySQL. Therefore the original data directory was saved at C:\Program Files\MySQL\data. But I wanted the data to be saved in C:\My Documents\MySQL Data Files. So I modified the my.ini file to reflect this, but I was still having a problem trying to start the mysqld server. The error log kept saying: Can't open privilege tables, table 'mysql.host' doesn't exist. I had no idea what this ment. After hours of searching I decided to read further on in the MySQL manual. It says you need to save the contents of the original data directory to your new data directory. So I copied ...\data\mysql and its contents to my new data directory and everything works fine.

Hope this helps anyone with the same problem.

I am running Windows XP, and installed MySQL 4.0.17-nt to D:\programs\mysql

I was getting "Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist".

I set the following in D:\windows\my.ini
basedir=d:/programs/mysql/
datadir=d:/data/mysql/

I then copied the mysql directory from D:\programs\mySQL\data\ to d:\data\mysql, so I now have the original mysql data files in d:\data\mysql\mysql

It seems that d:\data\mysql is the base for all databases, and each directory below that is a database. MySQL expects to find a databse named mysql, which contains the privelege tables.

If you have already run BASEDIR/bin/mysql_install_db, and the server won't start, check your error file (localhost.localdomain.err). If it says something like "Can't open and lock priviledge tables: table 'mysql.host' doesn't exist", and you have ALREADY run the db installer (see above), then you're not giving mysql the correct data directory. Even if you used a proper PREFIX compile time option (or, for the binary, if it seems like it _should_ work), try giving the startup script the explicit path:

shell> bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data &

Of course, if you use a different data path, then please replace that. I believe this is caused by a lack of /etc/my.conf file (I don't seem to have that file at all, perhaps part of my problem?). It took me a goodly amount of time to figure this out, and I don't think that I found any documentation anywhere that suggested it. By all accounts this is a very common problem, that many people don't seem to be able to fix.

Cheers,
Travis

have to use
mysql_install_db --basedir=/usr/local/mysql --user=mysql --ldata=/usr/local/mysql/var
then you can use
mysqld_safe --datadir=/usr/local/mysql/var --user=mysql
to resolve this, otherwise, the message is still there

I was having a problem trying to start the mysqld-nt server (version 5.0.15) on win2k pro. The error log kept saying: Can't open privilege tables, table 'mysql.host' doesn't exist.

After reading all the tips regarding a) moving the data files, and b) creating a dedicated user (MySQLservice) to log on to the MySQL service (which I did with no results), I found my problem was not renaming the default [mysqld] to [mysqld-nt] in my "my.ini" file. This solved it for me as I am using "mysqld-nt.exe and not mysqld.exe.

Hello there,

just wanted to add: I had the same problem on HP-UX 11.11. After creating the tables with "scripts/mysql_install_db" i then received "could not open and lock proc.frm" (Or was it user.frm?).
The db was created with the userrights "root:sys" on all files, so I had to change them to "mysql:mysql" and after that the DB is running perfectly.
Greetings from Germany,
Roy

One thing that threw me was something I discovered trying to copy all my databases from a Debian sarge system to RHEL4. DATADIR in sarge is /var/lib/mysql, and the 'mysql' database is stored in /var/lib/mysql/mysql. However, using a series of mysqlhotcopy command [see my comment on the mysqlhotcopy command ref page], all the database directories were created under /tmp/mysqlhotcopies/ as expected except for the 'mysql' DB, which was created under /tmp/mysqlhotcopies/mysql/mysql/. I had to manually move these files to /tmp/mysqlhotcopies/mysql/ and then copy them to the target machine to run MySQL without the 'host table not found' error.

Odd. If anyone has an explanation, I'd love to hear it.

Hope this helps someone else.

-Kevin

I had installed TopologiLinux and on start up of sql server i was getting the same error i.e. "Can't open privilege tables: Table 'mysql.host' doesn't exist"

In dir "/var/lib/mysql" I had run the following commands:

shell> chown -R mysql .
shell> chgrp -R mysql .
shell> mysql_install_db --user=mysql

It solved my problem.

Add your own comment.