1

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

3
  • mysql tries, but gets an error from the operating system, so it has no option than to stop. Did you check the harddrive for smart errors
    – nbk
    Jan 19 at 19:42
  • I deleted "ibdata1", "ib_logfile0", "ib_logfile1", "ib_buffer_pool", and "ibtmp1"
    – Matt
    Jan 20 at 19:37
  • Some questions after deleting /var/lib/mysql you lost all your databases? Do you have a backup? when ibdata1 is corrupted or deleted you cant start the server again(File ./ibdata1: 'open' returned OS error 71) and as a consequence of that you also cant to take a new dump. How do you synchronize the new ibdata1 with your old databases? Aug 4 at 2:40
0

I ended up resolving it by deleting the /var/lib/mysql directory and running

mysqld --initialize --datadir=/var/lib/mysql
0

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.