63

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.

Jagger
  • 10,350
  • 9
  • 51
  • 93
BValluri
  • 916
  • 1
  • 6
  • 10
  • 1
    I think this [SO post](http://stackoverflow.com/questions/9083408/fatal-error-cant-open-and-lock-privilege-tables-table-mysql-host-doesnt-ex) might help.. – vmachan Jan 02 '16 at 22:56

7 Answers7

87

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]

manjeet
  • 1,497
  • 10
  • 15
  • 7
    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? – Pacerier Apr 22 '16 at 12:06
  • 2
    Found related threads: http://dba.stackexchange.com/q/115701/9405 and http://superuser.com/q/660078/78897 – Pacerier Apr 22 '16 at 12:13
  • 1
    to run `mysqld --initialize` open **terminal or command** prompt and move to `xampp/mysql/bin/` folder then run this command . – SAYE Apr 25 '18 at 21:14
  • 1. before initialization make sure the data directory is empty. 2. random password is printed to data/HOST_NAME.err – naXa stands with Ukraine Jan 17 '19 at 13:24
  • 3
    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'` – Paul Serikov Feb 16 '19 at 22:06
  • 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 – WhiteHorse Aug 10 '19 at 09:16
23

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.

Charlie
  • 8,530
  • 2
  • 55
  • 53
  • You would most likely use `--user=mysql` here to set the directory permissions to the mysql user that MariaDB runs as. – danblack Jan 29 '22 at 02:14
5

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
Kenovo
  • 86
  • 1
  • 2
2

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.

Muthahir Nawaz
  • 51
  • 3
2

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.

ESP32
  • 8,089
  • 2
  • 40
  • 61
1

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.

Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
0

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.

distinct
  • 29
  • 3