MySQL Backup-Restore and PERSIST file handling

By | September 28, 2018

In Jesper’s blog on Persisted Variables …he introduced us to this new capability using the PERSIST and PERSIST_ONLY keywords.  With new MySQL features, such as PERSIST come new areas that we need to pay attention to when doing backup and restore activities.  Namely to manage the log file that the PERSIST capability uses, as it’s a configuration file that now resides in the datadir.

Backup and Restore

For Backups, everything is captured.  However, that being said the configuration files for both the server’s unique hash ID used for GTIDs (file: auto.cnf) and the PERSIST configuration log (file: mysqld-auto.cnf) both exist in the data directory.  The configurations that they define are captured, …but at this time, they are represented in alternate configuration files in the backup package, and are difficult to distinquish from all the rest.

Why is this important to remember

When we take a backup, we capture many things.  But the two files noted above naturally exist in the data directory.  What happens when we normally do a restore to a database instance?  That’s right, we delete the data directory!!!!   This means these files are likely to get deleted.

Which files you say? …these ones.

Here is what we can do about it

A little bit of scripting can go a long way.   We only need some handling for these files on the restore phase. Copy the files to a safe place before we delete the directory, and then restore them after the database files have been restored.

Conclusion

By copying those files to a safe directory out of harms way, they can easily be replaced when everything is done. We’ve probably been blowing away our auto.cnf file over and over again without this sort of handling….but may have never noticed the impact.  We’re certain to notice when we blow away key configuration entries if we don’t protect our PERSIST configuration file.

Play Safe and Happy MySQL’ing!

One thought on “MySQL Backup-Restore and PERSIST file handling

  1. Pingback: Weekly Reading for week of 10/27/2018 | Digital Owl's Prose

Comments are closed.