When MySQL server goes wrong, your first instinct maybe check the error log file. However, if the server fails to start due to incorrect options, you might not find any messages in the error log file, as even the option to specify the error file might not have worked.
oracle@Yuan mysql$ sudo systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
Here is a step-by-step approach to diagnose startup issue.
You can manually check the MySQL configuration file for incorrect options, but a more efficient way is to use --print-default option to make MySQL server daemon ( mysqld) list the options it uses, displaying the output on the console. Below is an example:
oracle@Yuan mysql$ /usr/sbin/mysqld --print-defaults
/usr/sbin/mysqld would have been started with the following arguments:
--innodb_redo_log_capacity=200M --innodb_print_ddl_logs=1 --log_slow_extra=ON --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --server-id=39 --port=3306 --basedir=/usr --datadir=/u01/mysql --tmpdir=/tmp --skip-name-resolve --default_time_zone=+8:00 --character-set-server=UTF8MB4 --innodb_buffer_pool_size=20G --innodb_buffer_pool_instances=8 --innodb_log_file_size=1G --innodb_io_capacity=5000 --innodb_io_capacity_max=10000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_lock_wait_timeout=10 --innodb_rollback_on_timeout=1 --innodb_print_all_deadlocks=1 --innodb_status_output=0 --innodb_status_output_locks=1 --innodb_sort_buffer_size=67108864 --innodb_adaptive_hash_index=OFF --innodb_flush_log_at_trx_commit=1 --lock_wait_timeout=3600 --open_files_limit=65535 --back_log=1024 --max_connections=512 --max_connect_errors=1000000 --table_open_cache=10240 --table_definition_cache=10240 --thread_stack=512K --sort_buffer_size=8M --join_buffer_size=8M --read_buffer_size=16M --read_rnd_buffer_size=16M --bulk_insert_buffer_size=64M --thread_cache_size=768 --interactive_timeout=600 --wait_timeout=600 --tmp_table_size=96M --max_heap_table_size=96M --max_allowed_packet=128M --skip-innodb-adaptive-hash-index=1 --performance_schema_instrument=%memory%=on --performance_schema_instrument=%lock%=on --performance-schema-consumer-events-stages-history-long --log_error=/var/log/mysql/error.log --log_error_verbosity=2 --slow_query_log=1 --long_query_time=2 --expire_logs_days=30 --log_bin=on --sync_binlog=200 --binlog_format=ROW --binlog_rows_query_log_events=1 --master_info_repository=TABLE --relay_log_info_repository=TABLE --gtid_mode=on --enforce_gtid_consistency=1 --log_salve_updates --relay_log_recovery=1
If there are so many options, you still can’t pinpoint which option is incorrect, you can add all these options (except --log_error) to the command to start MySQL server. This way, any error output will be on console:
oracle@Yuan mysql$ sudo mysqld --no-defaults --user mysql --innodb_redo_log_capacity=200M --innodb_print_ddl_logs=1 --log_slow_extra=ON --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --server-id=39 --port=3306 --basedir=/usr --datadir=/u01/mysql --tmpdir=/tmp --skip-name-resolve --default_time_zone=+8:00 --character-set-server=UTF8MB4 --innodb_buffer_pool_size=20G --innodb_buffer_pool_instances=8 --innodb_log_file_size=1G --innodb_io_capacity=5000 --innodb_io_capacity_max=10000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_lock_wait_timeout=10 --innodb_rollback_on_timeout=1 --innodb_print_all_deadlocks=1 --innodb_status_output=0 --innodb_status_output_locks=1 --innodb_sort_buffer_size=67108864 --innodb_adaptive_hash_index=OFF --innodb_flush_log_at_trx_commit=1 --lock_wait_timeout=3600 --open_files_limit=65535 --back_log=1024 --max_connections=512 --max_connect_errors=1000000 --table_open_cache=10240 --table_definition_cache=10240 --thread_stack=512K --sort_buffer_size=8M --join_buffer_size=8M --read_buffer_size=16M --read_rnd_buffer_size=16M --bulk_insert_buffer_size=64M --thread_cache_size=768 --interactive_timeout=600 --wait_timeout=600 --tmp_table_size=96M --max_heap_table_size=96M --max_allowed_packet=128M --skip-innodb-adaptive-hash-index=1 --performance_schema_instrument=%memory%=on --performance_schema_instrument=%lock%=on --performance-schema-consumer-events-stages-history-long --log_error_verbosity=2 --slow_query_log=1 --long_query_time=2 --expire_logs_days=30 --log_bin=on --sync_binlog=200 --binlog_format=ROW --binlog_rows_query_log_events=1 --master_info_repository=TABLE --relay_log_info_repository=TABLE --gtid_mode=on --enforce_gtid_consistency=1 --log_salve_updates --relay_log_recovery=1
2024-08-07T02:56:39.118864Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2024-08-07T02:56:39.118895Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2024-08-07T02:56:39.118907Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2024-08-07T02:56:39.122585Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 56667
2024-08-07T02:56:39.131240Z 0 [Warning] [MY-013869] [InnoDB] Ignored deprecated configuration parameter innodb_log_file_size. Used innodb_redo_log_capacity instead.
2024-08-07T02:56:39.134288Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-07T02:56:41.511535Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-07T02:56:41.907107Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-08-07T02:56:41.907183Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-08-07T02:56:41.910645Z 0 [ERROR] [MY-000068] [Server] unknown option '--log_salve_updates'.
2024-08-07T02:56:41.910721Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-08-07T02:56:43.949602Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31) MySQL Community Server - GPL.
The output indicates unknown option '--log_salve_updates' , which is obviously a typo.
Note: On windows, you should use --console option to direct the default error log output to be the console. This option is not necessary on Linux.
Even if all options are correct, the MySQL server can still fail to start due to incorrect relationships between these options. In such cases, you can identify the error by method of gradual elimination, i.e. , inputting part of the options and attempting multiple times.
Yuan Yao, An Oracle ACE's Blog 
Leave a comment