Converting a Database From Mysql to Sqlite

I recently had to convert migrate for a small side project from MySql to SQLite. So I had to convert the data base. While there are some online solutions, I was not too fond on uploading a database dump to site, so I had to do it by myself. In short, this is what I had to do:

1. Take my existing dump and convert it in a format that is usable for converson.
   • For this one I spinned my own mysql database server using docker.
   • Import the dump and then export the dump using a set of parameters: --skip-extended-insert --compact
   • Clean up the mess from docker
2. Use the mysql2sqlite from https://github.com/dumblob/mysql2sqlite to convert the newly exported dump to new sqlite database file.

Convert the dump in a format that is usable for converson

Starting a docker container with MySQL and Adminer

Here is the docker-compose.yml file for starting a mysql database along with the adminer web ui admin:

version: "3.2"
services:

  mysql:
    #image: mysql:5.6.40
    build: './mysql/'
##    restart: always
    networks:
      - backend
    environment:
      - MYSQL_ROOT_PASSWORD=rootpassword
  adminer:
    image: adminer
    networks:
      - backend
    ports:
      - 8080:8080      
networks:
  backend:
#  web:
#    external: true

Instead of using the default container I'm using a custom image named Dockerfile stored in './mysql/' looks something like this. All it does is to import some scripts in the database when the container image is built:

cat mysql/Dockerfile 
FROM mysql:5.6.40

ADD ./scripts/*.sql  /docker-entrypoint-initdb.d/

That means that when the container is started the first time, all the scripts which found in /docker-entrypoint-initdb.d/ folder, are executed in alphabetical order. So we can copy our database dump in mysql folder and it will be added to the image. However, we need to be aware that the dump migh look like this:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `blabla` (...
...

But there is no database where to import the dump, so we can prepend the following lines to the dump:

CREATE DATABASE IF NOT EXISTS my_database;

USE my_database;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `blabla` (...

Another alternative is to go to localhost:8080 and access adminer. From there login using root and rootpassword, create a database and import the dump using the adminer web ui.

Exporting the dump from mysql with custom parameters.

When we run 'docker ps' we will see:

> docker ps
CONTAINER ID   IMAGE                        COMMAND                  CREATED         STATUS         PORTS                                                                      NAMES
9323ae04a807   db_mysql                     "docker-entrypoint.s…"   3 minutes ago   Up 8 seconds   3306/tcp                                                                   db_mysql_1
2dd38377ae3b   adminer                      "entrypoint.sh php -…"   3 minutes ago   Up 8 seconds   0.0.0.0:8080->8080/tcp, :::8080->8080/tcp                                  db_adminer_1

We are going to export the mysqldump with --skip-extended-insert --compact, using docker exec command. That means we run a command inside the container we specify and we redirect the output from the terminal to a file which is stored on our machine:

docker exec -it 9323ae04a807 mysqldump --skip-extended-insert --compact -prootpassword my_database > new_dump_mysql.sql

Cleaning up the docker leftover files

Once done, we have the dump, so it's a good practice to clean the mess docker did, once we no longer use it:

Using mysql2sqlite to convert the newly exported dump to a new sqlite database file

We need to use the mysql2sqlite script from https://github.com/dumblob/mysql2sqlite to convert it:

# Download convert script file:
curl -o mysql2sqlite https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite
# or wget https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite

# make it excecutable:
chmod +x mysql2sqlite

# convert the dump to a sqlite database:
./mysql2sqlite new_dump_mysql.sql | sqlite3 my_database.db

When this is done, the sqlite database is available as a file named my_database.db

Converting a Database From Mysql to Sqlite

Check how to convert a database from MySql to Sqlite using mysql2sqlite and a docker container with MySQL and Adminer

Converting a Database From Mysql to Sqlite

Check how to convert a database from MySql to Sqlite using mysql2sqlite and a docker container with MySQL and Adminer