0

I have installed MySQL 8.0.17 via the official installer for Windows.

I am starting MySQL like this

mysqld --log-error-verbosity=3 \
         --console \
         --defaults-file='c:/mysql/etc/my.cnf' \
         --bind-address=127.0.0.1

I have configured in my.cnf these directories

datadir="C:/mysql/data/"
general_log_file="C:/mysql/log/mysql-general.log"
slow_query_log_file="C:/mysql/log/mysql-slow.log"
log-error="C:/Users/mysql/log/mysql-err.log"
log-bin="c:/Users/mysql/log/mysql-bin.log"

and please note that c:/mysql is created by me for the logs, data-directory and configuration file. The installer has installed MySQL to some other directory.

After starting mysqld with above line I get this response:

2019-10-22T08:08:08.808008Z 0 [Note] [MY-010098] [Server] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-10-22T08:08:08.808064Z 0 [Note] [MY-010949] [Server] Basedir set to C:\Program Files\MySQL\MySQL Server 8.0\.
2019-10-22T08:08:08.808076Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.17) starting as process 3412
2019-10-22T08:08:08.824282Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2019-10-22T08:08:08.826990Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2019-10-22T08:08:08.827278Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-22T08:08:08.828633Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-10-22T08:08:08.828657Z 0 [Note] [MY-010120] [Server] Binlog end
2019-10-22T08:08:08.828897Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'
2019-10-22T08:08:08.828913Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'InnoDB'
2019-10-22T08:08:08.828924Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'
2019-10-22T08:08:08.829690Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.17)  MySQL Community Server - GPL.

I am pretty sure that I got the server running the last time I tried (which was also the first time for me). However I have just CTRL-C the server in the console window when I was done. Is it possible that the data-directory got corrupted / or needs some cleaning? How can I get more verbose logs? E.g. I would like to get verification that the correct configuration file is picked up and the data-directory is really the one I thought I have configured.

EDIT: My complete my.cnf

[client]
port=3306

[mysql]
no-beep

[mysqld]
port=3306
datadir="C:/mysql/data/"
default_authentication_plugin=caching_sha2_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=1
general_log_file="C:/mysql/log/mysql-general.log"
slow-query-log=1
slow_query_log_file="C:/mysql/log/mysql-slow.log"
long_query_time=10
log-error="C:/mysql/log/mysql-err.log"
log-bin="c:/mysql/log/mysql-bin.log"
server-id=1
report_port=3306
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=52M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=95M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

EDIT2: content of the expected data-directory:

drwxr-xr-x+ 1 someuser Domain Users        0 Oct 18 11:44 '#innodb_temp'
-rwxr-xr-x+ 1 someuser Domain Users        6 Oct 18 11:44  ac-mpfeiffer01.pid
-rwxr-xr-x+ 1 someuser Domain Users       56 Oct 18 11:36  auto.cnf
-rwxr-xr-x+ 1 someuser Domain Users     1112 Oct 18 11:36  ca.pem
-rwxr-xr-x+ 1 someuser Domain Users     1680 Oct 18 11:36  ca-key.pem
-rwxr-xr-x+ 1 someuser Domain Users     1112 Oct 18 11:36  client-cert.pem
-rwxr-xr-x+ 1 someuser Domain Users     1676 Oct 18 11:36  client-key.pem
-rwxr-xr-x+ 1 someuser Domain Users      992 Oct 18 11:36  ib_buffer_pool
-rwxr-xr-x+ 1 someuser Domain Users 50331648 Oct 18 16:42  ib_logfile0
-rwxr-xr-x+ 1 someuser Domain Users 50331648 Oct 18 11:36  ib_logfile1
-rwxr-xr-x+ 1 someuser Domain Users 12582912 Oct 18 12:27  ibdata1
-rwxr-xr-x+ 1 someuser Domain Users 12582912 Oct 18 16:42  ibtmp1
drwxr-xr-x+ 1 someuser Domain Users        0 Oct 18 11:36  mysql
-rwxr-xr-x+ 1 someuser Domain Users 24117248 Oct 18 12:27  mysql.ibd
drwxr-xr-x+ 1 someuser Domain Users        0 Oct 18 11:36  performance_schema
-rwxr-xr-x+ 1 someuser Domain Users     1676 Oct 18 11:36  private_key.pem
-rwxr-xr-x+ 1 someuser Domain Users      452 Oct 18 11:36  public_key.pem
-rwxr-xr-x+ 1 someuser Domain Users     1112 Oct 18 11:36  server-cert.pem
-rwxr-xr-x+ 1 someuser Domain Users     1680 Oct 18 11:36  server-key.pem
drwxr-xr-x+ 1 someuser Domain Users        0 Oct 18 11:36  sys
-rwxr-xr-x+ 1 someuser Domain Users 10485760 Oct 18 12:27  undo_001
-rwxr-xr-x+ 1 someuser Domain Users 10485760 Oct 18 12:27  undo_002
drwxr-xr-x+ 1 someuser Domain Users        0 Oct 18 12:26  world
  • I have configured in my.cnf this data-directory but all data folders/files remains on default location? – Akina Oct 22 at 8:18
  • @Akina thanks for the point. I have added information. – Matthias Oct 22 at 9:08
  • You use datadir="C:/Users/matthias.pfeifer/mysql/data/". Does this folder now contains folders for system databases (mysql, sys, performance_schema) with their files in? does it contains InnoDb system tablespace (ibdata)? – Akina Oct 22 at 9:14
  • @Akina: I have added the directory content, please see EDIT2 above – Matthias Oct 22 at 10:24
  • @Akina i am using this data dir that you mention - I tried to keep my realname out of the data. I did not manage to do so when pasting my.cnf... looks a bit messy now... – Matthias Oct 22 at 10:29
0

The option

--defaults-file='c:/mysql/etc/my.cnf' \

is one of the options that must be given as the first argument. This solved my problem but does not answer the question how to get more verbose logging...

  • On Oct 23, 2019 at 1:42, Rick James has a suggestion for you. You may also wish to add to my.cnf in [mysqld] section log_warnings=2 for disconnects to be recorded in your error log. – Wilson Hauck Nov 11 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.