Will Selby  •  23 Jan 2020

MySQL Database Snapshots for Local Development in Homestead

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.

Getting Started

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".

Branching

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.

Reverting

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.

Ramble On

Remember:

You can find more information about lmm here.