MySQL: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
Solution 1
Run the following command
mysql_install_db
Solution 2
initialize mysql before start on windows.
mysqld --initialize
Solution 3
The first thing you need to do is run these commands:
use mysql
show tables;
Please note the differences
MySQL 5.0 has 17 tables in the mysql schema
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
MySQL 5.1 has 23 tables in the mysql schema
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
MySQL 5.5 has 24 tables in the mysql schema
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Please note that mysql.plugin does not exist in MySQL 5.0. It is very plausible to surmise that you somehow installed MySQL 5.0 and made vital tables for MySQL 5.5 disappear.
Here is some good news. There is something you can try.
For this example
- ServerA is where your MySQL 5.5 data lives
- ServerB is where you will create a separate MySQL 5.5 environment
Here are your steps
- On ServerA, mkdir /root/myusers
- On ServerA, cp /var/lib/mysql/mysql/user.* /root/myusers/.
- Install MySQL 5.5 on ServerB
- scp ServerB:/var/lib/mysql/mysql/* ServerA:/var/lib/mysql/mysql/.
- On ServerA, cp /root/myusers/user.* /var/lib/mysql/mysql/.
- service mysql start
That's it.
If you are running this in Windows, the same principles have to apply.
Give it a Try !!!
UPDATE 2011-07-29 16:15 EDT
If your usernames had DB Specific Privileges, here are your steps
- On ServerA, mkdir /root/myusers
- On ServerA, cp /var/lib/mysql/mysql/user.* /root/myusers/.
- On ServerA, cp /var/lib/mysql/mysql/db.* /root/myusers/.
- Install MySQL 5.5 on ServerB
- scp ServerB:/var/lib/mysql/mysql/* ServerA:/var/lib/mysql/mysql/.
- On ServerA, cp /root/myusers/* /var/lib/mysql/mysql/.
- service mysql start
Related videos on Youtube
Steve
Updated on September 18, 2022Comments
-
Steve almost 2 years
All of a sudden my local MySQL 5.5 server stopped running on my Windows XP SP3.
I also have WAMP Apache and WAMP MySQL installed, but WAMP MySQL is not running. Error log shows:
- Can't start server: Bind on TCP/IP port: No such file or directory
- Do you already have another mysqld server running on port: 3306 ?
I tried changing the port from 3306 to 3307, but the service would still not start, giving error:
The Event Viewer shows:
- Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
- Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Apparently, I can only run mysql_upgrade if the server is running. How can I create 'mysql.host' if the service can't be started?
I uninstalled MySQL Server and reinstalled it, and during the config wizard after installation, I receive an error: The service could not be started. Error:0.
How do I proceed from here?
-
Rik Schneider almost 13 yearsSteve are you running this on Windows, BSD, Linux, OSX, or some other OS? If you are running it on Windows the details for some steps will be different.
-
Steve almost 13 years@Rik Schneider: Rick, I am running MySQL on my Windows laptop. Thanks.
-
Steve almost 13 yearsThanks, but it was already in the Path variable.
-
Mike S over 9 yearsBTW, this is necessary when doing an install on (at least) Linux CentOS 7. Otherwise mysqld will not start. I saw this when I ran
systemctl status mysqld.service 2015-02-20 15:41:07 15160 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
-
Schiavini about 8 yearsI wish I could upvote this more than once. I've been searching for this simple solution. Thanks!