How can I recover the database?



  • I have a database that is around 5.8 GB in size. The system I was using originally was Ubuntu 14, and the database MySQL 5.5 with MyISAM, with Apache 2 and PHP 5. It had incredible number of locks on tables that we had registered, and the queries were hung without being able do anything except restarting Apache or MySQL.

    We received a suggestion to upgrade to Debian 10 and MariaDB 10.4 with the InnoDB engine. At the beginning it was seen that everything was going very fast, but soon we realized that it corrupted the data and the users could not work that way, despite the fact that it was very fast and there were no blocks; the users had data inconsistency.

    We returned to the environment of Ubuntu and the MyISAM engine. From that point the database has been worse, it crashes constantly and we have had to survive by restarting every 10 or 15 minutes because the users are blocked.

    At this point we believe that the database is at the point of no return. How can we save our system and recover the data that is consistent. I have made the backups with mysqldump.



  • Let's avoid death.

    • Wherever possible, switch from MyISAM to InnoDB for your tables. InnoDB locks rows, not tables; that makes the "lock" problem much less. InnoDB rarely hangs in any way and it recovers gracefully even after a power failure.
    • Then lower key_buffer_size and raise innodb_buffer_pool_size.
    • Ubuntu 14? As in 2014? That's quite old. Plan for upgrading it, MySQL/MariaDB, PHP, etc.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2