Dolt for Beginners: Importing MySQL Dumps
The Dolt for Beginners series focuses on topics people getting started with Dolt will likely be interested in. Most of our other blog articles go pretty deep so we want a space to talk about topics that experts may find boring.
After you get Dolt installed, you'll face a fork in the road. Are you creating a new database with new tables and new rows? Or do you already have data that you want to import into Dolt to take advantage of Dolt's unique version control features? Is the data already in a MySQL database? Or is it in another format like a CSV? If you have data already in a MySQL database, this article will show you how to get it into Dolt.
Install Dolt
The first thing you need to do to follow along with this article is install Dolt. Dolt is not complicated software. There is no complicated install process. You don't need a Docker container or multiple dependencies installed. You download the single Dolt program and run it.
We have even more convenient ways to install Dolt for every platform.
Once you have Dolt installed, you need to open a terminal like "Terminal" on Mac or Powershell on Windows. Make sure Dolt is on your PATH by typing dolt. If everything is working you'll be greeted by a help message with all the valid Dolt commands. For those familiar with the Git command line, Dolt should look pretty similar.
$ dolt
Valid commands for dolt are
init - Create an empty Dolt data repository.
status - Show the working tree status.
add - Add table changes to the list of staged table changes.
diff - Diff a table.
... <trimmed for length>
... <trimmed for length>
... <trimmed for length>
reflog - Show history of named refs.
rebase - Reapplies commits on top of another base tip
ci - Commands for working with Dolt continuous integration configuration.Import a MySQL Dump
Dolt is MySQL-compatible. This means the SQL used to define tables and insert rows is MySQL-flavored. If you have an existing MySQL database, you can create what is called a "dump" which contains all the SQL needed to make a fresh database look like the database you created the dump from. This dump can be imported into Dolt quite easily.
MySQL provides some sample dump files on its website. Let's take the "world database" and import it into Dolt. First, I download the gzipped tarball, or TGZ as they call it. This is stored in my Downloads folder. I'm going to create a new directory for the world database at ~/dolt/world and move the downloaded file there. I then decompress it using tar.
$ mkdir -p ~/dolt/world
$ cd ~/dolt/world
$ mv ~/Downloads/world-db.tar.gz .
$ tar -xzf world-db.tar.gz This creates a world-db directory with a world.sql file in it.
$ ls
world-db world-db.tar.gz
$ cd world-db
$ ls
world.sqlI'm just interested in that SQL file so I'll move it to the database root and delete the directory. This isn't necessary, I just don't want extra directories laying around.
$ mv world-db/world.sql .
$ rm -r world-dbNow, I create a new Dolt database in this directory using dolt init.
$ dolt init
Successfully initialized dolt data repository.Now, the moment of truth. I will import the world.sql dump file into Dolt. The sql subcommand in Dolt processes SQL sent to STDIN. This means I can just pipe the SQL file into dolt sql and it will execute. Pipe semantics is OS specific. On my Mac, the command is dolt sql < world.sql. On Windows machines using Powershell, you can do something like Get-Content world.sql | dolt sql.
Running dolt sql < world.sql generates a few thousand lines of output mostly saying Query OK. Seems like a good sign.
$ dolt sql < world.sql
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.08 sec)
Database changed
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
...
...
...
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)The command finished without error. It worked. Now let's look at what we made.
$ ls
world world-db.tar.gz world.sql
$ cd world
$ dolt sql -q "show tables"
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+It looks like the dump created a new database called world. In Dolt, new databases are created as subdirectories of your current directory. I cd into the world directory and now I can run SQL. It looks like the dump has three tables. Let's probe a little deeper using SQL.
$ dolt sql -q "describe country"
+----------------+------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) COLLATE utf8mb4_0900_ai_ci | NO | PRI | '' | |
| Name | char(52) COLLATE utf8mb4_0900_ai_ci | NO | | '' | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') COLLATE utf8mb4_0900_ai_ci | NO | | 'Asia' | |
| Region | char(26) COLLATE utf8mb4_0900_ai_ci | NO | | '' | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint | YES | | NULL | |
| Population | int | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) COLLATE utf8mb4_0900_ai_ci | NO | | '' | |
| GovernmentForm | char(45) COLLATE utf8mb4_0900_ai_ci | NO | | '' | |
| HeadOfState | char(60) COLLATE utf8mb4_0900_ai_ci | YES | | NULL | |
| Capital | int | YES | | NULL | |
| Code2 | char(2) COLLATE utf8mb4_0900_ai_ci | NO | | '' | |
+----------------+------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
$ dolt sql -q "select * from country limit 3"
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+-------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+-------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+-------------------------+---------+-------+It looks like everything is working great and we now have a database of cities, countries, and languages.
Create Your Own Dump
As you've seen, importing a MySQL dump to Dolt can be achieved using a single command. If you have an existing MySQL database and need to create a dump file, there's a lot of good resources online, including MySQL's thorough documentation. You'll need MySQL installed to get access to the mysqldump command line interface (CLI). Follow MySQL's instructions to get it installed on your own machine.
Let's serve this database we just created and create a dump from it as an example. Dolt databases can also be dumped using mysqldump. First we run dolt sql-server to start a MySQL-compatible server. The default port for the server is 3306. Once we run dolt sql-server, the terminal will just hang there. That's what you want to see.
$ dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"Open a new terminal to run the mysqldump. mysqldump needs the MySQL connection information and the database to dump. Our database server is running on localhost port 3306 with a default root user with no password. The database we want to dump is called world. By default, mysqldump sends the dump output to the command line. You need to pipe it to a file to save it. I won't do that here. Again this command will generate a few thousand lines of output which I've shortened here.
$ mysqldump -h localhost -P 3306 -u root world
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
/*M!999999\- enable the sandbox mode */
-- MariaDB dump 10.19-11.6.2-MariaDB, for osx10.20 (arm64)
--
-- Host: localhost Database: world
-- ------------------------------------------------------
-- Server version 8.0.33
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */;
--
-- Table structure for table `city`
--
DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
...
...
...
('ZMB','Nyanja','F',7.8),
('ZMB','Tonga','F',11.0),
('ZWE','English','T',2.2),
('ZWE','Ndebele','F',16.2),
('ZWE','Nyanja','F',2.2),
('ZWE','Shona','F',72.1);
/*!40000 ALTER TABLE `countrylanguage` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */;
-- Dump completed on 2025-02-07 12:58:57As you can see, creating a dump is almost as easy as importing one. Dump your MySQL and get it into Dolt today so you can access all of Dolt's great versioning features.
Conclusion
As you can see importing a MySQL dump to Dolt is straightforward. If you have an existing MySQL database, creating a dump and importing it to Dolt is a fast way to get started with your own data. Questions? Stop by our Discord and just ask. We love helping beginners get started with Dolt.