MySQL: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Question

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?

Steve 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. — Rik Schneider, Jul 28 '11 at 23:49
@Rik Schneider: Rick, I am running MySQL on my Windows laptop. Thanks. — Steve, Jul 31 '11 at 11:18

score 9 · Answer 1 · answered Jul 28 '11 at 14:42

9

Run the following command

mysql_install_db

answered Jul 28 '11 at 14:42

Mike

21,910
7
55
79

BTW, 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` – Mike S Feb 20 '15 at 16:20

score 5 · Answer 2 · answered Dec 21 '15 at 09:28

5

initialize mysql before start on windows.

mysqld --initialize

answered Dec 21 '15 at 09:28

tvrcgo

51
1
1

I wish I could upvote this more than once. I've been searching for this simple solution. Thanks! – Schiavini Jun 21 '16 at 12:51

RolandoMySQLDBA · Answer 3 · 2011-07-29T20:16:05.563

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

score 0 · Answer 4 · answered Jul 28 '11 at 15:11

0

The service could not be started. Error:0.

Add MySQL path to the environment variables.

answered Jul 28 '11 at 15:11

quanta

50,327
19
152
213

Thanks, but it was already in the Path variable. – Steve Jul 31 '11 at 08:23

score 0 · Answer 5 · answered Jul 28 '11 at 23:37

0

it looks like u already have mysql running

try

ps ax|grep mysql

if u see some output, probably u need stop it(or reboot box).

if u not see any output, try run mysqld without demonize and see output.

answered Jul 28 '11 at 23:37

arheops

708
1
5
13

score 0 · Answer 6 · answered Aug 01 '11 at 17:54

I've added a windows tag to help people answer the question.

My background is not windows but the principals apply.

Is there a mysql process already running? If so you will need to kill it before uninstalling. If it was still running when you tried to uninstall/reinstall you will have processes and files hanging around preventing you from uninstalling completely.

Open up the services window, look for anything with mysql in the name, and ensure that it is set to not attempt to start with a reboot. Then open up the running processes tab of the running programs window (use CTRL-ALT-DEL to open) and look for and kill running processes with mysql in the name.

If you are satisfied that there are no more running mysql processes you can continue. You may want to reboot to be certain no more running mysql processes are hanging around.

At this time you should be able to reinstall and start mysql.

score 0 · Answer 7 · answered Sep 14 '13 at 07:35

Run mysql-test-run and you should see the following output :

./mysql-test-run
Logging: ./mysql-test-run  
2013-09-14 03:39:32 2566 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/fr/sn4l2f393_v_ytcwkb0lx7_40000gp/T/_M2mZFQPjV/ is case insensitive
2013-09-14 03:39:32 2566 [Note] Plugin 'FEDERATED' is disabled.
2013-09-14 03:39:32 2566 [Note] Binlog end
2013-09-14 03:39:32 2566 [Note] Shutting down plugin 'MyISAM'
2013-09-14 03:39:32 2566 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.13
Checking supported features...
 - SSL connections supported
Using suites: main,sys_vars,binlog,federated,rpl,innodb,innodb_fts,perfschema,funcs_1,opt_trace,parts,auth_sec
Collecting tests...
 - adding combinations for binlog



 - adding combinations for rpl
Removing old var directory...
Creating var directory '/Users/das/mysql/mysql-5.6.13-osx10.7-x86_64/mysql-test/var'...
Installing system database...
Using server port 50409

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

score 0 · Answer 8 · answered Oct 09 '14 at 12:37

If you move your datadir, you not only need to give the new datadir permissions, but you need to insure all parent directories have permission.

I moved my datadir to a hard drive, mounted in Ubuntu as:

/media/*user*/Data/

and my datadir was Databases.

I had to set permissions to 771 to each of the media, user and Data directories:

sudo chmod 771 *DIR*

If this does not work, another way you can get mysql to work is to change user in /etc/mysql/my.cnf to root; though there are no doubt some issues with doing that from a security perspective.

MySQL: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

8 Answers8