mjog/sqlite3mysql repository overview

This application consumes a sqlite3 database and pumps the data directly into a MySQL database.

It is based on a Python script originally written by Klemen Tušar found via a StackOverflow link. This script was enhanced by James Green to cope with a real sqlite3 database with various column types and non-numeric primary keys.

There exists a Dockerfile which launches this Python script in a preconfigured environment. You can then launch the container just like a shell script.

By default, the container image simply invokes the script which shows the help documentation.

⁠Use

You will need (assuming Docker to be used):

1. A folder holding your SQLite database
2. A Docker user defined network
3. A MySQL host to target
4. Invocation of the Docker container hosting the script

If you are not using Docker, you will need Python:3.5, and MySQL. The remaining instructions assume a Docker based environment.

You should prepare a folder that contains your sqlite database file. Note that this is NOT a .dump file but the original data file. This folder should be bind-mounted to /data so that the container can copy the file. It is copied because of problems opening the file when the container runs on a Windows host.

Let us prepare a small user defined network for our Docker containers to operate within. This works just fine on your desktop!

$ docker network create --subnet=172.20.0.0/24 sqlite2mysql

You should prepare a MySQL server. This may be another Docker container - if so you may wish to connect the two via a user-defined network. Either way you need to specify how to connect to the MySQL database you will pump the data in to.

$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=mydb --net=sqlite2mysql mysql

Now that we have a network and a MySQL target, we can proceed.

Sample invocation:

$ docker run -it --rm --net=sqlite2mysql -v $(pwd)/sqlite-folder:/data mjog/sqlite3mysql /usr/bin/sqlite3mysql.py --sqlite-file /data/sqlite.db --mysql-user=root --mysql-password=my-secret-pw --mysql-database=mydb --mysql-host=mysql

In the above we have run the container just as if we were running a shell script. We attach the container to a pre-prepared user defined network named sqlite2mysql which has a MySQL container named mysql already attached.

We when bind-mount in our sqlite-folder that holds a sqlite.db file.

We tell the application to use root and my-secret-pw for the database named mydb.

The container launches the script sqlite3mysql.py passing in these arguments.

You should see some output including schemas which are there to help you should a failure occur in the mapping to MySQL column types.