There are different ways to do this. I also had this problem and I searched a lot and then I got a simple way to convert MySQL to SQLite.
Follow these steps:
First You Need to Install SQLite DB Browser (very small and fast to view Tables and Data)
Open your MySQL File in Notepad or it would be great if you open in Notepad++
Remove First extra Lines Contains Information or Queries and Save it.
Open SQLite DB Browser, Create Database, then Tables, and Same Types as it is in MySQL Database.
In Menu Bar of SQLite DB Browser Select File-> then Import data MySQL File which you saved.
It will easily Convert into SQLite After Warning Dialog.
If error then remove more extra lines if your MySQL file have.
You can also Install MySQL to SQLite Converter Software on trial Basis, but the information I am giving for conversion is life time.
Sqlite has officially list of converting tools.
There is a mysql2sqlite.sh script on GitHub
As described in the header, the script can be used like this:
./mysql2sqlite.sh myDbase | sqlite3 database.sqlite
alternatives
Not every DB schema can be converted. MySQL is more complex and feature-rich than SQLite. However, if your schema is simple enough, you could dump it into an SQL file and try to import it / load it into an SQLite DB.
I like the SQLite2009 Pro Enterprise Manager suggested by Jfly. However:
The MySQL datatype INT is not converted to SQlite datatype INTEGER (works with DBeaver )
It does not import foreign key constaints from MySQL (I could not find any tool that supports the transfer of foreign key constraints from MySQL to SQlite.)
Simplest way to Convert MySql DB to Sqlite:
1) Generate sql dump file for you MySql database.
2) Upload the file to RebaseData online converter here
3) A download button will appear on page to download database in Sqlite format
If you have been given a database file and have not installed the correct server (either SQLite or MySQL), try this tool: https://dbconvert.com/sqlite/mysql/ The trial version allows converting the first 50 records of each table, the rest of the data is watermarked. This is a Windows program, and can either dump into a running database server, or can dump output to a .sql file
Sequel (Ruby ORM) has a command line tool for dealing with databases, you must have ruby installed, then:
$ gem install sequel mysql2 sqlite3
$ sequel mysql2://user:password@host/database -C sqlite://db.sqlite
If you have experience write simple scripts by Perl\Python\etc, and convert MySQL to SQLite. Read data from Mysql and write it on SQLite.
From list of converter tools I found Kexi. It is a UI tool to import from various DB servers (including MySQL) into SQLite. When importing some database (say from MySQL) it stores it in Kexi format. The Kexi format is the 'native' SQLite format. So simply copy the kexi file and have your data in sqlite format
I tried a number of methods on this thread, but nothing worked for me. So here is a new solution, which I also found to be very simple:
sqlite queries necessary to create this database. That's it, now you have your SQLite database.
My solution to this issue running a Mac was to
Install sequel
$ gem install sequel
If still required
% gem install mysql sqlite3
then used the following based of the Sequel doc bin_sequel.rdoc (see Copy Database)
sequel -C mysql://myUserName:myPassword@host/databaseName sqlite://myConvertedDatabaseName.sqlite
A windows user could install Ruby and Sequel for a windows solution.
I faced the same problem about 2 days ago when I had to convert a 20GB+ MySQL database to SQLite. It was by no means an easy task and I ended up writing this Python package that does the job.
The upside of it being written in Python is that it's cross platform (unlike a shell/bash script) and can all be easily installed using pip install (even on Windows). It uses generators and chunking of the data being processed and is therefore very memory efficient.
I also put in some effort to correctly translate most of the datatypes from MySQL to SQLite.
The tool is also thoroughly tested and works on Python 2.7 and 3.5+.
It is invokable via command line but can also be used as a standard Python class which you can include in some larger Python orchestration.
Here's how you use it:
Usage: mysql2sqlite [OPTIONS]
Options:
-f, --sqlite-file PATH SQLite3 database file [required]
-d, --mysql-database TEXT MySQL database name [required]
-u, --mysql-user TEXT MySQL user [required]
-p, --mysql-password TEXT MySQL password
-h, --mysql-host TEXT MySQL host. Defaults to localhost.
-P, --mysql-port INTEGER MySQL port. Defaults to 3306.
-c, --chunk INTEGER Chunk reading/writing SQL records
-l, --log-file PATH Log file
-V, --vacuum Use the VACUUM command to rebuild the SQLite
database file, repacking it into a minimal amount
of disk space
--use-buffered-cursors Use MySQLCursorBuffered for reading the MySQL
database. This can be useful in situations where
multiple queries, with small result sets, need to
be combined or computed with each other.
--help Show this message and exit.
Here is a list of converters. (snapshot at archive.today)
An alternative method that would work even on windows but is rarely mentioned is: use an ORM class that abstracts specific database differences away for you. e.g. you get these in PHP (RedBean), Python (Django's ORM layer, Storm, SqlAlchemy), Ruby on Rails (ActiveRecord), Cocoa (CoreData) etc.
i.e. you could do this:
I found the perfect solution
First, you need this script (put it into a file called 'mysql-to-sqlite.sh'):
#!/bin/bash
if [ "x$1" == "x" ]; then
echo "Usage: $0 <dumpname>"
exit
fi
cat $1 |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
sed 's/\\r\\n/\\n/g' |
sed 's/\\"/"/g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
if (/^(INSERT.+?)\(/) {
$a=$1;
s/\\'\''/'\'\''/g;
s/\\n/\n/g;
s/\),\(/\);\n$a\(/g;
}
' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
echo "Conversion completed without error. Output file: $1.db"
rm $1.sql
rm $1.err
else
echo "There were errors during conversion. Please review $1.err and $1.sql for details."
fi
Then, dump a copy of your database:
you@prompt:~$ mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile
And now, run the conversion:
you@prompt:~$ mysql-to-sqlite.sh dumpfile
And if all goes well, you should now have a dumpfile.db which can be used via sqlite3.
you@prompt:~$ sqlite3 dumpfile.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
dg_cities dg_forms dg_surnames
dg_counties dg_provinces dg_user_accounts
dg_countries dg_provinces_netherlands
dg_first_names dg_states
Source: Stackoverflow.com