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:
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.
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
Once done, we have the dump, so it's a good practice to clean the mess docker did, once we no longer use it:
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