Mydumper Works with Dolt

December 13, 2024

4 min read

Dolt is a drop in-replacement for MySQL, which means it can be used with any MySQL-compatible tool. One such tool is Mydumper and its counterpart Myloader. Mydumper is a MySQL Logical Backup tool that emphasizes parallelism and performance.

In this guide, we'll show you how to use Mydumper with Dolt to import/restore your Dolt databases from a MySQL dump. Additionally, we'll discuss some of the challenges and bug fixes required to get Mydumper working with Dolt.

Setup

You can install Mydumper through Homebrew like so:

$ brew install mydumper

Alternatively, there are various installation methods listed on their README.

Mydumper requires a tls_key and tsl_cert, so we'll have to get one and configure our dolt server to use them. If you really wanted to, you could create and sign your own TLS certificates, but for the sake of this example, we can just use some testing ones in the dolt repo. Download chain_cert.pem and chain_key.pem and place them in the employees directory.

Next, we'll need to create a config.yaml file in the employees directory.

$ cd employees
$ touch config.yaml

Add the following to the config.yaml file:

log_level: info

behavior:
  read_only: false
  autocommit: true
  disable_client_multi_statements: false
  dolt_transaction_commit: false
  event_scheduler: "ON"

user:
  name: ""
  password: ""

listener:
  host: localhost
  port: 3306
  max_connections: 100
  read_timeout_millis: 28800000
  write_timeout_millis: 28800000
  tls_key: './chain_key.pem'
  tls_cert: './chain_cert.pem'
  require_secure_transport: null
  allow_cleartext_passwords: null

max_logged_query_len: 0

data_dir: .
cfg_dir: .doltcfg
privilege_file: .doltcfg/privileges.db
branch_control_file: .doltcfg/branch_control.db

# Advanced Configuration
metrics:
  labels: {}
  host: null
  port: -1

remotesapi:
  port: null
  read_only: null

system_variables: {}

user_session_vars: []

jwks: []

# Cluster configuration has required defaults.
# cluster: {}

This is just the default configuration file, taken from our docs. However, the tls_key and tls_cert fields are set to their respective filepaths.

Load

Let's say we want to import a Mydumper dump into dolt. Conveniently, I have one here that I can use:

$ ls -l export-20241210-144454
total 368008
-rw-r-----  1 james  staff        315 Dec 10 14:44 employees-schema-create.sql
-rw-r-----  1 james  staff        421 Dec 10 14:44 employees.departments-schema.sql
-rw-r-----  1 james  staff        443 Dec 10 14:44 employees.departments.00000.sql
-rw-r-----  1 james  staff        665 Dec 10 14:44 employees.dept_emp-schema.sql
-rw-r-----  1 james  staff   14160104 Dec 10 14:44 employees.dept_emp.00000.sql
-rw-r-----  1 james  staff        669 Dec 10 14:44 employees.dept_manager-schema.sql
-rw-r-----  1 james  staff       1260 Dec 10 14:44 employees.dept_manager.00000.sql
-rw-r-----  1 james  staff        504 Dec 10 14:44 employees.employees-schema.sql
-rw-r-----  1 james  staff   17723057 Dec 10 14:44 employees.employees.00000.sql
-rw-r-----  1 james  staff        529 Dec 10 14:44 employees.salaries-schema.sql
-rw-r-----  1 james  staff  118693274 Dec 10 14:44 employees.salaries.00000.sql
-rw-r-----  1 james  staff        533 Dec 10 14:44 employees.titles-schema.sql
-rw-r-----  1 james  staff   21708958 Dec 10 14:44 employees.titles.00000.sql
-rw-r--r--  1 james  staff       2458 Dec 10 14:44 metadata

This is a dump of the employees database, from here.

In one terminal, start a dolt server like so:

$ dolt sql-server --config config.yaml

In another terminal, we run Myloader like so:

$ myloader --user root --regex '^(?!(mysql\.))' --d export-20241210-144454

And boom all the data is there!

$ dolt sql -q "show tables"
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+

Challenges

Initally, this process was nowhere near as smooth; we encountered a variety of places where Dolt differed than MySQL, causing problems with Mydumper. These were in somewhat unexpected areas and tested areas of Dolt that we typically overlooked; now, Dolt is better for it.

Explain

For the first time, a tool actually relied on the output from an EXPLAIN statement. In fact, it actually segfaulted when it didn't get the expected output.

Dolt's EXPLAIN syntax returns what we call a plan in a Tree format; this illustrates the various steps the analyzer will take to execute the query. Meanwhile, MySQL's output is a table with details on the query plan, like so:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.0032 sec)

Specifically, Mydumper relied on the existence of the rows column. So, we implemented MySQL's EXPLAIN schema with dummy data to satisfy Mydumper and moved our existing syntax to a new EXPLAIN PLAN query.

Versioning and Deprecated Syntax

We encountered some errors around deprecated MySQL syntax. Specifically, Mydumper relied on SHOW SLAVE STATUS for replication information, but MySQL has deprecated this in favor of SHOW REPLICA STATUS. It turns out Mydumper actually checks for the version of MySQL, so we updated Dolt's reported version from 8.0.11 to 8.0.23. Additionally, we added a supported for the deprecated syntax just in case.

Lastly, Mydumper found some holes in our REPLICA syntax. Namely, we were couldn't parse stop replica io_thread and stop replica sql_thread. The fix was just a simple parse and no-op.

Conclusion

Mydumper works with Dolt! Using Mydumper with Dolt is relatively straight forward. We're a drop-in replacement for MySQL, so we can be used with any MySQL-compatible tool. Sometimes this requires some bug fixes and changes to Dolt, but we're always looking to improve. If you have any questions or need help, feel free to reach out to us on Discord.

Blog