Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

Question

I installed MySQL community server 5.7.10 using binary zip. I extracted the zip in c:\mysql and created the data folder in c:\mysql\data. I created the config file as my.ini and placed it in c:\mysql (root folder of extracted zip). Below is the content of the my.ini file

# set basedir to your installation path
basedir=C:\mysql
# set datadir to the location of your data directory
datadir=C:\mysql\data

I'm trying to start MySQL using mysqld --console, but the process is aborted with the below error.

2015-12-29T18:04:01.141930Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2015-12-29T18:04:01.141930Z 0 [ERROR] Aborting 

Any help on this will be appreciated.

Dunsinane

I think this SO post might help.. – Band

Does anyone have a solution that will work for xampp? I'm having an error with mysql, something about a missing file called servers.MAI. The mod keep suggesting this question, but it's not working, shocker. – Moving

Answer 1

You have to initialize the data directory by running the following command

mysqld --initialize [with random root password]

mysqld --initialize-insecure [with blank root password]

Hostetter

However, this is not an option for MySQL 5.6 and below. How would we deal with the error Can't open and lock privilege tables: Table 'mysql.user' doesn't exist then on such versions? What exactly is causing this issue and how can it be fixed without installation? – Engstrom

Found related threads: dba.stackexchange.com/q/115701/9405 and superuser.com/q/660078/78897 – Engstrom

to run mysqld --initialize open terminal or command prompt and move to xampp/mysql/bin/ folder then run this command . – Appointment

1. before initialization make sure the data directory is empty. 2. random password is printed to data/HOST_NAME.err – Alicea

What to do if mysqld has not --initialize and --initialize-insecure options? I got an error /usr/local/mysql/bin/mysqld: unknown option '--initialize-insecure' – Myrta

Using mysqld --initialize-insecure created data directory and default tables for mysql which was not performed by WAMP during installation resulting in non start of MYSQLD – Keeley

Answer 2

The mysql_install_db script also needs the datadir parameter:

mysql_install_db --user=root --datadir=$db_datapath

On Maria DB you use the install script mysql_install_db to install and initialize. In my case I use an environment variable for the data path. Not only does mysqld need to know where the data is (specified via commandline), but so does the install script.

Papeete

You would most likely use --user=mysql here to set the directory permissions to the mysql user that MariaDB runs as. – Henceforth

Updated command, for mysql-server 5.7 worked: mysqld --initialize --user=root --datadir=<datadir_location>. Default <datadir_location> is /var/lib/mysql – Frustrated

Answer 3

mysqld --initialize to initialize the data directory then mysqld &

If you had already launched mysqld & without mysqld --initialize you might have to delete all files in your data directory

You can also modify /etc/my.cnf to add a custom path to your data directory like this :

[mysqld]
...  
datadir=/path/to/directory

Poche

Answer 4

As suggested above, i had similar issue with mysql-5.7.18, I did this in this way:

1. Executed this command from "MYSQL_HOME\bin\mysqld.exe --initialize-insecure"

2. Then started "MYSQL_HOME\bin\mysqld.exe"

3. Connect workbench to this localhost:3306 with username 'root'

4. Then executed this query "SET PASSWORD FOR 'root'@'localhost' = 'root';

password was also updated successfully.

Hinterland

Answer 5

I had the same problem. For some reason --initialize did not work. After about 5 hours of trial and error with different parameters, configs and commands I found out that the problem was caused by the file system.

I wanted to run a database on a large USB HDD drive. Drives larger than 2 TB are GPT partitioned! Here is a bug report with a solution:

https://bugs.mysql.com/bug.php?id=28913

In short words: Add the following line to your my.ini:

innodb_flush_method=normal

I had this problem with mysql 5.7 on Windows.

Klara

Answer 6

My problem was caused by an incorrect db restore. When I dumed the db it also picked up the system mysql tables because I added a space after -p as mentioned here: mysqldump is dumping undesired system tables

Launching the docker instance would work, then I'd restore (and corrupt) the db and it would still keep running, but after restarting it would Exit with error code 1.

The solution was to dump and restore properly without the system tables.

Minicam

Thank you! I was facing exactly the same issue and this post helped me resolve it. – Adam

Answer 7

I face the same issue with version Mysql 5.7.33 when the server has rebooted. I fix it by copy other server user files scp /var/lib/mysql/mysql/user.* root@dest:/var/lib/mysql/mysql.

Chalybeate

Answer 8

I've had similar issues getting MariaDB up and running.

My system is Archlinux Linux 6.7.4-arch1-1 and MariaDB v10.5 but I built from source. Having read over the "Post-Install" scripts I wanted to make sure that everything was setup as intended by the devs.

Specifically in, scripts/mariadb-install-db, which is typically invoked as root mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql but if you're building from source (i.e. decompressing some binary or compiling) running the mariadb-install-db command will need the either --basedir or --srcdir not both.

For me, I ended up running sudo mariadb-install-db --srcdir=. --datadir=/var/lib/mysql. Since I compiled from source I had to direct the install script to all the toolings and binaries.

For what its worth if going this route you will need to update your PATH as well.

I can see you're running a Windows based system but the above should be easily adjusted to for the difference.

# Create database directories
for dir in "$ldata"
do
  if test ! -d "$dir"
  then
    if ! `mkdir -p "$dir"`
    then
      echo "Fatal error Can't create database directory '$dir'"
      link_to_help
      exit 1
    fi
    chmod 700 "$dir"
  fi
  if test -n "$user"
  then
    if test -z "$group"
    then
      chown $user $dir
    else
      chown $user:$group $dir
    fi
    if test $? -ne 0
    then
      echo "Cannot change ownership of the database directories to the '$user'"
      echo "user.  Check that you have the necessary permissions and try again."
      exit 1
    fi
  fi
done

Where $dir is --datadir and $user $group are mysql. You want to make sure that this directory is created and set with 0700 permissions (owner rwx is 0700).

With this setup you can then run the daemon. If you are still having issues there are steps to double-check from the INSTALL-BINARY file:

  41   │    The basic commands that you must execute to install and use a                                                     
  42   │    MariaDB binary distribution are:                                                                                  
  43   │                                                                                                                      
  44   │ shell> groupadd mysql                                                                                                
  45   │ shell> useradd -g mysql mysql                                                                                        
  46   │ shell> cd /usr/local                                                                                                 
  47   │ shell> gunzip < /path/to/mariadb-VERSION-OS.tar.gz | tar xvf -                                                       
  48   │ shell> ln -s full-path-to-mariadb-VERSION-OS mysql                                                                   
  49   │ shell> cd mysql                                                                                                      
  50   │ shell> chown -R mysql .                                                                                              
  51   │ shell> chgrp -R mysql .                                                                                              
  52   │ shell> scripts/mysql_install_db --user=mysql                                                                         
  53   │ shell> chown -R root .                                                                                               
  54   │ shell> chown -R mysql data                                                                                           
  55   │ shell> bin/mysqld_safe --user=mysql &  

There are very detailed instructions written up in the source

Hope this helps!

Kelcy