It is time to double check your backups! Every so often you should randomly check that data you have been backing up. Set up some fresh bare metal or cloud based fresh metal and get your MySQL instance running. Simple, easy, and no problem right?
Well, I hope that is true. But please give it a try, just to put your mind at ease.
I had a call this morning from an old friend who received an automated message overnight that a software build failed. After some digging, the cause of the failure was tracked to an on premises system with hardware issues. Luckily spare parts were on hand and the server was soon back on line and the build completed.
Then the friend doubled checked their cron based backup scripts and the logs looked good. A spare machine was fired up and the friend decided to show some new hires how to restore a backup. The new machine was built with a later version of the Linux distribution the company favored. By default that distro enables a stricter version of selinux than used in their shop. Some coffee and RTFM time later, they were able to install their version of MySQL (8.0.24) and have it run. They added their configuration settings to the configuration file and were ready to reinstall. Right?
Do to some network configuration issues they could not reach their backup machine and they highly restricted access to this machine. That is a great practice to secure your data. But in this case they had to wait for their security team to end their Monday morning standup to add the new machine to the allowed server list.
Then they only had ten gig of data to restore from a mysqldump. Just a simple mysql -u root -p < 10gigbackup.sql and we are ready to roll, right? Alas, nope. The accounts used for this server had not been setup on the server (Please see https://github.com/lefred/mysqlshell-plugins/wiki/user for how to do this easily with the MySQL Shell), were not part of the dump, and that took a little more time to get them setup.
By now the new hires were referring to the internal documentation on database restorations and making revisions.
The restoration with the dump file finished. Done? Nope. The old friend randomly checked a few dozen records and was happy for about five minutes. Then one of the new hires asked about Instance Dump Utility (https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html). The new hire started asking some good questions.
- Do we need to update our backup methods?
- Should we be saving the account records with the data?
- Is the new utility feature rich enough to switch to that as the backup methodology?
- Can any of this be automated?
- How does this fit with our corporate methodology, including security practices?
Hopefully you can do a quick check on your backups even if it is for your peace of mind.