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.
Steps that I followed:
- stop the containers on the docker-compose file
- docker-compose down
- full backup the mapped directory on the host machine (just in case you still need it later). Simply rename the mysql mapped directory to something else
- re-create the same directory
- change the permissions to allow full write to the folder. You can tune the permissions later but this will save you some headache during the process of restoring the backup
- chmod 777 -R /hosting/path/to/folder/mysql
- copy the .sql backup file to inside the mapped mysql directory. This will ensure it will be available from inside the container when you need it later.
- restart the docker container using the same docker-compose file and credentials (a database should be created)
- go inside the container
- docker exec -it <container_name> bash
- now, connect to the database using the mysql command (as you are running this from inside the container, localhost and the default 3306 MySQL port is assumed)
- mysql -u root
- you are now with a prompt like this: mysql>
- create a database with the same name of the one you are trying to restore. The .sql used for restoring might not have the instruction to create the database itself.
- create database <database_name>
- run the .sql file against the empty database. This will run each command in the .sql file and populate the database with the tables and all data. This can take a while depending on the size of your backups. It took around 5 minutes on my 10GB .sql file running on a very powerful computer using a M.2 SSD storage media. YMMV.
- source <backup.sql>
- add full permissions for the root user so that you can connect from the outside. You might want to skip this or better tune it depending on your security requirements or your system configuration.
- GRANT ALL PRIVIlEGES ON *.* TO “root”@"%";
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:
- Host ’localhost’ is not allowed to connect to this MySQL server
- The value specified for generated column ‘’ in table ‘’ is not allowed
- mysqld: Can’t create/write to file ‘/var/run/mysqld/mysqld.pid’
- option –initialize specified but the data directory has files in it. Aborting
- ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
- mysql error code 2013 lost connection to mysql server during query
By following the steps above you should be able to overcome each one.