yobasystems / alpine-mariadb

MariaDB running on Alpine Linux [Docker]
https://hub.docker.com/r/yobasystems/alpine-mariadb/
242 stars 72 forks source link

How to set Innodb_force_recovery? #50

Closed NaruZosa closed 1 year ago

NaruZosa commented 3 years ago

Hi there, It seems my container didn't stop gracefully, which has resulted in the following error and restart loop:

[ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 5160395 is in the future! Current system log sequence number 3981222.
[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

The mariadb knowledge base suggests setting innodb_force_recovery to 1, 2, 3 or 4 then starting MariaDB to attempt automatic repairs. How would I go about setting this for the docker container?

dominictayloruk commented 3 years ago

Not sure, would be easier restoring from a backup.

NaruZosa commented 3 years ago

Except when the past fortnight of backups face the exact same issue. Restoring from a backup means losing a fortnight of prod data.

Only once the container was restarted did the issue occur, until then it acted like nothing was wrong.

dominictayloruk commented 3 years ago

If you create a custom config file called my.cnf and map it to /etc/my.cnf within the container, that has this within it;

innodb_force_recovery=1

Would look something like


mysql:
  image: yobasystems/alpine-mariadb
  environment:
    MYSQL_ROOT_PASSWORD: 
    MYSQL_DATABASE: 
    MYSQL_USER: 
    MYSQL_PASSWORD: 
  expose:
    - "3306"
  volumes:
    - /data/example/mysql:/var/lib/mysql
    - /data/example/my.cnf:/etc/my.cnf
  restart: always
dominictayloruk commented 3 years ago

Little bit more info

https://mariadb.com/kb/en/innodb-recovery-modes/

https://mariadb.com/kb/en/innodb-system-variables/#innodb_corrupt_table_action

https://bobbyiliev.com/blog/start-mysql-mariadb-recovery-mode-due-to-innodb-coruption-on-cpanel