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.
You will need (assuming Docker to be used):
- A folder holding your SQLite database
- A Docker user defined network
- A MySQL host to target
- 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.
$ 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
We tell the application to use
my-secret-pw for the database named
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.