How to restore innoDB after a crash?

Question

linux

The standard situation is that the client applied with a problem on the server, in fact it turned out to be 100% of the disk occupied
. As a result, the innoDB logs refused to work as a result of the failure of the database.
After cleaning the server, the databases naturally stopped starting, reporting "111"
After mv files, mysql also refused to start.
I immediately had to write
innodb_force_recovere = 2
, while it started up quite well on it, but things didn’t go any further. An error in the InnoDB tables
. But they are locked for writing
, and mysqlchck doesn’t help either.

spoiler

You may download the Percona Server operations manual by visiting
www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
2019-02-12T15:27:56.984998Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 37080)
2019-02-12T15:27:56.985152Z 0 [Warning] Changed limits: table_open_cache: 2392 (requested 18432)
2019-02-12T15:27:57.126444Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-02-12T15:27:57.126917Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-25) starting as process 27262 ...
2019-02-12T15:27:57.132309Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2019-02-12T15:27:57.132406Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-02-12T15:27:57.132431Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-02-12T15:27:57.132448Z 0 [Note] InnoDB: Uses event mutexes
2019-02-12T15:27:57.132452Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-02-12T15:27:57.132473Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-02-12T15:27:57.132475Z 0 [Note] InnoDB: Using Linux native AIO
2019-02-12T15:27:57.132708Z 0 [Note] InnoDB: Number of pools: 1
2019-02-12T15:27:57.132810Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-02-12T15:27:57.154244Z 0 [Note] InnoDB: Initializing buffer pool, total size = 18G, instances = 8, chunk size = 128M
2019-02-12T15:27:57.352031Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-02-12T15:27:57.382065Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-02-12T15:27:57.395066Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-02-12T15:27:57.405972Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-02-12T15:27:57.433060Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=3] log sequence number 108677477433 is in the future! Current system log sequence number 21193390167.
2019-02-12T15:27:57.433095Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to dev.mysql.com/doc/refman/5.7/en/forcing-innodb-rec... for information about forcing recovery.
2019-02-12T15:27:57.433267Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 132140291279 is in the future! Current system log sequence number 21193390167.
2019-02-12T15:27:57.433286Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to dev.mysql.com/doc/refman/5.7/en/forcing-innodb-rec... for information about forcing recovery.
2019-02-12T15:27:57.433469Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=4] log sequence number 21194400206

Answer 1

Stop all apache and php-fpm, if any, so that nothing works with the base. Stop muscle itself and archive /var/lib/mysql just in case, so that if something goes wrong, you can return to the original state.
Then start the database with innodb_force_recovery from 3 to 6 and do a dump, start with 3, if the dump is not done, then increase it.
If everything is completely bad and the dump is not done even at level 6, then it is necessary to merge table by table. If it doesn’t merge anyway, often all sorts of large log tables, then you can merge the pure table schema --no-data key from them.
Then you stop everything, delete the ibdata, ib_log* files, and all files with the .ibd extension, start muscle in normal mode and download the dumps.