We continue to learn things about databases, and this time, it’s time to restore the backups we’ve made of our MariaDb or MySQL databases.
In the previous entry I showed you how to work with a custom script to make the copies, and that you can include in your Windows Startup to run automatically every time you access your computer, or launch it from a scheduled task, or when you need it. The case is to have the script ready.
Today another fundamental part of backups is to prove that they work. To do this, it may be a good option to try with some empty database. I have also shown you in several entries that, from files, you can have MySQL 8 or MariaDB 10.4 (although you can do exactly the same for other versions). This will be the case.
My actual situation is like this:
Our database is ready and the backup of the databases is ready too.
Restore a backup with mysql in MySQL or MariaDB
I am not going to make any script for this because it is more feasible to do this process manually. In the case shown in this entry, we will load the inst1 data, from some .sql backup files located in D:/backupsolar/20200618, which is where we have made the copies of different databases, separating the structure (STRU files) from the data (DATA files).
On other hand, I am working with MariaDB from d:/mariadb.
Let’s open a console, and move to the path where the backup’s database are stored, and from there, let’s call mysql, writting this sentence:
Let’s move into HeidiSQL to see what’s going on:
The structure of the database has been restored with no problems. But now, let’s restore the data by using the DATA file as well:
The data loading process is longer, as almost 4 Gb of data must be processed, but in the end, the result is achieved:
Restoring from HeidiSQL
Another alternative for data restoration is to use the database manager itself, both MySQL WorkBench and HeidiSQL. In this case, the process is just as simple: we load the file to be restored.
We press “run” and we already do it. The fact that the file is small allows to load the file, but for the data, because of that file exceeds 5 Mb, HeidiSQL gives several options:
If you need to load large files, my advice is to run directly without loading into the editor. After a while, the recovered data will be available.
The option you choose will already depend on your tastes, and of course, also on the size of your databases. In my case, when I work with not very big databases, for comfort I usually use the HeidiSQL option, but to work with big files, I prefer the command line.
I hope this content will keep ypu more secure for your data by using this advices, and … happy coding!