MySQL Logs – How to Enable Verbose Logs for mysqld Startup

MySQLwindows

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

Best Answer

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...

Related Question