Failed to find valid data directory. MySQL generic binary installion

Question

Im going to install mysql to linux server. But I dont have root access to that server. So I created two folders called mysql and mysqldata. mysql folder holds binary files. mysqldata folder holds data and the logs.

my.cnf

[mysqld]
user                    = mysql
port                    = 3306
bind-address            = localhost
basedir                 = /home/nwn/mysql/mysql-8.0
socket                  = /home/nwn/mysqldata/instA/socket/mysql.sock
datadir                 = /home/nwn/mysqldata/instA/data
tmpdir                  = /home/nwn/mysqldata/instA/tmp
secure_file_priv        = /home/nwn/mysqldata/instA/mysql-files
max_connections         = 150

# Logging
log-bin                 = /home/nwn/mysqldata/instA/logs/instA-binlog
log-error               = /home/nwn/mysqldata/instA/logs/instA-errorlog.err
slow_query_log          = 1
slow_query_log_file     = /home/nwn/mysqldata/instA/logs/instA-slowquery.log
long_query_time         = 0.5

# InnoDB
innodb_data_home_dir    = /home/nwn/mysqldata/instA/innodb/data
innodb_data_file_path   = ibdata1:50M;ibdata2:12M:autoextend:max:500M
innodb_log_group_home_dir = /home/nwn/mysqldata/instA/innodb/log
innodb_buffer_pool_size = 32M

# MyISAM
key_buffer_size         = 16M

server_id                = 1

I did all the other configurations. when I run following command

mysql-8.0]$ bin/mysqld --defaults-file=~/mysqldata/instA/my.cnf --initialize-insercure

I have following logs in the error_log

 cat ~/mysqldata/instA/logs/instA-errorlog.err
2018-10-09T10:39:51.127424Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8160)
2018-10-09T10:39:51.127523Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 432 (requested 4000)
2018-10-09T10:39:51.383986Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2018-10-09T10:39:51.384043Z 0 [System] [MY-010116] [Server] /home/nwn/mysql/mysql-8.0/bin/mysqld (mysqld 8.0.12) starting as process 32654
2018-10-09T10:39:51.386625Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2018-10-09T10:39:51.394675Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2018-10-09T10:39:51.394817Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-10-09T10:39:51.394831Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-10-09T10:39:51.395363Z 0 [System] [MY-010910] [Server] /home/nwn/mysql/mysql-8.0/bin/mysqld: Shutdown complete (mysqld 8.0.12)  MySQL Community Server - GPL.

Answer 1

I had a similar issue where say Failed to find valid data directory.

1. On path MySQL server, delete all content from the folder data.
2. In case you dont have that folder, then create a new one.
3. run mysqld --initialize --console

Answer 2

What Kevin Kopf and Lazycoder_007 indicate in https://stackoverflow.com/a/62003723/7733418 worked for me fine, along with

1. deleting the files within the "data" folder before initialization (thanks to P D)
2. running mysqld --initialize in cmd as administrator

Answer 3

As far as I understand from your console output you try to initialize the database while there exist files in your data directory. First you should remove all files (please be careful here, you may loss your data) in the data directory of your MySQL, and then you should run the command below

shell> mysqld --initialize --console

as stated by @Alex Karshin, @Lazycoder_0071 and @Freeze_H. MySQL should initialize the database seamlessly if there isn't another problem.

Answer 4

(context: new setup, mysql 8 on windows server 2016) What really helped me to get rid of "Failed to find valid data directory" was:

mysqld --initialize --console

The --console part tells you straight away on the command line what is wrong and what to do. Because I had a clean install, i lacked the "data" directory. However if you have settings in your my.ini that are not supported in the newly installed version, a "data" directory will be generated nevertheless with the command listed above. If you have more then one faulty setting in my.ini, you'll need to delete the newly created "data" directory first. CAUTION: check first if you have databases in the data directory, else you'll loose all your databases!! So only use this on fresh installs.

In steps:

1. run mysqld --initialize --console from a cmd as administrator
2. fix the my.ini faulty line, delete the data directory (ONLY IF YOU HAVE A NEW INSTALLATION: else you'll loose all your databases!)
3. rerun mysqld --initialize --console
4. repeat until all errors are fixed.

Now MySql should start.

Answer 5

Please follow these steps to reset your MySQL Password on Windows:

1. Stop MySQL Service @ services.msc

2. Create file change_mysql_pwd.txt with following content (replase YourNewPassword with desired new password you want to have for root user:

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';
   

3. Save the file under C:\ change_mysql_pwd.txt

4. Run CMD.exe as Admin (Start->Cmd->Right Click->Run as Administrator)

5. Type in cmd: cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

6. Create “Data” Folder under "C:\Program Files\MySQL\MySQL Server 8.0\ (if already exists delete its contents!)

7. Type in cmd: mysqld --install

8. Type in cmd: mysqld --initialize

9. Type in cmd: mysqld --init-file=C:\\change_mysql_pwd.txt

10. Login with root user account and the password set above.

11. Delete C:\ change_mysql_pwd.txt file

Enjoy...

Answer 6

Even though the post is very old, but posting my solution as it took me around 2 hours to figure it out and this can help someone save that time.

You can try doing :

shell> mysqld --initialize

This shall initialse the data directory in the path where you have installed MySql server. Generally placed at C:\Program Files\MySQL\MySQL Server 8.0.

Hope it helps someone.