Will Selby • 23 Jan 2020
If you use a backup of your production database for local development and your backups are larger than a couple of gigabytes, you probably know what a pain it can be to import that database when you want to revert back to the original state.
Thankfully, help is at hand with database snapshots. These work a lot like virtual machine snapshots, enabling you to quickly and easily revert your database to an earlier state. To work with them we're going to use a collection of shell scripts called lmm. LMM is an initialism for Logical MySQL Manager, so it goes without saying that this will only work with MySQL.
If you're using Laravel Homestead for your local development then lmm is already installed (https://laravel.com/docs/master/homestead#database-snapshots).
Once you have the scripts installed, running the command
sudo lmm status (all lmm commands need to be run using sudo) you should see something like the following;
> sudo lmm status Active snapshot: /homestead-vg/master Database snapshots: master 54.79% used by MySQL databases.
This tells us we only have a single existing snapshot, called "master".
To get started we're going to create and checkout a new branch (snapshot) for our databases:
> sudo lmm branch dev Using default stripesize 64.00 KiB. Logical volume "mysql-dev" created. > sudo lmm checkout dev /homestead-vg/master is the currently active database. Setting /homestead-vg/dev as the active database.
Now you can start work on that awesome new feature; migrate tables, create, edit and delete records as you please, safe in the knowledge that you can quickly and easily revert your database to its initial state, without having to reimport everything again.
Rather than create a duplicate of your database, LLM only stores the difference between your current branch and the original "master" branch, so additional storage usage will be minimal.
Once you get to the point where you want to revert your database to the state it was in after you imported your backup, all you need to do is run
sudo lmm merge master dev.
Warning #1: If you use git, then the names of these commands will be familiar to you, but beware that this doesn't perform a merge in the same way as git. Rather than end up with some combination of data from the "master" and "dev" branches, it will simply overwrite everything in the "dev" snapshot with the contents of the "master" branch.
Warning #2: These commands affect all active databases. Any changes you have made to other databases since creating and checking out the
dev snapshot will also be reverted.
> sudo lmm merge master dev Merging /homestead-vg/master into /homestead-vg/dev ./ ib_buffer_pool 1,802 100% 0.00kB/s 0:00:00 (xfr#1, to-chk=968/972) ib_logfile0 50,331,648 100% 101.48MB/s 0:00:00 (xfr#2, to-chk=967/972) ib_logfile1 50,331,648 100% 40.10MB/s 0:00:01 (xfr#3, to-chk=966/972) ibdata1 146,800,640 100% 88.16MB/s 0:00:01 (xfr#4, to-chk=965/972) project/ project/customer_updates.ibd 230,686,720 100% 74.37MB/s 0:00:02 (xfr#5, to-chk=777/972) project/billing_transactions.ibd 12,582,912 100% 7.38MB/s 0:00:01 (xfr#6, to-chk=772/972) ... project/users.ibd 658,505,728 100% 79.40MB/s 0:00:07 (xfr#21, to-chk=332/972) mysql/innodb_index_stats.ibd 573,440 100% 328.64kB/s 0:00:01 (xfr#22, to-chk=277/972) mysql/innodb_table_stats.ibd 163,840 100% 13.02MB/s 0:00:00 (xfr#23, to-chk=275/972) deleting project/payment_actions.ibd deleting project/payment_actions.frm
That's all there is to it. You're now back to the same state you were in when you first imported your backup, and it only took a few seconds, instead of many minutes or hours.
You can find more information about lmm here.