Mysql 8.0.23 on CentOS 7.9 with WHM
I ended up with a corrupted InnoDB table. I gave up on a long query and killed it. Then the query was still blocking so I did a forceful server reboot. I know now that the query was still blocking because it was undoing the process. Killing it with a forceful server reboot corrupted my database. The rest is history.
So I've been trying to work through this process: https://dev.iserversupport.com/recover-crashed-innodb-tables-on-mysql-database-server/
But it seems MySQL doesn't want to recreate the ibdata1 file.
my.cnf:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld] disable-log-bin=1 default-authentication-plugin=mysql_native_password performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid innodb_file_per_table=1
default-storage-engine=MyISAM interactive_timeout=300 key_cache_block_size=4096 max_heap_table_size=5G max_join_size=1000000000 max_allowed_packet=268435456 open_files_limit=40000 thread_cache_size=100 tmp_table_size=5G wait_timeout=7800 max_user_connections=500 myisam_recover_options=FORCE innodb_file_per_table=1 innodb_flush_log_at_trx_commit=0 innodb_purge_threads=1 innodb_thread_concurrency=8 pid-file=/var/lib/mysql/mysqld.pid
performance-schema=0 sql_mode='' interactive_timeout=30 wait_timeout=90
collation-server = utf8_unicode_ci character-set-server=utf8
key_buffer_size = 4G innodb_buffer_pool_size = 8G innodb_buffer_pool_chunk_size = 256M innodb_buffer_pool_instances = 8
max_connections=300
And error log:
2021-01-19T18:18:18.543601Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2021-01-19T18:18:18.543714Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2021-01-19T18:18:18.543792Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-01-19T18:18:18.543895Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2021-01-19T18:18:18.543986Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2021-01-19T18:18:19.908326Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 1777
2021-01-19T18:18:19.909547Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-01-19T18:18:19.909570Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2021-01-19T18:18:19.959205Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
This makes me think I have an ownership or read/write permissions error. But I checked the ownership on the /var/lib/mysql directory and it is mysql:mysql, permission is 751.
I was able to recover all of my data by forcing innodb_force_recovery = 3. But now that I've deleted those ibd files I can't get the server to start again either way. I did back them up, and I've tried copying them back but no dice.
I also attempted to re-install mysql via WHM. That failed.
Thank you Matt
