Dolt for Backups
Dolt started as a data sharing tool. Dolt is still a great data sharing tool as you can see from DoltHub Data Bounties where people around the world collaborate on building open databases.
But as Dolt got more popular, people wanted to run Dolt as a MySQL replacement. They wanted all the power of a MySQL relational database with all the version control features of a world class data sharing tool.
They wanted to give branch/merge to their customers. They were in the data business and version control allowed them to make better data, faster. Or, they were in the machine learning business and simply wanted model insurance. All these use cases require a migration. For lots of people, that's just too hard.
We think every MySQL or MariaDB could use the features of Dolt: an audit log of every cell, time travel, or cloning for local development. What if you could get Dolt features without switching databases? You can! This blog will explain how to version your MySQL dumps, or as we call it Dolt for Backups. Along the way, we'll give concrete examples of why you would want to version your MySQL dumps.
A Dump is not a Backup
I know. I know. "Dolt your Dumps" was a WAY worse title.
How to version your dumps
Set Up MySQL
We're going to use this test_db recommended by MySQL. It is a synthetic human resources style database with 300,000 employee records and 2.8M salary entries. As they say in the README:
The export data is 167 MB, which is not huge, but heavy enough to be non-trivial for testing.
I use Homebrew MySQL on my Mac. The first thing I do is start MySQL.
$ brew services start mysql
==> Tapping homebrew/services
Cloning into '/opt/homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Enumerating objects: 2353, done.
remote: Counting objects: 100% (385/385), done.
remote: Compressing objects: 100% (102/102), done.
remote: Total 2353 (delta 314), reused 294 (delta 283), pack-reused 1968
Receiving objects: 100% (2353/2353), 644.21 KiB | 2.65 MiB/s, done.
Resolving deltas: 100% (1074/1074), done.
Tapped 1 command (45 files, 810.0KB).
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)Now I clone the GitHub repo and import the data to my running MySQL using the provided instructions in the test_db repository.
$ git clone git@github.com:datacharmer/test_db.git
Cloning into 'test_db'...
remote: Enumerating objects: 120, done.
remote: Total 120 (delta 0), reused 0 (delta 0), pack-reused 120
Receiving objects: 100% (120/120), 74.27 MiB | 22.43 MiB/s, done.
Resolving deltas: 100% (62/62), done.
$ cd test_db
$ pwd
/Users/timsehn/dolthub/git/test_db
$ mysql -u root < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:17I start a MySQL shell and make sure the data is there.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use employees;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.03 sec)
mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
mysql>Looks like we're all good.
Dump MySQL
mysqldump ships with the standard MySQL install. It's a separate command line program you run by connecting to a running MySQL database. It's pretty simple. We only want the employees database we imported so we pass that as an argument.
$ mysqldump -uroot employees > employees-dump.sqlImport your Dump into Dolt
Dolt ships with an offline sql mode, dolt sql. This is the easiest way to import data in to Dolt. Just pipe the dump into dolt sql using your shell.
$ dolt init
Successfully initialized dolt data repository.
$ dolt sql < employees-dump.sql
Query OK, 9 rows affected (0.00 sec)
Query OK, 24916 rows affected (0.20 sec)
Query OK, 24916 rows affected (0.27 sec)
Query OK, 24916 rows affected (0.26 sec)
Query OK, 24911 rows affected (0.27 sec)
Query OK, 24337 rows affected (0.26 sec)
Query OK, 24337 rows affected (0.27 sec)
...
...
Query OK, 21231 rows affected (0.12 sec)
Query OK, 21250 rows affected (0.11 sec)
Query OK, 21239 rows affected (0.12 sec)
Query OK, 21229 rows affected (0.12 sec)
Query OK, 21241 rows affected (0.12 sec)
Query OK, 21254 rows affected (0.12 sec)
Query OK, 15815 rows affected (0.09 sec)
$ dolt sql -q "show tables";
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+As you can see, the data is in Dolt after about two minutes of importing.
Make a Dolt Commit
Now it's time to start using Dolt features. A Dolt commit saves the state of your current database for future reference. Once you have a commit, you can refer back to the committed version later.
$ dolt add -A
$ dolt commit -m "Initial import of employees test db"
commit 0fnljqvp2s42atonckba6fq315rphup8 (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Thu Feb 02 13:46:05 -0800 2023
Initial import of employees test db
There you have it, a permanently committed copy of the imported data. You can refer to this version by its commit hash, 0fnljqvp2s42atonckba6fq315rphup8.
Make some changes in MySQL
Now back to MySQL. I'm going to make some changes to the database and import the dump of those changes into Dolt to show off the versioning features of Dolt.
I'm going to add myself as an employee and pay myself $1,000,000!
mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select max(emp_no) from employees;
+-------------+
| max(emp_no) |
+-------------+
| 499999 |
+-------------+
1 row in set (0.00 sec)
mysql> insert into employees values (500000, '1980-02-03', 'Timothy', 'Sehn', 'M', '2023-02-03');
Query OK, 1 row affected (0.00 sec)
mysql> describe salaries;
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| salary | int | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into salaries values (500000, 1000000,'2023-02-03','2023-02-03');
Query OK, 1 row affected (0.00 sec)Dump and Import to Dolt again
This time, I'm going to skip the intermediate file employees-dump.sql and just pipe the output of dump into dolt sql. It's a one liner with no files left to clean up.
$ mysqldump -uroot employees | dolt sql Examine the diff
Now that you have a commit and have imported new data, you can see what changed between the committed version and the version you just imported. If that sounds like magic, it is!
$ dolt status
On branch main
Changes not staged for commit:
(use "dolt add <table>" to update what will be committed)
(use "dolt checkout <table>" to discard changes in working directory)
modified: dolt_schemas
modified: employees
modified: salaries
$ dolt diff
diff --dolt a/employees b/employees
--- a/employees @ 5508scksq9c6jrvfgjvb1fo9bb0cc2b4
+++ b/employees @ nak04fd5rg1jc1tc67ia3s5n0qd1vuhs
+---+--------+------------+------------+-----------+--------+------------+
| | emp_no | birth_date | first_name | last_name | gender | hire_date |
+---+--------+------------+------------+-----------+--------+------------+
| + | 500000 | 1980-02-03 | Timothy | Sehn | M | 2023-02-03 |
+---+--------+------------+------------+-----------+--------+------------+
diff --dolt a/salaries b/salaries
--- a/salaries @ 0kjejpdctgce2mtiop70268aghhkn7qt
+++ b/salaries @ ionlcu7vouvanbmjlspndhql1102sg5e
+---+--------+---------+------------+------------+
| | emp_no | salary | from_date | to_date |
+---+--------+---------+------------+------------+
| + | 500000 | 1000000 | 2023-02-03 | 2023-02-03 |
+---+--------+---------+------------+------------+You can clearly see from the diff what we changed in the database. Creating the diff is practically instantaneous given the way Dolt stores data.
If you're following along, you'll notice I ommitted the dolt_schemas table diff. That is where Dolt stores view definitions. We don't think deleting and reestablishing the same view should result in a diff. We have an open feature request to fix this if you are curious.
You can also query diffs using SQL. There are a bunch of Dolt system tables and functions to do this.
First, let's see which tables changed in the working set using the unscoped dolt_diff table.
$ dolt sql -q "select * from dolt_diff where commit_hash='WORKING'"
+-------------+--------------+-----------+-------+------+---------+-------------+---------------+
| commit_hash | table_name | committer | email | date | message | data_change | schema_change |
+-------------+--------------+-----------+-------+------+---------+-------------+---------------+
| WORKING | dolt_schemas | NULL | NULL | NULL | NULL | true | false |
| WORKING | employees | NULL | NULL | NULL | NULL | true | false |
| WORKING | salaries | NULL | NULL | NULL | NULL | true | false |
+-------------+--------------+-----------+-------+------+---------+-------------+---------------+Now let's examine what's changed in a table using the dolt_diff() table function.
$ dolt sql -q "select * from dolt_diff('HEAD', 'WORKING', 'employees')"
+-----------+---------------+---------------+--------------+-----------+--------------+-----------+----------------------------+-------------+-----------------+-----------------+----------------+-------------+----------------+-------------+-------------------------+-----------+
| to_emp_no | to_birth_date | to_first_name | to_last_name | to_gender | to_hire_date | to_commit | to_commit_date | from_emp_no | from_birth_date | from_first_name | from_last_name | from_gender | from_hire_date | from_commit | from_commit_date | diff_type |
+-----------+---------------+---------------+--------------+-----------+--------------+-----------+----------------------------+-------------+-----------------+-----------------+----------------+-------------+----------------+-------------+-------------------------+-----------+
| 500000 | 1980-02-03 | Timothy | Sehn | M | 2023-02-03 | WORKING | 2023-02-06 20:25:57.253975 | NULL | NULL | NULL | NULL | NULL | NULL | HEAD | 2023-02-02 22:49:26.637 | added |
+-----------+---------------+---------------+--------------+-----------+--------------+-----------+----------------------------+-------------+-----------------+-----------------+----------------+-------------+----------------+-------------+-------------------------+-----------+We'll use the SQL version later in this blog as we get more complicated diffs.
I'm going to make a commit now if you're following along to preserve the state of the Dolt database here.
$ dolt commit -am "Added Tim to database."
commit e907ete7v52ip2b8tcmqc2ot4blch4of (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Mon Feb 06 16:11:37 -0800 2023
Added Tim to database.QA your dumps using Dolt
Now, let's imagine something a little less innocuous. Let's imagine you have a rogue script that is updating people's historical salaries. This is happening in concert with the normal insert and update flow. You want to design a data quality assurance strategy.
Here's how the queries would look in MySQL. First a few good queries. Notice, now I'm worth $2M.
mysql> insert into employees values (500001, '1984-02-06', 'Aaron', 'Son', 'M', '2023-02-06');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (500002, '1984-02-06', 'Brian', 'Nendriks', 'M', '2023-02-06');
Query OK, 1 row affected (0.00 sec)
mysql> insert into salaries values (500001, 1,'2023-02-06','2023-02-06');
Query OK, 1 row affected (0.01 sec)
mysql> insert into salaries values (500002, 1,'2023-02-06','2023-02-06');
Query OK, 1 row affected (0.00 sec)
mysql> update salaries set salary=2000000, to_date='2023-02-06' where emp_no=500000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Now, let's update some random salaries.
mysql> update salaries set salary=salary-1 order by rand() limit 10;
Query OK, 10 rows affected (0.89 sec)
Rows matched: 10 Changed: 10 Warnings: 0Oh no, what have I done?!? With Dolt, let's find out.
$ mysqldump -uroot employees | dolt sql Let's start with the human-readable diffs.
$ dolt diff employees
diff --dolt a/employees b/employees
--- a/employees @ nak04fd5rg1jc1tc67ia3s5n0qd1vuhs
+++ b/employees @ m8g5fec0qrm9nuplt42rt1hufvkieah7
+---+--------+------------+------------+-----------+--------+------------+
| | emp_no | birth_date | first_name | last_name | gender | hire_date |
+---+--------+------------+------------+-----------+--------+------------+
| + | 500001 | 1984-02-06 | Aaron | Son | M | 2023-02-06 |
| + | 500002 | 1984-02-06 | Brian | Nendriks | M | 2023-02-06 |
+---+--------+------------+------------+-----------+--------+------------+
$ dolt diff salaries
diff --dolt a/salaries b/salaries
--- a/salaries @ ionlcu7vouvanbmjlspndhql1102sg5e
+++ b/salaries @ u3513h6kgsr7s732vkqjq1h7brc0er4m
+---+--------+---------+------------+------------+
| | emp_no | salary | from_date | to_date |
+---+--------+---------+------------+------------+
| < | 56954 | 48171 | 1996-02-20 | 1997-02-19 |
| > | 56954 | 48170 | 1996-02-20 | 1997-02-19 |
| < | 88691 | 60184 | 1996-10-28 | 1997-10-28 |
| > | 88691 | 60183 | 1996-10-28 | 1997-10-28 |
| < | 99475 | 58610 | 1996-06-20 | 1997-06-20 |
| > | 99475 | 58609 | 1996-06-20 | 1997-06-20 |
| < | 216729 | 59541 | 1993-07-01 | 1993-07-02 |
| > | 216729 | 59540 | 1993-07-01 | 1993-07-02 |
| < | 259400 | 110654 | 1993-08-25 | 1994-08-25 |
| > | 259400 | 110653 | 1993-08-25 | 1994-08-25 |
| < | 287843 | 43595 | 2000-02-14 | 2001-02-13 |
| > | 287843 | 43594 | 2000-02-14 | 2001-02-13 |
| < | 455679 | 39894 | 1996-06-11 | 1997-06-11 |
| > | 455679 | 39893 | 1996-06-11 | 1997-06-11 |
| < | 466872 | 73078 | 1997-09-02 | 1998-09-02 |
| > | 466872 | 73077 | 1997-09-02 | 1998-09-02 |
| < | 470817 | 66376 | 2002-04-08 | 9999-01-01 |
| > | 470817 | 66375 | 2002-04-08 | 9999-01-01 |
| < | 494854 | 99142 | 2000-04-17 | 2001-04-17 |
| > | 494854 | 99141 | 2000-04-17 | 2001-04-17 |
| < | 500000 | 1000000 | 2023-02-03 | 2023-02-03 |
| > | 500000 | 2000000 | 2023-02-03 | 2023-02-06 |
| + | 500001 | 1 | 2023-02-06 | 2023-02-06 |
| + | 500002 | 1 | 2023-02-06 | 2023-02-06 |
+---+--------+---------+------------+------------+Take a moment to appreciate how cool that is. You can actually see what changed. It kind of makes you think databases should just operate this way.
Now back to QA. Let's say I have a business rule that any changes to salaries can only happen for this calendar year and not in the future. If a change like that happens, I need to do some extra QA. I can now do that with a SQL query against the changes. If this query returns results, I have an issue.
$ $ dolt sql -q "select count(*) from dolt_diff('HEAD', 'WORKING', 'salaries')
where from_to_date < '2023-01-01' or from_to_date > now()"
+----------+
| count(*) |
+----------+
| 10 |
+----------+Notice how my previous change was legal.
$ dolt sql -q "select count(*) from dolt_diff('HEAD^', 'HEAD', 'salaries')
where from_to_date < '2023-01-01' or from_to_date > now()"
+----------+
| count(*) |
+----------+
| 0 |
+----------+All manner of data quality control can be implemented using SQL against what has changed.
Restore from a Dolt version
So, I've found a problem. Let's show Dolt can help you fix your production MySQL database.
First, I'm going to make a branch with the bad changes on it so I don't pollute main.
$ dolt checkout -b bad-data-found
Switched to branch 'bad-data-found'Now, I'll make a commit.
$ dolt commit -am "Bad data found in salaries table"
commit nmum77b9tlfpsckmifh4i5erestvupio (HEAD -> bad-data-found)
Author: timsehn <tim@dolthub.com>
Date: Mon Feb 06 16:35:23 -0800 2023
Bad data found in salaries table
Full Rollback
If this is really bad, I can just go back to main, do a dolt dump, and pipe a dolt dump of the previous version into MySQL
$ dolt checkout main
Switched to branch 'main'
$ dolt dump
Successfully exported data.Unfortunately, Dolt dumps now produce views in the Dolt way using the dolt_schemas table. I created an issue to give the option to create views in a MySQL compatible way but for now, we'll just delete any reference to dolt_schemas using grep.
$ cat doltdump.sql | grep -v dolt_schemas > doltdump-fixed.sqlAnd I send the fixed dump file into mysql.
$ mysql -uroot < doltdump-fixed.sqlAnd we're back, but we lost the new salaries we added.
mysql> select * from salaries where emp_no=500001;
Empty set (0.00 sec)
mysql> select * from salaries where emp_no=56954 and from_date='1996-02-20';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 56954 | 48171 | 1996-02-20 | 1997-02-19 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)Partial Rollback
Let's do a partial rollback instead so we can keep the good data we added. Back to my bad-data-found branch.
$ dolt checkout bad-data-found
Switched to branch 'bad-data-found'I put the MySQL database back to the broken state before the rollback using a dump from the head of this branch.
$ dolt dump
Successfully exported data.
$ cat doltdump.sql | grep -v dolt_schemas > doltdump-fixed.sql
$ mysql -uroot < doltdump-fixed.sqlNow on to the partial rollback. To target the bad rows, I'm going to use the dolt_diff() table function again but this time, I'm going to use it to make values I would like to roll back to.
$ dolt sql -q "select from_emp_no as emp_no, from_salary as salary, from_from_date as from_date, from_to_date as to_date
from dolt_diff('HEAD^', 'HEAD', 'salaries')
where from_to_date < '2023-01-01' or from_to_date > now()"
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 56954 | 48171 | 1996-02-20 | 1997-02-19 |
| 88691 | 60184 | 1996-10-28 | 1997-10-28 |
| 99475 | 58610 | 1996-06-20 | 1997-06-20 |
| 216729 | 59541 | 1993-07-01 | 1993-07-02 |
| 259400 | 110654 | 1993-08-25 | 1994-08-25 |
| 287843 | 43595 | 2000-02-14 | 2001-02-13 |
| 455679 | 39894 | 1996-06-11 | 1997-06-11 |
| 466872 | 73078 | 1997-09-02 | 1998-09-02 |
| 470817 | 66376 | 2002-04-08 | 9999-01-01 |
| 494854 | 99142 | 2000-04-17 | 2001-04-17 |
+--------+--------+------------+------------+Now, to rollback those rows, I'll use an update join. Notice emp_no and from_date are primary keys so I join on those.
$ dolt sql -q "update salaries
join (select from_emp_no as emp_no, from_salary as salary, from_from_date as from_date, from_to_date as to_date
from dolt_diff('HEAD^', 'HEAD', 'salaries')
where from_to_date < '2023-01-01' or from_to_date > now()) as rollback
on salaries.emp_no=rollback.emp_no and salaries.from_date=rollback.from_date
set salaries.salary=rollback.salary, salaries.to_date=rollback.to_date"
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0Now, I examine the diff to make sure everything is ok.
$ dolt diff
diff --dolt a/salaries b/salaries
--- a/salaries @ u3513h6kgsr7s732vkqjq1h7brc0er4m
+++ b/salaries @ 0rrpgu8p1dtencmnkn0ddmbr8ki4ck2v
+---+--------+--------+------------+------------+
| | emp_no | salary | from_date | to_date |
+---+--------+--------+------------+------------+
| < | 56954 | 48170 | 1996-02-20 | 1997-02-19 |
| > | 56954 | 48171 | 1996-02-20 | 1997-02-19 |
| < | 88691 | 60183 | 1996-10-28 | 1997-10-28 |
| > | 88691 | 60184 | 1996-10-28 | 1997-10-28 |
| < | 99475 | 58609 | 1996-06-20 | 1997-06-20 |
| > | 99475 | 58610 | 1996-06-20 | 1997-06-20 |
| < | 216729 | 59540 | 1993-07-01 | 1993-07-02 |
| > | 216729 | 59541 | 1993-07-01 | 1993-07-02 |
| < | 259400 | 110653 | 1993-08-25 | 1994-08-25 |
| > | 259400 | 110654 | 1993-08-25 | 1994-08-25 |
| < | 287843 | 43594 | 2000-02-14 | 2001-02-13 |
| > | 287843 | 43595 | 2000-02-14 | 2001-02-13 |
| < | 455679 | 39893 | 1996-06-11 | 1997-06-11 |
| > | 455679 | 39894 | 1996-06-11 | 1997-06-11 |
| < | 466872 | 73077 | 1997-09-02 | 1998-09-02 |
| > | 466872 | 73078 | 1997-09-02 | 1998-09-02 |
| < | 470817 | 66375 | 2002-04-08 | 9999-01-01 |
| > | 470817 | 66376 | 2002-04-08 | 9999-01-01 |
| < | 494854 | 99141 | 2000-04-17 | 2001-04-17 |
| > | 494854 | 99142 | 2000-04-17 | 2001-04-17 |
+---+--------+--------+------------+------------+Looks great right? Here's the true magic. Now I can make a patch file to apply to my production database.
$ dolt diff -r sql
UPDATE `salaries` SET `salary`=48171 WHERE `emp_no`=56954 AND `from_date`='1996-02-20';
UPDATE `salaries` SET `salary`=60184 WHERE `emp_no`=88691 AND `from_date`='1996-10-28';
UPDATE `salaries` SET `salary`=58610 WHERE `emp_no`=99475 AND `from_date`='1996-06-20';
UPDATE `salaries` SET `salary`=59541 WHERE `emp_no`=216729 AND `from_date`='1993-07-01';
UPDATE `salaries` SET `salary`=110654 WHERE `emp_no`=259400 AND `from_date`='1993-08-25';
UPDATE `salaries` SET `salary`=43595 WHERE `emp_no`=287843 AND `from_date`='2000-02-14';
UPDATE `salaries` SET `salary`=39894 WHERE `emp_no`=455679 AND `from_date`='1996-06-11';
UPDATE `salaries` SET `salary`=73078 WHERE `emp_no`=466872 AND `from_date`='1997-09-02';
UPDATE `salaries` SET `salary`=66376 WHERE `emp_no`=470817 AND `from_date`='2002-04-08';
UPDATE `salaries` SET `salary`=99142 WHERE `emp_no`=494854 AND `from_date`='2000-04-17';And I can apply it to production like so.
$ dolt diff -r sql | mysql -uroot employeesMaking sure the changes are live in the MySQL client. I have reversed the bad changes and kept the good changes!
mysql> select * from salaries where emp_no=500001;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 500001 | 1 | 2023-02-06 | 2023-02-06 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from salaries where emp_no=56954 and from_date='1996-02-20';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 56954 | 48171 | 1996-02-20 | 1997-02-19 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)I'll finish up by making a commit on my branch. Maybe I'll merge it into main later or wait for the next dump to populate main and fix my issues.
All good! Crisis averted. As you can see Dolt gives you very powerful rollback options.
Coming Soon: Replicate to Dolt
Some of you may be thinking, this all would be way cooler if I could just have a Dolt replica using standard binlog replication and configure Dolt to make a commit on every write transaction. We think so too! Replication is in preview. Come by our Discord and let us know if you want to try it.
Dolt for Backups
Using Dolt to version your MySQL dumps is easy. It requires no migration. Using Dolt to version your MySQL dumps gives you the ability to add quality assurance to your database. It also allows you to find and revert to a good version in the event of a bad change more quickly and easily. Come by our Discord and discuss!