Migrating data from MySQL to Turso

As developers, changing the tools we normally use to build solutions is as anticipated an occurrence as the turn of seasons. In the case of databases, whatever the circumstance one faces and feels the need to change their database, there are almost always tools readily available that would allow them to facilitate the migration of their data from one database to another.

In this tutorial, we are going to learn how we can use a few tools to migrate data from a MySQL database to Turso, a process that can be done in three easy steps.

We will be doing the database migration with the assistance of the mysql2sqlite tool.

Let's get to work.

#Step 1: Create a Dump of your MySQL database

You can create a SQL dump of your MySQL database using mysqldump by running the following command:

mysqldump -u <db-user> -P <port> -h <hostname> -p --skip-extended-insert <db-name> > db_dump.sql

Use your MySQL database user and the name of the database you want to migrate in place of <db-user> and <db-name> respectively. Pass the optional options <port> and <hostname> if your database is on a remote host.

At the end of the execution of this command, you should have a SQL dump of the whole of your database in a file named db_dump.sql.

#Step 2: Convert the MySQL database dump to SQLite

Use the mysql2sqlite tool to convert the dump into a SQLite file:

./mysql2sqlite db_dump.sql | sqlite3 dump-in-sqlite.db

On completion, the command should leave you with a dump-in-sqlite.db SQLite file that you can query with sqlite3 CLI and verify data integrity:

# Open database in the SQLite shell
sqlite3 dump-in-sqlite.db

# Perform queries
> select count(*) from employees;

#Step 3: Import the database to Turso

Finally you can import the database to Turso by running the following command using the Turso CLI:

turso db create new-database --from-file dump-in-sqlite.db

On completion of the database creation process you can query your newly created Turso database using the Turso CLI:

turso db shell new-database "select count(*) from employees"

And, you can also query your database data within your applications using one of the Turso SDKs, or using the HTTP API.

Obviously, migrating larger databases involves the posibility of running into things that you might need more help with. Don't hesitate to reach out to our Discord community when you need any assistance migrating to Turso.