How to recover a MySQL or Percona database backup

Escrito na categoria "How to fix" por Alex Benfica.

You must create periodic backups of every database instance you have. No matter where you run the database keeping multiple backups is essential to allow you to recover the data in case of an accident. I have databases running on a local server and from time to time I might interrupt the energy or power off the server in a less than desired way. I have backups and now I explain the steps I used to recover the backups.

The infrastructure I have runs using a docker-compose file using the powertic/percona-docker image. This docker compose also maps a local directory to the the /var/lib/mysql directory inside the container. This ensures that no MySQL data is saved in the container but stored in the host machine. By the way the host machine in this case is also a Linux virtual machine via VMWare Workstation Player.

So… I had many corrupted tables with many scary error messages every time the container started. I tried many commands to recover but as the number of tables was too big, a lot of manual SQL or mysql commands would be necessary. I then decided to full re-create the database from the .sql backup file.

This is an example of the ugly messages I had.

If you see no message after this, something went terrible wrong!

I can see a sense of humor here: If you see no message after this, something went terrible wrong!

Steps that I followed:

After running all this, I did some security configurations related to the directory permissions and limited the access to the containers to the application that needed it. Lesson learned: keep backups and store the steps you need to follow to avoid having to search each one again when the problem repeats later.

I got many error message during these steps:

By following the steps above you should be able to overcome each one.

Sobre o autor

Autor Alex Benfica

IT professional with over 20 years of experience in industry. BSc in Computational Mathematics, always learning and enthusiastic about software development and automation. Full stack developer proficient in Javascript, Node, React, Python, Docker, API design and many more!

Leave a Reply