Skip to content

Latest commit

 

History

History
169 lines (125 loc) · 4.85 KB

mysql_to_sqlite.md

File metadata and controls

169 lines (125 loc) · 4.85 KB

MySQL schema conversion to SQLite

This tutorial describes how to transfer a MySQL/MariaDB schema (both structure and data) to an SQLite database file.

Requirements

  • UNIX-like operating system
  • accessible MySQL/MariaDB server, with read permission on the schema to be converted (details at mysqldump page)
  • MySQL/MariaDB command line client
  • SQLite
  • one of the many implementations of AWK, e.g. GNU Awk

0. Preliminary operations

  • if not already available, create the MySQL/MariaDB data source:

    1. create the schema to convert:

      $ mysql -h[host] -u[username] -p
Enter password: [password]
Welcome to the MySQL [... and the rest of MySQL welcome message]
      mysql> CREATE SCHEMA my_database;
Query OK, 1 row affected (0.00 sec)

mysql> USE my_database;
Database changed

    2. together with a table:

      mysql> CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE,
    created TIMESTAMP DEFAULT NOW()
) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

    3. and populate it with a few records:

      mysql> INSERT INTO users (username) VALUES ('user_1');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO users (username) VALUES ('user_2');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO users (username) VALUES ('user_3');
Query OK, 1 row affected (0.05 sec)
      mysql> EXIT;

  • retrieve mysql2sqlite script:

    1. download mysql2sqlite script from its GitHub repository

    2. make the script executable:

      $ chmod +x mysql2sqlite.sh

1. Export MySQL data to MySQL dump file

Export data via MySQL/MariaDB client command line interface (or any MySQL/MariaDB client you like, of course):

$ mysqldump -h[host] -u[username] -p my_database > mysql_dump.sql --compact
Enter password: [password]

Output file mysql_dump.sql content will look like:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `users` VALUES (1,'user_1','2017-03-12 09:18:04'),
                           (2,'user_2','2017-03-12 09:18:06'),
                           (3,'user_3','2017-03-12 09:18:08');

2. Convert MySQL/MariaDB dump file to a SQLite dump file

$ ./mysql2sqlite.sh mysql_dump.sql > sqlite_dump.sql

Output file sqlite_dump.sql content will look like:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `users` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`username`)
);
INSERT INTO `users` VALUES (1,'user_1','2017-03-12 09:18:04'),
                           (2,'user_2','2017-03-12 09:18:04'),
                           (3,'user_3','2017-03-12 09:18:04');
END TRANSACTION;

Further details about how SQLite handles data types other than TEXT, INTEGER, REAL, NUMERIC and BLOB are available on SQLite documentation

3. Import SQLite dump file to SQLite database

Import data via SQLite command line interface:

$ sqlite3 my_database.sqlite
SQLite version 3.8.2 [... and the rest of SQLite welcome message]

sqlite> .read sqlite_dump.sql
memory

sqlite> .exit

4. Check data have been correctly imported to SQLite

$ sqlite3 my_database.sqlite
SQLite version 3.8.2 [... and the rest of SQLite welcome message]

sqlite> .tables
users

sqlite> .schema users
CREATE TABLE `users` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`username`)
);

sqlite> SELECT * FROM users;
1|user_1|2017-03-12 09:18:04
2|user_2|2017-03-12 09:18:04
3|user_3|2017-03-12 09:18:04

sqlite> .exit

References